agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedFrom: Amit Langote <amitlan@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix FK fast-path scan key ordering for mismatched column order
Date: Fri, 10 Apr 2026 04:34:46 +0000
Message-ID: <E1wB3Zt-000FIn-2G@gemulon.postgresql.org> (raw)
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 <fredrik.widlert@digpro.se>
Author: Matheus Alcantara <matheusssilv97@gmail.com>
Reviewed-by: Junwang Zhao <zhjwpku@gmail.com>
Discussion: https://www.postgresql.org/message-id/CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk%2BpwzKZFaQ%40mail.g...
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/980c1a85d819edc72c866001b9ca4fba454900fe
Modified Files
--------------
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(-)
Message-ID: <E1wB3Zt-000FIn-2G@gemulon.postgresql.org>
Permalink: ../E1wB3Zt-000FIn-2G@gemulon.postgresql.org/
Also on: postgresql.org/message-id/E1wB3Zt-000FIn-2G@gemulon.postgresql.org
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: pgsql-committers@postgresql.org
Cc: amitlan@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix FK fast-path scan key ordering for mismatched column order
In-Reply-To: <E1wB3Zt-000FIn-2G@gemulon.postgresql.org>
* 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