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

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.

"Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> I noticed that the Postgres autovacuum process was vacuuming some tables
> that had enabled = false in pg_autovacuum.

I think what is happening is that because you set
pg_autovacuum.freeze_max_age to zero, the thing always thinks that it's
time to force an anti-wraparound vacuum. IOW a nondefault freeze age is
still applied whether or not enabled is true. I'm not quite sure if
that's a bug or a feature --- you could argue that it's a feature
because it lets you suppress routine autovacuuming and still customize
the anti-wraparound timing. Maybe it's a documentation bug: the docs
say that enabled = false means the table is "never autovacuumed", but
what the code seems to be implementing is "it's only autovacuumed
when necessary to prevent wraparound". I think that's the behavior
we want ...

In any case, you should be setting "unused" fields of a pg_autovacuum
entry to -1, not zero.

regards, tom lane

Tom Lane escribió:
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> > I noticed that the Postgres autovacuum process was vacuuming some tables
> > that had enabled = false in pg_autovacuum.
>
> I think what is happening is that because you set
> pg_autovacuum.freeze_max_age to zero, the thing always thinks that it's
> time to force an anti-wraparound vacuum. IOW a nondefault freeze age is
> still applied whether or not enabled is true.

That's correct.

> I'm not quite sure if that's a bug or a feature --- you could argue
> that it's a feature because it lets you suppress routine autovacuuming
> and still customize the anti-wraparound timing. Maybe it's a
> documentation bug: the docs say that enabled = false means the table
> is "never autovacuumed", but what the code seems to be implementing is
> "it's only autovacuumed when necessary to prevent wraparound". I
> think that's the behavior we want ...

IMO it's a usability bug which will be gone when we move to
pg_class.reloptions -- you won't need to set random values for options
you don't know what to set to.

As for documentation, this is mentioned somewhere. Perhaps not clearly
enough? OTOH I think the real problem is that people think
documentation can be skipped, thus they don't know the "fine print" --
so it won't matter how non-fine we make it.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, 2008-01-24 at 00:01 -0300, Alvaro Herrera wrote:

> IMO it's a usability bug which will be gone when we move to
> pg_class.reloptions -- you won't need to set random values for options
> you don't know what to set to.

But this is a problem in *this* release (and the last also?).

> As for documentation, this is mentioned somewhere. Perhaps not clearly
> enough? OTOH I think the real problem is that people think
> documentation can be skipped, thus they don't know the "fine print" --
> so it won't matter how non-fine we make it.

Not clear enough. I don't think Tom's suggested wording goes far enough
because not everybody understands this sufficiently to make the leap
that low settings will put you into a cycle of constant vacuuming.

We clamp autovacuum_freeze_max_age and autovacuum_freeze_min_age to
certain values, so I think we should do the same for values in the
pg_autovacuum table. i.e. force freeze_min_age and freeze_max_age to the
same min/max values as their GUC equivalents. Or at very least issue a
WARNING to the logs if a too-low value is present.

The docs should say "If you set autovacuum_freeze_age to 0 or a low
positive number this will cause the table to be constantly VACUUM
FREEZEd, which you might want, but you very probably don't".

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On 1/23/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> > I noticed that the Postgres autovacuum process was vacuuming some tables
> > that had enabled = false in pg_autovacuum.
>
> I think what is happening is that because you set
> pg_autovacuum.freeze_max_age to zero, the thing always thinks that it's
> time to force an anti-wraparound vacuum.
>
> In any case, you should be setting "unused" fields of a pg_autovacuum
> entry to -1, not zero.

Ah, I see. Thanks.

On Thu, 2008-01-24 at 00:01 -0300, Alvaro Herrera wrote:
> IMO it's a usability bug which will be gone when we move to
> pg_class.reloptions -- you won't need to set random values for options
> you don't know what to set to.

Or alternatively, and this is surely a moot point now, have the
numeric columns in pg_autovacuum be nullable (or default value of -1
even)? NULLs would assume the global default. After setting vacrelid
and enabled = false, it felt awfully kludgy to have to populate the
rest of the columns with zeros (now -1's).

Steve