v1-0001-Ensure-PK-side-action-triggers-for-partitions-aft.patch

From f757e49a93aa5a43b673b973f900bdbf91bef8e4 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Wed, 9 Jan 2019 10:00:11 +0900
Subject: [PATCH v1 1/2] Ensure PK-side action triggers for partitions after
 being detached

Detaching a partition from the parent whose foreign key(s) would've
been duplicated in the partition makes the foreign key(s) stop
working corretly.  That's because PK-side action trigger for the
foreign key would refer to the parent as the referencing relation
and after the partition is detached it's data is no longer accessible
via parent.  So while the check triggers that remain even after
being detached takes care of the one side of enforcing the foreign
key of the detached partition, lack of corresponding PK-side action
trigger to check detached partition's data means the other side
doesn't work.

To fix, add the action triggers on the PK relation that points to
the detached partition when detaching.
---
 src/backend/catalog/pg_constraint.c |  3 +-
 src/backend/commands/tablecmds.c    | 57 +++++++++++++++++++++++++++++++++++--
 src/include/commands/tablecmds.h    |  3 +-
 3 files changed, 58 insertions(+), 5 deletions(-)

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 3c960c9423..c88e923327 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -714,7 +714,8 @@ clone_fk_constraints(Relation pg_constraint, Relation parentRel,
 		fkconstraint->initdeferred = constrForm->condeferred;
 
 		createForeignKeyTriggers(partRel, constrForm->confrelid, fkconstraint,
-								 constrOid, constrForm->conindid, false);
+								 constrOid, constrForm->conindid, false,
+								 true);
 
 		if (cloned)
 		{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c75c5808ba..a3eb39ffdd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7623,7 +7623,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	 * though the constraints also exist below.
 	 */
 	createForeignKeyTriggers(rel, RelationGetRelid(pkrel), fkconstraint,
-							 constrOid, indexOid, !recursing);
+							 constrOid, indexOid, !recursing, true);
 
 	/*
 	 * Tell Phase 3 to check that the constraint is satisfied by existing
@@ -8728,7 +8728,8 @@ createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid,
  */
 void
 createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
-						 Oid constraintOid, Oid indexOid, bool create_action)
+						 Oid constraintOid, Oid indexOid, bool create_action,
+						 bool create_check)
 {
 	/*
 	 * For the referenced side, create action triggers, if requested.  (If the
@@ -8744,7 +8745,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
 	 * For the referencing side, create the check triggers.  We only need
 	 * these on the partitions.
 	 */
-	if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+	if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE && create_check)
 		createForeignKeyCheckTriggers(RelationGetRelid(rel), refRelOid,
 									  fkconstraint, constraintOid, indexOid);
 
@@ -14675,13 +14676,63 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
 	{
 		ForeignKeyCacheInfo *fk = lfirst(cell);
 		HeapTuple	contup;
+		Form_pg_constraint conform;
+		Constraint *fkconstraint;
+		Relation	trigrel;
+		HeapTuple	trigtup;
+		HeapScanDesc scan;
+		ScanKeyData key[3];
 
 		contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
 		if (!contup)
 			elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
+		conform = (Form_pg_constraint) GETSTRUCT(contup);
 
 		ConstraintSetParentConstraint(fk->conoid, InvalidOid);
 
+		/*
+		 * We'll need to make the triggers on primary key relation to point
+		 * to this relation as the FK relation.  Currently, it points to
+		 * the parent from which this relation is being detached.
+		 */
+		fkconstraint = makeNode(Constraint);
+		fkconstraint->conname = pstrdup(NameStr(conform->conname));
+		fkconstraint->fk_upd_action = conform->confupdtype;
+		fkconstraint->fk_del_action = conform->confdeltype;
+		fkconstraint->deferrable = conform->condeferrable;
+		fkconstraint->initdeferred = conform->condeferred;
+
+		/* First check if such a trigger doesn't already exist. */
+		trigrel = heap_open(TriggerRelationId, AccessShareLock);
+		ScanKeyInit(&key[0],
+					Anum_pg_trigger_tgrelid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(conform->confrelid));
+		ScanKeyInit(&key[1],
+					Anum_pg_trigger_tgconstrrelid,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(RelationGetRelid(partRel)));
+		ScanKeyInit(&key[2],
+					Anum_pg_trigger_tgconstraint,
+					BTEqualStrategyNumber, F_OIDEQ,
+					ObjectIdGetDatum(fk->conoid));
+		scan = heap_beginscan_catalog(trigrel, 3, key);
+		trigtup = heap_getnext(scan, ForwardScanDirection);
+		if (trigtup == NULL)
+		{
+			/*
+			 * Nope, we didn't find such a trigger, so create one.
+			 *
+			 * As we already got the check triggers, no need to recreate them,
+			 * so pass false for create_check.
+			 */
+			createForeignKeyTriggers(partRel, conform->confrelid, fkconstraint,
+									 fk->conoid, conform->conindid,
+									 true, false);
+		}
+		heap_endscan(scan);
+		heap_close(trigrel, AccessShareLock);
+
 		ReleaseSysCache(contup);
 	}
 	list_free_deep(fks);
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index ec3bb90b01..6bebc68425 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -78,7 +78,8 @@ extern void check_of_type(HeapTuple typetuple);
 
 extern void createForeignKeyTriggers(Relation rel, Oid refRelOid,
 						 Constraint *fkconstraint, Oid constraintOid,
-						 Oid indexOid, bool create_action);
+						 Oid indexOid, bool create_action,
+						 bool create_check);
 
 extern void register_on_commit_action(Oid relid, OnCommitAction action);
 extern void remove_on_commit_action(Oid relid);
-- 
2.11.0