BUG #7798: Query in a rule does not see committed transaction

The following bug has been logged on the website:

Bug reference: 7798
Logged by: Lars Kanis
Email address: lars(at)greiz-reinsdorf(dot)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