The following bug has been logged online: Bug reference: 3898 Logged by: Steven Flatt Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: FreeBSD 6.1 Description: Postgres autovacuum not respecting pg_autovacuum.enabled = false Details:
I noticed that the Postgres autovacuum process was vacuuming some tables that had enabled = false in pg_autovacuum. (These tables are manually vacuumed at predictable times due to very predictable behaviour and ideally should not be touched by the autovacuum process). The problem is easily reproducible by following these steps: $ createdb -U pgsql -E UNICODE test $ psql test pgsql CREATE TABLE foo (a INT); SELECT oid FROM pg_class WHERE relname = 'foo'; oid -------- 147390 (1 row) -- Disable autovacuum on table foo. INSERT INTO pg_autovacuum ( vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) VALUES ( 147390,false,0,0,0,0,0,0,0,0 ); -- Generate some dead rows. INSERT INTO foo (a) SELECT generate_series(1,100000); DELETE FROM foo; -- Lock the table so we can catch the vacuum process. BEGIN; LOCK TABLE foo IN ACCESS EXCLUSIVE MODE; If I let that transaction just sit and wait a few minutes, I soon see: SELECT relation,pid,mode,granted FROM pg_locks WHERE relation = 147390; relation | pid | mode | granted ----------+-------+--------------------------+--------- 147390 | 38267 | AccessExclusiveLock | t 147390 | 38451 | ShareUpdateExclusiveLock | f (2 rows) test=# SELECT current_query FROM pg_stat_activity; current_query --------------------------------------------- SELECT current_query FROM pg_stat_activity; <IDLE> in transaction VACUUM ANALYZE public.foo <IDLE> <IDLE> <IDLE> (6 rows) Sure enough, pid 38451 is the autovacuumer: $ ps 38451 PID TT STAT TIME COMMAND 38451 ?? Is 0:00.02 postgres: autovacuum process test waiting (postgres A restart of the server after setting the pg_autovacuum entry makes no difference; the autovacuum process still tries to vacuum analyze table foo. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org