Changes pg_trigger and extend pg_rewrite in order to allow triggers and

rules to be defined with different, per session controllable, behaviors
for replication purposes.

This will allow replication systems like Slony-I and, as has been stated
on pgsql-hackers, other products to control the firing mechanism of
triggers and rewrite rules without modifying the system catalog directly.

The firing mechanisms are controlled by a new superuser-only GUC
variable, session_replication_role, together with a change to
pg_trigger.tgenabled and a new column pg_rewrite.ev_enabled. Both
columns are a single char data type now (tgenabled was a bool before).
The possible values in these attributes are:

     'O' - Trigger/Rule fires when session_replication_role is "origin"
           (default) or "local". This is the default behavior.

     'D' - Trigger/Rule is disabled and fires never

     'A' - Trigger/Rule fires always regardless of the setting of
           session_replication_role

     'R' - Trigger/Rule fires when session_replication_role is "replica"

The GUC variable can only be changed as long as the system does not have
any cached query plans. This will prevent changing the session role and
accidentally executing stored procedures or functions that have plans
cached that expand to the wrong query set due to differences in the rule
firing semantics.

The SQL syntax for changing a triggers/rules firing semantics is

     ALTER TABLE <tabname> <when> TRIGGER|RULE <name>;

     <when> ::= ENABLE | ENABLE ALWAYS | ENABLE REPLICA | DISABLE

psql's \d command as well as pg_dump are extended in a backward
compatible fashion.

Jan

3 files changed, 214 insertions, 89 deletions

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index eef9a4c875..90e78c36d1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12,7 +12,7 @@
  *	by PostgreSQL
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.460 2007/02/14 01:58:57 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.461 2007/03/19 23:38:30 wieck Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -3699,15 +3699,26 @@ getRules(int *numRules)
 	int			i_ruletable;
 	int			i_ev_type;
 	int			i_is_instead;
+	int			i_ev_enabled;
 
 	/* Make sure we are in proper schema */
 	selectSourceSchema("pg_catalog");
 
-	if (g_fout->remoteVersion >= 70100)
+	if (g_fout->remoteVersion >= 80300)
 	{
 		appendPQExpBuffer(query, "SELECT "
 						  "tableoid, oid, rulename, "
-						  "ev_class as ruletable, ev_type, is_instead "
+						  "ev_class as ruletable, ev_type, is_instead, "
+						  "ev_enabled "
+						  "FROM pg_rewrite "
+						  "ORDER BY oid");
+	}
+	else if (g_fout->remoteVersion >= 70100)
+	{
+		appendPQExpBuffer(query, "SELECT "
+						  "tableoid, oid, rulename, "
+						  "ev_class as ruletable, ev_type, is_instead, "
+						  "'O'::char as ev_enabled "
 						  "FROM pg_rewrite "
 						  "ORDER BY oid");
 	}
@@ -3716,7 +3727,8 @@ getRules(int *numRules)
 		appendPQExpBuffer(query, "SELECT "
 						  "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
 						  "oid, rulename, "
-						  "ev_class as ruletable, ev_type, is_instead "
+						  "ev_class as ruletable, ev_type, is_instead, "
+						  "'O'::char as ev_enabled "
 						  "FROM pg_rewrite "
 						  "ORDER BY oid");
 	}
@@ -3736,6 +3748,7 @@ getRules(int *numRules)
 	i_ruletable = PQfnumber(res, "ruletable");
 	i_ev_type = PQfnumber(res, "ev_type");
 	i_is_instead = PQfnumber(res, "is_instead");
