BUG #3326: Invalid lower bound of autovacuum_cost_limit

The following bug has been logged online:

Bug reference: 3326
Logged by: Galy Lee
Email address: lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp
PostgreSQL version: 8.3
Operating system: Red Hat 4.
Description: Invalid lower bound of autovacuum_cost_limit
Details:

Hello

I found some bugs which relative to the autovacuum_cost_limit GUC
parameter.

* Bug-1: Invalid lower bound of autovacuum_cost_limit

autovacuum_vacuum_cost_limit should be the following value:
autovacuum_vacuum_cost_limit = -1, or [1, 10000]
(0 should be prohibited. )

But 0 can also be accepted for autovacuum_vacuum_cost_limit now.

This causes zero-division error for autovacuum:

ERROR: floating-point exception
DETAIL: An invalid floating-point operation was signaled. This
probably means an out-of-range result or an invalid operation,
such as division by zero.

* Bug-2: 0-cost-limit for autovacuum worker

When autovacuum_max_workers > autovacuum_vacuum_cost_limit, the above
zero-division error also happened.

* Bug-3: no GUC constrain check for pg_autovacuum

The settings in pg_autovacuum are not checked enough now. Invalid value
can be passed to autovacuum, this also causes some columns in
pg_autovacuum has inconsistent upper and lower bound with their original
GUC constrain.

pg_autovacuum colum | definition | GUC constrain
--------------------+------------+--------------------------
vac_base_thresh | integer | [0, INT_MAX]
vac_scale_factor | real | [0.0, 100.0]
anl_base_thresh | integer | [0, INT_MAX]
anl_scale_factor | real | [0.0, 100.0]
vac_cost_delay | integer | [-1, 1000]
vac_cost_limit | integer | [-1, 10000]
freeze_min_age | integer | [0, 1000000000]
freeze_max_age | integer | [100000000, 2000000000]

The above table shows the wrong mapping between pg_autovacuum columns
and GUC constrain.

Galy Lee wrote:

Hi Galy,

> The following bug has been logged online:
>
> Bug reference: 3326
> Logged by: Galy Lee
> Email address: lee(dot)galy(at)oss(dot)ntt(dot)co(dot)jp
> PostgreSQL version: 8.3
> Operating system: Red Hat 4.
> Description: Invalid lower bound of autovacuum_cost_limit
> Details:

Damn. Thanks for reporting -- I'll investigate these issues.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Galy Lee wrote:

Hi,

I'll deal with each issue separately.

> * Bug-1: Invalid lower bound of autovacuum_cost_limit
>
> autovacuum_vacuum_cost_limit should be the following value:
> autovacuum_vacuum_cost_limit = -1, or [1, 10000]
> (0 should be prohibited. )
>
> But 0 can also be accepted for autovacuum_vacuum_cost_limit now.

This is solved easily by adding a check in an assign hook (attached).
The only remaining problem here is that other error messages could be
clearer.

So this is correct:

$ postmaster -c autovacuum_vacuum_cost_limit=0
17902 FATAL: invalid value for parameter "autovacuum_vacuum_cost_limit": 0

But this is misleading (started postmaster with good value, then edited
postgresql.conf and entered "-2"):

17903 LOG: received SIGHUP, reloading configuration files
17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)

Note how it still says the range is -1 .. 1000.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> But this is misleading (started postmaster with good value, then edited
> postgresql.conf and entered "-2"):

> 17903 LOG: received SIGHUP, reloading configuration files
> 17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)

> Note how it still says the range is -1 .. 1000.

Can we redefine things to make zero be the "disabled" value, thus
keeping the range of valid values contiguous?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > But this is misleading (started postmaster with good value, then edited
> > postgresql.conf and entered "-2"):
>
> > 17903 LOG: received SIGHUP, reloading configuration files
> > 17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)
>
> > Note how it still says the range is -1 .. 1000.
>
> Can we redefine things to make zero be the "disabled" value, thus
> keeping the range of valid values contiguous?

