>-----Original Message----- >From: pgsql-general-owner@xxxxxxxxxxxxxx >[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Brian Wipf >Sent: donderdag 25 januari 2007 22:42 >To: pgsql-general@xxxxxxxxxxxxxx >Subject: [GENERAL] Duplicate key violation > >I got a duplicate key violation when the following query was performed: > >INSERT INTO category_product_visible (category_id, product_id) > SELECT cp.category_id, cp.product_id > FROM category_product cp > WHERE cp.product_id = $1 AND > not exists ( > select 'x' > from category_product_visible cpv > where cpv.product_id = >cp.product_id and > cpv.category_id = cp.category_id > ); > >This is despite the fact the insert is written to only insert >rows that do not already exist. The second time the same query >was run it went through okay. This makes me think there is >some kind of race condition, which I didn't think was possible >with PostgreSQL's MVCC implementation. I'm unable to duplicate >the problem now and the error only occurred once in weeks of >use. This is on PostgreSQL 8.2.1 running on openSUSE Linux >10.2. Slony-I 1.2.6 is being used for replication to a single >slave database. > [snip] This section is relevant: http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html In the default isolation level "Read commited" you are protected against "dirty reads". You are not protected against "nonrepeatable reads" and "phantom reads". In fact if you start a transaction now, others are not prevented from inserting records. This can result in a situation where you did not find the record, since someone else has just instead it after your transaction was started. This is not a race condition, but a side-effect. - Joris
