From b813554dbdc5569ed9e22a2727707eff38e01964 Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Sat, 17 Aug 2002 12:15:49 +0000
Subject: [PATCH] Improve documentation of trigger firing queue handling,
 cleanup.

Neil Conway
---
 doc/src/sgml/ref/set_constraints.sgml     |  38 +++-
 src/backend/commands/trigger.c            | 263 +++++++++-------------
 src/backend/tcop/utility.c                |   7 +-
 src/test/regress/expected/foreign_key.out |  77 +++++++
 src/test/regress/sql/foreign_key.sql      |  91 ++++++++
 5 files changed, 307 insertions(+), 169 deletions(-)

diff --git a/doc/src/sgml/ref/set_constraints.sgml b/doc/src/sgml/ref/set_constraints.sgml
index ed15b09a24..32abdee89f 100644
--- a/doc/src/sgml/ref/set_constraints.sgml
+++ b/doc/src/sgml/ref/set_constraints.sgml
@@ -1,4 +1,4 @@
-<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.4 2002/06/03 01:10:38 momjian Exp $ -->
+<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.5 2002/08/17 12:15:48 momjian Exp $ -->
 <refentry id="SQL-SET-CONSTRAINTS">
  <refmeta>
   <refentrytitle id="SQL-SET-CONSTRAINTS-title">SET CONSTRAINTS</refentrytitle>
@@ -22,10 +22,32 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">constraint</replaceable>
 
   <para>
    <command>SET CONSTRAINTS</command> sets the behavior of constraint
-   evaluation in the current transaction. In
-   <option>IMMEDIATE</option> mode, constraints are checked at the end
-   of each statement. In <option>DEFERRED</option> mode, constraints
-   are not checked until transaction commit.
+   evaluation in the current transaction. In <option>IMMEDIATE
+   </option> mode, constraints are checked at the end of each
+   statement. In <option>DEFERRED</option> mode, constraints are not
+   checked until transaction commit.
+  </para>
+
+  <note>
+   <para>
+	This command only alters the behavior of constraints within the
+	current transaction. Thus, if you execute this command outside
+	of an explicit transaction block (such as one started with
+	<command>BEGIN</command>), it will not appear to have any effect.
+	If you wish to change the behavior of a constraint without needing
+	to issue a <command>SET CONSTRAINTS</command> command in every
+	transaction, specify <option>INITIALLY DEFERRED</option> or
+	<option>INITIALLY IMMEDIATE</option> when you create the constraint.
+   </para>
+  </note>
+
+  <para>
+   When you change the mode of a constraint to be <option>IMMEDIATE
+   </option>, the new constraint mode takes effect retroactively:
+   any outstanding data modifications that would have been checked
+   at the end of the transaction (when using
+   <option>DEFERRED</option>) are instead checked during the
+   execution of the <command>SET CONSTRAINTS</command> command.
   </para>
 
   <para>
@@ -52,7 +74,11 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">constraint</replaceable>
 
    <para>
     <command>SET CONSTRAINTS</command> is defined in
-    <acronym>SQL92</acronym> and <acronym>SQL99</acronym>.
+    <acronym>SQL92</acronym> and <acronym>SQL99</acronym>. The
+    implementation in <productname>PostgreSQL</productname> complies
+    with the behavior defined in the standard, except for the
+    <productname>PostgreSQL</productname> limitation that <command>SET
+    CONSTRAINTS</command> cannot be applied to check or unique constraints.
    </para>
   </refsect2>
  </refsect1>
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index cc97c569ba..d386fe5665 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/commands/trigger.c,v 1.125 2002/08/13 17:22:08 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/trigger.c,v 1.126 2002/08/17 12:15:48 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1266,12 +1266,10 @@ static MemoryContext deftrig_cxt = NULL;
  * state IMMEDIATE or DEFERRED.
  * ----------
  */
-static bool deftrig_dfl_all_isset = false;
-static bool deftrig_dfl_all_isdeferred = false;
 static List *deftrig_dfl_trigstates = NIL;
 
