public inbox for [email protected]
help / color / mirror / Atom feedFrom: Junwang Zhao <[email protected]>
To: Matheus Alcantara <[email protected]>
Cc: Fredrik Widlert <[email protected]>
Cc: [email protected]
Cc: Amit Langote <[email protected]>
Subject: Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Date: Fri, 10 Apr 2026 11:19:02 +0800
Message-ID: <CAEG8a3JR8quniKWNu4jTJJM_1fNiCPnsEQPCDO+NFtKup7MTbQ@mail.gmail.com> (raw)
In-Reply-To: <CAFY6G8cgNNw86oPAE6MUWm=sPd7T0xr+5qBJWEtvre2n-wUetg@mail.gmail.com>
References: <CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk+pwzKZFaQ@mail.gmail.com>
<CAFY6G8cgNNw86oPAE6MUWm=sPd7T0xr+5qBJWEtvre2n-wUetg@mail.gmail.com>
Hi Matheus,
On Fri, Apr 10, 2026 at 2:13 AM Matheus Alcantara
<[email protected]> 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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
In-Reply-To: <CAEG8a3JR8quniKWNu4jTJJM_1fNiCPnsEQPCDO+NFtKup7MTbQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox