Re: Duplicate key ( reindex and vacuum full logs )

[Database Administrator]

No clues?

Gaetano

----- Original Message -----
From: ""Gaetano Mendola""
Newsgroups: comp.databases.postgresql.admin
Sent: Tuesday, September 09, 2003 4:39 PM
Subject: Re: Duplicate key ( reindex and vacuum full logs )


> just for add informations on the problem:
> these are the logs received by the reindex and by
> vacuum full on that table ( before to delete the
> duplicated rows):
>
> ===== REINDEX =======
>
> Start Reindex table ua_user_data_exp at Fri Sep 5 07:12:26 CEST 2003
> ERROR: Cannot create unique index. Table contains non-unique values
>
>
> ===== VACUUM FULL ======
>
> Start Vacuum table ua_user_data_exp at Fri Sep 5 07:13:11 CEST 2003
> INFO: --Relation public.ua_user_data_exp--
> INFO: Pages 890: Changed 119, reaped 531, Empty 0, New 0; Tup 11503: Vac
> 223, Keep/VTL 206/206, UnUsed 1674, MinLen 44, MaxLen
> 696; Re-using: Free/Avail. Space 1007004/934048; EndEmpty/Avail. Pages
> 1/421.
> CPU 0.08s/0.01u sec elapsed 0.08 sec.
> INFO: Index ua_user_data_exp_id_user_key: Pages 52; Tuples 11505: Deleted
> 223.
> CPU 0.01s/0.01u sec elapsed 0.03 sec.
> WARNING: Index ua_user_data_exp_id_user_key: NUMBER OF INDEX' TUPLES
> (11505) IS NOT THE SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index ua_user_data_exp_login_key: Pages 81; Tuples 11505: Deleted
> 223.
> CPU 0.00s/0.00u sec elapsed 0.07 sec.
> WARNING: Index ua_user_data_exp_login_key: NUMBER OF INDEX' TUPLES
(11505)
> IS NOT THE SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_id_provider: Pages 87; Tuples 11505: Deleted 223.
> CPU 0.00s/0.02u sec elapsed 0.26 sec.
> WARNING: Index exp_id_provider: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_ci_login: Pages 81; Tuples 11505: Deleted 223.
> CPU 0.00s/0.01u sec elapsed 0.07 sec.
> WARNING: Index exp_ci_login: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_country: Pages 106; Tuples 11505: Deleted 223.
> CPU 0.00s/0.01u sec elapsed 0.28 sec.
> WARNING: Index exp_country: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_os_type: Pages 269; Tuples 11505: Deleted 223.
> CPU 0.03s/0.04u sec elapsed 0.92 sec.
> WARNING: Index exp_os_type: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_card: Pages 96; Tuples 11505: Deleted 223.
> CPU 0.01s/0.01u sec elapsed 0.22 sec.
> WARNING: Index exp_card: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS
> HEAP' (11503).
> Recreate the index.
> INFO: Index exp_status: Pages 848; Tuples 11505: Deleted 223.
> CPU 0.12s/0.03u sec elapsed 4.34 sec.
> WARNING: Index exp_status: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_email: Pages 123; Tuples 11505: Deleted 223.
> CPU 0.00s/0.02u sec elapsed 0.19 sec.
> WARNING: Index exp_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_ci_email: Pages 123; Tuples 11505: Deleted 223.
> CPU 0.02s/0.01u sec elapsed 0.20 sec.
> WARNING: Index exp_ci_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_lastname: Pages 79; Tuples 11505: Deleted 223.
> CPU 0.01s/0.00u sec elapsed 0.08 sec.
> WARNING: Index exp_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_ci_lastname: Pages 79; Tuples 11505: Deleted 223.
> CPU 0.03s/0.01u sec elapsed 0.12 sec.
> WARNING: Index exp_ci_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_orbital_ptns: Pages 670; Tuples 11505: Deleted 223.
> CPU 0.12s/0.05u sec elapsed 4.46 sec.
> WARNING: Index exp_orbital_ptns: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_stickers: Pages 210; Tuples 11505: Deleted 223.
> CPU 0.05s/0.01u sec elapsed 0.68 sec.
> WARNING: Index exp_stickers: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_pid: Pages 443; Tuples 11505: Deleted 223.
> CPU 0.05s/0.01u sec elapsed 2.16 sec.
> WARNING: Index exp_pid: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS
> HEAP' (11503).
> Recreate the index.
> INFO: Index exp_mac_address: Pages 114; Tuples 11505: Deleted 223.
> CPU 0.02s/0.02u sec elapsed 0.14 sec.
> WARNING: Index exp_mac_address: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_mac_address_normal: Pages 114; Tuples 11505: Deleted 223.
> CPU 0.01s/0.05u sec elapsed 0.11 sec.
> WARNING: Index exp_mac_address_normal: NUMBER OF INDEX' TUPLES (11505) IS
> NOT THE SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_provider: Pages 299; Tuples 7516: Deleted 223.
> CPU 0.05s/0.04u sec elapsed 1.28 sec.
> INFO: Rel ua_user_data_exp: Pages: 890 --> 878; Tuple(s) moved: 11.
> CPU 0.01s/0.09u sec elapsed 0.12 sec.
> INFO: Index ua_user_data_exp_id_user_key: Pages 52; Tuples 11505: Deleted
> 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index ua_user_data_exp_id_user_key: NUMBER OF INDEX' TUPLES
> (11505) IS NOT THE SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index ua_user_data_exp_login_key: Pages 81; Tuples 11505: Deleted
11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index ua_user_data_exp_login_key: NUMBER OF INDEX' TUPLES
(11505)
> IS NOT THE SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_id_provider: Pages 87; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_id_provider: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_ci_login: Pages 81; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_ci_login: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_country: Pages 106; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_country: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_os_type: Pages 269; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_os_type: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_card: Pages 96; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_card: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS
> HEAP' (11503).
> Recreate the index.
> INFO: Index exp_status: Pages 848; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_status: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_email: Pages 123; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
> AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_ci_email: Pages 123; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_ci_email: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_lastname: Pages 79; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_ci_lastname: Pages 79; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_ci_lastname: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_orbital_ptns: Pages 670; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_orbital_ptns: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_stickers: Pages 210; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_stickers: NUMBER OF INDEX' TUPLES (11505) IS NOT THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_pid: Pages 443; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_pid: NUMBER OF INDEX' TUPLES (11505) IS NOT THE SAME
AS
> HEAP' (11503).
> Recreate the index.
> INFO: Index exp_mac_address: Pages 114; Tuples 11505: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> WARNING: Index exp_mac_address: NUMBER OF INDEX' TUPLES (11505) IS NOT
THE
> SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_mac_address_normal: Pages 114; Tuples 11505: Deleted 11.
> CPU 0.00s/0.01u sec elapsed 0.00 sec.
> WARNING: Index exp_mac_address_normal: NUMBER OF INDEX' TUPLES (11505) IS
> NOT THE SAME AS HEAP' (11503).
> Recreate the index.
> INFO: Index exp_provider: Pages 299; Tuples 7516: Deleted 11.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>
>
>
>
>
> ----- Original Message -----
> From: "Tom Lane"
> Newsgroups: comp.databases.postgresql.admin
> Sent: Monday, September 08, 2003 11:41 PM
> Subject: Re: Duplicate key
>
>
> > "Gaetano Mendola" writes:
> > > I had one row duplicated with the same login and the same id_user,
> > > was failing was the update of that row complaining about the
duplicated
> > > key.
> >
> > Oh. Your report was quite unclear; I thought you were saying that
> > REINDEX had somehow built two copies of the same index.
> >
> > Is the row actually duplicated, or has it just managed to acquire two
> > pointers to itself in the indexes? You could check by seeing whether
> > the two apparent instances have the same or different ctid values
> > (ctid is a system column not shown unless you ask for it, like oid).
> > If they are different ctids, it would be useful to see whether they have
> > the same or different oid,xmin,cmin,xmax,cmax (more system columns).
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match