Thread: BUG #13769: SELECT ... FROM a JOIN b FOR UPDATE a; BREAKS SNAPSHOT

The following bug has been logged on the website:

Bug reference:      13769
Logged by:          Konstantin Evteev
Email address:      konst583@mail.ru
PostgreSQL version: 9.2.13
Operating system:   Debian GNU/Linux 7.9 (wheezy)
Description:

Select of 2-nd transaction will return new value from a and old value from
b

CREATE TABLE a
(
  id integer,
  val1 integer
);

CREATE TABLE b
(
  id integer,
  val1 integer
);

insert into a select 1,2;
insert into b select 1,3;

--FIRST TRANSACTION
begin

select * from a inner join b
on
    a.id = b.id
where
    a.id =1 for update of a;

update a set val1 = 0 where id = 1;
update b set val1 =0 where id = 1;

end

-- SECOND TRANSACTION (starts during executing of first transaction)
 select * from a
inner join
b on a.id = b.id
 where a.id =1 for update of a

 --result:
 1;0;1;3

konst583@mail.ru writes:
> Select of 2-nd transaction will return new value from a and old value from
> b

That's what FOR UPDATE is designed to do.  If you don't like it, use
serializable mode.

            regards, tom lane