Re: select where not exists returning multiple rows?

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