Thread: phantom record, is it a bug?

         I have a database running under Redhat Linux 7.1(kernel 2.4.9), this database is upgraded from version 7.0.2, and the current version is 7.1.3, but after upgrade, vacuum always fails, and I found this:

A table named users, which structure is like this:

         Username varchar(20) not null primary key,

         Password varchar(20) not null, ……

But sometimes when I select from this table, a existed record can not be retrieved(query like this: select password from users where username=’xxxx’ returns 0 row), sometimes I think the record missing, and re-insert it(and this insert will success), and it will cause vacuum failed(vacuum said: Cannot insert a duplicate key into unique index users_pkey

). Now I found when a record is missing, after a couple of time, re-query will get that record (same query return 1 row). It seems the record becomes a phantom record!

This problem caused many other problem, so I must manually clear these duplicate records, I tried run this:

Select username, count(username) from users group by username having count(username)>1, sometimes it returns some rows, and I delete some, but after that, vacuum still report same error. I choosed a solution: run this: create a same empty table named temp_users, then: insert into tempusers select * from users limit xxxxx offset xxxx, to insert stepping by xxxx, when it report error, I decrease step to find which record is really duplicated and then delete it. After all records transferred, I truncate original table, and insert all records back: insert into users select * from temp_users, still an error reported(can not insert duplicate record!). This is really confused me.

By the way, the table have problem have about 750K records(and many other tables have same problem), the table contains some no-ascii charater in username field. I used precompiled version of Postgresql from Postgresql.org.