Skip site navigation (1) Skip section navigation (2)

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

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
Cc: Boxuan Zhai <bxzhai2010(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid
Date: 2010-12-30 02:39:38
Message-ID: 4D1BF0EA.2040409@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Attachment: test-merge.sh
Description: application/x-sh (1.2 KB)
Attachment: update-merge.sql
Description: text/x-sql (791 bytes)
Attachment: simple-merge-test.sql
Description: text/x-sql (784 bytes)

In response to

Responses

pgsql-hackers by date

Next:From: Marko TiikkajaDate: 2010-12-30 02:45:31
Subject: Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid
Previous:From: Jie LiDate: 2010-12-30 01:54:10
Subject: Re: small table left outer join big table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group