回复： bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

Hi，hackers
 
 I may have discovered another issue in the concurrency scenario of merge,  and I am currently not sure if this new issue is related to the previous one. 
 It seems that it may also be an issue with the EPQ mechanism in the merge scenario? 
 I will provide this test case, hoping it will be helpful for you to fix related issues in the future.

    DROP TABLE IF EXISTS src1, tgt;
    CREATE TABLE src1 (a int, b text);
    CREATE TABLE tgt (a int, b text);
    INSERT INTO src1 SELECT x, 'Src1 '||x FROM generate_series(1, 3) g(x);
    INSERT INTO tgt SELECT x, 'Tgt '||x FROM generate_series(1, 6, 2) g(x);
    insert into src1 values(3,'src1 33');

 If I only execute merge , I will get the following error:
    merge into tgt a using src1 c on  a.a = c.a when matched then update set b = c.b when not matched then insert (a,b) values(c.a,c.b);  -- excute fail
    ERROR:  MERGE command cannot affect row a second time
    HIINT:  Ensure that not more than one source row matches any one target row.

 But when I execute the update and merge concurrently, I will get the following result set.

  --session1
    begin;

    update tgt set b = 'tgt333' where a =3;

  --session2
    merge into tgt a using src1 c on  a.a = c.a when matched then update set b = c.b when not matched then insert (a,b) values(c.a,c.b);  -- excute success
  --session1
    commit;
    select * from tgt;
     a |    b    
    ---+---------
     5 | Tgt 5
     1 | Src1 1
     2 | Src1 2
     3 | Src1 3
     3 | src1 33

  I think even if the tuple with id:3 is udpated, merge should still be able to retrieve new tuples with id:3,  and report the same error as above?

Regards,
wenjiang zhang

------------------ 原始邮件 ------------------
发件人: "jian he" <jian(dot)universality(at)gmail(dot)com&gt;;
发送时间:&nbsp;2024年2月29日(星期四) 中午11:04
收件人:&nbsp;"Dean Rasheed"<dean(dot)a(dot)rasheed(at)gmail(dot)com&gt;;
抄送:&nbsp;"Tom Lane"<tgl(at)sss(dot)pgh(dot)pa(dot)us&gt;;"zwj"<sxzwj(at)vip(dot)qq(dot)com&gt;;"pgsql-hackers"<pgsql-hackers(at)lists(dot)postgresql(dot)org&gt;;
主题:&nbsp;Re: bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)

On Wed, Feb 28, 2024 at 8:11 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com&gt; wrote:
&gt;
&gt; On Wed, 28 Feb 2024 at 09:16, jian he <jian(dot)universality(at)gmail(dot)com&gt; wrote:
&gt; &gt;
&gt; &gt; + oldcontext = MemoryContextSwitchTo(estate-&gt;es_query_cxt);
&gt; &gt; +
&gt; &gt; + node-&gt;as_epq_tupdesc = lookup_rowtype_tupdesc_copy(tupType, tupTypmod);
&gt; &gt; +
&gt; &gt; + ExecAssignExprContext(estate, &amp;node-&gt;ps);
&gt; &gt; +
&gt; &gt; + node-&gt;ps.ps_ProjInfo =
&gt; &gt; + ExecBuildProjectionInfo(castNode(Append, node-&gt;ps.plan)-&gt;epq_targetlist,
&gt; &gt; +
&gt; &gt; EvalPlanQualStart, EvalPlanQualNext will switch the memory context to
&gt; &gt; es_query_cxt.
&gt; &gt; so the memory context switch here is not necessary?
&gt; &gt;
&gt;
&gt; Yes it is necessary. The EvalPlanQual mechanism switches to the
&gt; epqstate-&gt;recheckestate-&gt;es_query_cxt memory context, which is not the
&gt; same as the main query's estate-&gt;es_query_cxt (they're different
&gt; executor states). Most stuff allocated under EvalPlanQual() is
&gt; intended to be short-lived (just for the duration of that specific EPQ
&gt; check), whereas this stuff (the TupleDesc and Projection) is intended
&gt; to last for the duration of the main query, so that it can be reused
&gt; in later EPQ checks.
&gt;
sorry for the noise. I understand it now.

Another small question:
for the Append case, we can set/initialize it at create_append_plan,
all other elements are initialized there,
why we set it at set_append_references.
just wondering.