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.
I'll monitor the problem and if it recurs, I'll rebuild the primary
key index. Perhaps the category_product_visible_pkey index was/is
corrupted in some way.
Brian Wipf
<brian@clickspace.com>
The exact error was:
select process_pending_changes(); FAILED!!! Message: ERROR: duplicate
key violates unique constraint "category_product_visible_pkey"
CONTEXT: SQL statement "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 an cpv.category_id = cp.category_id);" PL/pgSQL
function "insert_cpv" line 3 at SQL statement PL/pgSQL function
"process_mp_change" line 15 at assignment PL/pgSQL function
"process_pending_changes" line 13 at assignment
The insert_cpv(...) function and table definitions follow. I can
provide any other information required.
CREATE FUNCTION insert_cpv(
my_product_id int
) RETURNS boolean AS $$
DECLARE
BEGIN
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
);
return found;
END;
$$ LANGUAGE plpgSQL;
\d category_product
Table "public.category_product"
Column | Type | Modifiers
-------------+---------+-----------
category_id | integer | not null
product_id | integer | not null
Indexes:
"x_category_product_pk" PRIMARY KEY, btree (category_id,
product_id)
"x_category_product__category_id_fk_idx" btree (category_id)
"x_category_product__product_id_fk_idx" btree (product_id)
Foreign-key constraints:
"x_category_product_category_fk" FOREIGN KEY (category_id)
REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED
"x_category_product_product_fk" FOREIGN KEY (product_id)
REFERENCES product(product_id) DEFERRABLE INITIALLY DEFERRED
Triggers:
_ssprod_replication_cluster_logtrigger_17 AFTER INSERT OR DELETE
OR UPDATE ON category_product FOR EACH ROW EXECUTE PROCEDURE
_ssprod_replication_cluster.logtrigger('_ssprod_replication_cluster',
'17', 'kk')
category_product_trigger BEFORE INSERT OR DELETE ON
category_product FOR EACH ROW EXECUTE PROCEDURE
category_product_trigger()
\d category_product_visible
Table "public.category_product_visible"
Column | Type | Modifiers
---------------------+------------------------+-----------
category_id | integer | not null
product_id | integer | not null
Indexes:
"category_product_visible_pkey" PRIMARY KEY, btree (category_id,
product_id)
"category_product_visible__product_id_fk_idx" btree (product_id)
Triggers:
_ssprod_replication_cluster_logtrigger_18 AFTER INSERT OR DELETE
OR UPDATE ON category_product_visible FOR EACH ROW EXECUTE PROCEDURE
_ssprod_replication_cluster.logtrigger('_ssprod_replication_cluster',
'18', 'kvvvvvkvvvvvv')
Brian Wipf <brian@clickspace.com> writes:
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.
If you're doing more than one of these concurrently, then of course
there's a race condition: the NOT EXISTS is testing for nonexistence
as of the query snapshot. If two sessions do this concurrently then
they'll try to insert the same rows and one of them is gonna fail.
regards, tom lane
Sounds like you'll either need an explicit "LOCK TABLE"
command, set your transaction isolation to serializable,
or use advisory locking.
http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html#LOC
KING-TABLES
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT
-SERIALIZABLE
http://www.postgresql.org/docs/8.2/interactive/functions-admin.html#FUNC
TIONS-ADVISORY-LOCKS
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Thursday, January 25, 2007 6:21 PM
To: Brian Wipf
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Duplicate key violation
Brian Wipf <brian@clickspace.com> writes:
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.
If you're doing more than one of these concurrently, then of course
there's a race condition: the NOT EXISTS is testing for nonexistence
as of the query snapshot. If two sessions do this concurrently then
they'll try to insert the same rows and one of them is gonna fail.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Brian Wipf
Sent: donderdag 25 januari 2007 22:42
To: pgsql-general@postgresql.org
Subject: [GENERAL] Duplicate key violationI 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