Rows go missing when selecting "for update" after savepoint "play"

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Rows go missing when selecting "for update" after savepoint "play"
Date: 2016-10-06 18:05:33
Message-ID: 20161006180533.5wq6fcqbdeam3tib@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Used postgresql: 9.5.4 (from pgdg apt archive)
system: ubuntu trusty

repeatable case:

CREATE TABLE depesz (id serial PRIMARY KEY, x TEXT);
BEGIN;
INSERT INTO depesz (id, x) VALUES (1,'x');
SELECT * FROM depesz WHERE id = 1 for UPDATE;
UPDATE depesz SET x = 'y' WHERE id = 1;
savepoint i_hate_activerecord;
SELECT * FROM depesz WHERE id = 1 for UPDATE;
UPDATE depesz SET x = 'aa' WHERE id = 1;
rollback to SAVEPOINT i_hate_activerecord;
SELECT * FROM depesz WHERE id = 1;
SELECT * FROM depesz WHERE id = 1 for UPDATE; -- here is the problem
SELECT * FROM depesz WHERE id = 1;
rollback;
DROP TABLE depesz;

on my test system running it looks like:

CREATE TABLE depesz (id serial PRIMARY KEY, x TEXT);
CREATE TABLE
BEGIN;
BEGIN
INSERT INTO depesz (id, x) VALUES (1,'x');
INSERT 0 1
SELECT * FROM depesz WHERE id = 1 for UPDATE;
id | x
----+---
1 | x
(1 row)

UPDATE depesz SET x = 'y' WHERE id = 1;
UPDATE 1
savepoint i_hate_activerecord;
SAVEPOINT
SELECT * FROM depesz WHERE id = 1 for UPDATE;
id | x
----+---
1 | y
(1 row)

UPDATE depesz SET x = 'aa' WHERE id = 1;
UPDATE 1
rollback to SAVEPOINT i_hate_activerecord;
ROLLBACK
SELECT * FROM depesz WHERE id = 1;
id | x
----+---
1 | y
(1 row)

SELECT * FROM depesz WHERE id = 1 for UPDATE;
id | x
----+---
(0 rows)

SELECT * FROM depesz WHERE id = 1;
id | x
----+---
1 | y
(1 row)

rollback;
ROLLBACK
DROP TABLE depesz;
DROP TABLE

I think that the marked select for update should return 1 row, because - well, why wouldn't it?

I checked, and the problem is not there in 10.devel.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message hubert depesz lubaczewski 2016-10-06 18:35:58 Re: Rows go missing when selecting "for update" after savepoint "play"
Previous Message Tom Lane 2016-10-06 17:56:56 Re: BUG #14351: Upsert not working in case of partitioned tables