Re: [HACKERS] Cannot use all four trigger events at once

This was failing:

CREATE TRIGGER foo
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON foobar
FOR EACH STATEMENT EXECUTE PROCEDURE baz();

Turns out the parser wasn't set up to handle four different trigger
event types. Patch attached.

--
Greg Sabino Mullane greg(at)endpoint(dot)com greg(at)turnstep(dot)com
End Point Corporation 610-983-9073
PGP Key: 0x14964AC8 200906171620
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Greg Sabino Mullane <greg(at)turnstep(dot)com> writes:
> This was failing:
> CREATE TRIGGER foo
> AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
> ON foobar
> FOR EACH STATEMENT EXECUTE PROCEDURE baz();

> Turns out the parser wasn't set up to handle four different trigger
> event types.

Hmm, that's a problem ...

> Patch attached.

... but this patch doesn't come close to fixing it. struct CreateTrigStmt
needs changes. I kinda think the restriction to 4 (or whatever)
elements ought to go away altogether.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
NotDashEscaped: You need GnuPG to verify this message

> ... but this patch doesn't come close to fixing it. struct CreateTrigStmt
> needs changes. I kinda think the restriction to 4 (or whatever)
> elements ought to go away altogether.

Thanks. I'll leave the [5] restriction call to someone else, seems it
does no harm to me though (not as if we'd change it that often anyway).
Revised patch below. I looked through other places that might be affected,
but did not see anything else except ecpg/preproc/preproc.y, which
looks as though it's already been changed to handle four events.

Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.664
diff -c -r2.664 gram.y
*** src/backend/parser/gram.y 27 May 2009 20:42:29 -0000 2.664
--- src/backend/parser/gram.y 18 Jun 2009 00:44:15 -0000
***************
*** 3133,3139 ****
n->args = $13;
n->before = $4;
n->row = $8;
! memcpy(n->actions, $5, 4);
n->isconstraint = FALSE;
n->deferrable = FALSE;
n->initdeferred = FALSE;
--- 3133,3139 ----
n->args = $13;
n->before = $4;
n->row = $8;
! memcpy(n->actions, $5, 5);
n->isconstraint = FALSE;
n->deferrable = FALSE;
n->initdeferred = FALSE;
***************
*** 3153,3159 ****
n->args = $18;
n->before = FALSE;
n->row = TRUE;
! memcpy(n->actions, $6, 4);
n->isconstraint = TRUE;
n->deferrable = ($10 & 1) != 0;
n->initdeferred = ($10 & 2) != 0;
--- 3153,3159 ----
n->args = $18;
n->before = FALSE;
n->row = TRUE;
! memcpy(n->actions, $6, 5);
n->isconstraint = TRUE;
n->deferrable = ($10 & 1) != 0;
n->initdeferred = ($10 & 2) != 0;
***************
*** 3171,3192 ****
TriggerEvents:
TriggerOneEvent
{
! char *e = palloc(4);
e[0] = $1; e[1] = '\0';
$$ = e;
}
| TriggerOneEvent OR TriggerOneEvent
{
! char *e = palloc(4);
e[0] = $1; e[1] = $3; e[2] = '\0';
$$ = e;
}
| TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
{
! char *e = palloc(4);
e[0] = $1; e[1] = $3; e[2] = $5; e[3] = '\0';
$$ = e;
}
;

TriggerOneEvent:
--- 3171,3198 ----
TriggerEvents:
TriggerOneEvent
{
! char *e = palloc(5);
e[0] = $1; e[1] = '\0';
$$ = e;
}
| TriggerOneEvent OR TriggerOneEvent
{
! char *e = palloc(5);
e[0] = $1; e[1] = $3; e[2] = '\0';
$$ = e;
}
| TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
{
! char *e = palloc(5);
e[0] = $1; e[1] = $3; e[2] = $5; e[3] = '\0';
$$ = e;
}
+ | TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
+ {
+ char *e = palloc(5);
+ e[0] = $1; e[1] = $3; e[2] = $5; e[3] = $7; e[4] = '\0';
+ $$ = e;
+ }
;

TriggerOneEvent:
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.394
diff -c -r1.394 parsenodes.h
*** src/include/nodes/parsenodes.h 11 Jun 2009 14:49:11 -0000 1.394
--- src/include/nodes/parsenodes.h 18 Jun 2009 00:44:15 -0000
***************
*** 1551,1557 ****
List *args; /* list of (T_String) Values or NIL */
bool before; /* BEFORE/AFTER */
bool row; /* ROW/STATEMENT */
! char actions[4]; /* 1 to 3 of 'i', 'u', 'd', + trailing \0 */

/* The following are used for referential */
/* integrity constraint triggers */
--- 1551,1557 ----
List *args; /* list of (T_String) Values or NIL */
bool before; /* BEFORE/AFTER */
bool row; /* ROW/STATEMENT */
! char actions[5]; /* 1 to 4 of 'i', 'u', 'd', or 't', + trailing \0 */

/* The following are used for referential */
/* integrity constraint triggers */

--
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200906172045
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAko5jioACgkQvJuQZxSWSsgqBACfVMeTyQkT9wqJTPi9oTLHuipK
8sgAn0vowAiCNhnoYBnsZT310UFC1UV7
=LpeM
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> Thanks. I'll leave the [5] restriction call to someone else, seems it
> does no harm to me though (not as if we'd change it that often anyway).
> Revised patch below. I looked through other places that might be affected,
> but did not see anything else except ecpg/preproc/preproc.y, which
> looks as though it's already been changed to handle four events.

preproc.y is a derived file nowadays, so if you looked at it after
having fixed gram.y it would've looked fixed. However, I felt we
should fix this code properly instead of propagating its existing
klugy approach still further. It gets a whole lot simpler and shorter
if you make the grammar produce the bitmap representation that trigger.c
actually wants; as per attached applied patch.

regards, tom lane

Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.287
diff -c -r1.287 tablecmds.c
*** src/backend/commands/tablecmds.c 11 Jun 2009 20:46:11 -0000 1.287
--- src/backend/commands/tablecmds.c 18 Jun 2009 01:13:04 -0000
***************
*** 5206,5219 ****
if (on_insert)
{
fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
! fk_trigger->actions[0] = 'i';
}
else
{
fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd");
! fk_trigger->actions[0] = 'u';
}
- fk_trigger->actions[1] = '\0';

fk_trigger->isconstraint = true;
fk_trigger->deferrable = fkconstraint->deferrable;
--- 5206,5218 ----
if (on_insert)
{
fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins");
! fk_trigger->events = TRIGGER_TYPE_INSERT;
}
else
{
fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd");
! fk_trigger->events = TRIGGER_TYPE_UPDATE;
}

fk_trigger->isconstraint = true;
fk_trigger->deferrable = fkconstraint->deferrable;
***************
*** 5263,5271 ****
fk_trigger->relation = fkconstraint->pktable;
fk_trigger->before = false;
fk_trigger->row = true;
! fk_trigger->actions[0] = 'd';
! fk_trigger->actions[1] = '\0';
!
fk_trigger->isconstraint = true;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_del_action)
--- 5262,5268 ----
fk_trigger->relation = fkconstraint->pktable;
fk_trigger->before = false;
fk_trigger->row = true;
! fk_trigger->events = TRIGGER_TYPE_DELETE;
fk_trigger->isconstraint = true;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_del_action)
***************
*** 5316,5323 ****
fk_trigger->relation = fkconstraint->pktable;
fk_trigger->before = false;
fk_trigger->row = true;
! fk_trigger->actions[0] = 'u';
! fk_trigger->actions[1] = '\0';
fk_trigger->isconstraint = true;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_upd_action)
--- 5313,5319 ----
fk_trigger->relation = fkconstraint->pktable;
fk_trigger->before = false;
fk_trigger->row = true;
! fk_trigger->events = TRIGGER_TYPE_UPDATE;
fk_trigger->isconstraint = true;
fk_trigger->constrrel = myRel;
switch (fkconstraint->fk_upd_action)
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.247
diff -c -r1.247 trigger.c
*** src/backend/commands/trigger.c 11 Jun 2009 14:48:56 -0000 1.247
--- src/backend/commands/trigger.c 18 Jun 2009 01:13:04 -0000
***************
*** 100,106 ****
Oid funcoid;
Oid funcrettype;
Oid trigoid;
- int i;
char constrtrigname[NAMEDATALEN];
char *trigname;
char *constrname;
--- 100,105 ----
***************
*** 150,199 ****
TRIGGER_SETT_BEFORE(tgtype);
if (stmt->row)
TRIGGER_SETT_ROW(tgtype);

