diff options
| author | Tom Lane | 2025-04-07 19:54:09 +0000 |
|---|---|---|
| committer | Tom Lane | 2025-04-07 19:54:16 +0000 |
| commit | 8cfbdf8f4dfbb2204df32fbc7d5dcd4d1b465723 (patch) | |
| tree | a9334e07f9495988e19e6380f04a438feccf8380 /doc/src | |
| parent | 8e293e689bab0267d26e3705fe1d537cd43e633a (diff) | |
Fix some issues in contrib/spi/refint.c.
check_foreign_key incorrectly used a single cache entry for its saved
plans for a 'c' (cascade) trigger, although there are two different
queries to execute depending on whether it fires for an update or a
delete. This caused the wrong things to be done if both types of
event occur in one session. (This was indeed visible in the triggers
regression test, but apparently nobody ever questioned it.) To fix,
add the operation type to the cache key.
Its debug log output failed to distinguish update from delete
events, too.
Also, change the intended trigger usage from BEFORE ROW to AFTER ROW,
and add checks insisting on that usage. BEFORE is really rather
unsafe, since if there are other BEFORE triggers they might change or
cancel the operation we are trying to check. AFTER triggers are the
standard way to propagate changes to other rows, so we should follow
that way here.
In passing, remove a useless duplicate lookup of the cache entry.
This code is mostly intended as a documentation example, so we
won't consider a back-patch.
Author: Dmitrii Bondar <d.bondar@postgrespro.ru>
Reviewed-by: Paul Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Lilian Ontowhee <ontowhee@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/79755a2b18ed4fe5e29da6a87a1e00d1@postgrespro.ru
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/contrib-spi.sgml | 14 |
1 files changed, 12 insertions, 2 deletions
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> |