public inbox for [email protected]
help / color / mirror / Atom feedRe: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
2+ messages / 2 participants
[nested] [flat]
* Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
@ 2026-04-09 18:13 Matheus Alcantara <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Matheus Alcantara @ 2026-04-09 18:13 UTC (permalink / raw)
To: Fredrik Widlert <[email protected]>; [email protected]; +Cc: Junwang Zhao <[email protected]>; Amit Langote <[email protected]>
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
Attachments:
[application/octet-stream] v1-0001-Fix-FK-fast-path-scan-key-ordering-for-mismatched.patch (8.5K, 2-v1-0001-Fix-FK-fast-path-scan-key-ordering-for-mismatched.patch)
download | inline diff:
From 6edfabbb714267104c24ad73d2ed699e4740d909 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Thu, 9 Apr 2026 14:39:01 -0300
Subject: [PATCH v1] Fix FK fast-path scan key ordering for mismatched column
order
The fast-path foreign key check introduced in 2da86c1ef9b assumed that
constraint key positions directly correspond to index column positions.
This is not always true as a FK constraint can reference PK columns in a
different order than they appear in the PK's unique index.
For example, if the PK is (a, b, c) and the FK references them as
(a, c, b), the constraint stores keys in the FK-specified order, but
the index has columns in PK order. The buggy code used the constraint
key index to access rd_opfamily[i], which retrieved the wrong operator
family when columns were reordered, causing "operator X is not a member
of opfamily Y" errors.
After fixing the opfamily lookup, a second issue started to happen:
btree index scans require scan keys to be ordered by attribute number.
The code was placing scan keys at array position i with attribute number
idx_attno, producing out-of-order keys when columns were swapped. This
caused "btree index keys must be ordered by attribute" errors.
The fix adds an index_attnos array to FastPathMeta that maps each
constraint key position to its corresponding index column position.
In ri_populate_fastpath_metadata(), we search indkey to find the actual
index column for each pk_attnums[i] and use that position for the
opfamily lookup. In build_index_scankeys(), we place each scan key at
the array position corresponding to its index column (skeys[idx_attno-1])
rather than at the constraint key position, ensuring scan keys are
properly ordered by attribute number as btree requires.
Reported-by: Fredrik Widlert <[email protected]>
Discussion: https://www.postgresql.org/message-id/CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk%2BpwzKZFaQ%40mail.gmail.com
---
src/backend/utils/adt/ri_triggers.c | 40 +++++++++++++++++++----
src/test/regress/expected/foreign_key.out | 19 +++++++++++
src/test/regress/sql/foreign_key.sql | 18 ++++++++++
3 files changed, 70 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 18ec858357d..09a5ab24e56 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -156,6 +156,7 @@ typedef struct FastPathMeta
RegProcedure regops[RI_MAX_NUMKEYS];
Oid subtypes[RI_MAX_NUMKEYS];
int strats[RI_MAX_NUMKEYS];
+ AttrNumber index_attnos[RI_MAX_NUMKEYS]; /* index column positions */
} FastPathMeta;
/*
@@ -3095,14 +3096,17 @@ ri_FastPathFlushArray(RI_FastPathEntry *fpentry, TupleTableSlot *fk_slot,
* sort and deduplicate, then walk leaf pages in order.
*
* PK indexes are always btree, which supports SK_SEARCHARRAY.
+ *
+ * Reference index_attnos[0] for attribute number and collation since this
+ * is a single-column fast path.
*/
Assert(idx_rel->rd_indam->amsearcharray);
ScanKeyEntryInitialize(&skey[0],
SK_SEARCHARRAY,
- 1, /* attno */
+ fpmeta->index_attnos[0],
fpmeta->strats[0],
fpmeta->subtypes[0],
- idx_rel->rd_indcollation[0],
+ idx_rel->rd_indcollation[fpmeta->index_attnos[0] - 1],
fpmeta->regops[0],
PointerGetDatum(arr));
@@ -3414,15 +3418,20 @@ build_index_scankeys(const RI_ConstraintInfo *riinfo,
/*
* Set up ScanKeys for the index scan. This is essentially how
- * ExecIndexBuildScanKeys() sets them up.
+ * ExecIndexBuildScanKeys() sets them up. Use the cached index_attnos and
+ * the corresponding collation since FK columns may be in a different
+ * order than PK index columns. Place each scan key at the array position
+ * corresponding to its index column, since btree requires keys to be
+ * ordered by attribute number.
*/
for (int i = 0; i < riinfo->nkeys; i++)
{
- int pkattrno = i + 1;
+ AttrNumber pkattrno = fpmeta->index_attnos[i];
+ int skey_pos = pkattrno - 1; /* 0-based array position */
- ScanKeyEntryInitialize(&skeys[i], 0, pkattrno,
+ ScanKeyEntryInitialize(&skeys[skey_pos], 0, pkattrno,
fpmeta->strats[i], fpmeta->subtypes[i],
- idx_rel->rd_indcollation[i], fpmeta->regops[i],
+ idx_rel->rd_indcollation[skey_pos], fpmeta->regops[i],
pk_vals[i]);
}
}
@@ -3451,6 +3460,23 @@ ri_populate_fastpath_metadata(RI_ConstraintInfo *riinfo,
Oid typeid = RIAttType(fk_rel, riinfo->fk_attnums[i]);
Oid lefttype;
RI_CompareHashEntry *entry = ri_HashCompareOp(eq_opr, typeid);
+ int idx_col;
+
+ /*
+ * Find the index column position for this constraint key. The FK
+ * constraint may reference columns in a different order than they
+ * appear in the PK index, so we must map pk_attnums[i] to the
+ * corresponding index column position.
+ */
+ for (idx_col = 0; idx_col < riinfo->nkeys; idx_col++)
+ {
+ if (idx_rel->rd_index->indkey.values[idx_col] == riinfo->pk_attnums[i])
+ break;
+ }
+ Assert(idx_col < riinfo->nkeys);
+
+ /* 1-based attribute number */
+ fpmeta->index_attnos[i] = idx_col + 1;
fmgr_info_copy(&fpmeta->cast_func_finfo[i], &entry->cast_func_finfo,
CurrentMemoryContext);
@@ -3459,7 +3485,7 @@ ri_populate_fastpath_metadata(RI_ConstraintInfo *riinfo,
fpmeta->regops[i] = get_opcode(eq_opr);
get_op_opfamily_properties(eq_opr,
- idx_rel->rd_opfamily[i],
+ idx_rel->rd_opfamily[idx_col],
false,
&fpmeta->strats[i],
&lefttype,
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 91295754bab..9fa2e22329a 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3653,6 +3653,25 @@ INSERT INTO fp_fk_multi VALUES (1, 999, 999);
ERROR: insert or update on table "fp_fk_multi" violates foreign key constraint "fp_fk_multi_a_b_fkey"
DETAIL: Key (a, b)=(999, 999) is not present in table "fp_pk_multi".
DROP TABLE fp_fk_multi, fp_pk_multi;
+-- Multi-column FK with columns in different order than PK index.
+-- The FK references columns in a different order than they appear in the
+-- PK's primary key, which requires mapping constraint key positions to
+-- index column positions when building scan keys.
+CREATE TABLE fp_pk_order (a int, b text, c int, PRIMARY KEY (a, b, c));
+INSERT INTO fp_pk_order VALUES (1, 'one', 10), (2, 'two', 20);
+CREATE TABLE fp_fk_order (
+ x int,
+ c int,
+ b text,
+ a int,
+ FOREIGN KEY (a, c, b) REFERENCES fp_pk_order (a, c, b) -- c and b swapped
+);
+INSERT INTO fp_fk_order VALUES (1, 10, 'one', 1); -- should succeed
+INSERT INTO fp_fk_order VALUES (2, 20, 'two', 2); -- should succeed
+INSERT INTO fp_fk_order VALUES (3, 99, 'none', 9); -- should fail
+ERROR: insert or update on table "fp_fk_order" violates foreign key constraint "fp_fk_order_a_c_b_fkey"
+DETAIL: Key (a, c, b)=(9, 99, none) is not present in table "fp_pk_order".
+DROP TABLE fp_fk_order, fp_pk_order;
-- Deferred constraint: batch flushed at COMMIT, not at statement end
CREATE TABLE fp_pk_commit (a int PRIMARY KEY);
CREATE TABLE fp_fk_commit (a int REFERENCES fp_pk_commit
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index f646dd10401..9afee64d1e0 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2625,6 +2625,24 @@ INSERT INTO fp_fk_multi SELECT i, i, i FROM generate_series(1, 100) i;
INSERT INTO fp_fk_multi VALUES (1, 999, 999);
DROP TABLE fp_fk_multi, fp_pk_multi;
+-- Multi-column FK with columns in different order than PK index.
+-- The FK references columns in a different order than they appear in the
+-- PK's primary key, which requires mapping constraint key positions to
+-- index column positions when building scan keys.
+CREATE TABLE fp_pk_order (a int, b text, c int, PRIMARY KEY (a, b, c));
+INSERT INTO fp_pk_order VALUES (1, 'one', 10), (2, 'two', 20);
+CREATE TABLE fp_fk_order (
+ x int,
+ c int,
+ b text,
+ a int,
+ FOREIGN KEY (a, c, b) REFERENCES fp_pk_order (a, c, b) -- c and b swapped
+);
+INSERT INTO fp_fk_order VALUES (1, 10, 'one', 1); -- should succeed
+INSERT INTO fp_fk_order VALUES (2, 20, 'two', 2); -- should succeed
+INSERT INTO fp_fk_order VALUES (3, 99, 'none', 9); -- should fail
+DROP TABLE fp_fk_order, fp_pk_order;
+
-- Deferred constraint: batch flushed at COMMIT, not at statement end
CREATE TABLE fp_pk_commit (a int PRIMARY KEY);
CREATE TABLE fp_fk_commit (a int REFERENCES fp_pk_commit
--
2.53.0
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
@ 2026-04-09 23:39 Amit Langote <[email protected]>
parent: Matheus Alcantara <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Amit Langote @ 2026-04-09 23:39 UTC (permalink / raw)
To: Matheus Alcantara <[email protected]>; +Cc: Fredrik Widlert <[email protected]>; [email protected]; Junwang Zhao <[email protected]>
On Fri, Apr 10, 2026 at 3: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.
>
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-09 23:39 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-09 18:13 Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Matheus Alcantara <[email protected]>
2026-04-09 23:39 ` Amit Langote <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox