The following bug has been logged online:

Bug reference: 2568
Logged by: Jaroslav Prodelal
Email address: slavek (AT) pearshealthcyber (DOT) com
PostgreSQL version: 8.0.8
Operating system: Linux (CentOS 4.3 / kernel 2.6.13.4)
Description: Vacuum locks tables and do nothing
Details:

Hello!

We have problem with VACUUMing our databases. The problem is mainly on
databases which are in use a lot and on big tables and tables with lots of
changes. We use autovacuum daemon, but problem is the same when we VACUUM
manualy.

When vacuum starts vacuuming problematic table, it locks table and
although, I can't see any load on the server even on disk, cpu or memory,
table is lock and other processes can't access it. I have also tried strace
on VACUUM process, but there wasn't any activity on it.

This problems cause, we need to manually kill VACUUM proces on the
databse, down is a part from autovacuum log which is related to our
problem.

I'd like to ask you to help us to solve this problem (possible bug). If
there is anything we can help to provide more information, please let us
know.

Thank you in advcance!

Jaroslav

Part of autovacuum daemon log:
==============================
[2006-08-07 17:06:40 CEST] DEBUG: Performing: VACUUM ANALYZE
"pg_catalog"."pg_attribute"
[2006-08-07 17:13:37 CEST] INFO: table name:
obchod."pg_catalog"."pg_attribute"
[2006-08-07 17:13:37 CEST] INFO: relid: 1249; relisshared: 0
[2006-08-07 17:13:37 CEST] INFO: reltuples: 3904.000000; relpages:
1297
[2006-08-07 17:13:37 CEST] INFO: curr_analyze_count: 78174;
curr_vacuum_count: 39091
[2006-08-07 17:13:37 CEST] INFO: last_analyze_count: 78174;
last_vacuum_count: 39091
[2006-08-07 17:13:37 CEST] INFO: analyze_threshold: 4404;
vacuum_threshold: 8808
[2006-08-07 17:13:37 CEST] DEBUG: Performing: VACUUM ANALYZE
"pg_catalog"."pg_class"
[2006-08-07 17:14:34 CEST] INFO: table name:
obchod."pg_catalog"."pg_class"
[2006-08-07 17:14:34 CEST] INFO: relid: 1259; relisshared: 0
[2006-08-07 17:14:34 CEST] INFO: reltuples: 507.000000; relpages:
203
[2006-08-07 17:14:34 CEST] INFO: curr_analyze_count: 5457;
curr_vacuum_count: 2809
[2006-08-07 17:14:34 CEST] INFO: last_analyze_count: 5457;
last_vacuum_count: 2809
[2006-08-07 17:14:34 CEST] INFO: analyze_threshold: 1007;
vacuum_threshold: 2014
[2006-08-07 17:14:34 CEST] DEBUG: Performing: VACUUM ANALYZE
"public"."obch_vyrobek"
[2006-08-07 21:46:28 CEST] ERROR: Can not refresh statistics information
from the database obchod.
[2006-08-07 21:46:28 CEST] The error is [FATAL: terminating
connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
]
[2006-08-07 21:46:28 CEST] ERROR: Fatal error occured while sending query
(select oid,reltuples,relpages from pg_class where oid=37319987) to database
obchod
[2006-08-07 21:46:28 CEST] The error is []
[2006-08-07 21:46:28 CEST] INFO: table name:
obchod."public"."obch_vyrobek"
[2006-08-07 21:46:28 CEST] INFO: relid: 37319987; relisshared: 0
[2006-08-07 21:46:28 CEST] INFO: reltuples: 60303.000000; relpages:
363494
[2006-08-07 21:46:28 CEST] INFO: curr_analyze_count: 587262;
curr_vacuum_count: 587253
[2006-08-07 21:46:28 CEST] INFO: last_analyze_count: 264400;
last_vacuum_count: 264400
[2006-08-07 21:46:28 CEST] INFO: analyze_threshold: 60803;
vacuum_threshold: 121606
[2006-08-07 21:46:28 CEST] DEBUG: Performing: VACUUM ANALYZE
"pg_catalog"."pg_type"
[2006-08-07 21:46:28 CEST] ERROR: Fatal error occured while sending query
(VACUUM ANALYZE "pg_catalog"."pg_type") to database obchod
[2006-08-07 21:46:28 CEST] The error is []
[2006-08-07 21:46:28 CEST] ERROR: Fatal error occured while sending query
(select oid,reltuples,relpages from pg_class where oid=1247) to database
obchod
[2006-08-07 21:46:28 CEST]
.....
.....
.....

PostgreSQL configuration (different from default):
==================================================
max_connections = 125
password_encryption = true
shared_buffers = 16384
work_mem = 4096
maintenance_work_mem = 524288
max_fsm_pages = 500000
max_fsm_relations = 5000
vacuum_cost_delay = 200
vacuum_cost_page_hit = 5
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 100
checkpoint_segments = 16
effective_cache_size = 5000
random_page_cost = 2
cpu_tuple_cost = 0.02
cpu_index_tuple_cost = 0.002
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_min_messages = notice
log_min_duration_statement = 100
silent_mode = false
log_connections = false
log_statement = 'none'
stats_start_collector = true
stats_row_level = true
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Jaroslav Prodelal" <slavek (AT) pearshealthcyber (DOT) com> writes:
It sounds to me like vacuum is blocked waiting for some other process's
lock --- and then other processes queue up behind vacuum's request, but
that doesn't make it vacuum's fault. Try looking in pg_locks to see
which process actually has a granted lock on that table.

regards, tom lane

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