public inbox for [email protected]
help / color / mirror / Atom feedFrom: Amit Langote <[email protected]>
To: Fredrik Widlert <[email protected]>
Cc: Junwang Zhao <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: [email protected]
Subject: Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
Date: Fri, 10 Apr 2026 17:05:33 +0900
Message-ID: <CA+HiwqFhGaQWX+COwv0XM6Co9jFc9=auqDKQXnaST04XAYGrBw@mail.gmail.com> (raw)
In-Reply-To: <CADfhSr8hYc-4Cz7vfXH_oV-Jq81pyK9W4phLrOGspovsg2W7Kw@mail.gmail.com>
References: <CADfhSr-pCkbDxmiOVYSAGE5QGjsQ48KKH_W424SPk+pwzKZFaQ@mail.gmail.com>
<CAFY6G8cgNNw86oPAE6MUWm=sPd7T0xr+5qBJWEtvre2n-wUetg@mail.gmail.com>
<CAEG8a3JR8quniKWNu4jTJJM_1fNiCPnsEQPCDO+NFtKup7MTbQ@mail.gmail.com>
<CA+HiwqF+pBaQ5tnH7Mu=7rsNUsq1LSD9WvzCeQJz=2qD5unB0A@mail.gmail.com>
<CA+HiwqG_PXQqL9TvQFEdyJfB9Wq1-Wm3SFppWHhMg+z2gD_nag@mail.gmail.com>
<CADfhSr8hYc-4Cz7vfXH_oV-Jq81pyK9W4phLrOGspovsg2W7Kw@mail.gmail.com>
On Fri, Apr 10, 2026 at 4:29 PM Fredrik Widlert
<[email protected]> wrote:
> Thanks for the very quick fix - it's a great feeling to report a problem
> and get it fixed this quickly.
Credit goes to Matheus for quickly submitting a well-written patch.
> I can confirm that the fix solves not only the small reproducer but also
> the original problems I got when trying to install our (large) system
> on PostgreSQL 19 devel for testing.
Thanks for confirming that.
> We actually ran into the same problem in two ways:
> * the reordered FK with datatype mismatch (my reproducer)
> * other reordered FKs with the same datatypes, where we instead got
> regular FK violations
>
> In case you want a reproducer for the second case as well, I've included it below,
> but perhaps it's not relevant since the patch already fixed it.
>
> ----
> drop table if exists parent, child;
>
> create table parent (
> c1 integer,
> c2 integer,
> primary key (c1, c2)
> );
>
> create table child (
> c1 integer,
> c2 integer,
> constraint child_fk foreign key (c2, c1) references parent (c2, c1)
> );
>
> insert into parent (c1, c2) values (1, 2);
> insert into child (c1, c2) values (1, 2);
Good to know. It's worth adding this case next to the ones Matheus's
patch added, which the attached patch does. Will push it shortly.
--
Thanks, Amit Langote
Attachments:
[application/octet-stream] v1-0001-Add-test-case-for-same-type-reordered-FK-columns.patch (3.1K, 2-v1-0001-Add-test-case-for-same-type-reordered-FK-columns.patch)
download | inline diff:
From 01bacab54f5523911653aad76a78216878bd728b Mon Sep 17 00:00:00 2001
From: Amit Langote <[email protected]>
Date: Fri, 10 Apr 2026 17:01:29 +0900
Subject: [PATCH v1] Add test case for same-type reordered FK columns
The test added in 980c1a85d819 covered the case where reordered FK
columns have different types, which triggered an "operator not a member
of opfamily" error prior to that commit. Add a test for the same-type
case, where the wrong scan key ordering instead produced a spurious FK
violation without any internal error.
Reported-by: Fredrik Widlert <[email protected]>
Discussion: https://postgr.es/m/CADfhSr8hYc-4Cz7vfXH_oV-Jq81pyK9W4phLrOGspovsg2W7Kw@mail.gmail.com
---
src/test/regress/expected/foreign_key.out | 10 ++++++++++
src/test/regress/sql/foreign_key.sql | 9 +++++++++
2 files changed, 19 insertions(+)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 9fa2e22329a..8b3b268de0f 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3672,6 +3672,16 @@ 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;
+-- Same-type columns in different order:
+CREATE TABLE fp_pk_same (c1 int, c2 int, PRIMARY KEY (c1, c2));
+INSERT INTO fp_pk_same VALUES (1, 2);
+CREATE TABLE fp_fk_same (c1 int, c2 int,
+ FOREIGN KEY (c2, c1) REFERENCES fp_pk_same (c2, c1));
+INSERT INTO fp_fk_same VALUES (1, 2); -- should succeed
+INSERT INTO fp_fk_same VALUES (9, 9); -- should fail
+ERROR: insert or update on table "fp_fk_same" violates foreign key constraint "fp_fk_same_c2_c1_fkey"
+DETAIL: Key (c2, c1)=(9, 9) is not present in table "fp_pk_same".
+DROP TABLE fp_fk_same, fp_pk_same;
-- 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 9afee64d1e0..7eb86b188f0 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2643,6 +2643,15 @@ 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;
+-- Same-type columns in different order:
+CREATE TABLE fp_pk_same (c1 int, c2 int, PRIMARY KEY (c1, c2));
+INSERT INTO fp_pk_same VALUES (1, 2);
+CREATE TABLE fp_fk_same (c1 int, c2 int,
+ FOREIGN KEY (c2, c1) REFERENCES fp_pk_same (c2, c1));
+INSERT INTO fp_fk_same VALUES (1, 2); -- should succeed
+INSERT INTO fp_fk_same VALUES (9, 9); -- should fail
+DROP TABLE fp_fk_same, fp_pk_same;
+
-- 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.47.3
view thread (3+ messages) latest in thread
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: <CA+HiwqFhGaQWX+COwv0XM6Co9jFc9=auqDKQXnaST04XAYGrBw@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