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 07:02:51
Message-ID: 4D1C2E9B.4020904@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Marko Tiikkaja wrote:
> I have no idea why it worked in the past, but the patch was never 
> designed to work for UPSERT.  This has been discussed in the past and 
> some people thought that that's not a huge deal.

It takes an excessively large lock when doing UPSERT, which means its 
performance under a heavy concurrent load can't be good.  The idea is 
that if the syntax and general implementation issues can get sorted out, 
fixing the locking can be a separate performance improvement to be 
implemented later.  Using MERGE for UPSERT is the #1 use case for this 
feature by a gigantic margin.  If that doesn't do what's expected, the 
whole implementation doesn't provide the community anything really worth 
talking about.  That's why I keep hammering on this particular area in 
all my testing.

One of the reflexive "I can't switch to PostgreSQL easily" stopping 
points for MySQL users is "I can't convert my ON DUPLICATE KEY UPDATE 
code".  Every other use for MERGE is a helpful side-effect of adding the 
implementation in my mind, but not the primary driver of why this is 
important.  My hints in this direction before didn't get adopted, so I'm 
saying it outright now:  this patch must have an UPSERT implementation 
in its regression tests.  And the first thing I'm going to do every time 
a new rev comes in is try and break it with the pgbench test I 
attached.  If Boxuan can start doing that as part of his own testing, I 
think development here might start moving forward faster.  I don't care 
so much about the rate at which concurrent UPSERT-style MERGE happens, 
so long as it doesn't crash.  But that's where this has been stuck at 
for a while now.

-- 
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


In response to

Responses

pgsql-hackers by date

Next:From: Joel JacobsonDate: 2010-12-30 07:13:20
Subject: Re: pg_dump --split patch
Previous:From: Robert HaasDate: 2010-12-30 06:44:41
Subject: future-proofing relkind tests, take two

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