BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns

On Thu Apr 9, 2026 at 12:27 PM -03, Fredrik Widlert wrote:

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);

Hi, thanks for reporting the issue.

This seems to be related to commit 2da86c1ef9b. The issue is that in
ri_populate_fastpath_metadata, the code uses idx_rel->rd_opfamily[i]
where i is the constraint key position, but it should find the actual
index column position for pk_attnums[i]. When FK columns are in a
different order than PK columns, the constraint key position doesn't
match the index column position.

I didn't participate in the discussion of the feature but I studied the
code a little bit after it was committed, so I'm taking a try to fix
this issue with the attached patch, which seems to work for this case.

CC Junwang Zhao and Amit Langote since they are involved in the original
commit.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

On Fri, Apr 10, 2026 at 3:13 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

On Thu Apr 9, 2026 at 12:27 PM -03, Fredrik Widlert wrote:

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);

Hi, thanks for reporting the issue.

This seems to be related to commit 2da86c1ef9b. The issue is that in
ri_populate_fastpath_metadata, the code uses idx_rel->rd_opfamily[i]
where i is the constraint key position, but it should find the actual
index column position for pk_attnums[i]. When FK columns are in a
different order than PK columns, the constraint key position doesn't
match the index column position.

I didn't participate in the discussion of the feature but I studied the
code a little bit after it was committed, so I'm taking a try to fix
this issue with the attached patch, which seems to work for this case.

CC Junwang Zhao and Amit Langote since they are involved in the original
commit.

Thanks for the patch and for notifying me.

I will look at your patch later today, but in the meantime, I've added
an open item for this:
https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items

--
Thanks, Amit Langote

Hi Matheus,

On Fri, Apr 10, 2026 at 2:13 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

On Thu Apr 9, 2026 at 12:27 PM -03, Fredrik Widlert wrote:

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);

Hi, thanks for reporting the issue.

This seems to be related to commit 2da86c1ef9b. The issue is that in
ri_populate_fastpath_metadata, the code uses idx_rel->rd_opfamily[i]
where i is the constraint key position, but it should find the actual
index column position for pk_attnums[i]. When FK columns are in a
different order than PK columns, the constraint key position doesn't
match the index column position.

Yeah, I can reproduce the issue with a simplified version, that the FK/PK
columns are in different order:

drop table if exists parent, child;

create table parent (
app_id varchar(256) not null,
report_id smallint not null,
primary key (app_id, report_id)
);

create table child (
app_id varchar(256) not null,
report_id smallint not null,
constraint child_fk
foreign key (report_id, app_id)
references parent (report_id, app_id)
);

insert into child (app_id, report_id) values ('DEFAULT_APP', 0);

I didn't participate in the discussion of the feature but I studied the
code a little bit after it was committed, so I'm taking a try to fix
this issue with the attached patch, which seems to work for this case.

It appears we overlooked this specific case while working on the
feature. Your analysis looks correct to me, and I've tested the patch
it behaves as expected. The test case you added should effectively
guard against this bug.

CC Junwang Zhao and Amit Langote since they are involved in the original
commit.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

--
Regards
Junwang Zhao

On Fri, Apr 10, 2026 at 12:19 PM Junwang Zhao <zhjwpku@gmail.com> wrote:

On Fri, Apr 10, 2026 at 2:13 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:

On Thu Apr 9, 2026 at 12:27 PM -03, Fredrik Widlert wrote:

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);

Hi, thanks for reporting the issue.

This seems to be related to commit 2da86c1ef9b. The issue is that in
ri_populate_fastpath_metadata, the code uses idx_rel->rd_opfamily[i]
where i is the constraint key position, but it should find the actual
index column position for pk_attnums[i]. When FK columns are in a
different order than PK columns, the constraint key position doesn't
match the index column position.

Yeah, I can reproduce the issue with a simplified version, that the FK/PK
columns are in different order:

drop table if exists parent, child;

create table parent (
app_id varchar(256) not null,
report_id smallint not null,
primary key (app_id, report_id)
);

create table child (
app_id varchar(256) not null,
report_id smallint not null,
constraint child_fk
foreign key (report_id, app_id)
references parent (report_id, app_id)
);

insert into child (app_id, report_id) values ('DEFAULT_APP', 0);

I didn't participate in the discussion of the feature but I studied the
code a little bit after it was committed, so I'm taking a try to fix
this issue with the attached patch, which seems to work for this case.

