Re: Weird behavior: deleted row still in index?

On Sun, Dec 4, 2011 at 7:55 PM, Szymon Guz <mabewlun(at)gmail(dot)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?