-static bool deftrig_all_isset;
-static bool deftrig_all_isdeferred;
+static bool deftrig_all_isset = false;
+static bool deftrig_all_isdeferred = false;
 static List *deftrig_trigstates;
 
 /* ----------
@@ -1702,8 +1700,11 @@ DeferredTriggerBeginXact(void)
 										ALLOCSET_DEFAULT_MAXSIZE);
 	oldcxt = MemoryContextSwitchTo(deftrig_cxt);
 
-	deftrig_all_isset = deftrig_dfl_all_isset;
-	deftrig_all_isdeferred = deftrig_dfl_all_isdeferred;
+	deftrig_all_isset = false;
+	/*
+	 * If unspecified, constraints default to IMMEDIATE, per SQL
+	 */
+	deftrig_all_isdeferred = false;
 
 	deftrig_trigstates = NIL;
 	foreach(l, deftrig_dfl_trigstates)
@@ -1793,189 +1794,125 @@ DeferredTriggerAbortXact(void)
 /* ----------
  * DeferredTriggerSetState()
  *
- *	Called for the users SET CONSTRAINTS ... utility command.
+ *	Called for the SET CONSTRAINTS ... utility command.
  * ----------
  */
 void
 DeferredTriggerSetState(ConstraintsSetStmt *stmt)
 {
-	Relation	tgrel;
 	List	   *l;
-	List	   *ls;
-	List	   *loid = NIL;
-	MemoryContext oldcxt;
-	bool		found;
-	DeferredTriggerStatus state;
+
+	/*
+	 * If called outside a transaction block, we can safely return: this
+	 * command cannot effect any subsequent transactions, and there
+	 * are no "session-level" trigger settings.
+	 */
+	if (!IsTransactionBlock())
+		return;
 
 	/*
 	 * Handle SET CONSTRAINTS ALL ...
 	 */
 	if (stmt->constraints == NIL)
 	{
-		if (!IsTransactionBlock())
-		{
-			/*
-			 * ... outside of a transaction block
-			 *
-			 * Drop all information about individual trigger states per
-			 * session.
-			 */
-			l = deftrig_dfl_trigstates;
-			while (l != NIL)
-			{
-				List	   *next = lnext(l);
-
-				pfree(lfirst(l));
-				pfree(l);
-				l = next;
-			}
-			deftrig_dfl_trigstates = NIL;
-
-			/*
-			 * Set the session ALL state to known.
-			 */
-			deftrig_dfl_all_isset = true;
-			deftrig_dfl_all_isdeferred = stmt->deferred;
-
-			return;
-		}
-		else
+		/*
+		 * Drop all per-transaction information about individual trigger
+		 * states.
+		 */
+		l = deftrig_trigstates;
+		while (l != NIL)
 		{
-			/*
-			 * ... inside of a transaction block
-			 *
-			 * Drop all information about individual trigger states per
-			 * transaction.
-			 */
-			l = deftrig_trigstates;
-			while (l != NIL)
-			{
-				List	   *next = lnext(l);
-
-				pfree(lfirst(l));
-				pfree(l);
-				l = next;
-			}
-			deftrig_trigstates = NIL;
-
-			/*
-			 * Set the per transaction ALL state to known.
-			 */
-			deftrig_all_isset = true;
-			deftrig_all_isdeferred = stmt->deferred;
+			List	   *next = lnext(l);
 
-			return;
+			pfree(lfirst(l));
+			pfree(l);
+			l = next;
 		}
-	}
-
-	/* ----------
-	 * Handle SET CONSTRAINTS constraint-name [, ...]
-	 * First lookup all trigger Oid's for the constraint names.
-	 * ----------
-	 */
-	tgrel = heap_openr(TriggerRelationName, AccessShareLock);
-
-	foreach(l, stmt->constraints)
-	{
-		char	   *cname = strVal(lfirst(l));
-		ScanKeyData skey;
-		SysScanDesc	tgscan;
-		HeapTuple	htup;
-
-		/*
-		 * Check that only named constraints are set explicitly
-		 */
-		if (strlen(cname) == 0)
-			elog(ERROR, "unnamed constraints cannot be set explicitly");
+		deftrig_trigstates = NIL;
 
 		/*
-		 * Setup to scan pg_trigger by tgconstrname ...
+		 * Set the per-transaction ALL state to known.
 		 */
-		ScanKeyEntryInitialize(&skey,
-							   (bits16) 0x0,
-							   (AttrNumber) Anum_pg_trigger_tgconstrname,
-							   (RegProcedure) F_NAMEEQ,
-							   PointerGetDatum(cname));
-
-		tgscan = systable_beginscan(tgrel, TriggerConstrNameIndex, true,
-									SnapshotNow, 1, &skey);
-
-		/*
-		 * ... and search for the constraint trigger row
+		deftrig_all_isset = true;
+		deftrig_all_isdeferred = stmt->deferred;
+	}
+	else
+	{
+		Relation	tgrel;
+		MemoryContext oldcxt;
+		bool		found;
+		DeferredTriggerStatus state;
+		List	   *ls;
+		List	   *loid = NIL;
+
+		/* ----------
+		 * Handle SET CONSTRAINTS constraint-name [, ...]
+		 * First lookup all trigger Oid's for the constraint names.
+		 * ----------
 		 */
-		found = false;
+		tgrel = heap_openr(TriggerRelationName, AccessShareLock);
 
-		while (HeapTupleIsValid(htup = systable_getnext(tgscan)))
+		foreach(l, stmt->constraints)
 		{
-			Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);
-			Oid			constr_oid;
+			char	   *cname = strVal(lfirst(l));
+			ScanKeyData skey;
+			SysScanDesc	tgscan;
+			HeapTuple	htup;
 
 			/*
-			 * If we found some, check that they fit the deferrability but
-			 * skip ON <event> RESTRICT ones, since they are silently
-			 * never deferrable.
+			 * Check that only named constraints are set explicitly
 			 */
-			if (stmt->deferred && !pg_trigger->tgdeferrable &&
-				pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_UPD &&
-				pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_DEL)
-				elog(ERROR, "Constraint '%s' is not deferrable",
-					 cname);
-
-			AssertTupleDescHasOid(tgrel->rd_att);
-			constr_oid = HeapTupleGetOid(htup);
-			loid = lappendi(loid, constr_oid);
-			found = true;
-		}
-
-		systable_endscan(tgscan);
+			if (strlen(cname) == 0)
+				elog(ERROR, "unnamed constraints cannot be set explicitly");
 
-		/*
-		 * Not found ?
-		 */
-		if (!found)
-			elog(ERROR, "Constraint '%s' does not exist", cname);
-	}
-	heap_close(tgrel, AccessShareLock);
+			/*
+			 * Setup to scan pg_trigger by tgconstrname ...
+			 */
+			ScanKeyEntryInitialize(&skey, (bits16) 0x0,
+								   (AttrNumber) Anum_pg_trigger_tgconstrname,
+								   (RegProcedure) F_NAMEEQ,
+								   PointerGetDatum(cname));
 
