On Fri, Nov 20, 2020 at 9:37 PM Andreas Karlsson <andreas@proxel.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!