! for (i = 0; stmt->actions[i]; i++)
! {
! switch (stmt->actions[i])
! {
! case 'i':
! if (TRIGGER_FOR_INSERT(tgtype))
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("multiple INSERT events specified")));
! TRIGGER_SETT_INSERT(tgtype);
! break;
! case 'd':
! if (TRIGGER_FOR_DELETE(tgtype))
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("multiple DELETE events specified")));
! TRIGGER_SETT_DELETE(tgtype);
! break;
! case 'u':
! if (TRIGGER_FOR_UPDATE(tgtype))
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! 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);
! /* Disallow ROW-level TRUNCATE triggers */
! if (stmt->row)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("TRUNCATE FOR EACH ROW triggers are not supported")));
! break;
! default:
! elog(ERROR, "unrecognized trigger event: %d",
! (int) stmt->actions[i]);
! break;
! }
! }

/*
* Find and validate the trigger function.
--- 149,161 ----
TRIGGER_SETT_BEFORE(tgtype);
if (stmt->row)
TRIGGER_SETT_ROW(tgtype);
+ tgtype |= stmt->events;

! /* Disallow ROW-level TRUNCATE triggers */
! if (TRIGGER_FOR_ROW(tgtype) && TRIGGER_FOR_TRUNCATE(tgtype))
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("TRUNCATE FOR EACH ROW triggers are not supported")));

