SELECT FOR UPDATE regression in 9.5

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: Jaan-Magnus Tammepuu <magnus(at)voicecom(dot)ee>
Subject: SELECT FOR UPDATE regression in 9.5
Date: 2016-09-06 16:02:15
Message-ID: CABRT9RC81YUf1=jsmWopcKJEro=VoeG2ou6sPwyOUTx_qteRsg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2016-09-06 16:13:00 Re: Forbid use of LF and CR characters in database and role names
Previous Message Masahiko Sawada 2016-09-06 15:47:42 Re: Quorum commit for multiple synchronous replication.