Jeff Eckermann wrote:
> --- 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.
So I have to watch out for transactions on this?
Essentially what I'm trying to do is one of the following two:
if exists update a field to field+1 on one record
if it doesn't exist, insert a row with field = 1
In response to
pgsql-general by date
|Next:||From: Moharar, Mausumi (YBUSA-CDR)||Date: 2004-05-26 21:17:17|
|Subject: Problem to run MasterInit for Rserv in Mirroring of Databases|
|Previous:||From: Tom Lane||Date: 2004-05-26 20:31:27|
|Subject: Re: Problem with sequences on a reload of a pg_dump file |