Re: pg_autovacuum should allow NULL values

I just tried to add something to the pg_autovacuum table for the first
time today (with 8.1). I wanted to make the simplest possible entry:
Disable auto-vacuuming for a table. However, the data model requires
that I also enter values for:

vac_base_thresh
vac_scale_factor
anl_base_thres
anl_scale_factor
vac_cost_delay
vac_cost_limit

None of those values matter when vacuuming is disabled for the table! I
suggest all these fields be nullable, and default to global values if
they are NULL.

These are "guts" and I should have to learn about them or fake them if I
just want to disable vacuuming for a table.

Likewise, if I just want to set one of the values, I shouldn't have to
set /all/ of them if the defaults are otherwise reasonable.

For the moment, I suppose I'll go and fake all these values so I can
disable a table from Vacuuming.

Mark

Mark Stosberg wrote:
> I just tried to add something to the pg_autovacuum table for the first
> time today (with 8.1). I wanted to make the simplest possible entry:
> Disable auto-vacuuming for a table. However, the data model requires
> that I also enter values for:
>
> vac_base_thresh

You can use any negative value on these settings (-1 works fine, for
example).

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

On Fri, Feb 23, 2007 at 04:08:45PM -0300, Alvaro Herrera wrote:
> Mark Stosberg wrote:
> > I just tried to add something to the pg_autovacuum table for the first
> > time today (with 8.1). I wanted to make the simplest possible entry:
> > Disable auto-vacuuming for a table. However, the data model requires
> > that I also enter values for:
> >
> > vac_base_thresh
>
> You can use any negative value on these settings (-1 works fine, for
> example).

We should really make that the default so that you don't have to worry
about other fields...
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim C. Nasby wrote:
> On Fri, Feb 23, 2007 at 04:08:45PM -0300, Alvaro Herrera wrote:
>> Mark Stosberg wrote:
>>> I just tried to add something to the pg_autovacuum table for the first
>>> time today (with 8.1). I wanted to make the simplest possible entry:
>>> Disable auto-vacuuming for a table. However, the data model requires
>>> that I also enter values for:
>>>
>>> vac_base_thresh
>> You can use any negative value on these settings (-1 works fine, for
>> example).
>
> We should really make that the default so that you don't have to worry
> about other fields...

A default would be helpful, but I think "NULL" is a lot more intuitive
as a placeholder "don't know/ don't care", than "-1" is.

Adding a default of -1 seems like a more cumbersome way to express the
same thing to me.

Mark

Mark Stosberg wrote:
> Jim C. Nasby wrote:
> > On Fri, Feb 23, 2007 at 04:08:45PM -0300, Alvaro Herrera wrote:
> >> Mark Stosberg wrote:
> >>> I just tried to add something to the pg_autovacuum table for the first
> >>> time today (with 8.1). I wanted to make the simplest possible entry:
> >>> Disable auto-vacuuming for a table. However, the data model requires
> >>> that I also enter values for:
> >>>
> >>> vac_base_thresh
> >> You can use any negative value on these settings (-1 works fine, for
> >> example).
> >
> > We should really make that the default so that you don't have to worry
> > about other fields...
>
> A default would be helpful, but I think "NULL" is a lot more intuitive
> as a placeholder "don't know/ don't care", than "-1" is.
>
> Adding a default of -1 seems like a more cumbersome way to express the
> same thing to me.

To be frank, I don't remember what the rationale was for not using
NULLs. Simplicity of code, I guess.

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Mark Stosberg wrote:
>> Adding a default of -1 seems like a more cumbersome way to express the
>> same thing to me.

> To be frank, I don't remember what the rationale was for not using
> NULLs. Simplicity of code, I guess.

We tend to avoid allowing fixed-size fields to be NULL in the system
catalogs, because it prevents using the technique of overlaying C
structs onto the catalog tuples. In fact, if you wanted to have any
null fields in pg_autovacuum, you would need to find a way to prevent
initdb from enforcing that policy:

regression=# \d pg_autovacuum
Table "pg_catalog.pg_autovacuum"
Column | Type | Modifiers
------------------+---------+-----------
vacrelid | oid | not null
enabled | boolean | not null
vac_base_thresh | integer | not null
vac_scale_factor | real | not null
anl_base_thresh | integer | not null
anl_scale_factor | real | not null
vac_cost_delay | integer | not null
vac_cost_limit | integer | not null
freeze_min_age | integer | not null
freeze_max_age | integer | not null
Indexes:
"pg_autovacuum_vacrelid_index" UNIQUE, btree (vacrelid)

I don't find this particularly important, because we have never intended
direct update of catalog entries to be a primary way of interacting with
the system. The current pg_autovacuum setup is a stopgap until the dust
has settled enough that we know what sort of long-term API we want for
autovacuum.

regards, tom lane

I wrote:
> I don't find this particularly important, because we have never intended
> direct update of catalog entries to be a primary way of interacting with
> the system. The current pg_autovacuum setup is a stopgap until the dust
> has settled enough that we know what sort of long-term API we want for
> autovacuum.

I just had an epiphany about that. We've wanted to avoid setting the
autovacuum knobs in stone, because it's pretty obvious they're not
ready, and that has prevented us from inventing any nice SQL syntax for
managing the per-table settings.

Meanwhile, the storage-parameter infrastructure got added in 8.2.
Isn't that an absolutely ideal framework for managing per-table autovac
settings? We could drop the separate pg_autovacuum catalog altogether
and keep all the info in pg_class.reloptions. Advantages:

* The infrastructure is all there already, including ALTER TABLE and
pg_dump support.

* The parameter names are not SQL keywords, and the syntax isn't
hardwired to any particular set of them. So it would be fairly painless
to change the set of supported parameters, with or without backwards
compatibility to keep on recognizing an old parameter.

Disadvantages:

* Wouldn't be forwards-compatible with any hacks that people might
currently have to dump and restore pg_autovacuum contents. But you
could probably make a script to read your existing table and emit
ALTER TABLE SET commands instead.

regards, tom lane

On Fri, Feb 23, 2007 at 06:47:52PM -0500, Tom Lane wrote:
> I wrote:
> > I don't find this particularly important, because we have never intended
> > direct update of catalog entries to be a primary way of interacting with
> > the system. The current pg_autovacuum setup is a stopgap until the dust
> > has settled enough that we know what sort of long-term API we want for
> > autovacuum.
>
> I just had an epiphany about that. We've wanted to avoid setting the
> autovacuum knobs in stone, because it's pretty obvious they're not
> ready, and that has prevented us from inventing any nice SQL syntax for
> managing the per-table settings.
>
> Meanwhile, the storage-parameter infrastructure got added in 8.2.
> Isn't that an absolutely ideal framework for managing per-table autovac
> settings? We could drop the separate pg_autovacuum catalog altogether
> and keep all the info in pg_class.reloptions. Advantages:
>
> * The infrastructure is all there already, including ALTER TABLE and
> pg_dump support.
>
> * The parameter names are not SQL keywords, and the syntax isn't
> hardwired to any particular set of them. So it would be fairly painless
> to change the set of supported parameters, with or without backwards
> compatibility to keep on recognizing an old parameter.
>
> Disadvantages:
>
> * Wouldn't be forwards-compatible with any hacks that people might
> currently have to dump and restore pg_autovacuum contents. But you
> could probably make a script to read your existing table and emit
> ALTER TABLE SET commands instead.

Actually, if we wanted to we should be able to create a view that takes
the place of the current pg_autovacuum. With appropriate rules and some
functions, you could probably even make it updatable.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)