Re: select where not exists returning multiple rows?

On 11-11-02 08:49 AM, Merlin Moncure wrote:
> On Wed, Nov 2, 2011 at 6:22 AM, Chris Dumoulin<chris(at)blaze(dot)io> wrote:
>> We're using postgresql 9.1, and we've got a table that looks like this:
>>
>> testdb=# \d item
>> Table "public.item"
>> Column | Type | Modifiers
>> -------+----------+-----------
>> sig | bigint | not null
>> type | smallint |
>> data | text |
>> Indexes:
>> "item_pkey" PRIMARY KEY, btree (sig)
>>
>> And we're doing an insert like this:
>> INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT
>> NULL FROM Item WHERE Sig=$4)
>>
>> In this case $1 and $4 should always be the same. The idea is to insert if
>> the row doesn't already exist.
>> We're getting primary key constraint violations:
>>
>> 011-10-31 22:50:26 CDT STATEMENT: INSERT INTO Item (Sig, Type, Data) SELECT
>> $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4 FOR UPDATE)
>> 2011-10-31 22:52:56 CDT ERROR: duplicate key value violates unique
>> constraint "item_pkey"
>> 2011-10-31 22:52:56 CDT DETAIL: Key (sig)=(-4668668895560071572) already
>> exists.
>>
>> I don't see how it's possible to get duplicate rows here, unless maybe the
>> "select where not exists" is somehow returning multiple rows.
>> Any ideas what's going on here?
> race condition. lock the table first or retry the insert.
>
> merlin

Could you elaborate a little more on the race condition? Are you
suggesting that if two threads executed this statement at the same time,
the results from the inner "SELECT NULL ..." in one of the threads could
be incorrect by the time that thread did the INSERT? I thought about
this possibility and tried "SELECT NULL ... FOR UPDATE", but still saw
the same problem.

Thanks,
Chris