On 4 December 2011 12:32, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote:
Hi.
I have deleted a row from a table. Confirmed by "SELECT". All
associated children tables don't have this key value either.
Yet, when I insert this row back again, the primary key index on this
table gives me a duplicate error.
As demonstrated below. PGSQL version is 9.0.5.
Is this common? I have vacuum analyzed the table three times. Still
same problem. Why is the primary key index keeping a value that was
deleted?
Short of a REINDEX (which will lock the entire table....it's a large
one) is there anything I can do to clear up the index?
Thanks!
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 0.759 ms
mydb=# INSERT INTO stores (id) VALUES ('20xrrs3');
mydb-#
ERROR: duplicate key value violates unique constraint "idx_stores_pkey"
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=#
Hi,
could you run the following queries and show us the results?
SELECT count(*) FROM stores WHERE id = '20xrrs3';
delete from stores where id = '20xrrs3';
SELECT count(*) FROM stores WHERE id = '20xrrs3';
and then show us the whole table structure, especially any rules or triggers.
regards
Szymon
http://simononsoftware.com/