/*
* Find and validate the trigger function.
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.431
diff -c -r1.431 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 11 Jun 2009 14:48:58 -0000 1.431
--- src/backend/nodes/copyfuncs.c 18 Jun 2009 01:13:04 -0000
***************
*** 3081,3087 ****
COPY_NODE_FIELD(args);
COPY_SCALAR_FIELD(before);
COPY_SCALAR_FIELD(row);
! strcpy(newnode->actions, from->actions); /* in-line string field */
COPY_SCALAR_FIELD(isconstraint);
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
--- 3081,3087 ----
COPY_NODE_FIELD(args);
COPY_SCALAR_FIELD(before);
COPY_SCALAR_FIELD(row);
! COPY_SCALAR_FIELD(events);
COPY_SCALAR_FIELD(isconstraint);
COPY_SCALAR_FIELD(deferrable);
COPY_SCALAR_FIELD(initdeferred);
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.354
diff -c -r1.354 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 11 Jun 2009 14:48:58 -0000 1.354
--- src/backend/nodes/equalfuncs.c 18 Jun 2009 01:13:04 -0000
***************
*** 1639,1646 ****
COMPARE_NODE_FIELD(args);
COMPARE_SCALAR_FIELD(before);
COMPARE_SCALAR_FIELD(row);
! if (strcmp(a->actions, b->actions) != 0) /* in-line string field */
! return false;
COMPARE_SCALAR_FIELD(isconstraint);
COMPARE_SCALAR_FIELD(deferrable);
COMPARE_SCALAR_FIELD(initdeferred);
--- 1639,1645 ----
COMPARE_NODE_FIELD(args);
COMPARE_SCALAR_FIELD(before);
COMPARE_SCALAR_FIELD(row);
! COMPARE_SCALAR_FIELD(events);
COMPARE_SCALAR_FIELD(isconstraint);
COMPARE_SCALAR_FIELD(deferrable);
COMPARE_SCALAR_FIELD(initdeferred);
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.664
diff -c -r2.664 gram.y
*** src/backend/parser/gram.y 27 May 2009 20:42:29 -0000 2.664
--- src/backend/parser/gram.y 18 Jun 2009 01:13:04 -0000
***************
*** 53,58 ****
--- 53,59 ----

#include "catalog/index.h"
#include "catalog/namespace.h"
+ #include "catalog/pg_trigger.h"
#include "commands/defrem.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
***************
*** 244,250 ****
%type <boolean> TriggerActionTime TriggerForSpec opt_trusted opt_restart_seqs
%type <str> opt_lancompiler

! %type <str> TriggerEvents
%type <value> TriggerFuncArg

%type <str> relation_name copy_file_name
--- 245,251 ----
%type <boolean> TriggerActionTime TriggerForSpec opt_trusted opt_restart_seqs
%type <str> opt_lancompiler

! %type <ival> TriggerEvents TriggerOneEvent
%type <value> TriggerFuncArg

