On Wed, Nov 2, 2011 at 6:22 AM, Chris Dumoulin <chris@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