+	i_ev_enabled = PQfnumber(res, "ev_enabled");
 
 	for (i = 0; i < ntups; i++)
 	{
@@ -3759,6 +3772,7 @@ getRules(int *numRules)
 		ruleinfo[i].dobj.dump = ruleinfo[i].ruletable->dobj.dump;
 		ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
 		ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
+		ruleinfo[i].ev_enabled = *(PQgetvalue(res, i, i_ev_enabled));
 		if (ruleinfo[i].ruletable)
 		{
 			/*
@@ -3956,7 +3970,7 @@ getTriggers(TableInfo tblinfo[], int numTables)
 			tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
 			tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
 			tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
-			tginfo[j].tgenabled = *(PQgetvalue(res, j, i_tgenabled)) == 't';
+			tginfo[j].tgenabled = *(PQgetvalue(res, j, i_tgenabled));
 			tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
 			tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
 
@@ -8824,11 +8838,27 @@ dumpTrigger(Archive *fout, TriggerInfo *tginfo)
 	}
 	appendPQExpBuffer(query, ");\n");
 
-	if (!tginfo->tgenabled)
+	if (tginfo->tgenabled != 't' && tginfo->tgenabled != 'O')
 	{
 		appendPQExpBuffer(query, "\nALTER TABLE %s ",
 						  fmtId(tbinfo->dobj.name));
-		appendPQExpBuffer(query, "DISABLE TRIGGER %s;\n",
+		switch (tginfo->tgenabled)
+		{
+			case 'D':
+			case 'f':
+				appendPQExpBuffer(query, "DISABLE");
+				break;
+			case 'A':
+				appendPQExpBuffer(query, "ENABLE ALWAYS");
+				break;
+			case 'R':
+				appendPQExpBuffer(query, "ENABLE REPLICA");
+				break;
+			default:
+				appendPQExpBuffer(query, "ENABLE");
+				break;
+		}
+		appendPQExpBuffer(query, " TRIGGER %s;\n",
 						  fmtId(tginfo->dobj.name));
 	}
 
@@ -8915,6 +8945,33 @@ dumpRule(Archive *fout, RuleInfo *rinfo)
 	printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
 
 	/*
+	 * Add the command to alter the rules replication firing semantics
+	 * if it differs from the default.
+	 */
+	if (rinfo->ev_enabled != 'O')
+	{
+		appendPQExpBuffer(cmd, "ALTER TABLE %s.",
+					fmtId(tbinfo->dobj.namespace->dobj.name));
+		appendPQExpBuffer(cmd, "%s ",
+					fmtId(tbinfo->dobj.name));
+		switch (rinfo->ev_enabled)
+		{
+			case 'A':
+				appendPQExpBuffer(cmd, "ENABLE ALWAYS RULE %s;\n",
+							fmtId(rinfo->dobj.name));
+				break;
+			case 'R':
+				appendPQExpBuffer(cmd, "ENABLE REPLICA RULE %s;\n",
+							fmtId(rinfo->dobj.name));
+				break;
+			case 'D':
+				appendPQExpBuffer(cmd, "DISABLE RULE %s;\n",
+							fmtId(rinfo->dobj.name));
+				break;
+		}
+	}
+
+	/*
 	 * DROP must be fully qualified in case same name appears in pg_catalog
 	 */
 	appendPQExpBuffer(delcmd, "DROP RULE %s ",
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index b584ab1fe0..8694376b52 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.133 2007/02/19 15:05:06 mha Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.134 2007/03/19 23:38:30 wieck Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -314,6 +314,7 @@ typedef struct _ruleInfo
 	TableInfo  *ruletable;		/* link to table the rule is for */
 	char		ev_type;
 	bool		is_instead;
+	char		ev_enabled;
 	bool		separate;		/* TRUE if must dump as separate item */
 	/* separate is always true for non-ON SELECT rules */
 } RuleInfo;
@@ -330,7 +331,7 @@ typedef struct _triggerInfo
 	char	   *tgconstrname;
 	Oid			tgconstrrelid;
 	char	   *tgconstrrelname;
-	bool		tgenabled;
+	char		tgenabled;
 	bool		tgdeferrable;
 	bool		tginitdeferred;
 } TriggerInfo;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 5dfaf9367a..13d3be678e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2007, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.154 2007/03/18 16:50:44 neilc Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.155 2007/03/19 23:38:31 wieck Exp $
  */
 #include "postgres_fe.h"
 #include "describe.h"
@@ -1055,14 +1055,12 @@ describeOneTableDetails(const char *schemaname,
 				   *result3 = NULL,
 				   *result4 = NULL,
 				   *result5 = NULL,
-				   *result6 = NULL,
-				   *result7 = NULL;
+				   *result6 = NULL;
 		int			check_count = 0,
 					index_count = 0,
 					foreignkey_count = 0,
 					rule_count = 0,
 					trigger_count = 0,
-					disabled_trigger_count = 0,
 					inherits_count = 0;
 		int			count_footers = 0;
 
@@ -1105,11 +1103,24 @@ describeOneTableDetails(const char *schemaname,
 		/* count rules */
 		if (tableinfo.hasrules)
 		{
-			printfPQExpBuffer(&buf,
-							  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
+			if (pset.sversion < 80300)
+			{
+				printfPQExpBuffer(&buf,
+							  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
+							  "'O'::char AS ev_enabled\n"
 							  "FROM pg_catalog.pg_rewrite r\n"
 							  "WHERE r.ev_class = '%s' ORDER BY 1",
 							  oid);
+			}
+			else
+			{
+				printfPQExpBuffer(&buf,
+							  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
+							  "ev_enabled\n"
+							  "FROM pg_catalog.pg_rewrite r\n"
+							  "WHERE r.ev_class = '%s' ORDER BY 1",
+							  oid);
+			}
 			result3 = PSQLexec(buf.data, false);
 			if (!result3)
 			{
@@ -1125,10 +1136,10 @@ describeOneTableDetails(const char *schemaname,
 		if (tableinfo.triggers)
 		{
 			printfPQExpBuffer(&buf,
-					 "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
+					 "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid), "
+							"t.tgenabled\n"
 							  "FROM pg_catalog.pg_trigger t\n"
 							  "WHERE t.tgrelid = '%s' "
-							  "AND t.tgenabled "
 							  "AND t.tgconstraint = 0\n"
 							  "ORDER BY 1",
 							  oid);
@@ -1142,27 +1153,6 @@ describeOneTableDetails(const char *schemaname,
 			}
 			else
 				trigger_count = PQntuples(result4);
-
-			/* acquire disabled triggers as a separate list */
-			printfPQExpBuffer(&buf,
-					 "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
-							  "FROM pg_catalog.pg_trigger t\n"
-							  "WHERE t.tgrelid = '%s' "
-							  "AND NOT t.tgenabled "
-							  "AND t.tgconstraint = 0\n"
-							  "ORDER BY 1",
-							  oid);
-			result7 = PSQLexec(buf.data, false);
-			if (!result7)
-			{
-				PQclear(result1);
-				PQclear(result2);
-				PQclear(result3);
-				PQclear(result4);
-				goto error_return;
-			}
-			else
-				disabled_trigger_count = PQntuples(result7);
 		}
 
 		/* count foreign-key constraints (there are none if no triggers) */
@@ -1181,7 +1171,6 @@ describeOneTableDetails(const char *schemaname,
 				PQclear(result2);
 				PQclear(result3);
 				PQclear(result4);
-				PQclear(result7);
 				goto error_return;
 			}
 			else
@@ -1199,7 +1188,6 @@ describeOneTableDetails(const char *schemaname,
 			PQclear(result3);
 			PQclear(result4);
 			PQclear(result5);
-			PQclear(result7);
 			goto error_return;
 		}
 		else
@@ -1297,63 +1285,143 @@ describeOneTableDetails(const char *schemaname,
 		/* print rules */
 		if (rule_count > 0)
 		{
-			printfPQExpBuffer(&buf, _("Rules:"));
-			footers[count_footers++] = pg_strdup(buf.data);
-			for (i = 0; i < rule_count; i++)
-			{
-				const char *ruledef;
-
-				/* Everything after "CREATE RULE" is echoed verbatim */
-				ruledef = PQgetvalue(result3, i, 1);
-				ruledef += 12;
+			bool	have_heading;
+			int		category;
 
-				printfPQExpBuffer(&buf, "    %s", ruledef);
+			for (category = 0; category < 4; category++)
+			{
+				have_heading = false;
 
-				footers[count_footers++] = pg_strdup(buf.data);
+				for (i = 0; i < rule_count; i++)
+				{
+					const char *ruledef;
+					bool		list_rule = false;
+
+					switch (category)
+					{
+						case 0:
+							if (*PQgetvalue(result3, i, 2) == 'O')
+								list_rule = true;
+							break;
+						case 1:
+							if (*PQgetvalue(result3, i, 2) == 'D')
+								list_rule = true;
+							break;
+						case 2:
+							if (*PQgetvalue(result3, i, 2) == 'A')
+								list_rule = true;
+							break;
+						case 3:
+							if (*PQgetvalue(result3, i, 2) == 'R')
+								list_rule = true;
+							break;
+					}
+					if (!list_rule)
+						continue;
+
+					if (!have_heading)
+					{
+						switch (category)
+						{
+							case 0:
+								printfPQExpBuffer(&buf, _("Rules:"));
+								break;
+							case 1:
+								printfPQExpBuffer(&buf, _("Disabled Rules:"));
+								break;
+							case 2:
+								printfPQExpBuffer(&buf, _("Rules firing always:"));
+								break;
+							case 3:
+								printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
+								break;
+						}
+						footers[count_footers++] = pg_strdup(buf.data);
+						have_heading = true;
+					}
+
+					/* Everything after "CREATE RULE" is echoed verbatim */
+					ruledef = PQgetvalue(result3, i, 1);
+					ruledef += 12;
+					printfPQExpBuffer(&buf, "    %s", ruledef);
+					footers[count_footers++] = pg_strdup(buf.data);
+				}
 			}
 		}
 
 		/* print triggers */
 		if (trigger_count > 0)
 		{
-			printfPQExpBuffer(&buf, _("Triggers:"));
-			footers[count_footers++] = pg_strdup(buf.data);
-			for (i = 0; i < trigger_count; i++)
-			{
-				const char *tgdef;
-				const char *usingpos;
-
-				/* Everything after "TRIGGER" is echoed verbatim */
-				tgdef = PQgetvalue(result4, i, 1);
-				usingpos = strstr(tgdef, " TRIGGER ");
-				if (usingpos)
-					tgdef = usingpos + 9;
-
-				printfPQExpBuffer(&buf, "    %s", tgdef);
-
-				footers[count_footers++] = pg_strdup(buf.data);
-			}
-		}
-
-		/* print disabled triggers */
-		if (disabled_trigger_count > 0)
-		{
-			printfPQExpBuffer(&buf, _("Disabled triggers:"));
-			footers[count_footers++] = pg_strdup(buf.data);
-			for (i = 0; i < disabled_trigger_count; i++)
+			bool	have_heading;
+			int		category;
+
+			/* split the output into 4 different categories.
+			 * Enabled triggers, disabled triggers and the two
+			 * special ALWAYS and REPLICA configurations.
+			 */
+			for (category = 0; category < 4; category++)
 			{
-				const char *tgdef;
-				const char *usingpos;
-
-				/* Everything after "TRIGGER" is echoed verbatim */
-				tgdef = PQgetvalue(result7, i, 1);
-				usingpos = strstr(tgdef, " TRIGGER ");
-				if (usingpos)
-					tgdef = usingpos + 9;
-
-				printfPQExpBuffer(&buf, "    %s", tgdef);
-
-				footers[count_footers++] = pg_strdup(buf.data);
+				have_heading = false;
+				for (i = 0; i < trigger_count; i++)
+				{
+					bool		list_trigger;
+					const char *tgdef;
+					const char *usingpos;
+					const char *tgenabled;
+
+					/* Check if this trigger falls into the current category */
+					tgenabled = PQgetvalue(result4, i, 2);
+					list_trigger = false;
+					switch (category)
+					{
+						case 0:		if (*tgenabled == 'O' || *tgenabled == 't')
+										list_trigger = true;
+									break;
+						case 1:		if (*tgenabled == 'D' || *tgenabled == 'f')
+										list_trigger = true;
+									break;
+						case 2:		if (*tgenabled == 'A')
+										list_trigger = true;
+									break;
+						case 3:		if (*tgenabled == 'R')
+										list_trigger = true;
+									break;
+					}
+					if (list_trigger == false)
+						continue;
+
+					/* Print the category heading once */
+					if (have_heading == false)
+					{
+						switch (category)
+						{
+							case 0:
+								printfPQExpBuffer(&buf, _("Triggers:"));
+								break;
+							case 1:
+								printfPQExpBuffer(&buf, _("Disabled Triggers:"));
+								break;
+							case 2:
+								printfPQExpBuffer(&buf, _("Triggers firing always:"));
+								break;
+							case 3:
+								printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
+								break;
+								
+						}
+						footers[count_footers++] = pg_strdup(buf.data);
+						have_heading = true;
+					}
+
+					/* Everything after "TRIGGER" is echoed verbatim */
+					tgdef = PQgetvalue(result4, i, 1);
+					usingpos = strstr(tgdef, " TRIGGER ");
+					if (usingpos)
+						tgdef = usingpos + 9;
+
+					printfPQExpBuffer(&buf, "    %s", tgdef);
+					footers[count_footers++] = pg_strdup(buf.data);
+				}
 			}
 		}
 
@@ -1392,7 +1460,6 @@ describeOneTableDetails(const char *schemaname,
 		PQclear(result4);
 		PQclear(result5);
 		PQclear(result6);
-		PQclear(result7);
 	}
 
 	printTable(title.data, headers,