Re: select where not exists returning multiple rows? - Mailing list pgsql-general

On Tue, 2011-11-01 at 10:59 -0400, Chris Dumoulin wrote:
> 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.

Unrelated note: just use $1 twice.

>  The idea is to insert
> if the row doesn't already exist.
> We're getting primary key constraint violations:

What's happening is that the NOT EXISTS is running before the INSERT,
and between those two another INSERT can happen. The PRIMARY KEY is
saving you from this problem in this case.

I recommend that you look into using SERIALIZABLE isolation mode as your
default:

http://www.postgresql.org/docs/9.1/static/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-ISOLATION
http://www.postgresql.org/docs/9.1/static/sql-set-transaction.html
http://www.postgresql.org/docs/9.1/static/transaction-iso.html#XACT-SERIALIZABLE

That will still throw an error, but it protects you from all kinds of
similar problems that might not be caught by a primary key.

Regards,
    Jeff Davis