The following bug has been logged online:
Bug reference: 2794
Logged by:
Email address: andres.ortega@farmacity.com.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
