The following bug has been logged on the website: Bug reference: 7798 Logged by: Lars Kanis Email address: l...@greiz-reinsdorf.de PostgreSQL version: 9.1.7 Operating system: Linux - Ubuntu 12.04 Description:
Everything in isolation level "read committed". Database setup: CREATE TABLE cards ( id serial primary key, auswname varchar ); CREATE TABLE cards_status_cache ( id INT PRIMARY KEY, response_ok VARCHAR ); CREATE TABLE card_actions ( id serial PRIMARY KEY, card_id integer NOT NULL DEFAULT 0, uid VARCHAR ); INSERT INTO cards ( id, auswname ) VALUES (1, 'test1'); INSERT INTO card_actions (id, card_id) VALUES (5, 1); CREATE OR REPLACE RULE expire_cards_status_cache AS ON UPDATE TO card_actions DO ( SELECT id FROM cards WHERE (id = OLD.card_id) OR (id = NEW.card_id) FOR UPDATE OF cards; DELETE FROM cards_status_cache WHERE (id = OLD.card_id) OR (id = NEW.card_id); ); Now run within session one: BEGIN; SELECT id FROM cards WHERE id=1 FOR UPDATE; INSERT INTO cards_status_cache (id, response_ok) VALUES (1, 'resp1'); SELECT pg_sleep(10); COMMIT; While above pg_sleep() is running, start in a second session: UPDATE card_actions SET uid = 'abcdef01' WHERE card_actions.id = 5; SELECT * FROM cards_status_cache; The second session blocks until the first has finished, because the rule on card_actions acquires a row lock that is already held by the first session. When session one has committed, the DELETE statement should find the inserted row (in isolation level "read committed"), but does not. So the result of session two should be empty, but instead one row is returned. If the UPDATE statement is executed twice within a transaction, the second run sees the row to delete, but not the first one, that waits for the lock. Regards, Lars -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
