Hello list
While testing an application with PostgreSQL 9.5, we experienced an issue involving aborted subtransactions and SELECT FOR UPDATE. In certain situations, a locking query doesn't return rows that should be visible and already locked by the current transaction.
I bisected this down to commit 27846f02c176eebe7e08ce51ed4d52140454e196 "Optimize locking a tuple already locked by another subxact"
This issue is also reproducible on the current master branch. In an assertions-enabled build, it traps an assertion in HeapTupleHeaderGetCmax called by heap_lock_tuple. The following test case demonstrates the issue...
CREATE TABLE IF NOT EXISTS testcase(
id int PRIMARY KEY,
balance numeric
);
INSERT INTO testcase VALUES (1, 0);
BEGIN;
SELECT * FROM testcase WHERE
testcase.id = 1 FOR UPDATE;
UPDATE testcase SET balance = balance + 400 WHERE id=1;
SAVEPOINT subxact;
UPDATE testcase SET balance = balance - 100 WHERE id=1;
ROLLBACK TO SAVEPOINT subxact;
-- "division by zero" shouldn't occur because I never deleted any rows
SELECT 1/count(*) from (
SELECT * FROM testcase WHERE id=1 FOR UPDATE
)x;
ROLLBACK;
Regards,
Marti Raudsepp