%type <str> relation_name copy_file_name
***************
*** 266,272 ****
%type <privtarget> privilege_target
%type <funwithargs> function_with_argtypes
%type <list> function_with_argtypes_list
- %type <chr> TriggerOneEvent

%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
--- 267,272 ----
***************
*** 3133,3139 ****
n->args = $13;
n->before = $4;
n->row = $8;
! memcpy(n->actions, $5, 4);
n->isconstraint = FALSE;
n->deferrable = FALSE;
n->initdeferred = FALSE;
--- 3133,3139 ----
n->args = $13;
n->before = $4;
n->row = $8;
! n->events = $5;
n->isconstraint = FALSE;
n->deferrable = FALSE;
n->initdeferred = FALSE;
***************
*** 3153,3163 ****
n->args = $18;
n->before = FALSE;
n->row = TRUE;
! memcpy(n->actions, $6, 4);
n->isconstraint = TRUE;
n->deferrable = ($10 & 1) != 0;
n->initdeferred = ($10 & 2) != 0;
-
n->constrrel = $9;
$$ = (Node *)n;
}
--- 3153,3162 ----
n->args = $18;
n->before = FALSE;
n->row = TRUE;
! n->events = $6;
n->isconstraint = TRUE;
n->deferrable = ($10 & 1) != 0;
n->initdeferred = ($10 & 2) != 0;
n->constrrel = $9;
$$ = (Node *)n;
}
***************
*** 3170,3199 ****

TriggerEvents:
TriggerOneEvent
{
! char *e = palloc(4);
! e[0] = $1; e[1] = '\0';
! $$ = e;
! }
! | TriggerOneEvent OR TriggerOneEvent
! {
! char *e = palloc(4);
! e[0] = $1; e[1] = $3; e[2] = '\0';
! $$ = e;
! }
! | TriggerOneEvent OR TriggerOneEvent OR TriggerOneEvent
! {
! char *e = palloc(4);
! e[0] = $1; e[1] = $3; e[2] = $5; e[3] = '\0';
! $$ = e;
}
;

TriggerOneEvent:
! INSERT { $$ = 'i'; }
! | DELETE_P { $$ = 'd'; }
! | UPDATE { $$ = 'u'; }
! | TRUNCATE { $$ = 't'; }
;

TriggerForSpec:
--- 3169,3188 ----

TriggerEvents:
TriggerOneEvent
+ { $$ = $1; }
+ | TriggerEvents OR TriggerOneEvent
{
! if ($1 & $3)
! yyerror("duplicate trigger events specified");
! $$ = $1 | $3;
}
;

TriggerOneEvent:
! INSERT { $$ = TRIGGER_TYPE_INSERT; }
! | DELETE_P { $$ = TRIGGER_TYPE_DELETE; }
! | UPDATE { $$ = TRIGGER_TYPE_UPDATE; }
! | TRUNCATE { $$ = TRIGGER_TYPE_TRUNCATE; }
;

TriggerForSpec:
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.394
diff -c -r1.394 parsenodes.h
*** src/include/nodes/parsenodes.h 11 Jun 2009 14:49:11 -0000 1.394
--- src/include/nodes/parsenodes.h 18 Jun 2009 01:13:05 -0000
***************
*** 1551,1557 ****
List *args; /* list of (T_String) Values or NIL */
bool before; /* BEFORE/AFTER */
bool row; /* ROW/STATEMENT */
! char actions[4]; /* 1 to 3 of 'i', 'u', 'd', + trailing \0 */

/* The following are used for referential */
/* integrity constraint triggers */
--- 1551,1558 ----
List *args; /* list of (T_String) Values or NIL */
bool before; /* BEFORE/AFTER */
bool row; /* ROW/STATEMENT */
! /* events uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */
! int16 events; /* INSERT/UPDATE/DELETE/TRUNCATE */

/* The following are used for referential */
/* integrity constraint triggers */

On Wed, 2009-06-17 at 16:22 -0400, Greg Sabino Mullane wrote:
> This was failing:
>
> CREATE TRIGGER foo
> AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
> ON foobar
> FOR EACH STATEMENT EXECUTE PROCEDURE baz();
>
> Turns out the parser wasn't set up to handle four different trigger
> event types. Patch attached.

Thanks for fixing Greg.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support