MIME-Version: 1.0
From: Fredrik Widlert <fredrik.widlert@digpro.se>
Date: Thu, 9 Apr 2026 17:27:19 +0200
Message-ID: <CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk+pwzKZFaQ@mail.gmail.com>
Subject: BUG: PostgreSQL 19devel throws internal opfamily error for FK with
 reordered referenced columns
To: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a1e75d064f08a73f"
Archived-At: <https://www.postgresql.org/message-id/CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk%2BpwzKZFaQ%40mail.gmail.com>
Precedence: bulk

--000000000000a1e75d064f08a73f
Content-Type: text/plain; charset="UTF-8"

Hello,

I believe I may have found a regression in PostgreSQL 19devel, downloaded
on 2026-04-09
from https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz.

postgres=# select version();
                                               version
-----------------------------------------------------------------------------------------------------
 PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.2.0-23ubuntu4) 13.2.0, 64-bit


With the reproducer below, PostgreSQL 18 reports a normal foreign-key
violation
at INSERT time, but PostgreSQL 19devel instead throws an internal-looking
error:

ERROR:  operator 98 is not a member of opfamily 1976


-- reproducer:
drop table if exists parent, child;

create table parent (
    app_id      varchar(256) not null,
    report_id   smallint     not null,
    otype       integer      not null,
    subtype     integer      not null,
    ctype       integer      not null,
    column_name varchar(30)  not null,
    primary key (app_id, report_id, otype, subtype, ctype, column_name)
);

create table child (
    app_id      varchar(256) not null,
    report_id   smallint     not null,
    otype       integer      not null,
    subtype     integer      not null,
    column_name varchar(30)  not null,
    ctype       integer,
    -- intentionally swapped: column_name, ctype
    constraint child_fk
      foreign key (app_id, report_id, otype, subtype, column_name, ctype)
      references parent (app_id, report_id, otype, subtype, column_name,
ctype)
);


-- trigger the problem
insert into child (app_id, report_id, otype, subtype, column_name, ctype)
values ('DEFAULT_APP', 0, -1, -1, 'ID', -1);


/Fredrik Widlert
fredrik.widlert@digpro.se

--000000000000a1e75d064f08a73f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<br><br>I believe I may have found a regression in P=
ostgreSQL 19devel, downloaded on 2026-04-09<br>from <a href=3D"https://ftp.=
postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz">https://ftp.pos=
tgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.gz</a>.<br><br>postgre=
s=3D# select version();<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0version<br>-------------------=
---------------------------------------------------------------------------=
-------<br>=C2=A0PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc=
 (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit<br><br><br>With the reproducer be=
low, PostgreSQL 18 reports a normal foreign-key violation<br>at INSERT time=
, but PostgreSQL 19devel instead throws an internal-looking error:<br><br>E=
RROR: =C2=A0operator 98 is not a member of opfamily 1976<br><br><br><br>-- =
reproducer:<br>drop table if exists parent, child;<br><br>create table pare=
nt (<br>=C2=A0 =C2=A0 app_id =C2=A0 =C2=A0 =C2=A0varchar(256) not null,<br>=
=C2=A0 =C2=A0 report_id =C2=A0 smallint =C2=A0 =C2=A0 not null,<br>=C2=A0 =
=C2=A0 otype =C2=A0 =C2=A0 =C2=A0 integer =C2=A0 =C2=A0 =C2=A0not null,<br>=
=C2=A0 =C2=A0 subtype =C2=A0 =C2=A0 integer =C2=A0 =C2=A0 =C2=A0not null,<b=
r>=C2=A0 =C2=A0 ctype =C2=A0 =C2=A0 =C2=A0 integer =C2=A0 =C2=A0 =C2=A0not =
null,<br>=C2=A0 =C2=A0 column_name varchar(30) =C2=A0not null,<br>=C2=A0 =
=C2=A0 primary key (app_id, report_id, otype, subtype, ctype, column_name)<=
br>);<br><br>create table child (<br>=C2=A0 =C2=A0 app_id =C2=A0 =C2=A0 =C2=
=A0varchar(256) not null,<br>=C2=A0 =C2=A0 report_id =C2=A0 smallint =C2=A0=
 =C2=A0 not null,<br>=C2=A0 =C2=A0 otype =C2=A0 =C2=A0 =C2=A0 integer =C2=
=A0 =C2=A0 =C2=A0not null,<br>=C2=A0 =C2=A0 subtype =C2=A0 =C2=A0 integer =
=C2=A0 =C2=A0 =C2=A0not null,<br>=C2=A0 =C2=A0 column_name varchar(30) =C2=
=A0not null,<br>=C2=A0 =C2=A0 ctype =C2=A0 =C2=A0 =C2=A0 integer,<br>=C2=A0=
 =C2=A0 -- intentionally swapped: column_name, ctype<br>=C2=A0 =C2=A0 const=
raint child_fk<br>=C2=A0 =C2=A0 =C2=A0 foreign key (app_id, report_id, otyp=
e, subtype, column_name, ctype)<br>=C2=A0 =C2=A0 =C2=A0 references parent (=
app_id, report_id, otype, subtype, column_name, ctype)<br>);<br><br><br>-- =
trigger the problem<br>insert into child (app_id, report_id, otype, subtype=
, column_name, ctype)<br>values (&#39;DEFAULT_APP&#39;, 0, -1, -1, &#39;ID&=
#39;, -1);<br><br><div><br></div><div>/Fredrik Widlert</div><div><a href=3D=
"mailto:fredrik.widlert@digpro.se">fredrik.widlert@digpro.se</a></div></div=
>

--000000000000a1e75d064f08a73f--