Re: select where not exists returning multiple rows?
От | Jeff Davis |
---|---|
Тема | Re: select where not exists returning multiple rows? |
Дата | |
Msg-id | 1321123190.12386.8.camel@jdavis обсуждение исходный текст |
Ответ на | select where not exists returning multiple rows? (Chris Dumoulin <chris@blaze.io>) |
Список | 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
В списке pgsql-general по дате отправления: