Full implementation of truncate triggers on CVS HEAD, for Postgres 8.4
Includes various docs, regression tests for PL/pgSQL trigger support,
psql and pg_dump support.
Some further discussion on -hackers required around statement skipping.
Prototype code for that aspect included in this patch, #ifdef'd out.
Most interesting discussion point is the handling of multiple truncate
triggers when we are performing a multi-table TRUNCATE. Please read docs
in patch for details, plus comments.
This includes changes to PL/tcl, PL/perl and PL/python but support in
those languages have not yet been tested, not even a little bit. This
will be performed after review.
Provisional Slony support patch will also be posted to appropriate list.
diffstat
doc/src/sgml/ref/create_trigger.sgml | 32 +++!
doc/src/sgml/ref/truncate.sgml | 4
doc/src/sgml/trigger.sgml | 32 ++!!
src/backend/commands/tablecmds.c | 126 ++++++++++++++++++
src/backend/commands/trigger.c | 111 ++++++++++++++++
src/backend/parser/gram.y | 1
src/backend/utils/adt/ruleutils.c | 7 +
src/bin/pg_dump/pg_dump.c | 7 +
src/include/catalog/pg_trigger.h | 3
src/include/commands/trigger.h | 31 +!!!
src/include/utils/rel.h | 2
src/pl/plperl/plperl.c | 4
src/pl/plpgsql/src/pl_exec.c | 6
src/pl/plpython/plpython.c | 2
src/pl/tcl/pltcl.c | 2
src/test/regress/expected/truncate.out | 226 ++++++++++++++++++++...
src/test/regress/sql/truncate.sql | 127 ++++++++++++++++++
17 files changed, 672 insertions(+), 51 modifications(!)
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Index: doc/src/sgml/trigger.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/trigger.sgml,v
retrieving revision 1.51
diff -c -r1.51 trigger.sgml
*** doc/src/sgml/trigger.sgml 3 Dec 2007 23:49:51 -0000 1.51
--- doc/src/sgml/trigger.sgml 30 Jan 2008 17:34:18 -0000
***************
*** 35,44 ****
execute a particular function whenever a certain type of operation is
performed. Triggers can be defined to execute either before or after any
<command>INSERT</command>, <command>UPDATE</command>, or
! <command>DELETE</command> operation, either once per modified row,
! or once per <acronym>SQL</acronym> statement.
! If a trigger event occurs, the trigger's function is called
! at the appropriate time to handle the event.
</para>
<para>
--- 35,45 ----
execute a particular function whenever a certain type of operation is
performed. Triggers can be defined to execute either before or after any
<command>INSERT</command>, <command>UPDATE</command>, or
! <command>DELETE</command> operation, either once per modified row,
! or once per <acronym>SQL</acronym> statement. Triggers can also fire
! for <command>TRUNCATE</command> statements. If a trigger event occurs,
! the trigger's function is called at the appropriate time to handle the
! event.
</para>
<para>
***************
*** 69,75 ****
in the execution of any applicable per-statement triggers. These
two types of triggers are sometimes called <firstterm>row-level</>
triggers and <firstterm>statement-level</> triggers,
! respectively.
</para>
<para>
--- 70,77 ----
in the execution of any applicable per-statement triggers. These
two types of triggers are sometimes called <firstterm>row-level</>
triggers and <firstterm>statement-level</> triggers,
! respectively. Triggers on <command>TRUNCATE</command> may only be
! defined at statement-level.
</para>
<para>
***************
*** 194,199 ****
--- 196,212 ----
individual row(s) modified by the statement.
</para>
+ <para>
+ <command>TRUNCATE</> allows multiple tables to be truncated in a single
+ command. If triggers are defined on more than one of the tables then there
+ are special rules for trigger execution. All <literal>BEFORE</literal>
+ triggers fire before any truncate action occurs on any of the tables
+ listed. <literal>AFTER</literal> triggers begin firing after all of the
+ listed tables have been truncated. The triggers will fire in the order
+ the tables are listed for that command, allowing some user control over
+ firing sequence of the triggers by altering the <command>TRUNCATE</>
+ command.
+ </para>
</sect1>
<sect1 id="trigger-datachanges">
***************
*** 398,403 ****
--- 411,425 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>TRIGGER_FIRED_BY_TRUNCATE(tg_event)</literal></term>
+ <listitem>
+ <para>
+ Returns true if the trigger was fired by a <command>TRUNCATE</command> command.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</listitem>
Index: doc/src/sgml/ref/create_trigger.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/create_trigger.sgml,v
retrieving revision 1.47
diff -c -r1.47 create_trigger.sgml
*** doc/src/sgml/ref/create_trigger.sgml 1 Feb 2007 19:10:24 -0000 1.47
--- doc/src/sgml/ref/create_trigger.sgml 30 Jan 2008 11:50:35 -0000
***************
*** 66,71 ****
--- 66,77 ----
</para>
<para>
+ In addition, triggers may be defined to fire for a
+ <command>TRUNCATE</command>, though only
+ <literal>FOR EACH STATEMENT</literal>.
+ </para>
+
+ <para>
If multiple triggers of the same kind are defined for the same event,
they will be fired in alphabetical order by name.
</para>
***************
*** 110,119 ****
<term><replaceable class="parameter">event</replaceable></term>
<listitem>
<para>
! One of <command>INSERT</command>, <command>UPDATE</command>, or
! <command>DELETE</command>; this specifies the event that will
! fire the trigger. Multiple events can be specified using
! <literal>OR</literal>.
</para>
</listitem>
</varlistentry>
--- 116,125 ----
<term><replaceable class="parameter">event</replaceable></term>
<listitem>
<para>
! One of <command>INSERT</command>, <command>UPDATE</command>,
! <command>DELETE</command>, or <command>TRUNCATE</command>;
! this specifies the event that will fire the trigger. Multiple
! events can be specified using <literal>OR</literal>.
</para>
</listitem>
</varlistentry>
***************
*** 189,194 ****
--- 195,212 ----
</para>
<para>
+ <command>TRUNCATE</> allows multiple tables to be truncated in a single
+ command. If triggers are defined on more than one of the tables then there
+ are special rules for trigger execution. All <literal>BEFORE</literal>
+ triggers fire before any truncate action occurs on any of the tables
+ listed. <literal>AFTER</literal> triggers begin firing after all of the
+ listed tables have been truncated. The triggers will fire in the order
+ the tables are listed for that command, allowing some user control over
+ firing sequence of the triggers by altering the <command>TRUNCATE</>
+ command.
+ </para>
+
+ <para>
Use <xref linkend="sql-droptrigger"
endterm="sql-droptrigger-title"> to remove a trigger.
</para>
***************
*** 267,272 ****
--- 285,296 ----
<literal>OR</literal> is a <productname>PostgreSQL</> extension of
the SQL standard.
</para>
+
+ <para>
+ The ability to fire triggers for <command>TRUNCATE</command> is a
+ <productname>PostgreSQL</> extension of the SQL standard.
+ </para>
+
</refsect1>
<refsect1>
Index: doc/src/sgml/ref/truncate.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/truncate.sgml,v
retrieving revision 1.24
diff -c -r1.24 truncate.sgml
*** doc/src/sgml/ref/truncate.sgml 11 May 2007 19:40:08 -0000 1.24
--- doc/src/sgml/ref/truncate.sgml 30 Jan 2008 12:13:00 -0000
***************
*** 92,98 ****
<para>
<command>TRUNCATE</> will not run any <literal>ON DELETE</literal>
! triggers that might exist for the tables.
</para>
<warning>
--- 92,100 ----
<para>
<command>TRUNCATE</> will not run any <literal>ON DELETE</literal>
! triggers that might exist for the tables. <literal>ON TRUNCATE</literal>
! triggers will fire for each table if they exist, see
! <xref linkend="sql-createtrigger" endterm="sql-createtrigger-title">.
</para>
<warning>
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.240
diff -c -r1.240 tablecmds.c
*** src/backend/commands/tablecmds.c 17 Jan 2008 18:56:54 -0000 1.240
--- src/backend/commands/tablecmds.c 30 Jan 2008 16:40:42 -0000
***************
*** 538,543 ****
--- 538,546 ----
List *rels = NIL;
List *relids = NIL;
ListCell *cell;
+ #ifdef UNUSED_STATEMENT_SKIPPING_LOGIC
+ bool do_all_truncates = true;
+ #endif
/*
* Open, exclusive-lock, and check all the explicitly-specified relations
***************
*** 600,605 ****
--- 603,685 ----
#endif
/*
+ * Process BEFORE STATEMENT triggers
+ *
+ * We may be processing multiple tables here, so we may have
+ * multiple truncate triggers to fire. Truncate triggers allow
+ * the statement to be skipped. If we allowed one table to skip
+ * TRUNCATE but not the others we might end up with a constraint
+ * violation. So we implement the rule that if one of the tables
+ * has a BEFORE STATEMENT trigger which skips the statement then
+ * all tables must also skip the statement, otherwise error.
+ */
+ foreach(cell, rels)
+ {
+ Relation rel = (Relation) lfirst(cell);
+ ResultRelInfo *resultRelInfo;
+ EState *estate = CreateExecutorState();
+ #ifdef UNUSED_STATEMENT_SKIPPING_LOGIC
+ bool do_truncate = true;
+ #endif
+
+ /*
+ * We need an EState and ResultRelInfo for trigger execution
+ */
+ resultRelInfo = makeNode(ResultRelInfo);
+ resultRelInfo->ri_RangeTableIndex = 1; /* dummy */
+ resultRelInfo->ri_RelationDesc = rel;
+ resultRelInfo->ri_TrigDesc = CopyTriggerDesc(rel->trigdesc);
+ if (resultRelInfo->ri_TrigDesc)
+ resultRelInfo->ri_TrigFunctions = (FmgrInfo *)
+ palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(FmgrInfo));
+
+ /* We cannot EXPLAIN a TRUNCATE command, so no need to instrument */
+ resultRelInfo->ri_TrigInstrument = NULL;
+
+ estate->es_result_relations = resultRelInfo;
+ estate->es_num_result_relations = 1;
+ estate->es_result_relation_info = resultRelInfo;
+
+ /*
+ * Process BEFORE EACH STATEMENT triggers
+ */
+ ExecBSTruncateTriggers(estate, estate->es_result_relation_info);
+
+ #ifdef UNUSED_STATEMENT_SKIPPING_LOGIC
+ /* do_truncate is set via BS trigger execution like this:
+
+ ExecBSTruncateTriggers(estate, estate->es_result_relation_info,
+ &do_truncate);
+ */
+
+
+ if (!do_all_truncates && do_truncate)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot skip TRUNCATE for table \"%s\""
+ "when other tables in this statement do not",
+ RelationGetRelationName(rel))));
+
+ if (!do_truncate)
+ do_all_truncates = false;
+ #endif
+
+ if (resultRelInfo->ri_TrigDesc)
+ pfree(resultRelInfo->ri_TrigFunctions);
+ FreeExecutorState(estate);
+ }
+
+ #ifdef UNUSED_STATEMENT_SKIPPING_LOGIC
+ /*
+ * If we skipped the TRUNCATE nothing left for us to do.
+ *
+ * Note: AFTER triggers do *not* fire in this case.
+ */
+ if (!do_all_truncates)
+ return;
+ #endif
+
+ /*
* OK, truncate each table.
*/
foreach(cell, rels)
***************
*** 635,640 ****
--- 715,766 ----
*/
reindex_relation(heap_relid, true);
}
+
+ /*
+ * Process AFTER STATEMENT triggers
+ *
+ * We may be processing multiple tables here, so we may have
+ * multiple truncate triggers to fire. Before Truncate triggers
+ * are all executed before we perform any TRUNCATE, so we choose
+ * to implement after triggers the same way to provide symmetry.
+ */
+ foreach(cell, rels)
+ {
+ Relation rel = (Relation) lfirst(cell);
+ ResultRelInfo *resultRelInfo;
+ EState *estate = CreateExecutorState();
+
+ /*
+ * We need an EState and ResultRelInfo for trigger execution
+ */
+ resultRelInfo = makeNode(ResultRelInfo);
+ resultRelInfo->ri_RangeTableIndex = 1; /* dummy */
+ resultRelInfo->ri_RelationDesc = rel;
+ resultRelInfo->ri_TrigDesc = CopyTriggerDesc(rel->trigdesc);
+ if (resultRelInfo->ri_TrigDesc)
+ resultRelInfo->ri_TrigFunctions = (FmgrInfo *)
+ palloc0(resultRelInfo->ri_TrigDesc->numtriggers * sizeof(FmgrInfo));
+
+ /* We cannot EXPLAIN a TRUNCATE command, so no need to instrument */
+ resultRelInfo->ri_TrigInstrument = NULL;
+
+ estate->es_result_relations = resultRelInfo;
+ estate->es_num_result_relations = 1;
+ estate->es_result_relation_info = resultRelInfo;
+
+ AfterTriggerBeginQuery();
+
+ /*
+ * Process AFTER EACH STATEMENT trigger events and then execute them
+ */
+ ExecASTruncateTriggers(estate, estate->es_result_relation_info);
+
+ AfterTriggerEndQuery(estate);
+
+ if (resultRelInfo->ri_TrigDesc)
+ pfree(resultRelInfo->ri_TrigFunctions);
+ FreeExecutorState(estate);
+ }
}
/*
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.227
diff -c -r1.227 trigger.c
*** src/backend/commands/trigger.c 2 Jan 2008 23:34:42 -0000 1.227
--- src/backend/commands/trigger.c 30 Jan 2008 16:40:40 -0000
***************
*** 177,182 ****
--- 177,189 ----
errmsg("multiple UPDATE events specified")));
TRIGGER_SETT_UPDATE(tgtype);
break;
+ case 't':
+ if (TRIGGER_FOR_TRUNCATE(tgtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple TRUNCATE events specified")));
+ TRIGGER_SETT_TRUNCATE(tgtype);
+ break;
default:
elog(ERROR, "unrecognized trigger event: %d",
(int) stmt->actions[i]);
***************
*** 185,190 ****
--- 192,205 ----
}
/*
+ * Specifically disallow TRUNCATE triggers at ROW level
+ */
+ if (TRIGGER_FOR_ROW(tgtype) && TRIGGER_FOR_TRUNCATE(tgtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot fire TRUNCATE triggers FOR EACH ROW")));
+
+ /*
* Find and validate the trigger function.
*/
funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);
***************
*** 1297,1302 ****
--- 1312,1326 ----
(*tp)[n[TRIGGER_EVENT_UPDATE]] = indx;
(n[TRIGGER_EVENT_UPDATE])++;
}
+
+ if (TRIGGER_FOR_TRUNCATE(trigger->tgtype))
+ {
+ tp = &(t[TRIGGER_EVENT_TRUNCATE]);
+ if (*tp == NULL)
+ *tp = (int *) palloc(trigdesc->numtriggers * sizeof(int));
+ (*tp)[n[TRIGGER_EVENT_TRUNCATE]] = indx;
+ (n[TRIGGER_EVENT_TRUNCATE])++;
+ }
}
/*
***************
*** 2028,2033 ****
--- 2052,2138 ----
}
}
+ void
+ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
+ {
+ TriggerDesc *trigdesc;
+ int ntrigs;
+ int *tgindx;
+ int i;
+ TriggerData LocTriggerData;
+
+ trigdesc = relinfo->ri_TrigDesc;
+
+ if (trigdesc == NULL)
+ return;
+
+ ntrigs = trigdesc->n_before_statement[TRIGGER_EVENT_TRUNCATE];
+ tgindx = trigdesc->tg_before_statement[TRIGGER_EVENT_TRUNCATE];
+
+ if (ntrigs == 0)
+ return;
+
+ LocTriggerData.type = T_TriggerData;
+ LocTriggerData.tg_event = TRIGGER_EVENT_TRUNCATE |
+ TRIGGER_EVENT_BEFORE;
+ LocTriggerData.tg_relation = relinfo->ri_RelationDesc;
+ LocTriggerData.tg_trigtuple = NULL;
+ LocTriggerData.tg_newtuple = NULL;
+ LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
+ LocTriggerData.tg_newtuplebuf = InvalidBuffer;
+ for (i = 0; i < ntrigs; i++)
+ {
+ Trigger *trigger = &trigdesc->triggers[tgindx[i]];
+ HeapTuple newtuple;
+
+ if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+ {
+ if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+ trigger->tgenabled == TRIGGER_DISABLED)
+ continue;
+ }
+ else /* ORIGIN or LOCAL role */
+ {
+ if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+ trigger->tgenabled == TRIGGER_DISABLED)
+ continue;
+ }
+ LocTriggerData.tg_trigger = trigger;
+ newtuple = ExecCallTriggerFunc(&LocTriggerData,
+ tgindx[i],
+ relinfo->ri_TrigFunctions,
+ relinfo->ri_TrigInstrument,
+ GetPerTupleMemoryContext(estate));
+
+ #ifdef UNUSED_STATEMENT_SKIPPING_LOGIC
+ if (newtuple)
+ *do_trunc = false;
+ /*
+ * Note that we do *not* throw this error here
+ * ereport(ERROR,
+ * (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ * errmsg("BEFORE STATEMENT trigger cannot return a value")));
+ */
+ #else
+ if (newtuple)
+ ereport(ERROR,
+ (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("BEFORE STATEMENT trigger cannot return a value")));
+
+ #endif
+
+ }
+ }
+
+ void
+ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
+ {
+ TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+
+ if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0)
+ AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE,
+ false, NULL, NULL);
+ }
static HeapTuple
GetTupleForTrigger(EState *estate, ResultRelInfo *relinfo,
***************
*** 3570,3575 ****
--- 3675,3686 ----
elog(ERROR, "AfterTriggerSaveEvent() called outside of transaction");
/*
+ * event is used both as a bitmask and an array offset,
+ * so make sure we don't walk off the edge of our arrays
+ */
+ Assert(event >= 0 && event < TRIGGER_NUM_EVENT_CLASSES);
+
+ /*
* Get the CTID's of OLD and NEW
*/
if (oldtup != NULL)
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.605
diff -c -r2.605 gram.y
*** src/backend/parser/gram.y 1 Jan 2008 19:45:50 -0000 2.605
--- src/backend/parser/gram.y 29 Jan 2008 09:04:07 -0000
***************
*** 2717,2722 ****
--- 2717,2723 ----
INSERT { $$ = 'i'; }
| DELETE_P { $$ = 'd'; }
| UPDATE { $$ = 'u'; }
+ | TRUNCATE { $$ = 't'; }
;
TriggerForSpec:
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.269
diff -c -r1.269 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 6 Jan 2008 01:03:16 -0000 1.269
--- src/backend/utils/adt/ruleutils.c 29 Jan 2008 23:24:09 -0000
***************
*** 498,503 ****
--- 498,510 ----
else
appendStringInfo(&buf, " UPDATE");
}
+ if (TRIGGER_FOR_TRUNCATE(trigrec->tgtype))
+ {
+ if (findx > 0)
+ appendStringInfo(&buf, " OR TRUNCATE");
+ else
+ appendStringInfo(&buf, " TRUNCATE");
+ }
appendStringInfo(&buf, " ON %s ",
generate_relation_name(trigrec->tgrelid));
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.481
diff -c -r1.481 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 1 Jan 2008 19:45:55 -0000 1.481
--- src/bin/pg_dump/pg_dump.c 30 Jan 2008 17:00:09 -0000
***************
*** 9625,9630 ****
--- 9625,9637 ----
else
appendPQExpBuffer(query, " UPDATE");
}
+ if (TRIGGER_FOR_TRUNCATE(tginfo->tgtype))
+ {
+ if (findx > 0)
+ appendPQExpBuffer(query, " OR TRUNCATE");
+ else
+ appendPQExpBuffer(query, " TRUNCATE");
+ }
appendPQExpBuffer(query, " ON %s\n",
fmtId(tbinfo->dobj.name));
Index: src/include/catalog/pg_trigger.h
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/catalog/pg_trigger.h,v
retrieving revision 1.30
diff -c -r1.30 pg_trigger.h
*** src/include/catalog/pg_trigger.h 1 Jan 2008 19:45:57 -0000 1.30
--- src/include/catalog/pg_trigger.h 30 Jan 2008 10:58:43 -0000
***************
*** 94,99 ****
--- 94,100 ----
#define TRIGGER_TYPE_INSERT (1 << 2)
#define TRIGGER_TYPE_DELETE (1 << 3)
#define TRIGGER_TYPE_UPDATE (1 << 4)
+ #define TRIGGER_TYPE_TRUNCATE (1 << 5) /* patch note: no need for initdb */
/* Macros for manipulating tgtype */
#define TRIGGER_CLEAR_TYPE(type) ((type) = 0)
***************
*** 103,113 ****
--- 104,116 ----
#define TRIGGER_SETT_INSERT(type) ((type) |= TRIGGER_TYPE_INSERT)
#define TRIGGER_SETT_DELETE(type) ((type) |= TRIGGER_TYPE_DELETE)
#define TRIGGER_SETT_UPDATE(type) ((type) |= TRIGGER_TYPE_UPDATE)
+ #define TRIGGER_SETT_TRUNCATE(type) ((type) |= TRIGGER_TYPE_TRUNCATE)
#define TRIGGER_FOR_ROW(type) ((type) & TRIGGER_TYPE_ROW)
#define TRIGGER_FOR_BEFORE(type) ((type) & TRIGGER_TYPE_BEFORE)
#define TRIGGER_FOR_INSERT(type) ((type) & TRIGGER_TYPE_INSERT)
#define TRIGGER_FOR_DELETE(type) ((type) & TRIGGER_TYPE_DELETE)
#define TRIGGER_FOR_UPDATE(type) ((type) & TRIGGER_TYPE_UPDATE)
+ #define TRIGGER_FOR_TRUNCATE(type) ((type) & TRIGGER_TYPE_TRUNCATE)
#endif /* PG_TRIGGER_H */
Index: src/include/commands/trigger.h
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/commands/trigger.h,v
retrieving revision 1.66
diff -c -r1.66 trigger.h
*** src/include/commands/trigger.h 2 Jan 2008 23:34:42 -0000 1.66
--- src/include/commands/trigger.h 30 Jan 2008 13:22:55 -0000
***************
*** 38,48 ****
Buffer tg_newtuplebuf;
} TriggerData;
! /* TriggerEvent bit flags */
!
#define TRIGGER_EVENT_INSERT 0x00000000
#define TRIGGER_EVENT_DELETE 0x00000001
#define TRIGGER_EVENT_UPDATE 0x00000002
#define TRIGGER_EVENT_OPMASK 0x00000003
#define TRIGGER_EVENT_ROW 0x00000004
#define TRIGGER_EVENT_BEFORE 0x00000008
--- 38,66 ----
Buffer tg_newtuplebuf;
} TriggerData;
! /* TriggerEvent bit flags
! *
! * There are two distinct sets of DEFINEs associated with
! * trigger processing:
! *
! * TRIGGER_EVENT_*
! * defined in include/commands/trigger.h
! * used both as bitmasks for tg_type
! * and as array offsets within relcache arrays: be warned!
! *
! * The bitmasking only works if we never OR events together, which we
! * don't ever do, since a trigger is only invoked by one specific
! * event and hence just one event type, even if it could be invoked
! * by multiple event types.
! *
! * TRIGGER_TYPE_*
! * defined in include/catalog/pg_trigger.h
! * used as bitmaskable integers within pg_trigger
! */
#define TRIGGER_EVENT_INSERT 0x00000000
#define TRIGGER_EVENT_DELETE 0x00000001
#define TRIGGER_EVENT_UPDATE 0x00000002
+ #define TRIGGER_EVENT_TRUNCATE 0x00000003
#define TRIGGER_EVENT_OPMASK 0x00000003
#define TRIGGER_EVENT_ROW 0x00000004
#define TRIGGER_EVENT_BEFORE 0x00000008
***************
*** 66,71 ****
--- 84,93 ----
(((TriggerEvent) (event) & TRIGGER_EVENT_OPMASK) == \
TRIGGER_EVENT_UPDATE)
+ #define TRIGGER_FIRED_BY_TRUNCATE(event) \
+ (((TriggerEvent) (event) & TRIGGER_EVENT_OPMASK) == \
+ TRIGGER_EVENT_TRUNCATE)
+
#define TRIGGER_FIRED_FOR_ROW(event) \
((TriggerEvent) (event) & TRIGGER_EVENT_ROW)
***************
*** 141,146 ****
--- 163,173 ----
ItemPointer tupleid,
HeapTuple newtuple);
+ extern void ExecBSTruncateTriggers(EState *estate,
+ ResultRelInfo *relinfo);
+ extern void ExecASTruncateTriggers(EState *estate,
+ ResultRelInfo *relinfo);
+
extern void AfterTriggerBeginXact(void);
extern void AfterTriggerBeginQuery(void);
extern void AfterTriggerEndQuery(EState *estate);
Index: src/include/utils/rel.h
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/utils/rel.h,v
retrieving revision 1.104
diff -c -r1.104 rel.h
*** src/include/utils/rel.h 1 Jan 2008 19:45:59 -0000 1.104
--- src/include/utils/rel.h 30 Jan 2008 10:18:52 -0000
***************
*** 73,79 ****
* can appear in more than one class, for each class we provide a list of
* integer indexes into the triggers array.
*/
! #define TRIGGER_NUM_EVENT_CLASSES 3
uint16 n_before_statement[TRIGGER_NUM_EVENT_CLASSES];
uint16 n_before_row[TRIGGER_NUM_EVENT_CLASSES];
--- 73,79 ----
* can appear in more than one class, for each class we provide a list of
* integer indexes into the triggers array.
*/
! #define TRIGGER_NUM_EVENT_CLASSES 4
uint16 n_before_statement[TRIGGER_NUM_EVENT_CLASSES];
uint16 n_before_row[TRIGGER_NUM_EVENT_CLASSES];
Index: src/pl/plperl/plperl.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/pl/plperl/plperl.c,v
retrieving revision 1.136
diff -c -r1.136 plperl.c
*** src/pl/plperl/plperl.c 23 Jan 2008 00:55:47 -0000 1.136
--- src/pl/plperl/plperl.c 30 Jan 2008 11:16:01 -0000
***************
*** 689,694 ****
--- 689,696 ----
tupdesc));
}
}
+ else if (TRIGGER_FIRED_BY_TRUNCATE(tdata->tg_event))
+ event = "TRUNCATE";
else
event = "UNKNOWN";
***************
*** 1395,1400 ****
--- 1397,1404 ----
retval = (Datum) trigdata->tg_newtuple;
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
retval = (Datum) trigdata->tg_trigtuple;
+ else if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event))
+ retval = (Datum) trigdata->tg_trigtuple;
else
retval = (Datum) 0; /* can this happen? */
}
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.202
diff -c -r1.202 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 1 Jan 2008 19:46:00 -0000 1.202
--- src/pl/plpgsql/src/pl_exec.c 30 Jan 2008 10:28:51 -0000
***************
*** 524,530 ****
rec_old->tupdesc = trigdata->tg_relation->rd_att;
}
else
! elog(ERROR, "unrecognized trigger action: not INSERT, DELETE, or UPDATE");
/*
* Assign the special tg_ variables
--- 524,530 ----
rec_old->tupdesc = trigdata->tg_relation->rd_att;
}
else
! elog(ERROR, "unrecognized trigger action: not INSERT, DELETE, UPDATE or TRUNCATE");
/*
* Assign the special tg_ variables
***************
*** 537,544 ****
var->value = DirectFunctionCall1(textin, CStringGetDatum("UPDATE"));
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
var->value = DirectFunctionCall1(textin, CStringGetDatum("DELETE"));
else
! elog(ERROR, "unrecognized trigger action: not INSERT, DELETE, or UPDATE");
var->isnull = false;
var->freeval = true;
--- 537,546 ----
var->value = DirectFunctionCall1(textin, CStringGetDatum("UPDATE"));
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
var->value = DirectFunctionCall1(textin, CStringGetDatum("DELETE"));
+ else if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event))
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("TRUNCATE"));
else
! elog(ERROR, "unrecognized trigger action: not INSERT, DELETE, UPDATE or TRUNCATE");
var->isnull = false;
var->freeval = true;
Index: src/pl/plpython/plpython.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/pl/plpython/plpython.c,v
retrieving revision 1.106
diff -c -r1.106 plpython.c
*** src/pl/plpython/plpython.c 2 Jan 2008 03:10:27 -0000 1.106
--- src/pl/plpython/plpython.c 30 Jan 2008 11:17:56 -0000
***************
*** 714,719 ****
--- 714,721 ----
pltevent = PyString_FromString("DELETE");
else if (TRIGGER_FIRED_BY_UPDATE(tdata->tg_event))
pltevent = PyString_FromString("UPDATE");
+ else if (TRIGGER_FIRED_BY_TRUNCATE(tdata->tg_event))
+ pltevent = PyString_FromString("TRUNCATE");
else
{
elog(ERROR, "unrecognized OP tg_event: %u", tdata->tg_event);
Index: src/pl/tcl/pltcl.c
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/pl/tcl/pltcl.c,v
retrieving revision 1.117
diff -c -r1.117 pltcl.c
*** src/pl/tcl/pltcl.c 15 Nov 2007 21:14:46 -0000 1.117
--- src/pl/tcl/pltcl.c 30 Jan 2008 11:31:03 -0000
***************
*** 824,829 ****
--- 824,831 ----
Tcl_DStringAppendElement(&tcl_cmd, "DELETE");
else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
Tcl_DStringAppendElement(&tcl_cmd, "UPDATE");
+ else if (TRIGGER_FIRED_BY_TRUNCATE(trigdata->tg_event))
+ Tcl_DStringAppendElement(&tcl_cmd, "TRUNCATE");
else
elog(ERROR, "unrecognized OP tg_event: %u", trigdata->tg_event);
Index: src/test/regress/expected/truncate.out
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/test/regress/expected/truncate.out,v
retrieving revision 1.12
diff -c -r1.12 truncate.out
*** src/test/regress/expected/truncate.out 29 Jun 2006 16:07:29 -0000 1.12
--- src/test/regress/expected/truncate.out 30 Jan 2008 11:29:07 -0000
***************
*** 145,147 ****
--- 145,373 ----
NOTICE: drop cascades to constraint trunc_b_a_fkey on table trunc_b
NOTICE: drop cascades to constraint trunc_e_b_fkey on table trunc_e
NOTICE: drop cascades to constraint trunc_d_a_fkey on table trunc_d
+ -- TRUNCATE test runs much later than TRIGGER test, so just add a
+ -- few additional checks to ensure truncate triggers work
+ CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text);
+ CREATE TABLE trunc_trigger_log (tgop text, tglevel text, tgwhen text, tgargv text);
+ CREATE FUNCTION trunctrigger() RETURNS trigger as $$
+ begin
+ insert into trunc_trigger_log values (TG_OP, TG_LEVEL, TG_WHEN, TG_ARGV[0]);
+ return null;
+ end;
+ $$ LANGUAGE plpgsql;
+ INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar');
+ INSERT INTO trunc_trigger_test VALUES(2, 'baz', 'quux');
+ -- cannot have row level truncate triggers
+ CREATE TRIGGER t
+ BEFORE TRUNCATE ON trunc_trigger_test
+ FOR EACH ROW EXECUTE PROCEDURE trunctrigger();
+ ERROR: cannot fire TRUNCATE triggers FOR EACH ROW
+ -- cannot have row level truncate triggers
+ CREATE TRIGGER t
+ AFTER TRUNCATE OR INSERT ON trunc_trigger_test
+ FOR EACH ROW EXECUTE PROCEDURE trunctrigger();
+ ERROR: cannot fire TRUNCATE triggers FOR EACH ROW
+ -- duplicate event error
+ CREATE TRIGGER t
+ AFTER TRUNCATE OR TRUNCATE ON trunc_trigger_test
+ EXECUTE PROCEDURE trunctrigger();
+ ERROR: multiple TRUNCATE events specified
+ -- basic before trigger
+ CREATE TRIGGER t
+ BEFORE TRUNCATE ON trunc_trigger_test
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE trunctrigger('before trigger truncate');
+ \d trunc_trigger_test
+ Table "public.trunc_trigger_test"
+ Column | Type | Modifiers
+ --------+---------+-----------
+ f1 | integer |
+ f2 | text |
+ f3 | text |
+ Triggers:
+ t BEFORE TRUNCATE ON trunc_trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('before trigger truncate')
+
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ Row count in test table
+ -------------------------
+ 2
+ (1 row)
+
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ ------+---------+--------+--------
+ (0 rows)
+
+ TRUNCATE trunc_trigger_test;
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ Row count in test table
+ -------------------------
+ 0
+ (1 row)
+
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ ----------+-----------+--------+-------------------------
+ TRUNCATE | STATEMENT | BEFORE | before trigger truncate
+ (1 row)
+
+ DROP TRIGGER t ON trunc_trigger_test;
+ -- mixed before/after trigger
+ truncate trunc_trigger_log;
+ CREATE TRIGGER ti
+ BEFORE insert ON trunc_trigger_test
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE trunctrigger('before trigger insert');
+ INSERT INTO trunc_trigger_test VALUES(3, 'baz', 'quux');
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ --------+-----------+--------+-----------------------
+ INSERT | STATEMENT | BEFORE | before trigger insert
+ (1 row)
+
+ CREATE TRIGGER tt
+ AFTER TRUNCATE ON trunc_trigger_test
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE trunctrigger('after trigger truncate');
+ \d trunc_trigger_test
+ Table "public.trunc_trigger_test"
+ Column | Type | Modifiers
+ --------+---------+-----------
+ f1 | integer |
+ f2 | text |
+ f3 | text |
+ Triggers:
+ ti BEFORE INSERT ON trunc_trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('before trigger insert')
+ tt AFTER TRUNCATE ON trunc_trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('after trigger truncate')
+
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ Row count in test table
+ -------------------------
+ 1
+ (1 row)
+
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ --------+-----------+--------+-----------------------
+ INSERT | STATEMENT | BEFORE | before trigger insert
+ (1 row)
+
+ TRUNCATE trunc_trigger_test;
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ Row count in test table
+ -------------------------
+ 0
+ (1 row)
+
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ ----------+-----------+--------+------------------------
+ INSERT | STATEMENT | BEFORE | before trigger insert
+ TRUNCATE | STATEMENT | AFTER | after trigger truncate
+ (2 rows)
+
+ DROP TRIGGER ti ON trunc_trigger_test;
+ DROP TRIGGER tt ON trunc_trigger_test;
+ -- multiple tables, multiple triggers
+ TRUNCATE trunc_trigger_log;
+ CREATE TABLE trunc_trigger_test2 (f1 int, f2 text);
+ CREATE TRIGGER t1b
+ BEFORE TRUNCATE ON trunc_trigger_test
+ EXECUTE PROCEDURE trunctrigger('t1b');
+ CREATE TRIGGER t2b
+ BEFORE TRUNCATE ON trunc_trigger_test2
+ EXECUTE PROCEDURE trunctrigger('t2b');
+ CREATE TRIGGER t1a
+ AFTER TRUNCATE OR INSERT ON trunc_trigger_test
+ EXECUTE PROCEDURE trunctrigger('t1a');
+ CREATE TRIGGER t2at
+ AFTER TRUNCATE ON trunc_trigger_test2
+ EXECUTE PROCEDURE trunctrigger('t2at');
+ CREATE TRIGGER t2ai
+ AFTER INSERT ON trunc_trigger_test2
+ EXECUTE PROCEDURE trunctrigger('t2ai');
+ \d trunc_trigger_test
+ Table "public.trunc_trigger_test"
+ Column | Type | Modifiers
+ --------+---------+-----------
+ f1 | integer |
+ f2 | text |
+ f3 | text |
+ Triggers:
+ t1a AFTER INSERT OR TRUNCATE ON trunc_trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('t1a')
+ t1b BEFORE TRUNCATE ON trunc_trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('t1b')
+
+ \d trunc_trigger_test2
+ Table "public.trunc_trigger_test2"
+ Column | Type | Modifiers
+ --------+---------+-----------
+ f1 | integer |
+ f2 | text |
+ Triggers:
+ t2ai AFTER INSERT ON trunc_trigger_test2 FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('t2ai')
+ t2at AFTER TRUNCATE ON trunc_trigger_test2 FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('t2at')
+ t2b BEFORE TRUNCATE ON trunc_trigger_test2 FOR EACH STATEMENT EXECUTE PROCEDURE trunctrigger('t2b')
+
+ INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar');
+ INSERT INTO trunc_trigger_test VALUES(2, 'baz', 'quux');
+ INSERT INTO trunc_trigger_test2 VALUES(1, 'foo');
+ INSERT INTO trunc_trigger_test2 VALUES(2, 'bar');
+ INSERT INTO trunc_trigger_test2 VALUES(3, 'baz');
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ Row count in test table
+ -------------------------
+ 2
+ (1 row)
+
+ SELECT count(*) as "Row count in test table2" FROM trunc_trigger_test2;
+ Row count in test table2
+ --------------------------
+ 3
+ (1 row)
+
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ --------+-----------+--------+--------
+ INSERT | STATEMENT | AFTER | t1a
+ INSERT | STATEMENT | AFTER | t1a
+ INSERT | STATEMENT | AFTER | t2ai
+ INSERT | STATEMENT | AFTER | t2ai
+ INSERT | STATEMENT | AFTER | t2ai
+ (5 rows)
+
+ TRUNCATE trunc_trigger_test, trunc_trigger_test2;
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ Row count in test table
+ -------------------------
+ 0
+ (1 row)
+
+ SELECT count(*) as "Row count in test table2" FROM trunc_trigger_test2;
+ Row count in test table2
+ --------------------------
+ 0
+ (1 row)
+
+ SELECT * FROM trunc_trigger_log;
+ tgop | tglevel | tgwhen | tgargv
+ ----------+-----------+--------+--------
+ INSERT | STATEMENT | AFTER | t1a
+ INSERT | STATEMENT | AFTER | t1a
+ INSERT | STATEMENT | AFTER | t2ai
+ INSERT | STATEMENT | AFTER | t2ai
+ INSERT | STATEMENT | AFTER | t2ai
+ TRUNCATE | STATEMENT | BEFORE | t1b
+ TRUNCATE | STATEMENT | BEFORE | t2b
+ TRUNCATE | STATEMENT | AFTER | t1a
+ TRUNCATE | STATEMENT | AFTER | t2at
+ (9 rows)
+
+ DROP TRIGGER t1b ON trunc_trigger_test;
+ DROP TRIGGER t2b ON trunc_trigger_test2;
+ DROP TRIGGER t1a ON trunc_trigger_test;
+ DROP TRIGGER t2at ON trunc_trigger_test2;
+ DROP TRIGGER t2ai ON trunc_trigger_test2;
+ DROP TABLE trunc_trigger_test;
+ DROP TABLE trunc_trigger_test2;
+ DROP TABLE trunc_trigger_log;
+ DROP FUNCTION trunctrigger();
Index: src/test/regress/sql/truncate.sql
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/test/regress/sql/truncate.sql,v
retrieving revision 1.5
diff -c -r1.5 truncate.sql
*** src/test/regress/sql/truncate.sql 29 Jun 2006 16:07:29 -0000 1.5
--- src/test/regress/sql/truncate.sql 30 Jan 2008 10:56:18 -0000
***************
*** 77,79 ****
--- 77,206 ----
SELECT * FROM trunc_e;
DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
+
+ -- TRUNCATE test runs much later than TRIGGER test, so just add a
+ -- few additional checks to ensure truncate triggers work
+
+ CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text);
+ CREATE TABLE trunc_trigger_log (tgop text, tglevel text, tgwhen text, tgargv text);
+
+ CREATE FUNCTION trunctrigger() RETURNS trigger as $$
+ begin
+ insert into trunc_trigger_log values (TG_OP, TG_LEVEL, TG_WHEN, TG_ARGV[0]);
+ return null;
+ end;
+ $$ LANGUAGE plpgsql;
+
+ INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar');
+ INSERT INTO trunc_trigger_test VALUES(2, 'baz', 'quux');
+
+ -- cannot have row level truncate triggers
+ CREATE TRIGGER t
+ BEFORE TRUNCATE ON trunc_trigger_test
+ FOR EACH ROW EXECUTE PROCEDURE trunctrigger();
+
+ -- cannot have row level truncate triggers
+ CREATE TRIGGER t
+ AFTER TRUNCATE OR INSERT ON trunc_trigger_test
+ FOR EACH ROW EXECUTE PROCEDURE trunctrigger();
+
+ -- duplicate event error
+ CREATE TRIGGER t
+ AFTER TRUNCATE OR TRUNCATE ON trunc_trigger_test
+ EXECUTE PROCEDURE trunctrigger();
+
+ -- basic before trigger
+ CREATE TRIGGER t
+ BEFORE TRUNCATE ON trunc_trigger_test
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE trunctrigger('before trigger truncate');
+
+ \d trunc_trigger_test
+
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ SELECT * FROM trunc_trigger_log;
+ TRUNCATE trunc_trigger_test;
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ SELECT * FROM trunc_trigger_log;
+
+ DROP TRIGGER t ON trunc_trigger_test;
+
+ -- mixed before/after trigger
+ truncate trunc_trigger_log;
+ CREATE TRIGGER ti
+ BEFORE insert ON trunc_trigger_test
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE trunctrigger('before trigger insert');
+
+ INSERT INTO trunc_trigger_test VALUES(3, 'baz', 'quux');
+ SELECT * FROM trunc_trigger_log;
+
+ CREATE TRIGGER tt
+ AFTER TRUNCATE ON trunc_trigger_test
+ FOR EACH STATEMENT
+ EXECUTE PROCEDURE trunctrigger('after trigger truncate');
+
+ \d trunc_trigger_test
+
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ SELECT * FROM trunc_trigger_log;
+ TRUNCATE trunc_trigger_test;
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ SELECT * FROM trunc_trigger_log;
+
+ DROP TRIGGER ti ON trunc_trigger_test;
+ DROP TRIGGER tt ON trunc_trigger_test;
+
+ -- multiple tables, multiple triggers
+ TRUNCATE trunc_trigger_log;
+ CREATE TABLE trunc_trigger_test2 (f1 int, f2 text);
+
+ CREATE TRIGGER t1b
+ BEFORE TRUNCATE ON trunc_trigger_test
+ EXECUTE PROCEDURE trunctrigger('t1b');
+
+ CREATE TRIGGER t2b
+ BEFORE TRUNCATE ON trunc_trigger_test2
+ EXECUTE PROCEDURE trunctrigger('t2b');
+
+ CREATE TRIGGER t1a
+ AFTER TRUNCATE OR INSERT ON trunc_trigger_test
+ EXECUTE PROCEDURE trunctrigger('t1a');
+
+ CREATE TRIGGER t2at
+ AFTER TRUNCATE ON trunc_trigger_test2
+ EXECUTE PROCEDURE trunctrigger('t2at');
+
+ CREATE TRIGGER t2ai
+ AFTER INSERT ON trunc_trigger_test2
+ EXECUTE PROCEDURE trunctrigger('t2ai');
+
+ \d trunc_trigger_test
+ \d trunc_trigger_test2
+
+ INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar');
+ INSERT INTO trunc_trigger_test VALUES(2, 'baz', 'quux');
+
+ INSERT INTO trunc_trigger_test2 VALUES(1, 'foo');
+ INSERT INTO trunc_trigger_test2 VALUES(2, 'bar');
+ INSERT INTO trunc_trigger_test2 VALUES(3, 'baz');
+
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ SELECT count(*) as "Row count in test table2" FROM trunc_trigger_test2;
+ SELECT * FROM trunc_trigger_log;
+ TRUNCATE trunc_trigger_test, trunc_trigger_test2;
+ SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
+ SELECT count(*) as "Row count in test table2" FROM trunc_trigger_test2;
+ SELECT * FROM trunc_trigger_log;
+
+ DROP TRIGGER t1b ON trunc_trigger_test;
+ DROP TRIGGER t2b ON trunc_trigger_test2;
+ DROP TRIGGER t1a ON trunc_trigger_test;
+ DROP TRIGGER t2at ON trunc_trigger_test2;
+ DROP TRIGGER t2ai ON trunc_trigger_test2;
+
+ DROP TABLE trunc_trigger_test;
+ DROP TABLE trunc_trigger_test2;
+ DROP TABLE trunc_trigger_log;
+
+ DROP FUNCTION trunctrigger();
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
