Search Postgresql Archives

Re: select where not exists returning multiple rows?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux