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
>From 567f0b172564feeb055226b38b81da37e2d0b2bf Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@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
