Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Solution to UPDATE...INSERT problem

From: Haroldo Stenger <hstenger(at)adinet(dot)com(dot)uy>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org,pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Solution to UPDATE...INSERT problem
Date: 2003-03-27 21:15:27
Message-ID: 1048799727.22659.45.camel@caverna (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-hackers
El jue, 27-03-2003 a las 03:41, Tom Lane escribió:
> "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> > INSERT INTO table SELECT 1, 'foo' EXCEPT SELECT 1, 'foo' FROM table WHERE
> > pkcol=1;
> > See? So now that INSERT statement will insert the row if it doesn't exist,
> > or insert zero rows if it does.  You are then guaranteed that your
> > transaction will not fail and rollback, so you can repeat your update, or do
> > the insert first and then the update, etc.
> Uh, why exactly do you think this is race-free?
> It looks fancy, but AFAICS the SELECT will return info that is correct
> as of its starting timestamp; which is not enough to guarantee that the
> INSERT won't conflict with another transaction doing the same thing
> concurrently.

This approach certainly reduces significantly the time span within which
a race could occur, compared to, say, using two separate statements, or
worse, two statements in two consecutive transactions. But race
conditions either exist or they don't, so you're right.

Now, up to my knowledge this problem was only a an intractable one in
PostgreSQL because of transactions going into abort state once a unique
restriction violation happened. If savepoints/nested transactions were
there, one would simply put the insert within a protected area, and
retry as much as needed.

That's my bet why other databases don't seem to have a problem with this
one. Am I right? Or do they have some magic solution other than locking
the whole table?


In response to

pgsql-hackers by date

Next:From: Kevin BrownDate: 2003-03-27 22:50:01
Subject: Re: Deadlock while doing VACUUM??
Previous:From: Thomas T. ThaiDate: 2003-03-27 17:51:43
Subject: Re: Deadlock while doing VACUUM??

pgsql-general by date

Next:From: Tom LaneDate: 2003-03-27 21:25:01
Subject: Re: file in data doesn't correspond to any oid in pg_class?
Previous:From: Jonathan BartlettDate: 2003-03-27 21:14:30
Subject: Re: About OIDs

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group