Hi Hackers,
Attached is a new version of this patch. I resurrected it after removing it
from the commitfest last year; I'll add it back in to the next CF.
The main change is a switch to using SPI for expression evaluation. The plans
are also cached along the same lines as the RI trigger plans.
Some random thoughts on the allowable expressions:
a. I originally disallowed functions and table-valued functions from appearing
in the expression as they could potentially do anything and everything.
However, I noticed that we allow functions in FOR EACH ROW triggers so we are
already in that position. Do we want to continue allowing that in FOR EACH
STATEMENT triggers? If so, then the choice to restrict the expression to just
OLD, NEW and the table being triggered against might be wrong.
b. If a WHEN expression is defined as "n = (SELECT ...)", there is the
possibility that a user gets the error "more than one row returned by a
subquery used as an expression" when performing DML, which would be rather
cryptic if they didn't know there was a trigger involved. To avoid this, we
could disallow scalar expressions, with a hint to use the ANY/ALL quantifiers.
-Joe
From 32cc660e51dc8a157e98cf3f1862fc149b4f68ea Mon Sep 17 00:00:00 2001
From: Joe Wildish <j...@sql.dev>
Date: Wed, 2 Jun 2021 12:48:34 +0100
Subject: [PATCH] Allow queries in WHEN expression of FOR EACH STATEMENT
triggers
Adds support to the trigger system to allow queries in the WHEN condition
of FOR EACH STATEMENT triggers. The expression can contain references to
the transition tables NEW and OLD, as well as the table which the
trigger is attached to, but other table references are disallowed.
---
doc/src/sgml/ref/create_trigger.sgml | 45 +-
doc/src/sgml/trigger.sgml | 7 +-
src/backend/commands/tablecmds.c | 2 +
src/backend/commands/trigger.c | 852 ++++++++++++++++++++-----
src/backend/parser/parse_expr.c | 4 +-
src/backend/utils/adt/ruleutils.c | 100 +--
src/include/nodes/execnodes.h | 2 +-
src/include/utils/reltrigger.h | 1 +
src/test/regress/expected/triggers.out | 66 +-
src/test/regress/sql/triggers.sql | 57 ++
10 files changed, 898 insertions(+), 238 deletions(-)
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 561af989a4..47f9a65fe4 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -160,13 +160,14 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
</informaltable>
<para>
- Also, a trigger definition can specify a Boolean <literal>WHEN</literal>
- condition, which will be tested to see whether the trigger should
+ A trigger definition can specify a Boolean <literal>WHEN</literal>
+ condition which will be tested to see whether the trigger should
be fired. In row-level triggers the <literal>WHEN</literal> condition can
- examine the old and/or new values of columns of the row. Statement-level
- triggers can also have <literal>WHEN</literal> conditions, although the feature
- is not so useful for them since the condition cannot refer to any values
- in the table.
+ examine the old and/or new values of the columns of each row which the
+ statement affects. Statement-level triggers can also have
+ <literal>WHEN</literal> conditions, and are able to examine old and/or new
+ transition relations, that comprise of all rows either deleted or inserted
+ respectively by the triggering statement.
</para>
<para>
@@ -375,23 +376,41 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
will actually be executed. If <literal>WHEN</literal> is specified, the
function will only be called if the <replaceable
class="parameter">condition</replaceable> returns <literal>true</literal>.
- In <literal>FOR EACH ROW</literal> triggers, the <literal>WHEN</literal>
+ </para>
+
+ <para>
+ In <literal>FOR EACH ROW</literal> triggers the <literal>WHEN</literal>
condition can refer to columns of the old and/or new row values
by writing <literal>OLD.<replaceable
class="parameter">column_name</replaceable></literal> or
<literal>NEW.<replaceable
class="parameter">column_name</replaceable></literal> respectively.
- Of course, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
- and <literal>DELETE</literal> triggers cannot refer to <literal>NEW</literal>.
+ The <literal>WHEN</literal> expression of a <literal>FOR EACH ROW</literal>
+ trigger cannot contain a subquery.
</para>
- <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
- conditions.
+ <para>
+ In <literal>FOR EACH STATEMENT</literal> triggers the
+ <literal>WHEN</literal> condition can refer to the transition relations
+ <literal>OLD</literal> and <literal>NEW</literal>, and the relation that the
+ trigger is for. No other relations can be referenced. As <literal>OLD</literal>
+ and <literal>NEW</literal> are relations rather than row values, a
+ <replaceable class="parameter">condition</replaceable> will typically comprise of
+ subquery expressions defined over those relations. Refer to
+ <xref linkend="functions-subquery"/> for subquery expression examples.
</para>
<para>
- Currently, <literal>WHEN</literal> expressions cannot contain
- subqueries.
+ In both <literal>FOR EACH ROW</literal> and <literal>FOR EACH STATEMENT</literal>
+ triggers, <literal>INSERT</literal> triggers cannot refer to <literal>OLD</literal>
+ row values or transition tables, and <literal>DELETE</literal> triggers cannot refer
+ to <literal>NEW</literal> row values or transition tables. However,
+ <literal>UPDATE</literal> triggers are able to refer to both <literal>OLD</literal>
+ and <literal>NEW</literal>
+ </para>
+
+ <para><literal>INSTEAD OF</literal> triggers do not support <literal>WHEN</literal>
+ conditions.
</para>
<para>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 4a0e74652f..6161b0c0e5 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -277,10 +277,9 @@
A trigger definition can also specify a Boolean <literal>WHEN</literal>
condition, which will be tested to see whether the trigger should
be fired. In row-level triggers the <literal>WHEN</literal> condition can
- examine the old and/or new values of columns of the row. (Statement-level
- triggers can also have <literal>WHEN</literal> conditions, although the feature
- is not so useful for them.) In a <literal>BEFORE</literal> trigger, the
- <literal>WHEN</literal>
+ examine the old and/or new values of columns of the row, whereas statement-level
+ triggers can examine old and/or new relations that contain the relevant rows.
+ In a <literal>BEFORE</literal> trigger, the <literal>WHEN</literal>
condition is evaluated just before the function is or would be executed,
so using <literal>WHEN</literal> is not materially different from testing the
same condition at the beginning of the trigger function. However, in
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 028e8ac46b..45821c8590 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11005,6 +11005,7 @@ validateForeignKeyConstraint(char *conname,
*/
MemSet(&trig, 0, sizeof(trig));
trig.tgoid = InvalidOid;
+ trig.tgrelid = InvalidOid;
trig.tgname = conname;
trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trig.tgisinternal = true;
@@ -18559,6 +18560,7 @@ ATDetachCheckNoForeignKeyRefs(Relation partition)
MemSet(&trig, 0, sizeof(trig));
trig.tgoid = InvalidOid;
+ trig.tgrelid = InvalidOid;
trig.tgname = NameStr(constrForm->conname);
trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trig.tgisinternal = true;
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 07c73f39de..7290f8c51c 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -36,10 +36,11 @@
#include "commands/trigger.h"
#include "executor/executor.h"
#include "executor/execPartition.h"
+#include "executor/spi.h"
#include "miscadmin.h"
#include "nodes/bitmapset.h"
+#include "nodes/nodeFuncs.h"
#include "nodes/makefuncs.h"
-#include "optimizer/optimizer.h"
#include "parser/parse_clause.h"
#include "parser/parse_collate.h"
#include "parser/parse_func.h"
@@ -59,10 +60,12 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/tuplestore.h"
+#define WHENQUERYCACHE_INIT_SIZE 64
/* GUC variables */
int SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
@@ -70,6 +73,25 @@ int SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
/* How many levels deep into trigger execution are we? */
static int MyTriggerDepth = 0;
+/* Context for validating a trigger's WHEN expression */
+typedef struct ValidateWhenExprContext
+{
+ ParseState *pstate; /* expression parse state */
+ Relation rel; /* relation being triggered against */
+ int16 tgtype; /* trigger type data */
+ char *oldtablename; /* declared name for OLD */
+ char *newtablename; /* declared name for NEW */
+ List *rtable; /* current range table, if any */
+} ValidateWhenExprContext;
+
+typedef struct WhenQueryCacheEntry {
+ Oid tgoid; /* trigger oid */
+ SPIPlanPtr plan; /* prepared plan for WHEN expression */
+} WhenQueryCacheEntry;
+
+/* Local data */
+static HTAB *WhenQueryCache = NULL;
+
/* Local function prototypes */
static void SetTriggerFlags(TriggerDesc *trigdesc, Trigger *trigger);
static bool GetTupleForTrigger(EState *estate,
@@ -82,7 +104,15 @@ static bool GetTupleForTrigger(EState *estate,
static bool TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
Trigger *trigger, TriggerEvent event,
Bitmapset *modifiedCols,
- TupleTableSlot *oldslot, TupleTableSlot *newslot);
+ TupleTableSlot *oldslot, TupleTableSlot *newslot,
+ Tuplestorestate *oldstore, Tuplestorestate *newstore);
+static bool EvalARWhenExpr(EState *estate, ResultRelInfo *relinfo,
+ Trigger *trigger,
+ TupleTableSlot *oldslot,
+ TupleTableSlot *newslot);
+static bool EvalASWhenExpr(Trigger *trigger,
+ Tuplestorestate *oldstore,
+ Tuplestorestate *newstore);
static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata,
int tgindx,
FmgrInfo *finfo,
@@ -95,6 +125,21 @@ static void AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture);
static void AfterTriggerEnlargeQueryState(void);
static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void WhenQueryCache_Init(void);
+static SPIPlanPtr WhenQueryCache_FetchPlan(Oid key);
+static void WhenQueryCache_StorePlan(Oid key, SPIPlanPtr plan);
+static ParseState *makeWhenExprParseState(const char *queryString, int16 tgtype,
+ Relation rel, char *oldtablename,
+ char *newtablename);
+static void freeWhenExprParseState(ParseState *pstate,
+ char *oldtablename, char *newtablename);
+static void validateWhenExpr(ParseState *pstate, Node *whenClause,
+ Relation rel, int16 tgtype,
+ char *oldtablename, char *newtablename);
+static bool validateWhenExpr_Walker(Node *node,
+ ValidateWhenExprContext *ctx);
+static EphemeralNamedRelation makeENR(char *name, Oid relid,
+ Tuplestorestate *data);
/*
@@ -178,6 +223,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
bool trigger_exists = false;
Oid existing_constraint_oid = InvalidOid;
bool existing_isInternal = false;
+ bool recordDeps = false;
if (OidIsValid(relOid))
rel = table_open(relOid, ShareRowExclusiveLock);
@@ -541,125 +587,57 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
*/
if (!whenClause && stmt->whenClause)
{
- ParseState *pstate;
- ParseNamespaceItem *nsitem;
- List *varList;
- ListCell *lc;
-
- /* Set up a pstate to parse with */
- pstate = make_parsestate(NULL);
- pstate->p_sourcetext = queryString;
+ ParseState *pstate;
/*
- * Set up nsitems for OLD and NEW references.
- *
- * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+ * Setup the parse state. We will re-use the rtable from the parse
+ * state afterwards to help record dependencies.
*/
- nsitem = addRangeTableEntryForRelation(pstate, rel,
- AccessShareLock,
- makeAlias("old", NIL),
- false, false);
- addNSItemToQuery(pstate, nsitem, false, true, true);
- nsitem = addRangeTableEntryForRelation(pstate, rel,
- AccessShareLock,
- makeAlias("new", NIL),
- false, false);
- addNSItemToQuery(pstate, nsitem, false, true, true);
+ pstate = makeWhenExprParseState(queryString, tgtype, rel,
+ oldtablename, newtablename);
+
+ /* Copy to be sure we don't modify original when validating. */
+ whenClause = copyObject(stmt->whenClause);
- /* Transform expression. Copy to be sure we don't modify original */
+ /* Transform expression. */
whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
+ whenClause,
EXPR_KIND_TRIGGER_WHEN,
"WHEN");
- /* we have to fix its collations too */
+
+ /* We have to fix its collations too. */
assign_expr_collations(pstate, whenClause);
/*
- * Check for disallowed references to OLD/NEW.
+ * Check for disallowed references to OLD, NEW, or other relations.
*
- * NB: pull_var_clause is okay here only because we don't allow
- * subselects in WHEN clauses; it would fail to examine the contents
- * of subselects.
+ * If we are a FOR EACH ROW trigger then the use of OLD/NEW is as a
+ * row variable. If we are a FOR EACH STATEMENT trigger then OLD/NEW
+ * are tables. Any expressions in WHEN should be written accordingly.
*/
- varList = pull_var_clause(whenClause, 0);
- foreach(lc, varList)
- {
- Var *var = (Var *) lfirst(lc);
-
- switch (var->varno)
- {
- case PRS2_OLD_VARNO:
- if (!TRIGGER_FOR_ROW(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("statement trigger's WHEN condition cannot reference column values"),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_INSERT(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
- parser_errposition(pstate, var->location)));
- /* system columns are okay here */
- break;
- case PRS2_NEW_VARNO:
- if (!TRIGGER_FOR_ROW(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("statement trigger's WHEN condition cannot reference column values"),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_DELETE(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
- parser_errposition(pstate, var->location)));
- if (var->varattno < 0 && TRIGGER_FOR_BEFORE(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_BEFORE(tgtype) &&
- var->varattno == 0 &&
- RelationGetDescr(rel)->constr &&
- RelationGetDescr(rel)->constr->has_generated_stored)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
- errdetail("A whole-row reference is used and the table contains generated columns."),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_BEFORE(tgtype) &&
- var->varattno > 0 &&
- TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
- errdetail("Column \"%s\" is a generated column.",
- NameStr(TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attname)),
- parser_errposition(pstate, var->location)));
- break;
- default:
- /* can't happen without add_missing_from, so just elog */
- elog(ERROR, "trigger WHEN condition cannot contain references to other relations");
- break;
- }
- }
+ validateWhenExpr(pstate, whenClause, rel, tgtype,
+ oldtablename, newtablename);
- /* we'll need the rtable for recordDependencyOnExpr */
+ /* Keep the rtable for recordDependencyOnExpr for ROW triggers. */
whenRtable = pstate->p_rtable;
-
qual = nodeToString(whenClause);
+ recordDeps = true;
- free_parsestate(pstate);
+ /* Free parse state stuff */
+ freeWhenExprParseState(pstate, oldtablename, newtablename);
}
else if (!whenClause)
{
whenClause = NULL;
whenRtable = NIL;
qual = NULL;
+ recordDeps = false;
}
else
{
qual = nodeToString(whenClause);
whenRtable = NIL;
+ recordDeps = false;
}
/*
@@ -1106,9 +1084,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* If it has a WHEN clause, add dependencies on objects mentioned in the
* expression (eg, functions, as well as any columns used).
*/
- if (whenRtable != NIL)
+ if (recordDeps)
+ {
+ Assert((TRIGGER_FOR_ROW(tgtype) && whenRtable != NULL) ||
+ (!TRIGGER_FOR_ROW(tgtype) && whenRtable == NULL));
recordDependencyOnExpr(&myself, whenClause, whenRtable,
DEPENDENCY_NORMAL);
+ }
/* Post creation hook for new trigger */
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
@@ -1684,6 +1666,7 @@ RelationBuildTriggers(Relation relation)
build = &(triggers[numtrigs]);
build->tgoid = pg_trigger->oid;
+ build->tgrelid = pg_trigger->tgrelid;
build->tgname = DatumGetCString(DirectFunctionCall1(nameout,
NameGetDatum(&pg_trigger->tgname)));
build->tgfoid = pg_trigger->tgfoid;
@@ -2204,7 +2187,7 @@ ExecBSInsertTriggers(EState *estate, ResultRelInfo *relinfo)
TRIGGER_TYPE_INSERT))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, NULL, NULL))
+ NULL, NULL, NULL, NULL, NULL))
continue;
LocTriggerData.tg_trigger = trigger;
@@ -2258,7 +2241,7 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_TYPE_INSERT))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, NULL, slot))
+ NULL, NULL, slot, NULL, NULL))
continue;
if (!newtuple)
@@ -2314,10 +2297,11 @@ ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+ int event = TRIGGER_EVENT_INSERT | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
if ((trigdesc && trigdesc->trig_insert_after_row) ||
(transition_capture && transition_capture->tcs_insert_new_table))
- AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_INSERT,
+ AfterTriggerSaveEvent(estate, relinfo, event,
true, NULL, slot,
recheckIndexes, NULL,
transition_capture);
@@ -2349,7 +2333,7 @@ ExecIRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_TYPE_INSERT))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, NULL, slot))
+ NULL, NULL, slot, NULL, NULL))
continue;
if (!newtuple)
@@ -2418,7 +2402,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo)
TRIGGER_TYPE_DELETE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, NULL, NULL))
+ NULL, NULL, NULL, NULL, NULL))
continue;
LocTriggerData.tg_trigger = trigger;
@@ -2513,7 +2497,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate,
TRIGGER_TYPE_DELETE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, slot, NULL))
+ NULL, slot, NULL, NULL, NULL))
continue;
LocTriggerData.tg_trigslot = slot;
@@ -2545,6 +2529,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+ int event = TRIGGER_EVENT_DELETE | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
if ((trigdesc && trigdesc->trig_delete_after_row) ||
(transition_capture && transition_capture->tcs_delete_old_table))
@@ -2563,7 +2548,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
else
ExecForceStoreHeapTuple(fdw_trigtuple, slot, false);
- AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_DELETE,
+ AfterTriggerSaveEvent(estate, relinfo, event,
true, slot, NULL, NIL, NULL,
transition_capture);
}
@@ -2597,7 +2582,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_TYPE_DELETE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, slot, NULL))
+ NULL, slot, NULL, NULL, NULL))
continue;
LocTriggerData.tg_trigslot = slot;
@@ -2657,7 +2642,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
TRIGGER_TYPE_UPDATE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- updatedCols, NULL, NULL))
+ updatedCols, NULL, NULL, NULL, NULL))
continue;
LocTriggerData.tg_trigger = trigger;
@@ -2771,7 +2756,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
TRIGGER_TYPE_UPDATE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- updatedCols, oldslot, newslot))
+ updatedCols, oldslot, newslot, NULL, NULL))
continue;
if (!newtuple)
@@ -2831,6 +2816,10 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
TransitionCaptureState *transition_capture)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
+ TupleTableSlot *oldslot = ExecGetTriggerOldSlot(estate, relinfo);
+ int event = TRIGGER_EVENT_UPDATE | TRIGGER_EVENT_ROW | TRIGGER_EVENT_AFTER;
+
+ ExecClearTuple(oldslot);
if ((trigdesc && trigdesc->trig_update_after_row) ||
(transition_capture &&
@@ -2858,7 +2847,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
else
ExecClearTuple(oldslot);
- AfterTriggerSaveEvent(estate, relinfo, TRIGGER_EVENT_UPDATE,
+ AfterTriggerSaveEvent(estate, relinfo, event,
true, oldslot, newslot, recheckIndexes,
ExecGetAllUpdatedCols(relinfo, estate),
transition_capture);
@@ -2895,7 +2884,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo,
TRIGGER_TYPE_UPDATE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, oldslot, newslot))
+ NULL, oldslot, newslot, NULL, NULL))
continue;
if (!newtuple)
@@ -2961,7 +2950,7 @@ ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo)
TRIGGER_TYPE_TRUNCATE))
continue;
if (!TriggerEnabled(estate, relinfo, trigger, LocTriggerData.tg_event,
- NULL, NULL, NULL))
+ NULL, NULL, NULL, NULL, NULL))
continue;
LocTriggerData.tg_trigger = trigger;
@@ -3112,7 +3101,8 @@ static bool
TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
Trigger *trigger, TriggerEvent event,
Bitmapset *modifiedCols,
- TupleTableSlot *oldslot, TupleTableSlot *newslot)
+ TupleTableSlot *oldslot, TupleTableSlot *newslot,
+ Tuplestorestate *oldstore, Tuplestorestate *newstore)
{
/* Check replication-role-dependent enable state */
if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
@@ -3151,60 +3141,194 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
return false;
}
- /* Check for WHEN clause */
- if (trigger->tgqual)
+ /*
+ * Evaluate the WHEN clause for a FOR EACH ROW trigger, if any.
+ */
+ if (TRIGGER_FIRED_FOR_ROW(event) && trigger->tgqual)
+ return EvalARWhenExpr(estate, relinfo, trigger,
+ oldslot, newslot);
+
+ /*
+ * Evaluate the WHEN clause for a FOR EACH STATEMENT trigger, if any.
+ */
+ if (TRIGGER_FIRED_FOR_STATEMENT(event) && trigger->tgqual)
+ return EvalASWhenExpr(trigger, oldstore, newstore);
+
+ return true;
+}
+
+
+static bool
+EvalARWhenExpr(EState *estate, ResultRelInfo *relinfo,
+ Trigger *trigger,
+ TupleTableSlot *oldslot,
+ TupleTableSlot *newslot)
+{
+ ExprState **predicate;
+ ExprContext *econtext;
+ MemoryContext oldContext;
+ int i;
+
+ Assert(estate != NULL);
+
+ /*
+ * trigger is an element of relinfo->ri_TrigDesc->triggers[]; find the
+ * matching element of relinfo->ri_TrigWhenExprs[]
+ */
+ i = trigger - relinfo->ri_TrigDesc->triggers;
+ predicate = &relinfo->ri_TrigWhenExprs[i];
+
+ /*
+ * If first time through for this WHEN expression, build expression
+ * nodetrees for it. Keep them in the per-query memory context so
+ * they'll survive throughout the query.
+ */
+ if (*predicate == NULL)
{
- ExprState **predicate;
- ExprContext *econtext;
- MemoryContext oldContext;
- int i;
+ Node *tgqual;
+
+ oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
+ tgqual = stringToNode(trigger->tgqual);
+ /* Change references to OLD and NEW to INNER_VAR and OUTER_VAR */
+ ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER_VAR, 0);
+ ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER_VAR, 0);
+ /* ExecPrepareQual wants implicit-AND form */
+ tgqual = (Node *) make_ands_implicit((Expr *) tgqual);
+ *predicate = ExecPrepareQual((List *) tgqual, estate);
+ MemoryContextSwitchTo(oldContext);
+ }
- Assert(estate != NULL);
+ /*
+ * We will use the EState's per-tuple context for evaluating WHEN
+ * expressions (creating it if it's not already there).
+ */
+ econtext = GetPerTupleExprContext(estate);
- /*
- * trigger is an element of relinfo->ri_TrigDesc->triggers[]; find the
- * matching element of relinfo->ri_TrigWhenExprs[]
- */
- i = trigger - relinfo->ri_TrigDesc->triggers;
- predicate = &relinfo->ri_TrigWhenExprs[i];
+ /*
+ * Finally evaluate the expression, making the old and/or new tuples
+ * available as INNER_VAR/OUTER_VAR respectively.
+ */
+ econtext->ecxt_innertuple = oldslot;
+ econtext->ecxt_outertuple = newslot;
+ if (!ExecQual(*predicate, econtext))
+ return false;
- /*
- * If first time through for this WHEN expression, build expression
- * nodetrees for it. Keep them in the per-query memory context so
- * they'll survive throughout the query.
- */
- if (*predicate == NULL)
- {
- Node *tgqual;
-
- oldContext = MemoryContextSwitchTo(estate->es_query_cxt);
- tgqual = stringToNode(trigger->tgqual);
- /* Change references to OLD and NEW to INNER_VAR and OUTER_VAR */
- ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER_VAR, 0);
- ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER_VAR, 0);
- /* ExecPrepareQual wants implicit-AND form */
- tgqual = (Node *) make_ands_implicit((Expr *) tgqual);
- *predicate = ExecPrepareQual((List *) tgqual, estate);
- MemoryContextSwitchTo(oldContext);
- }
+ return true;
+}
- /*
- * We will use the EState's per-tuple context for evaluating WHEN
- * expressions (creating it if it's not already there).
- */
- econtext = GetPerTupleExprContext(estate);
+
+
+static bool
+EvalASWhenExpr(Trigger *trigger, Tuplestorestate *oldstore, Tuplestorestate *newstore)
+{
+ EphemeralNamedRelation oldenr = NULL,
+ newenr = NULL;
+ SPIPlanPtr spi_plan;
+ bool result;
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /*
+ * Attach relevant ENR tables into the query environment.
+ *
+ * This needs doing now in case we need to create a plan for the
+ * expression.
+ */
+ if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable))
+ {
+ oldenr = makeENR(trigger->tgoldtable, trigger->tgrelid, oldstore);
+ if (SPI_register_relation(oldenr) != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register_relation returned %s for OLD",
+ SPI_result_code_string(SPI_result));
+ }
+ if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable))
+ {
+ newenr = makeENR(trigger->tgnewtable, trigger->tgrelid, newstore);
+ if (SPI_register_relation(newenr) != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register_relation returned %s for NEW",
+ SPI_result_code_string(SPI_result));
+ }
+
+ /* Check for a cached WHEN query expression plan. */
+ if ((spi_plan = WhenQueryCache_FetchPlan(trigger->tgoid)) == NULL)
+ {
+ StringInfoData buffer;
+ Node *node;
+ char *expr;
+
+ /* Convert the qual into an expression suitable for SPI */
+ node = stringToNode(trigger->tgqual);
+ expr = deparse_expression(node, NIL, false, false);
+
+ /* Create the query text to evaluate the expression */
+ initStringInfo(&buffer);
+ appendStringInfoString(&buffer, "SELECT ");
+ appendStringInfoString(&buffer, expr);
+ appendStringInfoString(&buffer, " AS \"WHEN\"");
+
+ /* Prepare the plan and save it into the cache */
+ spi_plan = SPI_prepare(buffer.data, 0, NULL);
+
+ if (spi_plan == NULL)
+ elog(ERROR, "SPI_prepare returned %s for %s",
+ SPI_result_code_string(SPI_result), buffer.data);
+
+ SPI_keepplan(spi_plan);
+ WhenQueryCache_StorePlan(trigger->tgoid, spi_plan);
+ }
+
+ /*
+ * Execute the plan and get the result.
+ */
+ if (SPI_execute_plan(spi_plan, NULL, NULL, true, 1) > 0)
+ {
+ TupleDesc desc;
+ HeapTuple tuple;
+ Datum datum;
+ bool null;
+
+ /* The query should always return one-and-only-one tuple. */
+ Assert(SPI_processed == 1);
+
+ /* Get the first (and only) tuple, first (and only) attribute. */
+ tuple = SPI_tuptable->vals[0];
+ desc = SPI_tuptable->tupdesc;
+ datum = SPI_getbinval(tuple, desc, 1, &null);
/*
- * Finally evaluate the expression, making the old and/or new tuples
- * available as INNER_VAR/OUTER_VAR respectively.
+ * It is possible that the expression evaluated to NULL. We
+ * take that to mean "not true" rather than e.g. "unknown" hence
+ * mapping it to "false".
*/
- econtext->ecxt_innertuple = oldslot;
- econtext->ecxt_outertuple = newslot;
- if (!ExecQual(*predicate, econtext))
- return false;
+ result = null ? false : DatumGetBool(datum);
}
+ else
+ elog(ERROR, "SPI_execute_plan returned %s",
+ SPI_result_code_string(SPI_result));
- return true;
+ /*
+ * Detach ENR tables before finishing with SPI.
+ */
+ if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgoldtable))
+ {
+ if (SPI_unregister_relation(trigger->tgoldtable) != SPI_OK_REL_UNREGISTER)
+ elog(ERROR, "SPI_unregister_relation returned %s for OLD",
+ SPI_result_code_string(SPI_result));
+ pfree(oldenr);
+ }
+ if (TRIGGER_USES_TRANSITION_TABLE(trigger->tgnewtable))
+ {
+ if (SPI_unregister_relation(trigger->tgnewtable) != SPI_OK_REL_UNREGISTER)
+ elog(ERROR, "SPI_unregister_relation returned %s for NEW",
+ SPI_result_code_string(SPI_result));
+ pfree(newenr);
+ }
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+
+ return result;
}
@@ -5494,14 +5618,14 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
* NULL when the event is for a row being inserted, whereas NEW is
* NULL when the event is for a row being deleted.
*/
- Assert(!(event == TRIGGER_EVENT_DELETE && delete_old_table &&
+ Assert(!(TRIGGER_FIRED_BY_DELETE(event) && delete_old_table &&
TupIsNull(oldslot)));
- Assert(!(event == TRIGGER_EVENT_INSERT && insert_new_table &&
+ Assert(!(TRIGGER_FIRED_BY_INSERT(event) && insert_new_table &&
TupIsNull(newslot)));
if (!TupIsNull(oldslot) &&
- ((event == TRIGGER_EVENT_DELETE && delete_old_table) ||
- (event == TRIGGER_EVENT_UPDATE && update_old_table)))
+ ((TRIGGER_FIRED_BY_DELETE(event) && delete_old_table) ||
+ (TRIGGER_FIRED_BY_UPDATE(event) && update_old_table)))
{
Tuplestorestate *old_tuplestore;
@@ -5520,8 +5644,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
tuplestore_puttupleslot(old_tuplestore, oldslot);
}
if (!TupIsNull(newslot) &&
- ((event == TRIGGER_EVENT_INSERT && insert_new_table) ||
- (event == TRIGGER_EVENT_UPDATE && update_new_table)))
+ ((TRIGGER_FIRED_BY_INSERT(event) && insert_new_table) ||
+ (TRIGGER_FIRED_BY_UPDATE(event) && update_new_table)))
{
Tuplestorestate *new_tuplestore;
@@ -5551,10 +5675,10 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
* of them to be NULL.
*/
if (trigdesc == NULL ||
- (event == TRIGGER_EVENT_DELETE && !trigdesc->trig_delete_after_row) ||
- (event == TRIGGER_EVENT_INSERT && !trigdesc->trig_insert_after_row) ||
- (event == TRIGGER_EVENT_UPDATE && !trigdesc->trig_update_after_row) ||
- (event == TRIGGER_EVENT_UPDATE && (TupIsNull(oldslot) ^ TupIsNull(newslot))))
+ (TRIGGER_FIRED_BY_DELETE(event) && !trigdesc->trig_delete_after_row) ||
+ (TRIGGER_FIRED_BY_INSERT(event) && !trigdesc->trig_insert_after_row) ||
+ (TRIGGER_FIRED_BY_UPDATE(event) && !trigdesc->trig_update_after_row) ||
+ (TRIGGER_FIRED_BY_UPDATE(event) && (TupIsNull(oldslot) ^ TupIsNull(newslot))))
return;
}
@@ -5570,7 +5694,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
* if so. This preserves the behavior that statement-level triggers fire
* just once per statement and fire after row-level triggers.
*/
- switch (event)
+ switch (event & TRIGGER_EVENT_OPMASK)
{
case TRIGGER_EVENT_INSERT:
tgtype_event = TRIGGER_TYPE_INSERT;
@@ -5587,8 +5711,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
Assert(newslot == NULL);
ItemPointerSetInvalid(&(new_event.ate_ctid1));
ItemPointerSetInvalid(&(new_event.ate_ctid2));
- cancel_prior_stmt_triggers(RelationGetRelid(rel),
- CMD_INSERT, event);
+ cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_INSERT,
+ (event & TRIGGER_EVENT_OPMASK));
}
break;
case TRIGGER_EVENT_DELETE:
@@ -5606,8 +5730,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
Assert(newslot == NULL);
ItemPointerSetInvalid(&(new_event.ate_ctid1));
ItemPointerSetInvalid(&(new_event.ate_ctid2));
- cancel_prior_stmt_triggers(RelationGetRelid(rel),
- CMD_DELETE, event);
+ cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_DELETE,
+ (event & TRIGGER_EVENT_OPMASK));
}
break;
case TRIGGER_EVENT_UPDATE:
@@ -5625,8 +5749,8 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
Assert(newslot == NULL);
ItemPointerSetInvalid(&(new_event.ate_ctid1));
ItemPointerSetInvalid(&(new_event.ate_ctid2));
- cancel_prior_stmt_triggers(RelationGetRelid(rel),
- CMD_UPDATE, event);
+ cancel_prior_stmt_triggers(RelationGetRelid(rel), CMD_UPDATE,
+ (event & TRIGGER_EVENT_OPMASK));
}
break;
case TRIGGER_EVENT_TRUNCATE:
@@ -5637,13 +5761,14 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
ItemPointerSetInvalid(&(new_event.ate_ctid2));
break;
default:
- elog(ERROR, "invalid after-trigger event code: %d", event);
+ elog(ERROR, "invalid after-trigger event code: %d",
+ (event & TRIGGER_EVENT_OPMASK));
tgtype_event = 0; /* keep compiler quiet */
break;
}
if (!(relkind == RELKIND_FOREIGN_TABLE && row_trigger))
- new_event.ate_flags = (row_trigger && event == TRIGGER_EVENT_UPDATE) ?
+ new_event.ate_flags = (row_trigger && TRIGGER_FIRED_BY_UPDATE(event)) ?
AFTER_TRIGGER_2CTID : AFTER_TRIGGER_1CTID;
/* else, we'll initialize ate_flags for each trigger */
@@ -5652,14 +5777,25 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
for (i = 0; i < trigdesc->numtriggers; i++)
{
Trigger *trigger = &trigdesc->triggers[i];
+ Tuplestorestate *oldstore,
+ *newstore;
+
+ if (transition_capture)
+ {
+ Assert(transition_capture->tcs_private);
+ oldstore = transition_capture->tcs_private->old_tuplestore;
+ newstore = transition_capture->tcs_private->new_tuplestore;
+ }
+ else
+ oldstore = newstore = NULL;
if (!TRIGGER_TYPE_MATCHES(trigger->tgtype,
tgtype_level,
TRIGGER_TYPE_AFTER,
tgtype_event))
continue;
- if (!TriggerEnabled(estate, relinfo, trigger, event,
- modifiedCols, oldslot, newslot))
+ if (!TriggerEnabled(estate, relinfo, trigger, event, modifiedCols,
+ oldslot, newslot, oldstore, newstore))
continue;
if (relkind == RELKIND_FOREIGN_TABLE && row_trigger)
@@ -5789,6 +5925,374 @@ before_stmt_triggers_fired(Oid relid, CmdType cmdType)
return result;
}
+
+
+/*
+ * WhenQueryCache_Init
+ *
+ * Initialise cache for WHEN expression query plans.
+ */
+static void
+WhenQueryCache_Init(void)
+{
+ HASHCTL ctl;
+
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(WhenQueryCacheEntry);
+ WhenQueryCache = hash_create("Trigger WHEN expression plan cache",
+ WHENQUERYCACHE_INIT_SIZE,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+}
+
+/*
+ * WhenQueryCache_FetchPlan
+ *
+ * Retrieve a plan for the WHEN expression query from the cache.
+ */
+static SPIPlanPtr
+WhenQueryCache_FetchPlan(Oid key)
+{
+ WhenQueryCacheEntry *entry;
+
+ /* Initialise if required */
+ if (!WhenQueryCache)
+ WhenQueryCache_Init();
+
+ entry = (WhenQueryCacheEntry *) hash_search(WhenQueryCache,
+ (void *) &key,
+ HASH_FIND, NULL);
+ if (entry == NULL)
+ return NULL;
+
+ /*
+ * Check that the plan is still valid; else, discard it and allow it to
+ * be re-planned.
+ *
+ * We can use SPI_plan_is_valid as we have a lock on the relation being
+ * triggered. That is the only relation (other than the transition
+ * tables, of course) that can be referenced in the query.
+ */
+ if (entry->plan && !SPI_plan_is_valid(entry->plan))
+ {
+ SPI_freeplan(entry->plan);
+ entry->plan = NULL;
+
+ return NULL;
+ }
+
+ return entry->plan;
+}
+
+
+/*
+ * WhenQueryCache_StorePlan
+ *
+ * Store a plan for a WHEN expression query in the cache.
+ */
+static void
+WhenQueryCache_StorePlan(Oid key, SPIPlanPtr plan)
+{
+ WhenQueryCacheEntry *entry;
+ bool found;
+
+ /* Initialise if required */
+ if (!WhenQueryCache)
+ WhenQueryCache_Init();
+
+ entry = (WhenQueryCacheEntry *) hash_search(WhenQueryCache,
+ (void *) &key,
+ HASH_ENTER, &found);
+ Assert(!found || entry->plan == NULL);
+ entry->plan = plan;
+}
+
+
+/*
+ * makeWhenExprParseState - create a parse state suitable for transforming
+ * a WHEN expression.
+ */
+static ParseState *
+makeWhenExprParseState(const char *queryString, int16 tgtype, Relation rel,
+ char *oldtablename, char *newtablename)
+{
+ ParseState *pstate;
+
+ pstate = make_parsestate(NULL);
+ pstate->p_sourcetext = queryString;
+ pstate->p_queryEnv = create_queryEnv();
+
+ /*
+ * In ROW triggers, the references to NEW or OLD are treated as tuple
+ * variables, whereas in STATEMENT triggers they are treated as relation
+ * variables.
+ *
+ * That dictates a slightly different parser state. In ROW triggers we
+ * need to give the parser a range table that contains entries for the
+ * special var numbers PRS2_OLD_VARNO and PRS2_NEW_VARNO. No such range
+ * table is required for STATEMENT triggers as the expression itself
+ * will have defined a range table; but, we do need to attach ephemeral
+ * relations into the query environment to satisfy any OLD and NEW
+ * references.
+ */
+ if (TRIGGER_FOR_ROW(tgtype))
+ {
+ ParseNamespaceItem *nsitem;
+
+ /*
+ * Set up nsitems for OLD and NEW references.
+ *
+ * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+ */
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ makeAlias("old", NIL),
+ false, false);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ makeAlias("new", NIL),
+ false, false);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+ }
+ else
+ {
+ /*
+ * Set up QueryEnvironment for OLD and NEW references.
+ *
+ * This allows subqueries to resolve column names etc.
+ */
+ if (oldtablename != NULL)
+ {
+ EphemeralNamedRelation oldenr;
+
+ oldenr = makeENR(oldtablename, RelationGetRelid(rel), NULL);
+ register_ENR(pstate->p_queryEnv, oldenr);
+ }
+ if (newtablename != NULL)
+ {
+ EphemeralNamedRelation newenr;
+
+ newenr = makeENR(newtablename, RelationGetRelid(rel), NULL);
+ register_ENR(pstate->p_queryEnv, newenr);
+ }
+ }
+
+ return pstate;
+}
+
+
+/*
+ * freeWhenExprParseState - free parse state created by makeWhenExprParseState.
+ */
+static void
+freeWhenExprParseState(ParseState *pstate, char *oldtablename, char *newtablename)
+{
+ EphemeralNamedRelation enr;
+
+ Assert(pstate != NULL);
+ Assert(pstate->p_queryEnv != NULL);
+
+ /* Old ENR */
+ if (oldtablename != NULL &&
+ get_ENR(pstate->p_queryEnv, oldtablename) != NULL)
+ {
+ enr = get_ENR(pstate->p_queryEnv, oldtablename);
+ unregister_ENR(pstate->p_queryEnv, oldtablename);
+ pfree(enr);
+ }
+
+ /* New ENR */
+ if (newtablename != NULL &&
+ get_ENR(pstate->p_queryEnv, newtablename) != NULL)
+ {
+ enr = get_ENR(pstate->p_queryEnv, newtablename);
+ unregister_ENR(pstate->p_queryEnv, newtablename);
+ pfree(enr);
+ }
+
+ pfree(pstate->p_queryEnv);
+ free_parsestate(pstate);
+}
+
+
+/*
+ * validateWhenExpr - check WHEN expression is defined appropriately.
+ *
+ * A WHEN expression must only refer to certain tables or variables,
+ * depending on if it is a ROW or STATEMENT trigger, if it is BEFORE or
+ * AFTER INSERT, etc.
+ */
+static void
+validateWhenExpr(ParseState *pstate, Node *whenClause, Relation rel,
+ int16 tgtype, char *oldtablename, char *newtablename)
+{
+ ValidateWhenExprContext context;
+
+ /* Setup the walker context and validate the expression. */
+ context.pstate = pstate;
+ context.rel = rel;
+ context.tgtype = tgtype;
+ context.oldtablename = oldtablename;
+ context.newtablename = newtablename;
+ context.rtable = pstate->p_rtable;
+
+ (void) validateWhenExpr_Walker(whenClause, (void *) &context);
+}
+
+
+/*
+ * validateWhenExpr_Walker - logic for validateWhenExpr.
+ */
+static bool
+validateWhenExpr_Walker(Node *node, ValidateWhenExprContext *ctx)
+{
+ if (node == NULL)
+ return false;
+ else if (IsA(node, Var))
+ {
+ Var *var;
+ RangeTblEntry *rte;
+ bool oldvar, newvar;
+
+ var = castNode(Var, node);
+ rte = rt_fetch(var->varno, ctx->rtable);
+
+ /*
+ * We need to figure out if the Var is for the OLD or NEW table so
+ * that we can check for certain illegal references.
+ *
+ * If the Var is for the table that is being triggered against, that
+ * is allowed, but if the Var is for some other table, then we
+ * disallow that. But, we do that when visiting the RangeTblRef
+ * rather than here.
+ */
+ oldvar = TRIGGER_FOR_ROW(ctx->tgtype)
+ ? var->varno == PRS2_OLD_VARNO
+ : rte->rtekind == RTE_NAMEDTUPLESTORE &&
+ ctx->oldtablename != NULL &&
+ strcmp(rte->enrname, ctx->oldtablename) == 0;
+ newvar = TRIGGER_FOR_ROW(ctx->tgtype)
+ ? var->varno == PRS2_NEW_VARNO
+ : rte->rtekind == RTE_NAMEDTUPLESTORE &&
+ ctx->newtablename != NULL &&
+ strcmp(rte->enrname, ctx->newtablename) == 0;
+
+ /*
+ * Check Vars from the OLD table.
+ */
+ if (oldvar && TRIGGER_FOR_INSERT(ctx->tgtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
+ parser_errposition(ctx->pstate, var->location)));
+
+ /*
+ * Check Vars from the NEW table.
+ */
+ if (newvar && TRIGGER_FOR_DELETE(ctx->tgtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
+ parser_errposition(ctx->pstate, var->location)));
+
+ if (newvar && TRIGGER_FOR_BEFORE(ctx->tgtype) && var->varattno < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
+ parser_errposition(ctx->pstate, var->location)));
+
+ if (newvar && TRIGGER_FOR_BEFORE(ctx->tgtype) &&
+ var->varattno == 0 &&
+ RelationGetDescr(ctx->rel)->constr &&
+ RelationGetDescr(ctx->rel)->constr->has_generated_stored)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+ errdetail("A whole-row reference is used and the table contains generated columns."),
+ parser_errposition(ctx->pstate, var->location)));
+
+ if (newvar && TRIGGER_FOR_BEFORE(ctx->tgtype) &&
+ var->varattno > 0 &&
+ TupleDescAttr(RelationGetDescr(ctx->rel), var->varattno - 1)->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+ errdetail("Column \"%s\" is a generated column.",
+ NameStr(TupleDescAttr(RelationGetDescr(ctx->rel), var->varattno - 1)->attname)),
+ parser_errposition(ctx->pstate, var->location)));
+
+ /* fall through to recurse */
+ }
+ else if (IsA(node, RangeTblRef))
+ {
+ RangeTblRef *ref;
+ RangeTblEntry *rte;
+
+ ref = castNode(RangeTblRef, node);
+ rte = rt_fetch(ref->rtindex, ctx->rtable);
+
+ if (rte->rtekind == RTE_RELATION &&
+ rte->relid != RelationGetRelid(ctx->rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("trigger WHEN condition cannot contain references to other relations")));
+ /* fall through to recurse */
+ }
+ else if (IsA(node, Query))
+ {
+ Query *query;
+ List *rtable;
+ bool result;
+
+ if (TRIGGER_FOR_ROW(ctx->tgtype))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("row trigger's WHEN condition cannot use subqueries")));
+
+ /* Statement triggers allow subqueries, so traverse into it. */
+ query = castNode(Query, node);
+ rtable = ctx->rtable;
+
+ ctx->rtable = query->rtable;
+ result = query_tree_walker(query, validateWhenExpr_Walker, ctx, 0);
+ ctx->rtable = rtable;
+
+ return result;
+ }
+ return expression_tree_walker(node, validateWhenExpr_Walker, ctx);
+}
+
+
+/*
+ * makeENR - creates an EphemeralNamedRelation with the given name and
+ * relation.
+ */
+static EphemeralNamedRelation
+makeENR(char *name, Oid relid, Tuplestorestate *data)
+{
+ int64 tuple_count;
+ EphemeralNamedRelation enr;
+
+ /*
+ * We may have been called during analysis of the expression, rather
+ * than during execution, in which case we won't have any actual data.
+ */
+ if (data == NULL)
+ tuple_count = 0;
+ else
+ tuple_count = tuplestore_tuple_count(data);
+
+ enr = (EphemeralNamedRelation) palloc(sizeof(EphemeralNamedRelationData));
+ enr->reldata = (void *) data;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.reliddesc = relid;
+ enr->md.name = name;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtuples = (double) tuple_count;
+
+ return enr;
+}
+
/*
* If we previously queued a set of AFTER STATEMENT triggers for the given
* relation + operation, and they've not been fired yet, cancel them. The
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f928c32311..768e2f5f54 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1725,6 +1725,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_RETURNING:
case EXPR_KIND_VALUES:
case EXPR_KIND_VALUES_SINGLE:
+ case EXPR_KIND_TRIGGER_WHEN:
case EXPR_KIND_CYCLE_MARK:
/* okay */
break;
@@ -1751,9 +1752,6 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_EXECUTE_PARAMETER:
err = _("cannot use subquery in EXECUTE parameter");
break;
- case EXPR_KIND_TRIGGER_WHEN:
- err = _("cannot use subquery in trigger WHEN condition");
- break;
case EXPR_KIND_PARTITION_BOUND:
err = _("cannot use subquery in partition bound");
break;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 84ad62caea..92a341cee6 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1002,53 +1002,15 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
if (!isnull)
{
Node *qual;
- char relkind;
deparse_context context;
- deparse_namespace dpns;
- RangeTblEntry *oldrte;
- RangeTblEntry *newrte;
appendStringInfoString(&buf, "WHEN (");
qual = stringToNode(TextDatumGetCString(value));
- relkind = get_rel_relkind(trigrec->tgrelid);
-
- /* Build minimal OLD and NEW RTEs for the rel */
- oldrte = makeNode(RangeTblEntry);
- oldrte->rtekind = RTE_RELATION;
- oldrte->relid = trigrec->tgrelid;
- oldrte->relkind = relkind;
- oldrte->rellockmode = AccessShareLock;
- oldrte->alias = makeAlias("old", NIL);
- oldrte->eref = oldrte->alias;
- oldrte->lateral = false;
- oldrte->inh = false;
- oldrte->inFromCl = true;
-
- newrte = makeNode(RangeTblEntry);
- newrte->rtekind = RTE_RELATION;
- newrte->relid = trigrec->tgrelid;
- newrte->relkind = relkind;
- newrte->rellockmode = AccessShareLock;
- newrte->alias = makeAlias("new", NIL);
- newrte->eref = newrte->alias;
- newrte->lateral = false;
- newrte->inh = false;
- newrte->inFromCl = true;
-
- /* Build two-element rtable */
- memset(&dpns, 0, sizeof(dpns));
- dpns.rtable = list_make2(oldrte, newrte);
- dpns.subplans = NIL;
- dpns.ctes = NIL;
- dpns.appendrels = NULL;
- set_rtable_names(&dpns, NIL, NULL);
- set_simple_column_names(&dpns);
-
- /* Set up context with one-deep namespace stack */
+ /* Set up context */
context.buf = &buf;
- context.namespaces = list_make1(&dpns);
+ context.namespaces = NIL;
context.windowClause = NIL;
context.windowTList = NIL;
context.varprefix = true;
@@ -1058,6 +1020,54 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty)
context.special_exprkind = EXPR_KIND_NONE;
context.appendparents = NULL;
+ /* For ROW triggers we need to build the OLD and NEW RTEs for the rel.
+ * This isn't necessary for STATEMENT triggers as their WHEN expression
+ * will already have a range table defined in the EXISTS expressions. */
+ if (TRIGGER_FOR_ROW(trigrec->tgtype))
+ {
+ char relkind;
+ deparse_namespace dpns;
+ RangeTblEntry *oldrte;
+ RangeTblEntry *newrte;
+
+ relkind = get_rel_relkind(trigrec->tgrelid);
+
+ /* Build minimal OLD and NEW RTEs for the rel */
+ oldrte = makeNode(RangeTblEntry);
+ oldrte->rtekind = RTE_RELATION;
+ oldrte->relid = trigrec->tgrelid;
+ oldrte->relkind = relkind;
+ oldrte->rellockmode = AccessShareLock;
+ oldrte->alias = makeAlias("old", NIL);
+ oldrte->eref = oldrte->alias;
+ oldrte->lateral = false;
+ oldrte->inh = false;
+ oldrte->inFromCl = true;
+
+ newrte = makeNode(RangeTblEntry);
+ newrte->rtekind = RTE_RELATION;
+ newrte->relid = trigrec->tgrelid;
+ newrte->relkind = relkind;
+ newrte->rellockmode = AccessShareLock;
+ newrte->alias = makeAlias("new", NIL);
+ newrte->eref = newrte->alias;
+ newrte->lateral = false;
+ newrte->inh = false;
+ newrte->inFromCl = true;
+
+ /* Build two-element rtable */
+ memset(&dpns, 0, sizeof(dpns));
+ dpns.rtable = list_make2(oldrte, newrte);
+ dpns.subplans = NIL;
+ dpns.ctes = NIL;
+ dpns.appendrels = NULL;
+ set_rtable_names(&dpns, NIL, NULL);
+ set_simple_column_names(&dpns);
+
+ /* Make the deparse context aware */
+ context.namespaces = list_make1(&dpns);
+ }
+
get_rule_expr(qual, &context, false);
appendStringInfoString(&buf, ") ");
@@ -4203,7 +4213,8 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte,
* get_rte_attribute_name, except that it's important to disregard dropped
* columns. We put NULL into the array for a dropped column.
*/
- if (rte->rtekind == RTE_RELATION)
+ if (rte->rtekind == RTE_RELATION ||
+ rte->rtekind == RTE_NAMEDTUPLESTORE)
{
/* Relation --- look to the system catalogs for up-to-date info */
Relation rel;
@@ -4338,7 +4349,8 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte,
* print if we changed anything OR if there were user-written column
* aliases (since the latter would be part of the underlying "reality").
*/
- if (rte->rtekind == RTE_RELATION)
+ if (rte->rtekind == RTE_RELATION ||
+ rte->rtekind == RTE_NAMEDTUPLESTORE)
colinfo->printaliases = changed_any;
else if (rte->rtekind == RTE_FUNCTION)
colinfo->printaliases = true;
@@ -10821,6 +10833,10 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
case RTE_CTE:
appendStringInfoString(buf, quote_identifier(rte->ctename));
break;
+ case RTE_NAMEDTUPLESTORE:
+ /* Ephemeral RTE */
+ appendStringInfo(buf, "%s", rte->enrname);
+ break;
default:
elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind);
break;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 7795a69490..3e358ecd62 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -440,7 +440,7 @@ typedef struct ResultRelInfo
/* cached lookup info for trigger functions */
FmgrInfo *ri_TrigFunctions;
- /* array of trigger WHEN expr states */
+ /* array of trigger WHEN expr states for FOR EACH ROW triggers */
ExprState **ri_TrigWhenExprs;
/* optional runtime measurements for triggers */
diff --git a/src/include/utils/reltrigger.h b/src/include/utils/reltrigger.h
index 7dc7699b5c..9509fa19a9 100644
--- a/src/include/utils/reltrigger.h
+++ b/src/include/utils/reltrigger.h
@@ -24,6 +24,7 @@ typedef struct Trigger
{
Oid tgoid; /* OID of trigger (pg_trigger row) */
/* Remaining fields are copied from pg_trigger, see pg_trigger.h */
+ Oid tgrelid;
char *tgname;
Oid tgfoid;
int16 tgtype;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index e8af9a9589..875e14606a 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -588,6 +588,70 @@ create trigger oid_unchanged_trig after update on table_with_oids
update table_with_oids set a = a + 1;
NOTICE: trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW
drop table table_with_oids;
+-- Test FOR EACH STATEMENT triggers with queries in their WHEN condition
+--
+-- disable other triggers
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_stmt_trig;
+SELECT a, b FROM main_table ORDER BY a, b;
+ a | b
+----+----
+ 6 | 10
+ 21 | 20
+ 30 | 40
+ 31 | 10
+ 50 | 35
+ 50 | 60
+ 81 | 15
+ |
+(8 rows)
+
+-- legal WHEN expressions
+CREATE TRIGGER after_insert AFTER INSERT ON main_table
+ REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+ WHEN (500 <= ANY(SELECT b FROM NEW))
+ EXECUTE PROCEDURE trigger_func('after_insert');
+INSERT INTO main_table (a, b) VALUES -- after_insert won't fire
+ (101, 498),
+ (102, 499);
+INSERT INTO main_table (a, b) VALUES -- after_insert will fire
+ (103, 501),
+ (104, -99);
+NOTICE: trigger_func(after_insert) called: action = INSERT, when = AFTER, level = STATEMENT
+DROP TRIGGER after_insert ON main_table;
+CREATE TRIGGER after_delete AFTER DELETE ON main_table
+ REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT
+ WHEN (0 >= ANY(SELECT b FROM OLD))
+ EXECUTE PROCEDURE trigger_func('after_delete');
+DELETE FROM main_table WHERE a IN (101, 102); -- after delete won't fire
+DELETE FROM main_table WHERE a IN (103, 104); -- after delete will fire
+NOTICE: trigger_func(after_delete) called: action = DELETE, when = AFTER, level = STATEMENT
+DROP TRIGGER after_delete ON main_table;
+CREATE TRIGGER after_update AFTER UPDATE ON main_table
+ REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW FOR EACH STATEMENT
+ WHEN (EXISTS (SELECT 1 FROM new JOIN old ON (new.a = old.a) WHERE new.a < 50 AND new.b < old.b))
+ EXECUTE PROCEDURE trigger_func('after_update');
+UPDATE main_table SET b = (b + 1); -- after_update won't fire
+UPDATE main_table SET b = (b - 1); -- after_update will fire
+NOTICE: trigger_func(after_update) called: action = UPDATE, when = AFTER, level = STATEMENT
+DROP TRIGGER after_update ON main_table;
+-- illegal WHEN expressions
+CREATE TABLE other_table (n INTEGER NOT NULL PRIMARY KEY);
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+ REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+ WHEN (1 = ANY (SELECT a FROM NEW INTERSECT SELECT n FROM other_table))
+ EXECUTE PROCEDURE trigger_func('error_due_to_other_table');
+ERROR: trigger WHEN condition cannot contain references to other relations
+DROP TABLE other_table;
+-- re-enable other triggers
+ALTER TABLE main_table ENABLE TRIGGER after_upd_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+--
+-- Done testing FOR EACH STATEMENT with queries in the WHEN condition
-- Test column-level triggers
DROP TRIGGER after_upd_row_trig ON main_table;
CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON main_table
@@ -700,7 +764,7 @@ LINE 2: FOR EACH ROW WHEN (NEW.tableoid <> 0)
CREATE TRIGGER error_stmt_when BEFORE UPDATE OF a ON main_table
FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE trigger_func('error_stmt_when');
-ERROR: statement trigger's WHEN condition cannot reference column values
+ERROR: missing FROM-clause entry for table "old"
LINE 2: FOR EACH STATEMENT WHEN (OLD.* IS DISTINCT FROM NEW.*)
^
-- check dependency restrictions
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index b50f500045..91047361ca 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -369,6 +369,63 @@ create trigger oid_unchanged_trig after update on table_with_oids
update table_with_oids set a = a + 1;
drop table table_with_oids;
+-- Test FOR EACH STATEMENT triggers with queries in their WHEN condition
+--
+
+-- disable other triggers
+ALTER TABLE main_table DISABLE TRIGGER before_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table DISABLE TRIGGER after_upd_stmt_trig;
+
+SELECT a, b FROM main_table ORDER BY a, b;
+
+-- legal WHEN expressions
+CREATE TRIGGER after_insert AFTER INSERT ON main_table
+ REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+ WHEN (500 <= ANY(SELECT b FROM NEW))
+ EXECUTE PROCEDURE trigger_func('after_insert');
+INSERT INTO main_table (a, b) VALUES -- after_insert won't fire
+ (101, 498),
+ (102, 499);
+INSERT INTO main_table (a, b) VALUES -- after_insert will fire
+ (103, 501),
+ (104, -99);
+DROP TRIGGER after_insert ON main_table;
+
+CREATE TRIGGER after_delete AFTER DELETE ON main_table
+ REFERENCING OLD TABLE AS OLD FOR EACH STATEMENT
+ WHEN (0 >= ANY(SELECT b FROM OLD))
+ EXECUTE PROCEDURE trigger_func('after_delete');
+DELETE FROM main_table WHERE a IN (101, 102); -- after delete won't fire
+DELETE FROM main_table WHERE a IN (103, 104); -- after delete will fire
+DROP TRIGGER after_delete ON main_table;
+
+CREATE TRIGGER after_update AFTER UPDATE ON main_table
+ REFERENCING OLD TABLE AS OLD NEW TABLE AS NEW FOR EACH STATEMENT
+ WHEN (EXISTS (SELECT 1 FROM new JOIN old ON (new.a = old.a) WHERE new.a < 50 AND new.b < old.b))
+ EXECUTE PROCEDURE trigger_func('after_update');
+UPDATE main_table SET b = (b + 1); -- after_update won't fire
+UPDATE main_table SET b = (b - 1); -- after_update will fire
+DROP TRIGGER after_update ON main_table;
+
+-- illegal WHEN expressions
+CREATE TABLE other_table (n INTEGER NOT NULL PRIMARY KEY);
+CREATE TRIGGER error_due_to_other_table AFTER INSERT ON main_table
+ REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT
+ WHEN (1 = ANY (SELECT a FROM NEW INTERSECT SELECT n FROM other_table))
+ EXECUTE PROCEDURE trigger_func('error_due_to_other_table');
+DROP TABLE other_table;
+
+-- re-enable other triggers
+ALTER TABLE main_table ENABLE TRIGGER after_upd_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_upd_row_trig;
+ALTER TABLE main_table ENABLE TRIGGER after_ins_stmt_trig;
+ALTER TABLE main_table ENABLE TRIGGER before_ins_stmt_trig;
+
+--
+-- Done testing FOR EACH STATEMENT with queries in the WHEN condition
+
-- Test column-level triggers
DROP TRIGGER after_upd_row_trig ON main_table;
--
2.31.1
