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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
