Обсуждение: 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

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