Re: MERGE Specification

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: MERGE Specification
Date: 2008-04-22 08:02:44
Message-ID: 20080422080244.GB9883@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Apr 22, 2008 at 08:24:58AM +0100, Simon Riggs wrote:
> The way MERGE works we first test to see if it matches or not, then if
> not matched we would activate the NOT MATCHED action, which standard
> says must be an insert. The gap between the two actions allows a race
> condition to exist.
>
> We could close the gap by taking a lock on the row when we perform the
> is-matched test, but that would be expensive for bulk operations. ISTM
> the lock should be optional. Not sure what the default should be. Input
> welcome.

ISTM that if the original select does a SELECT FOR UPDATE then it
should work fine for UPDATEs since any update with overwrite the xmax
field anyway.

What you can't do is prevent multiple inserts. Though if its a unique
index you should be able to do the same trick as normal inserts: create
the row, try to insert into the index and if that fails fall back to
doing an update.

What you really need for this though is a non-fatal _bt_check_unique so
you can recover without having a savepoint for every row.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2008-04-22 08:13:38 Re: get rid of psql welcome message
Previous Message Magnus Hagander 2008-04-22 07:56:08 Re: pgkill on win32