public inbox for [email protected]
help / color / mirror / Atom feedRe: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
3+ messages / 2 participants
[nested] [flat]
* Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
@ 2026-04-10 07:29 Fredrik Widlert <[email protected]>
2026-04-10 08:05 ` Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Amit Langote <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Fredrik Widlert @ 2026-04-10 07:29 UTC (permalink / raw)
To: Amit Langote <[email protected]>; +Cc: Junwang Zhao <[email protected]>; Matheus Alcantara <[email protected]>; [email protected]
Thanks for the very quick fix - it's a great feeling to report a problem
and get it fixed this quickly.
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.
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);
/Fredrik Widlert
On Fri, Apr 10, 2026 at 8:32 AM Amit Langote <[email protected]>
wrote:
> On Fri, Apr 10, 2026 at 1:42 PM Amit Langote <[email protected]>
> wrote:
> > Thanks Junwang for checking. I've just pushed this: 980c1a85d819.
>
> Pushed a follow up commit to add an Assert making the single-column
> invariant explicit in ri_FastPathFlushArray(), per Junwang's
> suggestion off-list.
>
> --
> Thanks, Amit Langote
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
2026-04-10 07:29 Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Fredrik Widlert <[email protected]>
@ 2026-04-10 08:05 ` Amit Langote <[email protected]>
2026-04-10 09:13 ` Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Amit Langote <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Amit Langote @ 2026-04-10 08:05 UTC (permalink / raw)
To: Fredrik Widlert <[email protected]>; +Cc: Junwang Zhao <[email protected]>; Matheus Alcantara <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns
2026-04-10 07:29 Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Fredrik Widlert <[email protected]>
2026-04-10 08:05 ` Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Amit Langote <[email protected]>
@ 2026-04-10 09:13 ` Amit Langote <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Amit Langote @ 2026-04-10 09:13 UTC (permalink / raw)
To: Fredrik Widlert <[email protected]>; +Cc: Junwang Zhao <[email protected]>; Matheus Alcantara <[email protected]>; [email protected]
On Fri, Apr 10, 2026 at 5:05 PM Amit Langote <[email protected]> wrote:
> 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.
And done.
--
Thanks, Amit Langote
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-10 09:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-10 07:29 Re: BUG: PostgreSQL 19devel throws internal opfamily error for FK with reordered referenced columns Fredrik Widlert <[email protected]>
2026-04-10 08:05 ` Amit Langote <[email protected]>
2026-04-10 09:13 ` 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