Re: Duplicate key insert question

On Wed, Jul 02, 2003 at 09:45:23AM +0900, Jean-Christian Imbeault wrote:
> Reuben D. Budiardja wrote:

> > http://marc.theaimsgroup.com/?l=postgresql-general&w=2&r=1&s=WHERE+NOT+EXISTS&q=b
>
> Thanks for the link!
>
> I read the thread and it looks like even the above solution is not
> perfect because of a possible race condition where two inserts trying to
> insert a row with a pk not in the table will both get think it is ok to
> do so, try it and then both will fail?

No, only the "second" one will fail (though it's difficult which one is
the second)

> If I followed all the arguments correctly according to the thread there
> is *no* way to do what I (and you ;) want in one simple query.

No, there's not.  You should check the returned value from the insertion
function to see if it succeeded or not.  Sadly, an error will cause the
whole transaction to abort, but if they come from the MySQL side it will
hardly matter.  But you should try to use a sequence if at all possible
to avoid all these problems.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"A wizard is never late, Frodo Baggins, nor is he early.
He arrives precisely when he means to."  (Gandalf, en LoTR FoTR)