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"
<[email protected]>;
????????: 2024??2??29??(??????) ????11:04
??????: "Dean Rasheed"<[email protected]>;
????: "Tom
Lane"<[email protected]>;"zwj"<[email protected]>;"pgsql-hackers"<[email protected]>;
????: Re: bug report: some issues about
pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445)
On Wed, Feb 28, 2024 at 8:11?6?2PM Dean Rasheed <[email protected]>
wrote:
>
> On Wed, 28 Feb 2024 at 09:16, jian he <[email protected]>
wrote:
> >
> > + oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
> > +
> > + node->as_epq_tupdesc = lookup_rowtype_tupdesc_copy(tupType,
tupTypmod);
> > +
> > + ExecAssignExprContext(estate, &node->ps);
> > +
> > + node->ps.ps_ProjInfo =
> > + ExecBuildProjectionInfo(castNode(Append,
node->ps.plan)->epq_targetlist,
> > +
> > EvalPlanQualStart, EvalPlanQualNext will switch the memory context to
> > es_query_cxt.
> > so the memory context switch here is not necessary?
> >
>
> Yes it is necessary. The EvalPlanQual mechanism switches to the
> epqstate->recheckestate->es_query_cxt memory context, which is not
the
> same as the main query's estate->es_query_cxt (they're different
> executor states). Most stuff allocated under EvalPlanQual() is
> intended to be short-lived (just for the duration of that specific EPQ
> check), whereas this stuff (the TupleDesc and Projection) is intended
> to last for the duration of the main query, so that it can be reused
> in later EPQ checks.
>
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.
