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

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

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: Greg Smith <greg(at)2ndquadrant(dot)com>, Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, 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: 2011-01-03 13:12:10
Message-ID: 4D21CB2A.2080407@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 03.01.2011 11:37, Simon Riggs wrote:
> On Mon, 2011-01-03 at 01:53 -0500, Greg Smith wrote:
>
>> In advance of the planned but not available yet ability to
>> lock individual index key values, locking the whole table is the only
>> possible implementation that can work correctly here I'm aware of.
>
> This was discussed here
> http://archives.postgresql.org/pgsql-hackers/2010-10/msg01903.php
> with suggested resolutions for this release here
> http://archives.postgresql.org/pgsql-hackers/2010-10/msg01907.php
>
> In summary, that means we can either
>
> 1. Lock the table for ShareRowExclusiveLock
>
> 2. throw a SERIALIZABLE error, if we come up against a row that cannot
> be neither MATCHED nor NON MATCHED.
>
> 3. Bounce the patch to 9.2, commit early and then work on a full
> concurrency solution before commit. The solution strawman is something
> like EvalPlanQual with a new snapshot for each re-checked row, emulating
> the pattern of snapshots/rechecks that would happen in a PL/pgSQL
> version of an UPSERT.
>
> Either way, we're saying that MERGE will not support concurrent
> operations safely, in this release.
>
> Given the continued lack of test cases for this patch, and the possible
> embarrassment over not doing concurrent actions, do we think (3) is the
> right road?

This patch has never tried to implement concurrency-safe upsert. It 
implements the MERGE command as specified by the SQL standard, nothing 
more, nothing less. Let's not move the goalposts. Googling around, at 
least MS SQL Server's MERGE command is the same 
(http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx). 
There is nothing embarrassing about it, we just have to document it clearly.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2011-01-03 13:35:53
Subject: Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid
Previous:From: Christian UllrichDate: 2011-01-03 13:11:37
Subject: SSPI client authentication in non-Windows builds

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