Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid

Marko Tiikkaja wrote:
> As far as I can tell, this should work. I played around with the
> patch and the problem seems to be the VALUES:
>
> INTO Stock t
> USING (SELECT 30, 2000) AS s(item_id,balance)
> ON s.item_id=t.item_id
> WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
> WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
> ;
> MERGE 1

Good catch...while I think the VALUES syntax should work, that is a
useful workaround so I could keep testing. I rewrote like this
(original syntax commented out):

MERGE INTO Stock t
-- USING (VALUES(10,100)) AS s(item_id,balance)
USING (SELECT 10,100) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;

And that got me back again to concurrent testing.

Moving onto next two problems...the basic MERGE feature seems to have
stepped backwards a bit too. I'm now seeing these quite often:

ERROR: duplicate key value violates unique constraint
"pgbench_accounts_pkey"
DETAIL: Key (aid)=(176641) already exists.
STATEMENT: MERGE INTO pgbench_accounts t USING (SELECT 176641,1+(176641
/ 1000000)::integer,168,'') AS s(aid,bid,balance,filler) ON s.aid=t.aid
WHEN MATCHED THEN UPDATE SET abalance=abalance + s.balance WHEN NOT
MATCHED THEN INSERT VALUES(s.aid,s.bid,s.balance,s.filler);

On my concurrent pgbench test, which had been working before. Possibly
causing that, the following assertion is tripping:

TRAP: FailedAssertion("!(epqstate->origslot != ((void *)0))", File:
"execMain.c", Line: 1762)

That's coming from the following code:

void
EvalPlanQualFetchRowMarks(EPQState *epqstate)
{
ListCell *l;

Assert(epqstate->origslot != NULL);

foreach(l, epqstate->rowMarks)

Stepping back to summarize...here's a list of issues I know about with
the current v204 code:

1) VALUE syntax doesn't work anymore
2) Assertion failure in EvalPlanQualFetchRowMarks
3) Duplicate key bug (possibly a direct result of #3)
4) Attempts to use MERGE in a fuction spit back "ERROR: <table> is not
a known fuction"
5) The ctid junk attr handling needs to be reviewed more carefully,
based on author request.

I've attached the current revisions of all my testing code in hopes that
Boxuan might try and replicate these (this makes it simple to replicate
#1 through #3), and therefore confirm whether changes made do better.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books