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

Hi Andreas:

Thanks for your input.

On Fri, Nov 20, 2020 at 9:37 PM Andreas Karlsson <andreas(at)proxel(dot)se> wrote:

> 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 admit my way is bad after reading your below question, but I
would not think *it might be surprising to some people* is a good signal
for a design. Would you think "re-evaluate the quals" after grabbing the
lock should be a good idea? And do you know if any other database uses
the postgres's way or Oracle's way? I just heard Oracle might do the
re-check just some minutes before reading your reply and I also found
Oracle doesn't lock the extra rows per my test.

> I do not see why these two scenarios should behave differently (which I
> think they would with your proposed patch):
>
>
Good question! I think my approach doesn't make sense now!

--
Best Regards
Andy Fan