pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Patrik Kudo <kudo@partitur.se>
To: pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
Cc: girgen@partitur.se
Subject: Duplicate tuples with unique index
Date: Fri, 21 Jan 2000 16:09:33 +0100
Message-ID: <388876AD.A7FE47D3@partitur.se> (raw)

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



view thread (13+ messages)  latest in thread

Message-ID: <388876AD.A7FE47D3@partitur.se>
Permalink:  ../388876AD.A7FE47D3@partitur.se/
Also on:    postgresql.org/message-id/388876AD.A7FE47D3@partitur.se

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: kudo@partitur.se, girgen@partitur.se
  Subject: Re: Duplicate tuples with unique index
  In-Reply-To: <388876AD.A7FE47D3@partitur.se>
  References: <388876AD.A7FE47D3@partitur.se>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox