Re: select where not exists returning multiple rows?

On 11-11-02 09:13 AM, Martijn van Oosterhout wrote:
> On Wed, Nov 02, 2011 at 07:22:09AM -0400, Chris Dumoulin wrote:
>> 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.
> FWIW, If they're always going to be the same, you can put that it the query,
> like so:
>
> INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS
> ( SELECT NULL FROM Item WHERE Sig=$1)
>
> Saves a parameter.
>
>> 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?
> As pointed out by others, you don't say if it this is a race condition
> between processes or if it always does this.

It's only happening intermittently, but it doesn't appear to be a race
condition; I'm pretty sure there's only one thread or process issuing
this statement.

Thanks,
Chris

>
> Have a nice day,