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.bon...@postgrespro.ru> Reviewed-by: Paul Jungwirth <p...@illuminatedcomputing.com> Reviewed-by: Lilian Ontowhee <ontow...@gmail.com> Reviewed-by: Tom Lane <t...@sss.pgh.pa.us> Discussion: https://postgr.es/m/79755a2b18ed4fe5e29da6a87a1e0...@postgrespro.ru Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/8cfbdf8f4dfbb2204df32fbc7d5dcd4d1b465723 Modified Files -------------- contrib/spi/refint.c | 26 +++++++++++++++++------ doc/src/sgml/contrib-spi.sgml | 14 ++++++++++-- src/test/regress/expected/triggers.out | 39 +++++++++++++++++----------------- src/test/regress/sql/triggers.sql | 10 ++++----- 4 files changed, 55 insertions(+), 34 deletions(-)
