MIME-Version: 1.0
References: <CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk+pwzKZFaQ@mail.gmail.com>
 <CAFY6G8cgNNw86oPAE6MUWm=sPd7T0xr+5qBJWEtvre2n-wUetg@mail.gmail.com> <CAEG8a3JR8quniKWNu4jTJJM_1fNiCPnsEQPCDO+NFtKup7MTbQ@mail.gmail.com>
In-Reply-To: <CAEG8a3JR8quniKWNu4jTJJM_1fNiCPnsEQPCDO+NFtKup7MTbQ@mail.gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Fri, 10 Apr 2026 13:42:01 +0900
Message-ID: <CA+HiwqF+pBaQ5tnH7Mu=7rsNUsq1LSD9WvzCeQJz=2qD5unB0A@mail.gmail.com>
Subject: Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK
 with reordered referenced columns
To: Junwang Zhao <zhjwpku@gmail.com>
Cc: Matheus Alcantara <matheusssilv97@gmail.com>, Fredrik Widlert <fredrik.widlert@digpro.se>, 
	pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BHiwqF%2BpBaQ5tnH7Mu%3D7rsNUsq1LSD9WvzCeQJz%3D2qD5unB0A%40mail.gmail.com>
Precedence: bulk

On Fri, Apr 10, 2026 at 12:19=E2=80=AFPM Junwang Zhao <zhjwpku@gmail.com> w=
rote:
> On Fri, Apr 10, 2026 at 2:13=E2=80=AFAM 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, downlo=
aded
> > > on 2026-04-09
> > > from https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.=
tar.gz.
> > >
> > > postgres=3D# 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-loo=
king
> > > 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_nam=
e)
> > > );
> > >
> > > 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, ct=
ype)
> > >       references parent (app_id, report_id, otype, subtype, column_na=
me,
> > > ctype)
> > > );
> > >
> > >
> > > -- trigger the problem
> > > insert into child (app_id, report_id, otype, subtype, column_name, ct=
ype)
> > > 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.

--=20
Thanks, Amit Langote