-	if (!IsTransactionBlock())
-	{
-		/*
-		 * Outside of a transaction block set the trigger states of
-		 * individual triggers on session level.
-		 */
-		oldcxt = MemoryContextSwitchTo(deftrig_gcxt);
+			tgscan = systable_beginscan(tgrel, TriggerConstrNameIndex, true,
+										SnapshotNow, 1, &skey);
 
-		foreach(l, loid)
-		{
+			/*
+			 * ... and search for the constraint trigger row
+			 */
 			found = false;
-			foreach(ls, deftrig_dfl_trigstates)
-			{
-				state = (DeferredTriggerStatus) lfirst(ls);
-				if (state->dts_tgoid == (Oid) lfirsti(l))
-				{
-					state->dts_tgisdeferred = stmt->deferred;
-					found = true;
-					break;
-				}
-			}
-			if (!found)
+
+			while (HeapTupleIsValid(htup = systable_getnext(tgscan)))
 			{
-				state = (DeferredTriggerStatus)
-					palloc(sizeof(DeferredTriggerStatusData));
-				state->dts_tgoid = (Oid) lfirsti(l);
-				state->dts_tgisdeferred = stmt->deferred;
+				Form_pg_trigger pg_trigger = (Form_pg_trigger) GETSTRUCT(htup);
+				Oid			constr_oid;
 
-				deftrig_dfl_trigstates =
-					lappend(deftrig_dfl_trigstates, state);
+				/*
+				 * If we found some, check that they fit the deferrability but
+				 * skip ON <event> RESTRICT ones, since they are silently
+				 * never deferrable.
+				 */
+				if (stmt->deferred && !pg_trigger->tgdeferrable &&
+					pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_UPD &&
+					pg_trigger->tgfoid != F_RI_FKEY_RESTRICT_DEL)
+					elog(ERROR, "Constraint '%s' is not deferrable",
+						 cname);
+
+				AssertTupleDescHasOid(tgrel->rd_att);
+				constr_oid = HeapTupleGetOid(htup);
+				loid = lappendi(loid, constr_oid);
+				found = true;
 			}
-		}
 
-		MemoryContextSwitchTo(oldcxt);
+			systable_endscan(tgscan);
+
+			/*
+			 * Not found ?
+			 */
+			if (!found)
+				elog(ERROR, "Constraint '%s' does not exist", cname);
+		}
+		heap_close(tgrel, AccessShareLock);
 
