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

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема Rows go missing when selecting "for update" after savepoint "play"
Дата
Msg-id 20161006180533.5wq6fcqbdeam3tib@depesz.com
обсуждение исходный текст
Ответы Re: Rows go missing when selecting "for update" after savepoint "play"  (hubert depesz lubaczewski <depesz@depesz.com>)
Список 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/

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #14351: Upsert not working in case of partitioned tables
Следующее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Rows go missing when selecting "for update" after savepoint "play"