pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: marc@oscar.eng.cv.net
To: pgsql-sql@postgresql.org
Subject: Re: how do I update or insert efficently in postgres
Date: Tue, 13 Nov 2001 22:27:37 GMT
Message-ID: <slrn9v37ho.314k.marc@oscar.eng.cv.net> (raw)
References: <3557.1005685484@sss.pgh.pa.us>

In article <3557.1005685484@sss.pgh.pa.us>, Tom Lane wrote:
> 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
> 

after the first pass it will be mostly updates and I know I have a
flawed design, but I have only 1 batch job that loads information so
there should be no major problems there, yes I know famious last
words. And I need to get something finished quickly to start
generating reports off of it.  

Thanks 

marc



> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



view thread (5+ messages)  latest in thread

Message-ID: <slrn9v37ho.314k.marc@oscar.eng.cv.net>
Permalink:  ../slrn9v37ho.314k.marc@oscar.eng.cv.net/
Also on:    postgresql.org/message-id/slrn9v37ho.314k.marc@oscar.eng.cv.net

 · 

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: marc@oscar.eng.cv.net
  Subject: Re: how do I update or insert efficently in postgres
  In-Reply-To: <slrn9v37ho.314k.marc@oscar.eng.cv.net>

* 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