Re: A few new options for vacuumdb

On Thu, Jan 24, 2019 at 12:49:28AM +0000, Bossart, Nathan wrote:
> Oh, wow. Thanks for pointing this out. I should have caught this.
> With 0002, we are basically just throwing out the column lists
> entirely as we obtain the qualified identifiers from the catalog
> query. To fix this, I've added an optional CTE for tracking any
> provided column lists. v5-0001 is your test patch for this case, and
> v5-0002 splits out the work for split_table_columns_spec().

I think that the query generation could be simplified by always using
the CTE if column lists are present or not, by associating NULL if no
column list is present, and by moving the regclass casting directly
into the CTE.

This way, for the following vacuumdb command with a set of tables
wanted:
vacuumdb --table aa --table 'bb(b)' --table 'cc(c)'

Then the query generated looks like that:
WITH column_lists (table_name, column_list) AS (
VALUES ('aa'::pg_catalog.regclass, NULL),
('bb'::pg_catalog.regclass, '(b)'),
('cc'::pg_catalog.regclass, '(c)')
)
SELECT c.relname, ns.nspname, column_lists.column_list
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace ns ON c.relnamespace
OPERATOR(pg_catalog.=) ns.oid
JOIN column_lists ON
column_lists.table_name
OPERATOR(pg_catalog.=) c.oid
WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
ORDER BY c.relpages DESC;

So only the following parts are added:
- The CTE with a table and its column list.
- A join on pg_class.oid and column_lists.table_name.
The latest version of the patch is doing a double amount of work by
using a left join and an extra set of clauses in WHERE to fetch the
matching column list from the table name entry.

If no tables are listed, then we just finish with that:
SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace ns ON c.relnamespace
OPERATOR(pg_catalog.=) ns.oid
WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
ORDER BY c.relpages DESC;
--
Michael