Duplicate key violation
От | Brian Wipf |
---|---|
Тема | Duplicate key violation |
Дата | |
Msg-id | 212959ED-5FFA-450B-81BF-693F9022FFE4@clickspace.com обсуждение исходный текст |
Ответы |
Re: Duplicate key violation
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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')
В списке pgsql-general по дате отправления: