Re: [GENERAL] Weird behavior: deleted row still in index?
On 4 Dec 2011, at 12:32, Phoenix Kiula wrote: mydb=# delete from stores where id = '20xrrs3'; DELETE 0 Time: 0.759 ms It says it didn't delete any rows. Since you get a duplicate key violation on inserting a row to that table, there's obviously a row with that id there. Perhaps there's a DELETE trigger or rule on this table that does something unexpected? It is indeed a possibility that this is a corrupted index, but that is not something that happens unless more serious matters have been (or are) at hand, like hardware failures. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Weird behavior: deleted row still in index?
On 4 December 2011 12:32, Phoenix Kiula phoenix.ki...@gmail.com 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 tableit'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/* http://simononsoftware.com/
Re: [GENERAL] Weird behavior: deleted row still in index?
On Sun, Dec 4, 2011 at 7:55 PM, Szymon Guz mabew...@gmail.com wrote:
.
and then show us the whole table structure, especially any rules or
triggers.
Not many rules or triggers. See below.
I ran a REINDEX on the key allegedly being violated, and it finished
it in 30 mins or so, but still the same problem:
In fact, I deleted one rule -- and maybe I cancelled it before it
finished, but it does look gone now. Could it be not entirely deleted
and maybe corrupted somewhere out of sight?
The row is surely not in the table. Below some things..
.
VACUUM
Time: 366952.162 ms
mydb=#
mydb=#
mydb=# select * from stores where id = '20xrrs3';
id | url | user_registered | private_key | modify_date | ip | url_md5
---+-+-+-+-++-
(0 rows)
Time: 90.711 ms
mydb=#
mydb=#
mydb=# delete from stores where id = '20xrrs3';
DELETE 0
Time: 2.519 ms
mydb=#
mydb=#
mydb=# INSERT INTO stores (id) values ('20xrrs3');
ERROR: duplicate key value violates unique constraint idx_stores_pkey
DETAIL: Key (id)=(20xrrs3) already exists.
mydb=#
mydb=# \d stores
Table public.stores
Column |Type |Modifiers
-+-+-
id | character varying(35) | not null
modify_date | timestamp without time zone | default now()
ip | bigint |
Indexes:
idx_stores_pkey PRIMARY KEY, btree (id)
idx_stores_modify_date btree (modify_date)
Check constraints:
stores_id_check CHECK (id::text ~ '[-.~a-z0-9_]'::text)
Referenced by:
TABLE stores_stats CONSTRAINT fk_stats FOREIGN KEY (id)
REFERENCES stores(id) ON DELETE CASCADE
Rules:
__track_stores_deleted AS
ON DELETE TO stores
WHERE NOT (EXISTS ( SELECT stores_deleted.id
FROM stores_deleted
WHERE stores_deleted.id = old.id)) DO INSERT INTO
stores_deleted (id, modify_date, ip)
VALUES (old.id, old.modify_date, old.ip)
Any other ideas?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
