Thread: BUG #2794: corrupted table pk index

BUG #2794: corrupted table pk index

From
""
Date:
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

Re: BUG #2794: corrupted table pk index

From
"Heikki Linnakangas"
Date:
andres.ortega@farmacity.com.co wrote:
> this is a global database problem and pg_dump does not help
> how can i fix the database

I don't know how your table got into that state, but since there's only
17 duplicate rows, you could pg_dump, remove the duplicate rows by hand
from the dump-file, and restore.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com