Re: Duplicate key ( reindex and vacuum full logs )
От | Gaetano Mendola |
---|---|
Тема | Re: Duplicate key ( reindex and vacuum full logs ) |
Дата | |
Msg-id | 000f01c378af$eb58ef70$0f02c9d9@mm.eutelsat.org обсуждение исходный текст |
Ответ на | Duplicate key ("Gaetano Mendola" <mendola@bigfoot.com>) |
Список | pgsql-admin |
No clues? Gaetano ----- Original Message ----- From: ""Gaetano Mendola"" <mendola@bigfoot.com> 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" <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 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
В списке pgsql-admin по дате отправления: