Duplicate tuples with unique index
От | Patrik Kudo |
---|---|
Тема | Duplicate tuples with unique index |
Дата | |
Msg-id | 388876AD.A7FE47D3@partitur.se обсуждение исходный текст |
Ответы |
Re: [SQL] Duplicate tuples with unique index
|
Список | pgsql-sql |
We've experienced problems with a database duplicating tuples in one of the tables. The problem was found during the daily vacuums. Here is an example: db=> vacuum ; NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE SAME AS HEAP' (1258) ERROR: Cannot insert a duplicate key into a unique index db=> vacuum analyze; NOTICE: CreatePortal: portal <vacuum> already exists NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0. NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE SAME AS HEAP' (1258) ERROR: Cannot insert a duplicate key into a unique index First I found two doubled tuples where the duplicates had different oid (using "select * from html_text a, html_text b where a.id = b.id and a.oid != b.oid"). I removed the duplicates and tried with another vacuum. This did not solve the problem, so I did the following: drop index html_text_idx; (this was a unique index...) db=> select count(*) from html_text; count -----1259 (1 row) db=> select distinct id from html_text; ... lot of rows ... (1211 rows) Since I could not find any duplicates using my first method, I started looking for duplicate oids... excerpt from "select id, oid from html_text" id oid ----------- 3180|667648 3180|667648 3181|676704 3185|668576 3185|668576 3187|673281 3200|672992 3200|672992 3201|699074 3201|699074 3206|672513 3208|680128 3208|680128 3212|674849 3218|679137 3220|674851 3221|680129 3221|680129 This doesn't feel very well... Has anyone experienced similar problems? Our config looks like this: Postgres 6.5.2 (version() = PostgreSQL 6.5.2 on i386-unknown-freebsd3.3, compiled by cc) FreeBSD 3.3-RELEASE 2xPII SMP vinum configured scsi (mirroring) /Patrik Kudo
В списке pgsql-sql по дате отправления: