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: Robert Haas <robertmhaas(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, 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 17:01:00
Message-ID: 4D2200CC.9020306@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 03.01.2011 18:49, Simon Riggs wrote:
> On Mon, 2011-01-03 at 18:35 +0200, Heikki Linnakangas wrote:
>> On 03.01.2011 18:29, Simon Riggs wrote:
>>> On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
>>>
>>>> It works in read committed mode, because you acquire a new snapshot
>>>> after the LOCK TABLE, and anyone else who modified the table must commit
>>>> before the lock is granted. In serializable mode you get a serialization
>>>> error.
>>>
>>> If its not safe without this
>>>
>>> LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE
>>>
>>> then we should do that automatically, and document that.
>>
>> No we should not. The SQL standard doesn't require that, and it would
>> unnecessarily restrict concurrent updates on unrelated rows in the table.
>
> If we do that, then we definitely need a catch-all WHEN statement, so
> that we can say
>
> WHEN NOT MATCHED
>    INSERT
> WHEN MATCHED
>    UPDATE
> ELSE
>    { INSERT into another table so we can try again in a minute
>   or RAISE error }
>
> Otherwise we will silently drop rows. Throwing an error every time isn't
> useful behaviour.

An ELSE clause would be nice, but it's not related to the question at 
hand. Only some serialization anomalities result in a row that matches 
neither WHEN MATCHED nor WHEN NOT MATCHED. Others result in a duplicate 
key exception, for example.

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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-01-03 17:09:32
Subject: Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid
Previous:From: Simon RiggsDate: 2011-01-03 16:49:23
Subject: Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid

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