pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (5+ messages)  latest in thread

Message-ID: <3557.1005685484@sss.pgh.pa.us>
Permalink:  ../3557.1005685484@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3557.1005685484@sss.pgh.pa.us

 · 

reply

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