Обсуждение: Rows go missing when selecting "for update" after savepoint "play"
Rows go missing when selecting "for update" after savepoint "play"
От
hubert depesz lubaczewski
Дата:
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/
Re: Rows go missing when selecting "for update" after savepoint "play"
От
hubert depesz lubaczewski
Дата:
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
Re: Rows go missing when selecting "for update" after savepoint "play"
От
hubert depesz lubaczewski
Дата:
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