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.