pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feeddeferrable FK constraints on partitioned rels
3+ messages / 1 participants
[nested] [flat]
* deferrable FK constraints on partitioned rels
@ 2019-11-05 19:19 Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-05 21:29 ` Re: deferrable FK constraints on partitioned rels Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 3+ messages in thread
From: Alvaro Herrera @ 2019-11-05 19:19 UTC (permalink / raw)
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
While messing around, I noticed that SET CONSTRAINTS ... DEFERRED
does not work with partitioned tables. I had some code to cover this
case, but it has a bug that prevents it from working at all: the sanity
check that verifies whether triggers exist fails.
The attached patch fixes this problem: it merely removes the sanity
check. With that, everything works.
(Another approach I tried was to split out constraints in partitioned
tables vs. constraints in regular ones. That's indeed workable, but it
requires us to do two additional syscache access per partition for
get_rel_relkind, which seems excessive.)
The UNIQUE DEFERRABLE case works after the patch. (I didn't try without
the patch.)
--
Álvaro Herrera Developer, https://www.PostgreSQL.org/
Attachments:
[text/x-diff] 0001-Fix-deferred-constraints-on-partitioned-rels.patch (2.5K, ../../20191105191948.GA11182@alvherre.pgsql/2-0001-Fix-deferred-constraints-on-partitioned-rels.patch)
download | inline diff:
From e08007504e1c0ec88e583b7cba6f6b08effe26d7 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 5 Nov 2019 12:52:27 -0300
Subject: [PATCH] Fix deferred constraints on partitioned rels
---
src/backend/commands/trigger.c | 29 ++++++++++++++++++++++-------
1 file changed, 22 insertions(+), 7 deletions(-)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 57c98912d5..5e302fee64 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -5362,6 +5362,7 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
Relation conrel;
Relation tgrel;
List *conoidlist = NIL;
+ List *pcl = NIL;
List *tgoidlist = NIL;
ListCell *lc;
@@ -5440,7 +5441,18 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
if (con->condeferrable)
- conoidlist = lappend_oid(conoidlist, con->oid);
+ {
+ /*
+ * If we found it and it's deferrable, save it to one
+ * of the lists depending on whether it belongs to a
+ * partitioned table.
+ */
+ if (get_rel_relkind(con->conrelid) ==
+ RELKIND_PARTITIONED_TABLE)
+ pcl = lappend_oid(pcl, con->oid);
+ else
+ conoidlist = lappend_oid(conoidlist, con->oid);
+ }
else if (stmt->deferred)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -5472,12 +5484,12 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
}
/*
- * Scan for any possible descendants of the constraints. We append
- * whatever we find to the same list that we're scanning; this has the
- * effect that we create new scans for those, too, so if there are
- * further descendents, we'll also catch them.
+ * Scan for any possible descendants of constraints in partitioned
+ * tables. If we find any that are on further partitioned tables,
+ * append them to the list we're scanning. This ensures we process
+ * all descendant relations.
*/
- foreach(lc, conoidlist)
+ foreach(lc, pcl)
{
Oid parent = lfirst_oid(lc);
ScanKeyData key;
@@ -5495,7 +5507,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
{
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
- conoidlist = lappend_oid(conoidlist, con->oid);
+ if (get_rel_relkind(con->conrelid) == RELKIND_PARTITIONED_TABLE)
+ pcl = lappend_oid(pcl, con->oid);
+ else
+ conoidlist = lappend_oid(conoidlist, con->oid);
}
systable_endscan(scan);
--
2.20.1
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: deferrable FK constraints on partitioned rels
2019-11-05 19:19 deferrable FK constraints on partitioned rels Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-11-05 21:29 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-07 17:29 ` Re: deferrable FK constraints on partitioned rels Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 3+ messages in thread
From: Alvaro Herrera @ 2019-11-05 21:29 UTC (permalink / raw)
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
On 2019-Nov-05, Alvaro Herrera wrote:
> While messing around, I noticed that SET CONSTRAINTS ... DEFERRED
> does not work with partitioned tables. I had some code to cover this
> case, but it has a bug that prevents it from working at all: the sanity
> check that verifies whether triggers exist fails.
>
> The attached patch fixes this problem: it merely removes the sanity
> check. With that, everything works.
>
> (Another approach I tried was to split out constraints in partitioned
> tables vs. constraints in regular ones. That's indeed workable, but it
> requires us to do two additional syscache access per partition for
> get_rel_relkind, which seems excessive.)
Uh, somehow I posted a previous version of the patch that implements my
rejected approach, instead of the final version I described. Here's the
real patch (which also includes tests).
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
[text/x-diff] 0001-Fix-deferred-constraints-on-partitioned-rels.patch (4.4K, ../../20191105212915.GA11324@alvherre.pgsql/2-0001-Fix-deferred-constraints-on-partitioned-rels.patch)
download | inline diff:
From 567f0b172564feeb055226b38b81da37e2d0b2bf Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 5 Nov 2019 12:52:27 -0300
Subject: [PATCH] Fix deferred constraints on partitioned rels
---
src/backend/commands/trigger.c | 10 --------
src/test/regress/expected/foreign_key.out | 31 +++++++++++++++++++++++
src/test/regress/sql/foreign_key.sql | 25 ++++++++++++++++++
3 files changed, 56 insertions(+), 10 deletions(-)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 57c98912d5..e2048c19f7 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -5512,13 +5512,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
foreach(lc, conoidlist)
{
Oid conoid = lfirst_oid(lc);
- bool found;
ScanKeyData skey;
SysScanDesc tgscan;
HeapTuple htup;
- found = false;
-
ScanKeyInit(&skey,
Anum_pg_trigger_tgconstraint,
BTEqualStrategyNumber, F_OIDEQ,
@@ -5539,16 +5536,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
*/
if (pg_trigger->tgdeferrable)
tgoidlist = lappend_oid(tgoidlist, pg_trigger->oid);
-
- found = true;
}
systable_endscan(tgscan);
-
- /* Safety check: a deferrable constraint should have triggers */
- if (!found)
- elog(ERROR, "no triggers found for constraint with OID %u",
- conoid);
}
table_close(tgrel, AccessShareLock);
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 894084f94f..2014d38a8b 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2267,6 +2267,37 @@ INSERT INTO fk4 VALUES (50);
ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
ERROR: insert or update on table "fk4" violates foreign key constraint "fk_a_fkey"
DETAIL: Key (a)=(50) is not present in table "pk".
+-- Verify constraint deferrability
+CREATE SCHEMA fkpart9;
+SET search_path TO fkpart9;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
+CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
+CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
+CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
+INSERT INTO fk VALUES (1); -- should fail
+ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
+DETAIL: Key (a)=(1) is not present in table "pk".
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+COMMIT; -- should fail
+ERROR: insert or update on table "fk" violates foreign key constraint "fk_a_fkey"
+DETAIL: Key (a)=(1) is not present in table "pk".
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+INSERT INTO pk VALUES (1);
+COMMIT; -- OK
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+DELETE FROM pk WHERE a = 1;
+DELETE FROM fk WHERE a = 1;
+COMMIT; -- OK
+DROP SCHEMA fkpart9 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table pk
+drop cascades to table fk
-- Verify ON UPDATE/DELETE behavior
CREATE SCHEMA fkpart6;
SET search_path TO fkpart6;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index b67bef01df..63db7f667a 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1595,6 +1595,31 @@ CREATE TABLE fk4 (LIKE fk);
INSERT INTO fk4 VALUES (50);
ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES IN (50);
+-- Verify constraint deferrability
+CREATE SCHEMA fkpart9;
+SET search_path TO fkpart9;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY LIST (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES IN (1, 2) PARTITION BY LIST (a);
+CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES IN (1);
+CREATE TABLE pk3 PARTITION OF pk FOR VALUES IN (3);
+CREATE TABLE fk (a int REFERENCES pk DEFERRABLE INITIALLY IMMEDIATE);
+INSERT INTO fk VALUES (1); -- should fail
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+COMMIT; -- should fail
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+INSERT INTO fk VALUES (1);
+INSERT INTO pk VALUES (1);
+COMMIT; -- OK
+BEGIN;
+SET CONSTRAINTS fk_a_fkey DEFERRED;
+DELETE FROM pk WHERE a = 1;
+DELETE FROM fk WHERE a = 1;
+COMMIT; -- OK
+DROP SCHEMA fkpart9 CASCADE;
+
-- Verify ON UPDATE/DELETE behavior
CREATE SCHEMA fkpart6;
SET search_path TO fkpart6;
--
2.20.1
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: deferrable FK constraints on partitioned rels
2019-11-05 19:19 deferrable FK constraints on partitioned rels Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-05 21:29 ` Re: deferrable FK constraints on partitioned rels Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-11-07 17:29 ` Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2019-11-07 17:29 UTC (permalink / raw)
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
On 2019-Nov-05, Alvaro Herrera wrote:
> Uh, somehow I posted a previous version of the patch that implements my
> rejected approach, instead of the final version I described. Here's the
> real patch (which also includes tests).
This was broken in pg11 also. Pushed to all branches.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-11-07 17:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-05 19:19 deferrable FK constraints on partitioned rels Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-05 21:29 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-11-07 17:29 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox