Thread: Weird behavior: deleted row still in index?

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=#

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
mattershave been (or are) at hand, like hardware failures. 

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

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