Re: Duplicate key violation

>-----Original Message-----
>From: pgsql-general-owner(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Brian Wipf
>Sent: donderdag 25 januari 2007 22:42
>To: pgsql-general(at)postgresql(dot)org
>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