From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Doing a conditional insert/update
Date: Thu, 19 Apr 2007 14:55:21 -0400
Message-ID: <20070419185521.GB14821@phlogiston.dyndns.org> (raw)
In-Reply-To: <978075550704191127g781a4a26i400f4fc80d491df6@mail.gmail.com>
References: <978075550704191127g781a4a26i400f4fc80d491df6@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: Doing a conditional insert/update
In-Reply-To: <20070419185521.GB14821@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox