So it seems I got bitten today by this to-do list item:
Have AFTER triggers execute after the appropriate SQL statement in a
function, not at the end of the function
Under normal circumstances, delaying this stuff until the end of the
function doesn't bother me; in fact I've even used it to get around the
fact that SET CONSTRAINTS won't let you delay non-referential constraint
checks.
However, it seems that cascading deletes are also delayed, which leads to
a pretty serious problem. The following code:
INSERT INTO master (master_id) VALUES (400);
INSERT INTO dependent (master_id) VALUES (400);
DELETE FROM master WHERE master_id = 400;
works just fine outside a function, but inside a function it fails with
ERROR: $1 referential integrity violation - key referenced from
dependent not found in master
It seems that the integrity check for the dependent is happening before the
cascaded delete, but the check is operating not on the data at the time of
the statement, but the data as it stands after the statement following the
one that triggered the check. Ouch!
Having spent the better part of a day tracking down this problem
(because of course, as always, it only decides to appear in one's own
code after it's gotten quite complex), I think for a start it would
be a really, really good idea to put something about this in the
documentation for the 7.4 release. Probably the SET CONSTRAINTS page
would be a good place to have it, or at least a pointer to it.
In the long run, of course, I'd like to see a fix, but preferably after
we fix the system to allow delay of non-referential constraints as well,
since I am use this "bug" now in production code to delay constraint
checking for non-referential constraints. (You might even document that
workaround in the SET CONSTRAINTS manual page, with an appropriate
warning, if one seems necessary.)
I've attached a short shell script that will demonstrate the problem.
cjs
--
Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org
Don't you know, in this new Dark Age, we're all light. --XTC#!/bin/sh
schema="pfcb_test"
cat <<EOF | psql -q
SET client_min_messages TO WARNING;
CREATE SCHEMA $schema;
SET search_path TO $schema;
\qecho
CREATE TABLE junk (junk int); -- just to supress some select results
CREATE TABLE master (master_id int PRIMARY KEY);
CREATE TABLE dependent (dependent_id serial PRIMARY KEY,
master_id int REFERENCES master ON DELETE CASCADE);
\qecho Outside a function, inserting into the dependent before the master
\qecho correctly produces a referential integrity error:
BEGIN;
SET CONSTRAINTS ALL IMMEDIATE;
INSERT INTO dependent (master_id) VALUES (200);
--INSERT INTO master (master_id) VALUES (200);
COMMIT;
\qecho
CREATE OR REPLACE FUNCTION reverse_order_insert() RETURNS int AS '
SET CONSTRAINTS ALL IMMEDIATE;
INSERT INTO dependent (master_id) VALUES (300);
INSERT INTO master (master_id) VALUES (300);
SELECT 0;
' LANGUAGE 'SQL';
\qecho The reverse order insert, when in a function, somehow passes
\qecho the integrity constraint, even in immediate mode:
INSERT INTO junk SELECT reverse_order_insert();
SELECT count(*) = 1 AS reverse_order_insert_in_function_succeeded
FROM master NATURAL JOIN dependent
WHERE master_id = 300;
\qecho Outside a function, a cascaded delete works:
INSERT INTO master (master_id) VALUES (100);
INSERT INTO dependent (master_id) VALUES (100);
DELETE FROM master WHERE master_id = 100;
SELECT count(*) = 0 AS inserted_row_not_present
FROM master NATURAL JOIN dependent
WHERE master_id = 100;
CREATE OR REPLACE FUNCTION delete_using_cascade() RETURNS int AS '
INSERT INTO master (master_id) VALUES (400);
INSERT INTO dependent (master_id) VALUES (400);
DELETE FROM master WHERE master_id = 400;
SELECT 0;
' LANGUAGE 'SQL';
\qecho A properly ordered insert and cascaded delete, inside a function,
\qecho does not work:
INSERT INTO junk SELECT delete_using_cascade();
\qecho
DROP SCHEMA $schema CASCADE;
EOF
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
