Re: 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