That would be another solution ... though it would be different from the
valid value for autovacuum_vacuum_cost_delay (on which 0 is a valid
value). Also it would be a different value from previous versions.

I don't think either of these is a showstopper, so let's go for that if
nobody objects.

--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
<inflex> really, I see PHP as like a strange amalgamation of C, Perl, Shell
<crab> inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
<crab> i.e., "deadly poison"

Alvaro Herrera wrote:
> Tom Lane wrote:
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> But this is misleading (started postmaster with good value, then edited
>>> postgresql.conf and entered "-2"):
>>> 17903 LOG: received SIGHUP, reloading configuration files
>>> 17903 LOG: -2 is outside the valid range for parameter "autovacuum_vacuum_cost_limit" (-1 .. 1000)
>>> Note how it still says the range is -1 .. 1000.
>> Can we redefine things to make zero be the "disabled" value, thus
>> keeping the range of valid values contiguous?
>
> That would be another solution ... though it would be different from the
> valid value for autovacuum_vacuum_cost_delay (on which 0 is a valid
> value). Also it would be a different value from previous versions.
>
> I don't think either of these is a showstopper, so let's go for that if
> nobody objects.

Can you make 0 and -1 both valid disabled values? That way it will be
compatible with previous releases.

Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Tom Lane wrote:
> >>Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> >>>But this is misleading (started postmaster with good value, then edited
> >>>postgresql.conf and entered "-2"):
> >>>17903 LOG: received SIGHUP, reloading configuration files
> >>>17903 LOG: -2 is outside the valid range for parameter
> >>>"autovacuum_vacuum_cost_limit" (-1 .. 1000)
> >>>Note how it still says the range is -1 .. 1000.
> >>Can we redefine things to make zero be the "disabled" value, thus
> >>keeping the range of valid values contiguous?
> >
> >That would be another solution ... though it would be different from the
> >valid value for autovacuum_vacuum_cost_delay (on which 0 is a valid
> >value). Also it would be a different value from previous versions.
> >
> >I don't think either of these is a showstopper, so let's go for that if
> >nobody objects.
>
> Can you make 0 and -1 both valid disabled values? That way it will be
> compatible with previous releases.

Heh, sure, we can do that too and it doesn't seem like anybody would
object. I will patch the documentation so that that the "disabled"
value is zero, and still allow -1. That way it doesn't seem like there
should be any objection.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)

Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:

> > Can you make 0 and -1 both valid disabled values? That way it will be
> > compatible with previous releases.
>
> Heh, sure, we can do that too and it doesn't seem like anybody would
> object. I will patch the documentation so that that the "disabled"
> value is zero, and still allow -1. That way it doesn't seem like there
> should be any objection.

Patch attached.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Matthew T. O'Connor wrote:
>>> Can you make 0 and -1 both valid disabled values? That way it will be
>>> compatible with previous releases.
>>
>> Heh, sure, we can do that too and it doesn't seem like anybody would
>> object. I will patch the documentation so that that the "disabled"
>> value is zero, and still allow -1. That way it doesn't seem like there
>> should be any objection.

> Patch attached.

It seems like documenting vac_cost_limit as being different from the
others will just create perceived complexity/confusion, with no real
benefit. I'd suggest leaving the documentation and the default value
alone, and applying just the part of the patch that causes 0 to be
silently treated as if it were -1.

A comment at the spot where this is done would be a good idea, but
I don't think we need to say anything in the SGML docs.

regards, tom lane

Galy Lee wrote:

> * Bug-2: 0-cost-limit for autovacuum worker
>
> When autovacuum_max_workers > autovacuum_vacuum_cost_limit, the above
> zero-division error also happened.

Ah, this is a problem in the balance code -- it fails to consider that
the cost limit may be end up being 0 in the integer calculations. This
patch fixes this problem.

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