Re: insert/update

--- Tom Allison <tallison(at)tacocat(dot)net> wrote:
> I seemed to remember being able to do this but I
> can't find the docs.
>
> Can I run a sql query to insert new or update
> existing rows in one query?
>
> Otherwise I have to run a select query to see if
> it's there and then
> another one to update/insert.

This is what you have to do.

This question comes up a lot on the lists. You can
read endless discussions about it if you want to
search the archives.

The issue is concurrency, i.e. multiple users
accessing the data at the same time, and perhaps two
of them wanting to do the same update-else-insert
combination at the same time. Then you have the so
called "race condition", i.e. user1 does a select,
finds the record does not exist, attempts to insert;
in between those, user2 inserts the row. So, you now
either have duplicate data (bad), or user1's insert
fails because of a unique constraint (also bad,
because the operation has failed).

The only way to guarantee against this is to lock the
table for the duration of the exercise, which prevents
any concurrent access at all. This may be acceptable
if you have few users, or a low insert/update load,
but may be a performance killer otherwise.

Every now and then someone pops up on the list(s)
claiming to have found some new miracle method for
getting around these limitations, but no such has yet
been proven.

>
> What I'm trying to do is create a counter for each
> key, insert a value
> of 1 or increment the value by 1 and then set
> another specific row
> (where key = $key) to always increment by 1.
>
> And the more I type, the more this sounds like the
> answer is going to be
> part function, part trigger.... Maybe I should post
> to 'novice' for a
> while! ;)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to
> majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list
cleanly

__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/