-		return;
-	}
-	else
-	{
 		/*
 		 * Inside of a transaction block set the trigger states of
 		 * individual triggers on transaction level.
@@ -2008,9 +1945,17 @@ DeferredTriggerSetState(ConstraintsSetStmt *stmt)
 		}
 
 		MemoryContextSwitchTo(oldcxt);
-
-		return;
 	}
+
+	/*
+	 * SQL99 requires that when a constraint is set to IMMEDIATE, any
+	 * deferred checks against that constraint must be made when the
+	 * SET CONSTRAINTS command is executed -- i.e. the effects of the
+	 * SET CONSTRAINTS command applies retroactively. This happens "for
+	 * free" since we have already made the necessary modifications to
+	 * the constraints, and deferredTriggerEndQuery() is called by
+	 * finish_xact_command().
+	 */
 }
 
 
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index e75b52b670..ded117dbac 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.170 2002/08/15 16:36:05 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.171 2002/08/17 12:15:49 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -215,9 +215,8 @@ ProcessUtility(Node *parsetree,
 							BeginTransactionBlock();
 
 							/*
-							 * Currently, the only option that can be set is
-							 * the transaction isolation level by START
-							 * TRANSACTION.
+							 * Currently, the only option that can be set by
+							 * START TRANSACTION is the isolation level.
 							 */
 							if (stmt->options)
 							{
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index f5272891b9..6f3b5dd3df 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -945,3 +945,80 @@ ERROR:  Unable to identify an operator '=' for types 'inet' and 'integer'
 drop table pktable;
 ERROR:  table "pktable" does not exist
 drop table pktable_base;
+--
+-- Deferrable constraints
+--		(right now, only FOREIGN KEY constraints can be deferred)
+--
+-- deferrable, explicitely deferred
+CREATE TABLE pktable (
+	id		INT4 PRIMARY KEY,
+	other	INT4
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
+CREATE TABLE fktable (
+	id		INT4 PRIMARY KEY,
+	fk		INT4 REFERENCES pktable DEFERRABLE
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable'
+NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+-- default to immediate: should fail
+INSERT INTO fktable VALUES (5, 10);
+ERROR:  $1 referential integrity violation - key referenced from fktable not found in pktable
+-- explicitely defer the constraint
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+INSERT INTO fktable VALUES (10, 15);
+INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
+COMMIT;
+DROP TABLE fktable, pktable;
+-- deferrable, initially deferred
+CREATE TABLE pktable (
+	id		INT4 PRIMARY KEY,
+	other	INT4
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
+CREATE TABLE fktable (
+	id		INT4 PRIMARY KEY,
+	fk		INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable'
+NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+-- default to deferred, should succeed
+BEGIN;
+INSERT INTO fktable VALUES (100, 200);
+INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
+COMMIT;
+-- default to deferred, explicitely make immediate
+BEGIN;
+SET CONSTRAINTS ALL IMMEDIATE;
+-- should fail
+INSERT INTO fktable VALUES (500, 1000);
+ERROR:  $1 referential integrity violation - key referenced from fktable not found in pktable
+COMMIT;
+DROP TABLE fktable, pktable;
+-- tricky behavior: according to SQL99, if a deferred constraint is set
+-- to 'immediate' mode, it should be checked for validity *immediately*,
+-- not when the current transaction commits (i.e. the mode change applies
+-- retroactively)
+CREATE TABLE pktable (
+	id		INT4 PRIMARY KEY,
+	other	INT4
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
+CREATE TABLE fktable (
+	id		INT4 PRIMARY KEY,
+	fk		INT4 REFERENCES pktable DEFERRABLE
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable'
+NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+BEGIN;
+SET CONSTRAINTS ALL DEFERRED;
+-- should succeed, for now
+INSERT INTO fktable VALUES (1000, 2000);
+-- should cause transaction abort, due to preceding error
+SET CONSTRAINTS ALL IMMEDIATE;
+ERROR:  $1 referential integrity violation - key referenced from fktable not found in pktable
+INSERT INTO pktable VALUES (2000, 3); -- too late
+ERROR:  current transaction is aborted, queries ignored until end of transaction block
+COMMIT;
+DROP TABLE fktable, pktable;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index c6b50e4b32..f314b5f199 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -583,3 +583,94 @@ create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), forei
 drop table pktable;
 drop table pktable_base;
 
+--
+-- Deferrable constraints
+--		(right now, only FOREIGN KEY constraints can be deferred)
+--
+
+-- deferrable, explicitely deferred
+CREATE TABLE pktable (
+	id		INT4 PRIMARY KEY,
+	other	INT4
+);
+
+CREATE TABLE fktable (
+	id		INT4 PRIMARY KEY,
+	fk		INT4 REFERENCES pktable DEFERRABLE
+);
+
+-- default to immediate: should fail
+INSERT INTO fktable VALUES (5, 10);
+
+-- explicitely defer the constraint
+BEGIN;
+
+SET CONSTRAINTS ALL DEFERRED;
+
+INSERT INTO fktable VALUES (10, 15);
+INSERT INTO pktable VALUES (15, 0); -- make the FK insert valid
+
+COMMIT;
+
+DROP TABLE fktable, pktable;
+
+-- deferrable, initially deferred
+CREATE TABLE pktable (
+	id		INT4 PRIMARY KEY,
+	other	INT4
+);
+
+CREATE TABLE fktable (
+	id		INT4 PRIMARY KEY,
+	fk		INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
+);
+
+-- default to deferred, should succeed
+BEGIN;
+
+INSERT INTO fktable VALUES (100, 200);
+INSERT INTO pktable VALUES (200, 500); -- make the FK insert valid
+
+COMMIT;
+
+-- default to deferred, explicitely make immediate
+BEGIN;
+
+SET CONSTRAINTS ALL IMMEDIATE;
+
+-- should fail
+INSERT INTO fktable VALUES (500, 1000);
+
+COMMIT;
+
+DROP TABLE fktable, pktable;
+
+-- tricky behavior: according to SQL99, if a deferred constraint is set
+-- to 'immediate' mode, it should be checked for validity *immediately*,
+-- not when the current transaction commits (i.e. the mode change applies
+-- retroactively)
+CREATE TABLE pktable (
+	id		INT4 PRIMARY KEY,
+	other	INT4
+);
+
+CREATE TABLE fktable (
+	id		INT4 PRIMARY KEY,
+	fk		INT4 REFERENCES pktable DEFERRABLE
+);
+
+BEGIN;
+
+SET CONSTRAINTS ALL DEFERRED;
+
+-- should succeed, for now
+INSERT INTO fktable VALUES (1000, 2000);
+
+-- should cause transaction abort, due to preceding error
+SET CONSTRAINTS ALL IMMEDIATE;
+
+INSERT INTO pktable VALUES (2000, 3); -- too late
+
+COMMIT;
+
+DROP TABLE fktable, pktable;
-- 
2.50.1