It appears we overlooked this specific case while working on the
feature. Your analysis looks correct to me, and I've tested the patch
it behaves as expected. The test case you added should effectively
guard against this bug.

Thanks Junwang for checking. I've just pushed this: 980c1a85d819.

Thanks Matheus again for the quick patch and Fredrik for the report.

--
Thanks, Amit Langote

On Fri, Apr 10, 2026 at 1:42 PM Amit Langote <amitlangote09@gmail.com> wrote:

Thanks Junwang for checking. I've just pushed this: 980c1a85d819.

Pushed a follow up commit to add an Assert making the single-column
invariant explicit in ri_FastPathFlushArray(), per Junwang's
suggestion off-list.

--
Thanks, Amit Langote

Thanks for the very quick fix - it's a great feeling to report a problem
and get it fixed this quickly.

I can confirm that the fix solves not only the small reproducer but also
the original problems I got when trying to install our (large) system
on PostgreSQL 19 devel for testing.

We actually ran into the same problem in two ways:
* the reordered FK with datatype mismatch (my reproducer)
* other reordered FKs with the same datatypes, where we instead got
regular FK violations

In case you want a reproducer for the second case as well, I've included it
below,
but perhaps it's not relevant since the patch already fixed it.

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

create table parent (
c1 integer,
c2 integer,
primary key (c1, c2)
);

create table child (
c1 integer,
c2 integer,
constraint child_fk foreign key (c2, c1) references parent (c2, c1)
);

insert into parent (c1, c2) values (1, 2);
insert into child (c1, c2) values (1, 2);

/Fredrik Widlert

On Fri, Apr 10, 2026 at 8:32 AM Amit Langote <amitlangote09@gmail.com>
wrote:

On Fri, Apr 10, 2026 at 4:29 PM Fredrik Widlert
<fredrik.widlert@digpro.se> wrote:

Thanks for the very quick fix - it's a great feeling to report a problem
and get it fixed this quickly.

Credit goes to Matheus for quickly submitting a well-written patch.

I can confirm that the fix solves not only the small reproducer but also
the original problems I got when trying to install our (large) system
on PostgreSQL 19 devel for testing.

Thanks for confirming that.

We actually ran into the same problem in two ways:
* the reordered FK with datatype mismatch (my reproducer)
* other reordered FKs with the same datatypes, where we instead got
regular FK violations

In case you want a reproducer for the second case as well, I've included it below,
but perhaps it's not relevant since the patch already fixed it.

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

create table parent (
c1 integer,
c2 integer,
primary key (c1, c2)
);

create table child (
c1 integer,
c2 integer,
constraint child_fk foreign key (c2, c1) references parent (c2, c1)
);

insert into parent (c1, c2) values (1, 2);
insert into child (c1, c2) values (1, 2);

Good to know. It's worth adding this case next to the ones Matheus's
patch added, which the attached patch does. Will push it shortly.

--
Thanks, Amit Langote

On Fri, Apr 10, 2026 at 5:05 PM Amit Langote <amitlangote09@gmail.com> wrote:

On Fri, Apr 10, 2026 at 4:29 PM Fredrik Widlert
<fredrik.widlert@digpro.se> wrote:

Thanks for the very quick fix - it's a great feeling to report a problem
and get it fixed this quickly.

Credit goes to Matheus for quickly submitting a well-written patch.

I can confirm that the fix solves not only the small reproducer but also
the original problems I got when trying to install our (large) system
on PostgreSQL 19 devel for testing.

Thanks for confirming that.

We actually ran into the same problem in two ways:
* the reordered FK with datatype mismatch (my reproducer)
* other reordered FKs with the same datatypes, where we instead got
regular FK violations

In case you want a reproducer for the second case as well, I've included it below,
but perhaps it's not relevant since the patch already fixed it.

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

create table parent (
c1 integer,
c2 integer,
primary key (c1, c2)
);

create table child (
c1 integer,
c2 integer,
constraint child_fk foreign key (c2, c1) references parent (c2, c1)
);

insert into parent (c1, c2) values (1, 2);
insert into child (c1, c2) values (1, 2);

Good to know. It's worth adding this case next to the ones Matheus's
patch added, which the attached patch does. Will push it shortly.

And done.

--
Thanks, Amit Langote

On 10/04/26 01:42, Amit Langote wrote:

Thanks Junwang for checking. I've just pushed this: 980c1a85d819.

Thanks Matheus again for the quick patch and Fredrik for the report.

Thank you for everyone.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com