Обсуждение: Primary key duplicated: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP (18607)'
Primary key duplicated: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP (18607)'
От
"Mendola Gaetano"
Дата:
Hi all, I'm getting the "duplication primary key" error since 2 months ( happen 4 times ), the table affected is always the same, no power down, no db shutdown, no strange situation. This is the scenario: All morning at 7:10 AM these commands are scheduled: /usr/bin/vacuumdb -v -f --table=jobs ${DATABASE} /usr/bin/vacuumdb -v -f --table=programs ${DATABASE} /usr/bin/psql -t -c "reindex table jobs" ${DATABASE} 2>&1 /usr/bin/psql -t -c "reindex table ua_user_data_exp" ${DATABASE} 2>&1 /usr/bin/psql -t -c "reindex table user_traffic" ${DATABASE} 2>&1 /usr/bin/psql -t -c "reindex table user_logs" ${DATABASE} 2>&1 /usr/bin/psql -t -c "reindex table current_connection" ${DATABASE} 2>&1 /usr/bin/vacuumdb -v -f --table=last_connection ${DATABASE} /usr/bin/vacuumdb -v -f --table=user_traffic ${DATABASE} /usr/bin/vacuumdb -v -f --table=transaction_history ${DATABASE} /usr/bin/vacuumdb -v -f --table=user_logs ${DATABASE} /usr/bin/vacuumdb -v -f --table=current_connection ${DATABASE} all these commands are completed in 27 minutes. Inside the email that I receive all morning I found these row: WARNING: Index idx_user_user_traffic: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP' (18607). Recreate the index. INFO: Index idx_sdr_contract_user_traffic: Pages 66; Tuples 18617: Deleted 2532. CPU 0.00s/0.03u sec elapsed 0.02 sec. WARNING: Index idx_sdr_contract_user_traffic: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP' (18607). Recreate the index. INFO: Index user_traffic_pkey: Pages 64; Tuples 18617: Deleted 2532. CPU 0.00s/0.02u sec elapsed 0.02 sec. WARNING: Index user_traffic_pkey: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP' (18607). Recreate the index. We manage a service 24/24 7/7 and is very annoing shutdown the DB, adjust manualy the dump, re-init the db, recreate a new one, fill it again.... What can I do in order to avoid this and reindex the table user_traffic? Postgres version: 7.3.2 on a Linux Box :-( Gaetano