Στις Δευτέρα 05 Φεβρουάριος 2007 16:00, ο/η Michael Monnerie έγραψε: > dear list, > > I've found database corruptions occuring with openSUSE 10.1, postgreSQL > version # rpm -qa|grep postg > postgresql-libs-8.1.4-1.2 > postgresql-server-8.1.4-1.2 > postgresql-8.1.4-1.2 > > I found this in database logs: > 2007-02-05 13:15:51 CET DB=bayes_pg3 HOST=195.202.170.130(54789) > SESSTRT=2007-02-04 05:01:15 CET FEHLER: duplizierter Schlüssel > verletzt Unique-Constraint »bayes_seen_pkey« > > translated it says "duplicate key violates unique constraint", and it's > true, I have 2 rows with the same Primary Key in that table (make dump, > then reload fails on it). This ERROR is normally thrown when you *try* to violate a unique constraint. Can you demonstrate the presence of the UNIQUE constraint on your table and the pair of identical key values rows? Post a description (\d) of your table, and then the select stmt which return the 2 bad rows. I might be wrong, but i would bet the whole issue began because some rows were inserted by a client (other than the reloading psql) during reload but before the UNIQUE KEY DDL was executed, or smth like that, or simply because this bayes_seen value is attempted to be inserted again (for reasons beyond our scope) > > The DB is a bayes DB for spamassassin. I've had this error before, and > dumped/edited dump/reloaded the db. But it keeps coming again. So there > might be hardware or software problems. > > The hardware is a server running one AMD Opteron dual core 2210, with > 8GB RAM, and VMware. We have www, db, mailserver and mailrelays running > within a VM, and the only problem I see is with the db, so it doesn't > look like a hardware problem. > > I could believe it's a software problem. But which? Could a bad config > cause such problems? I've set this in postgresql.conf: > > listen_addresses = '*' > port = 5432 > max_connections = 200 > shared_buffers = 50000 > max_prepared_transactions = 0 > work_mem = 128000 > maintenance_work_mem = 128000 > max_fsm_pages = 60000 > max_fsm_relations = 1000 > max_files_per_process = 5000 > vacuum_cost_delay = 100 > vacuum_cost_page_hit = 1 > vacuum_cost_page_miss = 10 > vacuum_cost_page_dirty = 20 > vacuum_cost_limit = 10000 > bgwriter_delay = 1000 > bgwriter_lru_percent = 5.0 > bgwriter_lru_maxpages = 100 > bgwriter_all_percent = 1.333 > bgwriter_all_maxpages = 100 > fsync = off > wal_buffers = 8 > commit_delay = 3000 > commit_siblings = 25 > checkpoint_segments = 4 > checkpoint_timeout = 300 > checkpoint_warning = 30 > enable_bitmapscan = on > enable_hashagg = on > enable_hashjoin = on > enable_indexscan = on > enable_mergejoin = on > enable_nestloop = on > enable_seqscan = on > enable_sort = on > enable_tidscan = on > effective_cache_size = 64000 > geqo = on > log_destination = 'stderr' > redirect_stderr = on > log_directory = '/var/log/' > log_filename = 'postgresql.%Y' > log_truncate_on_rotation = off > log_rotation_age = 0 > log_rotation_size = 0 > log_min_duration_statement = 5000 > log_connections = off > log_disconnections = off > log_duration = off > log_line_prefix = '%t DB=%d HOST=%r SESSTRT=%s ' > log_statement = 'ddl' > stats_start_collector = on > stats_block_level = on > stats_row_level = on > autovacuum = on > autovacuum_naptime = 3600 > autovacuum_vacuum_threshold = 5000 > autovacuum_analyze_threshold = 2500 > autovacuum_vacuum_scale_factor = 0.4 > autovacuum_analyze_scale_factor = 0.2 > autovacuum_vacuum_cost_delay = -1 > autovacuum_vacuum_cost_limit = -1 > lc_messages = 'de_DE.UTF-8' > lc_monetary = 'de_DE.UTF-8' > lc_numeric = 'de_DE.UTF-8' > lc_time = 'de_DE.UTF-8' > > log_filename = 'postgresql.%Y' > > mfg zmi -- Achilleas Mantzios
