Skip site navigation (1) Skip section navigation (2)

BUG #3898: Postgres autovacuum not respecting pg_autovacuum.enabled = false

From: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3898: Postgres autovacuum not respecting pg_autovacuum.enabled = false
Date: 2008-01-23 20:26:41
Message-ID: 200801232026.m0NKQfD6052274@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3898
Logged by:          Steven Flatt
Email address:      steven(dot)flatt(at)gmail(dot)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.

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-01-23 20:52:35
Subject: Re: Statement Timeout Message Incorrect
Previous:From: eleinDate: 2008-01-23 19:22:06
Subject: Statement Timeout Message Incorrect

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group