On Wed, Nov 2, 2011 at 6:22 AM, Chris Dumoulin <chris@blaze.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
