From: | Patrik Kudo <kudo(at)partitur(dot)se> |
---|---|
To: | "pgsql-sql(at)postgreSQL(dot)org" <pgsql-sql(at)postgreSQL(dot)org> |
Cc: | girgen(at)partitur(dot)se |
Subject: | Duplicate tuples with unique index |
Date: | 2000-01-21 15:09:33 |
Message-ID: | 388876AD.A7FE47D3@partitur.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Dirk Lutzebaeck | 2000-01-21 15:38:37 | Re: [SQL] Problem with large tuples. |
Previous Message | Patrik Kudo | 2000-01-21 15:05:52 | Problem with large tuples. |