Date: Thu, 19 Apr 2007 14:55:21 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Doing a conditional insert/update
Message-ID: <20070419185521.GB14821@phlogiston.dyndns.org>
References: <978075550704191127g781a4a26i400f4fc80d491df6@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <978075550704191127g781a4a26i400f4fc80d491df6@mail.gmail.com>
User-Agent: Mutt/1.5.9i

On Thu, Apr 19, 2007 at 08:27:30PM +0200, Markus Holzer wrote:
> How do I perform a conditional insert/update?

The short answer is that you can't, at least not the way you want to.

> already in the table, or an update if it is. I have currently solved this by
> SELECTing for the primary key, then looking if there is a row, and if there
> is I do an UPDATE otherwise I do an INSERT. But since this is a web app this
> way of course leaves a big race condition.

Only if you don't do it in one transaction.  If you use a
savepoint to do this, then you can BEGIN, SELECT FOR UPDATE, then if
you get it UPDATE; if you don't, start a savepoint, insert, and if
you don't get a failure, commit the whole thing.  If you _do_ get a
failure, you rollback the savepoint, and then do the update.  There's
a (cleaner) example of this in the UPDATE doc from the 8.2 release.

But I have doubts that your program design is right if this is the
approach you're trying to take (it's not impossible that it's the
right way, just that this is often a workaround for a dodgy data
model in the first place).  What is the conflict you're trying to
avoid?

A
-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
		--Philip Greenspun