Date: Tue, 5 Nov 2019 18:29:16 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: deferrable FK constraints on partitioned rels
Message-ID: <20191105212915.GA11324@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="fdj2RfSjLxBAspz7"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20191105191948.GA11182@alvherre.pgsql>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--fdj2RfSjLxBAspz7
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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

--fdj2RfSjLxBAspz7
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0001-Fix-deferred-constraints-on-partitioned-rels.patch"

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


--fdj2RfSjLxBAspz7--