Related to bug #6123, Wisconsin Courts are now using triggers with the workaround to be safe with the patch put forward by Tom, even though they are still running with the earlier patch proposal by me (which tolerates an UPDATE or DELETE of a row being deleted). The general structure of the BEFORE DELETE triggers with cascading logic was like this:
DECLARE return_value parenttable; BEGIN return_value := OLD; DELETE FROM childtable1 WHERE <child of parent>; IF FOUND THEN return_value := NULL; END IF; DELETE FROM childtablen WHERE <child of parent>; IF FOUND THEN return_value := NULL; END IF; IF return_value IS NULL THEN DELETE FROM parent WHERE <primary key matches OLD>; END IF; RETURN return_value; END; This did not work for cases where the AFTER DELETE trigger performed an action which was not idempotent because, while return_value was NULL enough to enter that last IF clause, it was not NULL enough to prevent the DELETE attempt and fire subsequent triggers. The assignment of NULL to a variable with a record type doesn't assign a "simple" NULL, but a record with NULL in each element. It seems like a POLA violation that: return_value := NULL; RETURN return_value; behaves differently than: RETURN NULL; We've fixed the afflicted trigger functions by adding a RETURN NULL; inside that last IF block, but: - Is this behavior required by standard? - If not, do we want to keep this behavior? - If we keep this behavior, should the triggering operation be suppressed when (NOT return_value IS NOT NULL) instead of when (return_value IS NOT DISTINCT FROM NULL)? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
