diff options
| -rw-r--r-- | contrib/spi/refint.c | 26 | ||||
| -rw-r--r-- | doc/src/sgml/contrib-spi.sgml | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/triggers.out | 39 | ||||
| -rw-r--r-- | src/test/regress/sql/triggers.sql | 10 |
4 files changed, 55 insertions, 34 deletions
diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c index d954f5c838f..d5e25e07ae9 100644 --- a/contrib/spi/refint.c +++ b/contrib/spi/refint.c @@ -84,6 +84,10 @@ check_primary_key(PG_FUNCTION_ARGS) /* internal error */ elog(ERROR, "check_primary_key: must be fired for row"); + if (!TRIGGER_FIRED_AFTER(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_primary_key: must be fired by AFTER trigger"); + /* If INSERTion then must check Tuple to being inserted */ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) tuple = trigdata->tg_trigtuple; @@ -287,6 +291,10 @@ check_foreign_key(PG_FUNCTION_ARGS) /* internal error */ elog(ERROR, "check_foreign_key: cannot process INSERT events"); + if (!TRIGGER_FIRED_AFTER(trigdata->tg_event)) + /* internal error */ + elog(ERROR, "check_foreign_key: must be fired by AFTER trigger"); + /* Have to check tg_trigtuple - tuple being deleted */ trigtuple = trigdata->tg_trigtuple; @@ -338,10 +346,10 @@ check_foreign_key(PG_FUNCTION_ARGS) kvals = (Datum *) palloc(nkeys * sizeof(Datum)); /* - * Construct ident string as TriggerName $ TriggeredRelationId and try to - * find prepared execution plan(s). + * Construct ident string as TriggerName $ TriggeredRelationId $ + * OperationType and try to find prepared execution plan(s). */ - snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); + snprintf(ident, sizeof(ident), "%s$%u$%c", trigger->tgname, rel->rd_id, is_update ? 'U' : 'D'); plan = find_plan(ident, &FPlans, &nFPlans); /* if there is no plan(s) then allocate argtypes for preparation */ @@ -573,8 +581,6 @@ check_foreign_key(PG_FUNCTION_ARGS) relname = args[0]; - snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id); - plan = find_plan(ident, &FPlans, &nFPlans); ret = SPI_execp(plan->splan[r], kvals, NULL, tcount); /* we have no NULLs - so we pass ^^^^ here */ @@ -596,9 +602,15 @@ check_foreign_key(PG_FUNCTION_ARGS) else { #ifdef REFINT_VERBOSE + const char *operation; + + if (action == 'c') + operation = is_update ? "updated" : "deleted"; + else + operation = "set to null"; + elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s", - trigger->tgname, SPI_processed, relname, - (action == 'c') ? "deleted" : "set to null"); + trigger->tgname, SPI_processed, relname, operation); #endif } args += nkeys + 1; /* to the next relation */ diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml index e7cae4e38dc..55d3fac7a69 100644 --- a/doc/src/sgml/contrib-spi.sgml +++ b/doc/src/sgml/contrib-spi.sgml @@ -36,7 +36,7 @@ <para> <function>check_primary_key()</function> checks the referencing table. - To use, create a <literal>BEFORE INSERT OR UPDATE</literal> trigger using this + To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this function on a table referencing another table. Specify as the trigger arguments: the referencing table's column name(s) which form the foreign key, the referenced table name, and the column names in the referenced table @@ -46,7 +46,7 @@ <para> <function>check_foreign_key()</function> checks the referenced table. - To use, create a <literal>BEFORE DELETE OR UPDATE</literal> trigger using this + To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this function on a table referenced by other table(s). Specify as the trigger arguments: the number of referencing tables for which the function has to perform checking, the action if a referencing key is found @@ -61,6 +61,16 @@ </para> <para> + Note that if these triggers are executed from + another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For + example, if a user inserts row1 and then the <literal>BEFORE</literal> + trigger inserts row2 and calls a trigger with the + <function>check_foreign_key()</function>, + the <function>check_foreign_key()</function> + function will not see row1 and will fail. + </para> + + <para> There are examples in <filename>refint.example</filename>. </para> </sect2> diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 247c67c32ae..e6f585d9740 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -46,12 +46,12 @@ create unique index pkeys_i on pkeys (pkey1, pkey2); -- (fkey3) --> fkeys2 (pkey23) -- create trigger check_fkeys_pkey_exist - before insert or update on fkeys + after insert or update on fkeys for each row execute function check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); create trigger check_fkeys_pkey2_exist - before insert or update on fkeys + after insert or update on fkeys for each row execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); -- @@ -59,7 +59,7 @@ create trigger check_fkeys_pkey2_exist -- (fkey21, fkey22) --> pkeys (pkey1, pkey2) -- create trigger check_fkeys2_pkey_exist - before insert or update on fkeys2 + after insert or update on fkeys2 for each row execute procedure check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); @@ -74,7 +74,7 @@ COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL; -- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) -- create trigger check_pkeys_fkey_cascade - before delete or update on pkeys + after delete or update on pkeys for each row execute procedure check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', @@ -85,7 +85,7 @@ create trigger check_pkeys_fkey_cascade -- fkeys (fkey3) -- create trigger check_fkeys2_fkey_restrict - before delete or update on fkeys2 + after delete or update on fkeys2 for each row execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); insert into fkeys2 values (10, '1', 1); @@ -116,12 +116,11 @@ delete from pkeys where pkey1 = 40 and pkey2 = '4'; NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; -NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted -ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys" -CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 " +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated +NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; -NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted -NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted +ERROR: duplicate key value violates unique constraint "pkeys_i" +DETAIL: Key (pkey1, pkey2)=(7, 70) already exists. SELECT trigger_name, event_manipulation, event_object_schema, event_object_table, action_order, action_condition, action_orientation, action_timing, action_reference_old_table, action_reference_new_table @@ -130,16 +129,16 @@ SELECT trigger_name, event_manipulation, event_object_schema, event_object_table ORDER BY trigger_name COLLATE "C", 2; trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table ----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+---------------------------- - check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | BEFORE | | - check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | BEFORE | | - check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | BEFORE | | - check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | BEFORE | | - check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | BEFORE | | - check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | BEFORE | | - check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | BEFORE | | - check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | BEFORE | | - check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | BEFORE | | - check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | BEFORE | | + check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | | + check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | | + check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | | + check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | | + check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | | + check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | | + check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | | + check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | | + check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | | + check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | | (10 rows) DROP TABLE pkeys; diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 659972f1135..e5a491be7ab 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -57,13 +57,13 @@ create unique index pkeys_i on pkeys (pkey1, pkey2); -- (fkey3) --> fkeys2 (pkey23) -- create trigger check_fkeys_pkey_exist - before insert or update on fkeys + after insert or update on fkeys for each row execute function check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); create trigger check_fkeys_pkey2_exist - before insert or update on fkeys + after insert or update on fkeys for each row execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23'); @@ -72,7 +72,7 @@ create trigger check_fkeys_pkey2_exist -- (fkey21, fkey22) --> pkeys (pkey1, pkey2) -- create trigger check_fkeys2_pkey_exist - before insert or update on fkeys2 + after insert or update on fkeys2 for each row execute procedure check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); @@ -88,7 +88,7 @@ COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL; -- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) -- create trigger check_pkeys_fkey_cascade - before delete or update on pkeys + after delete or update on pkeys for each row execute procedure check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', @@ -100,7 +100,7 @@ create trigger check_pkeys_fkey_cascade -- fkeys (fkey3) -- create trigger check_fkeys2_fkey_restrict - before delete or update on fkeys2 + after delete or update on fkeys2 for each row execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); |