Thread
Commits
-
Add test case for same-type reordered FK columns
- 009ea1b08d7b 19 (unreleased) landed
-
Assert index_attnos[0] == 1 in ri_FastPathFlushArray()
- 155c03ee9d44 19 (unreleased) landed
-
Fix FK fast-path scan key ordering for mismatched column order
- 980c1a85d819 19 (unreleased) landed
-
Add fast path for foreign key constraint checks
- 2da86c1ef9b5 19 (unreleased) cited
-
BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Fredrik Widlert <fredrik.widlert@digpro.se> — 2026-04-09T15:27:19Z
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 -
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Matheus Alcantara <matheusssilv97@gmail.com> — 2026-04-09T18:13:07Z
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 -
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Amit Langote <amitlangote09@gmail.com> — 2026-04-09T23:39:24Z
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 -
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Junwang Zhao <zhjwpku@gmail.com> — 2026-04-10T03:19:02Z
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 -
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Amit Langote <amitlangote09@gmail.com> — 2026-04-10T04:42:01Z
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 -
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Amit Langote <amitlangote09@gmail.com> — 2026-04-10T06:32:31Z
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
-
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Fredrik Widlert <fredrik.widlert@digpro.se> — 2026-04-10T07:29:44Z
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 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 > -
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Amit Langote <amitlangote09@gmail.com> — 2026-04-10T08:05:33Z
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
-
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Amit Langote <amitlangote09@gmail.com> — 2026-04-10T09:13:33Z
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
-
Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Matheus Alcantara <matheusssilv97@gmail.com> — 2026-04-10T12:42:56Z
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