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

Hi Matheus,

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, download=
ed
> > on 2026-04-09
> > from https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.ta=
r.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-looki=
ng
> > 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, ctyp=
e)
> >       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, ctyp=
e)
> > 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


--=20
Regards
Junwang Zhao