The following bug has been logged online:
Bug reference: 3898
Logged by: Steven Flatt
Email address: steven.flatt@gmail.com
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.
