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/
