Обсуждение: Rows go missing when selecting "for update" after savepoint "play"

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/

On Thu, Oct 06, 2016 at 08:05:33PM +0200, hubert depesz lubaczewski wrote:
> Used postgresql: 9.5.4 (from pgdg apt archive)
> system: ubuntu trusty

some more tests showed that the problem is also missing in 9.4 and 9.6,
so it looks like it was introduced in 9.5, and then fixed in 9.6...

Best regards,

depesz

hubert depesz lubaczewski wrote:
> On Thu, Oct 06, 2016 at 08:05:33PM +0200, hubert depesz lubaczewski wrote:
> > Used postgresql: 9.5.4 (from pgdg apt archive)
> > system: ubuntu trusty
>
> some more tests showed that the problem is also missing in 9.4 and 9.6,
> so it looks like it was introduced in 9.5, and then fixed in 9.6...

I think this is the bug fixed by this commit

commit f337658850801706334fcfec07928a804fb4e24f
Author:     Alvaro Herrera <alvherre@alvh.no-ip.org>
AuthorDate: Fri Sep 9 15:54:29 2016 -0300
CommitDate: Fri Sep 9 15:54:29 2016 -0300

    Fix locking a tuple updated by an aborted (sub)transaction


So you're now "Waiting for 9.5.5", I'm afraid.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Oct 06, 2016 at 04:49:03PM -0300, Alvaro Herrera wrote:
> hubert depesz lubaczewski wrote:
> > On Thu, Oct 06, 2016 at 08:05:33PM +0200, hubert depesz lubaczewski wrote:
> > > Used postgresql: 9.5.4 (from pgdg apt archive)
> > > system: ubuntu trusty
> >
> > some more tests showed that the problem is also missing in 9.4 and 9.6,
> > so it looks like it was introduced in 9.5, and then fixed in 9.6...
>
> I think this is the bug fixed by this commit
>
> commit f337658850801706334fcfec07928a804fb4e24f
> Author:     Alvaro Herrera <alvherre@alvh.no-ip.org>
> AuthorDate: Fri Sep 9 15:54:29 2016 -0300
> CommitDate: Fri Sep 9 15:54:29 2016 -0300
>
>     Fix locking a tuple updated by an aborted (sub)transaction
>
>
> So you're now "Waiting for 9.5.5", I'm afraid.

Thanks a lot. Checked, and it looks that 9.5 HEAD is free of the bug, so
I'll just wait. It's ok.

Best regards,

depesz