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

Re: UPSERT

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Jonathan Scher <js(at)oxado(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: UPSERT
Date: 2007-03-02 15:41:56
Message-ID: 45E845C4.6030000@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> My instinct would be to follow your first strategy, i.e. detect which 
>> path is needed rather than try one and then if it fails do the other.
> 
> The very first thing you need to think about is how to solve the race
> condition problem, ie, two backends concurrently trying to insert
> identical data.  Until you have a plausible mechanism for that, the
> whole thing is pie-in-the-sky.

How about:

1. Insert new heap tuple
2. Try to insert the index tuple. If there's a duplicate tuple, lock the 
existing tuple instead of throwing an error.
3. If there was no duplicate, we're done.

4. Otherwise, kill the new tuple inserted in step 1, by setting it's 
xmin to InvalidTransactionId.
5. Perform the UPDATE on the existing tuple.

This requires one change to the indexam api: a duplicate key violation 
needs to lock the existing tuple instead of throwing an error.

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

In response to

Responses

  • Re: UPSERT at 2007-03-02 18:00:09 from Simon Riggs

pgsql-hackers by date

Next:From: Florian G. PflugDate: 2007-03-02 16:02:28
Subject: Re: UPSERT
Previous:From: Pavan DeolaseeDate: 2007-03-02 15:32:02
Subject: Re: HOT - whats next ?

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