From: Tom Lane <tgl@sss.pgh.pa.us>
To: marc@oscar.eng.cv.net
Cc: pgsql-sql@postgresql.org
Subject: Re: how do I update or insert efficently in postgres
Date: Tue, 13 Nov 2001 16:04:44 -0500
Message-ID: <3557.1005685484@sss.pgh.pa.us> (raw)
In-Reply-To: <slrn9v2ouq.30qa.marc@oscar.eng.cv.net>
References: <slrn9v2ouq.30qa.marc@oscar.eng.cv.net>
marc@oscar.eng.cv.net (Marc Spitzer) writes:
> I need to do the follwoing logic for a db I am building:
> if row exists update some fields
> else insert all fields
> I have come across this befor and have used select to drive the
> choice, if I could get the row update else insert.
Which case do you think will be more common? If UPDATE is the more
common scenario then it's a win to do
UPDATE set modifiable-fields = whatever WHERE key = whatever
if (zero rows updated)
INSERT ...
Alternatively you can do
INSERT ...
if (fail due to duplicate key)
UPDATE ...
if you think INSERT is the more common case. (This all assumes you
have a unique key for the table, but if you don't, then what do you
mean by "the row already exists"?)
Neither of these are perfect, however. The former has a race condition
if two clients might try to insert the same key at about the same time.
You can improve it to
BEGIN;
UPDATE set modifiable-fields = whatever WHERE key = whatever
if (zero rows updated)
{
INSERT ...
if (fail due to duplicate key)
{
ABORT;
loop back to BEGIN;
}
}
COMMIT;
but this is kinda ugly. (Of course, if you could have two clients
independently inserting/updating the same row at the same time, you
have problems anyway: which one should win, and why? I think the
coding difficulty may tell you you have a design problem.)
As for the INSERT-then-UPDATE approach, you have the same problem
that you have to ABORT and start a new transaction if the INSERT
fails. This is uncool if you really want the whole thing to be part
of a larger transaction.
But as long as you've guessed right about which case is more common,
you have only one query most of the time.
regards, tom lane
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: tgl@sss.pgh.pa.us, marc@oscar.eng.cv.net
Subject: Re: how do I update or insert efficently in postgres
In-Reply-To: <3557.1005685484@sss.pgh.pa.us>
* 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