回复： 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:

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)

  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(dot)a(dot)rasheed(at)gmail(dot)com&gt;;
发送时间:&nbsp;2024年2月22日(星期四) 凌晨1:00
收件人:&nbsp;"zwj"<sxzwj(at)vip(dot)qq(dot)com&gt;;
抄送:&nbsp;"pgsql-hackers"<pgsql-hackers(at)lists(dot)postgresql(dot)org&gt;;
主题:&nbsp;Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

On Tue, 20 Feb 2024 at 14:49, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com&gt; wrote:
&gt;
&gt; On the face of it, the simplest fix is to tweak is_simple_union_all()
&gt; to prevent UNION ALL subquery pullup for MERGE, forcing a
&gt; subquery-scan plan. A quick test shows that that fixes the reported
&gt; issue.
&gt;
&gt; However, that leaves the question of whether we should do the same for
&gt; UPDATE and DELETE.
&gt;

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