Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

On Thu, 22 Feb 2024 at 03:46, zwj <sxzwj(at)vip(dot)qq(dot)com> wrote:
>
> If I want to get the same results as Oracle, do I need to adjust the lock behavior of the update and merge statements?
> If I want to achieve the same results as Oracle, can I achieve exclusive locking by adjusting update and merge? Do you have any suggestions?
>

I think that trying to get the same results in Oracle and Postgres may
not always be possible. Each has their own (probably quite different)
implementation of these features, that simply may not be compatible.

In Postgres, MERGE aims to make UPDATE and DELETE actions behave in
the same way as standalone UPDATE and DELETE commands under concurrent
modifications. However, it does not attempt to prevent INSERT actions
from inserting duplicates.

In that context, the UNION ALL issue is a clear bug, and I'll aim to
get that patch committed and back-patched sometime in the next few
days, if there are no objections from other hackers.

However, the issue with INSERT actions inserting duplicates is a
design choice, rather than something that we regard as a bug. It's
possible that a future version of Postgres might improve MERGE,
providing some way round that issue, but there's no guarantee of that
ever happening. Similarly, it sounds like Oracle also sometimes allows
duplicates, as well as having other "bugs" like the one discussed in
[1], that may be difficult for them to fix within their
implementation.

In Postgres, if the target table is subject to concurrent inserts (or
primary key updates), it might be better to use INSERT ... ON CONFLICT
DO UPDATE [2] instead of MERGE. That would avoid inserting duplicates
(though I can't say how compatible that is with anything in Oracle).

Regards,
Dean

[1] https://www.postgresql.org/message-id/CAEZATCV_6t5E57q7HsWQBX6a5YOjN5o7K-HicZ8a73EPzfwo=A@mail.gmail.com

[2] https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT