BUG #2794: corrupted table pk index

From: "" <andres(dot)ortega(at)farmacity(dot)com(dot)co>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2794: corrupted table pk index
Date: 2006-11-29 15:32:14
Message-ID: 200611291532.kATFWEwI003328@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 2794
Logged by:
Email address: andres(dot)ortega(at)farmacity(dot)com(dot)co
PostgreSQL version: 8.1.3
Operating system: macosx
Description: corrupted table pk index
Details:

when i try to reconstruct a table index

reindex table "public"."itemfactura" force;

get

ERROR: could not create unique index
Detail: Table contains duplicated values.

when

select oid,count(*)
from itemfactura
group by oid
having count(*)>1

return

oid count
------- --------
8485900 2
8486062 2
8486063 2
8486064 2
8486065 2
8486066 2
8486067 2
8486068 2
8486084 2
8486094 2
8486108 2
8486109 2
8486110 2
8486128 2
8486129 2
8486130 2
8486131 2

17 record(s) selected [Fetch MetaData: 6/ms] [Fetch Data: 3/ms]

the table PK is (id_sucursal,id_factura ,id_item)

when

select ctid,oid,*
from itemfactura
where oid=8486110 --i.e. a bad tuple

get

ctid oid id_sucursal id_factura id_item id_area
id_grupo id_categoria id_subcategoria id_segmento
id_farmacity costo costo_promedio cantidad precio
descuento iva_generado id_tipo_iva iva id_acuerdo
precio_acuerdo id_plan iva_generado_acuerdo id_descuento
---------- ------- -------------- ------------- ---------- ----------
----------- --------------- ------------------ --------------
--------------- -------- ----------------- ----------- ---------
------------ --------------- -------------- ------ -------------
----------------- ---------- ----------------------- ---------------
(11899,24) 8486110 1000008 263341 2 2
1730 1734 1755 1780 1017946
720 720 1 1200 0 166
4 0.16 0 0 0
0 0
(11912,13) 8486110 1000008 263341 2 2
1730 1734 1755 1780 1017946
720 720 1 1200 0 166
4 0.16 0 0 0
0 0

this is a global database problem and pg_dump does not help
how can i fix the database

tks

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Ilia 2006-11-30 13:33:34 BUG #2795: can't start service postgre
Previous Message Michael Fuhr 2006-11-29 14:46:10 Re: BUG #2791: Error while commiting