On Sun, 4 Feb 2024 at 18:19, zwj <sx...@vip.qq.com> wrote: > > I found an issue while using the latest version of PG15 > (8fa4a1ac61189efffb8b851ee77e1bc87360c445). > > This issue is related to Megre into and other concurrent statements being > written. > I obtained different results in Oracle and PG using the same statement > below. > Based on my personal judgment, the result set of this statement in pg is > abnormal. >
I would say that the Postgres result is correct here. The merge update action would have updated the row with id=2, setting year=30, but since there is a concurrent update, changing that id to 5, it waits to see if that transaction commits. Once it does, the id no longer matches, and merge update action is aborted, and the "not matched" action is performed instead. That's consistent with the result that you'd get if you performed the 2 transactions serially, doing the update first then the merge. Oracle, on the other hand, proceeds with the merge update action, after the other transaction commits, even though the row being updated no longer matches the join condition. Not only does that seem to be incorrect, it's inconsistent with what both Oracle and Postgres do if you replace the merge command with the equivalent standalone update for that row: "update mergeinto_0023_tb01 set year = 30 where id = 2" in the second session. So I'd say that this is an Oracle bug. Regards, Dean
