Обсуждение: 回复： bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)
Thanks your reply.
I understand what you mean and have tried to correct this patch.
According to the previous use case, the result obtained is as follows:
According to the previous use case, the result obtained is as follows:
id | name | year | xmax | xmin | ctid
----+----------+------+------+------+-------
1 | liuwei | 20 | 0 | 859 | (0,1)
2 | zhangbin | 30 | 866 | 866 | (0,7)
3 | fuguo | 44 | 866 | 866 | (0,8)
4 | yihe | 33 | 0 | 865 | (0,6)
4 | yihe | 33 | 0 | 866 | (0,9)
(5 rows)
----+----------+------+------+------+-------
1 | liuwei | 20 | 0 | 859 | (0,1)
2 | zhangbin | 30 | 866 | 866 | (0,7)
3 | fuguo | 44 | 866 | 866 | (0,8)
4 | yihe | 33 | 0 | 865 | (0,6)
4 | yihe | 33 | 0 | 866 | (0,9)
(5 rows)
At present, the behavior of the number of rows for ‘id’ 2 and 3 appears to be normal, but there is duplicate data in the data for ‘id’ 4.
According to what you said, this is a normal manifestation of transaction isolation level.
But there are still differences between the results and those of Oracle(no duplicate data 'id' 4).
After that I have tried several scenarios in Oracle and PG:
1、session1： insert， session2：merge into； duplicate data may also occur （pg and oracle consistent）.
2、session1: update + insert ,session2： merge into； there will be no duplicate data in oracle ，pg has duplicate data.
It looks like there is an exclusive lock between the update statement and merge statement in oracle. After submitting both update and insert, merge will proceed with locking and execution.
(Of course, this is just my guess.)
However, it seems that both PG and Oracle have no obvious issues, and their respective designs are reasonable.
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?
Regards,
wenjiang zhang
------------------ 原始邮件 ------------------
发件人: "Dean Rasheed" <dean.a.rasheed@gmail.com>;
发送时间: 2024年2月22日(星期四) 凌晨1:00
收件人: "zwj"<sxzwj@vip.qq.com>;
抄送: "pgsql-hackers"<pgsql-hackers@lists.postgresql.org>;
主题: Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)
>
> On the face of it, the simplest fix is to tweak is_simple_union_all()
> to prevent UNION ALL subquery pullup for MERGE, forcing a
> subquery-scan plan. A quick test shows that that fixes the reported
> issue.
>
> However, that leaves the question of whether we should do the same for
> UPDATE and DELETE.
>
Attached is a patch that prevents UNION ALL subquery pullup in MERGE only.
I've re-used and extended the isolation test cases added by
1d5caec221, since it's clear that replacing the plain source relation
in those tests with a UNION ALL subquery that returns the same results
should produce the same end result. (Without this patch, the UNION ALL
subquery is pulled up, EPQ rechecking fails to re-find the match, and
a WHEN NOT MATCHED THEN INSERT action is executed instead, resulting
in a primary key violation.)
It's still not quite clear whether preventing UNION ALL subquery
pullup should also apply to UPDATE and DELETE, but I wasn't able to
find any live bug there, so I think they're best left alone.
This fixes the reported issue, though it's worth noting that
concurrent WHEN NOT MATCHED THEN INSERT actions will still lead to
duplicate rows being inserted, which is a limitation that is already
documented [1].
[1] https://www.postgresql.org/docs/current/transaction-iso.html
Regards,
Dean
Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)
От
Dean Rasheed
Дата:
On Thu, 22 Feb 2024 at 03:46, zwj <sxzwj@vip.qq.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 youhave 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