Re: Duplicate key insert question

On Tuesday 01 July 2003 08:45 pm, Jean-Christian Imbeault wrote:
> Reuben D. Budiardja wrote:
> > Hi, not sure if this is answering your question, but I just asked similar
> > questions here. I asked about using INSERT WHERE NOT EXISTS (which you
> > can do in PostgreSQL). Here is what you can do:
> >
> > INSERT INTO mytable
> > SELECT 'value1', 'value2'
> > WHERE NOT EXISTS
> > (SELECT NULL FROM mytable
> > WHERE mycondition)
> >
> > http://marc.theaimsgroup.com/?l=postgresql-general&w=2&r=1&s=WHERE+NOT+EX
> >ISTS&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, onlu *one* of them will fail, but yes, the other will then generate error.
So it really is a trade off. Another way would be to lock the table, as other
has suggested. But then there is disadvantages to that also.

RDB

--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\ ASCII Ribbon Campaign against HTML
\ / email and proprietary format
X attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------