Re: A few new options for vacuumdb

On 1/28/19, 6:35 PM, "Michael Paquier" <michael@paquier.xyz> wrote:
> Anyway, patches 1 and 2 have been merged, and committed after some
> cleanup and adjustments.  Patch 3 gets much easier now.

Thanks!

> -    " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n");
> +    " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
> +    " LEFT JOIN pg_catalog.pg_class t"
> +    " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
> Why do need this part?

This is modeled after the query provided in the docs for preventing
transaction ID wraparound [0].  I think the idea is to combine the
relation with its TOAST table so that it does not need to be
considered separately.  The VACUUM commands generated in vacuumdb will
also process the corresponding TOAST table for the relation, anyway.

I noticed a behavior change from the catalog query patch that we
probably ought to fix.  The "WHERE c.relkind IN ('r', 'm')" clause
seems sufficient to collect all vacuumable relations (TOAST tables are
handled when vacuuming the main relation, and partitioned tables are
handled by vacuuming the partitions individually), but it is not
sufficient to match the previous behavior when --table is used.
Previously, we did not filter by relkind at all when --table is used.
Instead, we let the server emit a WARNING when a relation that
couldn't be processed was specified.

Previous behavior:
    ~% vacuumdb -d postgres -t foreign_table
    vacuumdb: vacuuming database "postgres"
    WARNING:  skipping "foreign_table" --- cannot vacuum non-tables or special system tables
    ~% vacuumdb -d postgres -t pg_toast.pg_toast_2600 --analyze-only
    vacuumdb: vacuuming database "postgres"
    WARNING:  skipping "pg_toast_2600" --- cannot analyze non-tables or special system tables

Current behavior:
    ~% vacuumdb -d postgres -t foreign_table
    vacuumdb: vacuuming database "postgres"
    ~% vacuumdb -d postgres -t pg_toast.pg_toast_2600 --analyze-only
    vacuumdb: vacuuming database "postgres"

I think the simplest way to fix this is to remove the relkind clause
altogether when --table is used and to let the server decide whether
it should be processed.  This effectively reinstates the previous
behavior so that users can specify TOAST tables, partitioned tables,
etc.

Unfortunately, this complicates the --min-xid-age and --min-mxid-age
patch a bit, as some of the relation types that can be vacuumed and/or
analyzed do not really have a transaction ID age.  AFAICT the simplest
way to handle this case is to filter out relations with a relfrozenxid
or relminmxid of 0.

The v7 patch set implements these proposed approaches.

Nathan

[0] https://www.postgresql.org/docs/devel/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND