On May 14, 2010, at 2:37 , Greg Stark wrote: > On Thu, May 13, 2010 at 10:25 PM, Florian Pflug <f...@phlo.org> wrote: >> C1: BEGIN >> C1: SELECT * FROM t WHERE id = 1 FOR UPDATE >> C2: BEGIN >> C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE >> C2: SELECT * FROM t -- Take snapshot before C1 commits >> C1: COMMIT >> C2: DELETE FROM t WHERE id = 1 >> C2: COMMIT >> > > Can you give an actual realistic example -- ie, not doing a select for > update and then never updating the row or with an explanation of what > the programmer is attempting to accomplish with such an unusual > sequence? The rest of the post talks about FKs but I don't see any > here...
The table "t" is supposed to represent the parent table of a FK constraint. The SELECT FOR UPDATE is done upon an INSERT to the child table to protect the parent row against concurrent deletion. I've used FOR UPDATE instead of FOR SHARE because I did test this against oracle also, and oracle does not support FOR SHARE. Here's a full example of a pair of FK triggers in PL/PGSQL that work correctly in READ COMMITTED mode but fail to enforce the constraint in SERIALIZABLE mode as the following sequence of commands show. With my proposal, the DELETE would again raise a serialization error and hence keep the constraint satisfied. C1: BEGIN C1: INSERT INTO child (parent_id) VALUES (1) -- Locks the parent row FOR UPDATE C2: BEGIN C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE C2: SELECT TRUE -- Take snapshot *before* C1 commits C1: COMMIT C2: DELETE FROM parent WHERE parent_id = 1 -- Succeeds C2: COMMIT ---------- CREATE TABLE parent (parent_id SERIAL NOT NULL PRIMARY KEY); CREATE TABLE child (child_id SERIAL NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL); CREATE FUNCTION ri_parent() RETURNS TRIGGER AS $body$ BEGIN PERFORM TRUE FROM child WHERE parent_id = OLD.parent_id; IF FOUND THEN RAISE SQLSTATE '23503' USING MESSAGE = 'Parent ' || OLD.parent_id || ' still referenced during ' || TG_OP; END IF; RETURN NULL; END; $body$ LANGUAGE PLPGSQL VOLATILE; CREATE TRIGGER ri_parent AFTER UPDATE OR DELETE ON parent FOR EACH ROW EXECUTE PROCEDURE ri_parent(); CREATE FUNCTION ri_child() RETURNS TRIGGER AS $body$ BEGIN PERFORM TRUE FROM parent WHERE parent_id = NEW.parent_id FOR UPDATE OF parent; IF NOT FOUND THEN RAISE SQLSTATE '23503' USING MESSAGE = 'Parent ' || NEW.parent_id || ' does not exist during ' || TG_OP; END IF; RETURN NULL; END; $body$ LANGUAGE PLPGSQL VOLATILE; CREATE TRIGGER ri_child AFTER INSERT OR UPDATE ON child FOR EACH ROW EXECUTE PROCEDURE ri_child(); ---------- best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
