Re: Different results between PostgreSQL and Oracle for "for update" statement

On 11/20/20 9:57 AM, Andy Fan wrote:
> Thank you for your attention. Your suggestion would fix the issue.  However
> The difference will cause some risks when users move their application
> from Oracle
> to PostgreSQL. So I'd like to think which behavior is more reasonable.

I think PostgreSQL's behavior is more reasonable since it only locks the
rows it claims to lock and no extra rows. This makes the code easy to
reason about. And PostgreSQL does not re-evaluate sub queries after
grabbing the lock which while it might be surprising to some people is
also a quite nice consistent behavior in practice as long as you are
aware of it.

I do not see why these two scenarios should behave differently (which I
think they would with your proposed patch):

== Scenario 1

create table su (a int, b int);
insert into su values(1, 1);

- session 1:
begin;
update su set b = 2 where b = 1;

- sess 2:
select * from su where a in (select a from su where b = 1) for update;

- sess 1:
commit;

== Scenario 2

create table su (a int, b int);
insert into su values(1, 1);

create table su2 (a int, b int);
insert into su2 values(1, 1);

- session 1:
begin;
update su set b = 2 where b = 1;
update su2 set b = 2 where b = 1;

- sess 2:
select * from su where a in (select a from su2 where b = 1) for update;

- sess 1:
commit;

Andreas