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
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