On 11-11-02 08:49 AM, Merlin Moncure wrote:
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
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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
