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

От

Andy Fan

Тема

Дата

20 ноября 2020 г. в 14:25:23

Msg-id

CAKU4AWo-Uz5LxEPuJDDVF4CioHaYjOKZZXw6SQqRC-Q8DvmL3g@mail.gmail.com

обсуждение

Ответ на

Re: Different results between PostgreSQL and Oracle for "for update" statement (Andreas Karlsson)

Список

pgsql-hackers

Дерево обсуждения

Different results between PostgreSQL and Oracle for "for update" statement Andy Fan <zhihui.fan1213@gmail.com> 19 ноября 2020 г. в 13:11:48

Re: Different results between PostgreSQL and Oracle for "for update" statement Tom Lane <tgl@sss.pgh.pa.us> 19 ноября 2020 г. в 15:49:45

Re: Different results between PostgreSQL and Oracle for "for update" statement Andy Fan <zhihui.fan1213@gmail.com> 20 ноября 2020 г. в 08:57:09

Re: Different results between PostgreSQL and Oracle for "for update" statement Andreas Karlsson <andreas@proxel.se> 20 ноября 2020 г. в 13:37:03

Re: Different results between PostgreSQL and Oracle for "for update" statement Andy Fan <zhihui.fan1213@gmail.com> 20 ноября 2020 г. в 14:25:23

Re: Different results between PostgreSQL and Oracle for "for update" statement Andreas Karlsson <andreas@proxel.se> 20 ноября 2020 г. в 23:03:32

Re: Different results between PostgreSQL and Oracle for "for update" statement Peter Geoghegan <pg@bowt.ie> 21 ноября 2020 г. в 01:04:20

Re: Different results between PostgreSQL and Oracle for "for update" statement Andy Fan <zhihui.fan1213@gmail.com> 21 ноября 2020 г. в 08:58:47

Re: Different results between PostgreSQL and Oracle for "for update" statement Pavel Stehule <pavel.stehule@gmail.com> 21 ноября 2020 г. в 15:27:09

Re: Different results between PostgreSQL and Oracle for "for update" statement Andy Fan <zhihui.fan1213@gmail.com> 22 ноября 2020 г. в 02:29:21

Re: Different results between PostgreSQL and Oracle for "for update" statement Peter Geoghegan <pg@bowt.ie> 21 ноября 2020 г. в 21:55:57

Re: Different results between PostgreSQL and Oracle for "for update" statement Andy Fan <zhihui.fan1213@gmail.com> 22 ноября 2020 г. в 02:35:30

Hi Andreas:

Thanks for your input.

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.