Received: from bastuba.partitur.se (bastuba.partitur.se [193.219.246.194]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA40140 for ; Fri, 21 Jan 2000 10:09:35 -0500 (EST) (envelope-from kudo@partitur.se) Received: from partitur.se (tb303.partitur.se [193.219.246.230]) by bastuba.partitur.se (8.8.8/8.8.8) with ESMTP id QAA26924; Fri, 21 Jan 2000 16:09:33 +0100 (CET) (envelope-from kudo@partitur.se) Sender: kudo@partitur.se Message-ID: <388876AD.A7FE47D3@partitur.se> Date: Fri, 21 Jan 2000 16:09:33 +0100 From: Patrik Kudo Organization: Partitur Informationsteknik X-Mailer: Mozilla 4.7 [en] (X11; I; FreeBSD 3.3-RELEASE i386) X-Accept-Language: sv,en MIME-Version: 1.0 To: "pgsql-sql@postgreSQL.org" CC: girgen@partitur.se Subject: Duplicate tuples with unique index Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 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