Re: Duplicate key ( reindex and vacuum full logs ) - Mailing list pgsql-admin

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" <tgl@sss.pgh.pa.us>
Newsgroups: comp.databases.postgresql.admin
Sent: Monday, September 08, 2003 11:41 PM
Subject: Re: Duplicate key


> "Gaetano Mendola" <mendola@bigfoot.com> 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
>