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/
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 |