Re: vacuumdb parallel has a deadlock detected in 9.5.4

Hello all:

On Thu, Sep 29, 2016 at 7:10 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Francisco Olarte wrote:
>
>> I would propose another behaviour, which I think can solve the problem
>> without introducing more complex code. Put a couple of flags to vacuum
>> only catalog tables / non catalog tables ( I believe this can be
>> served by include/exclude schemas too, which will be even more useful
>> for other things ). This way one can do a full paralell vacuum of
>> non-catalog objects followed by a serial one on catalog objects (
>> which should not be too slow on 'normal' databases )
>
> OK, that sounds easier to implement. Are you going to submit a patch?

After looking at vacuumdb source I'm trying to develop and submit a
patch implementing the following:

Two new flags --include-schema=NAME, --exclude-schema=NAME

Two new flags --system-schemas --non-system-schemas, as alias to
--include-schema=pg_catalog and --exclude-schema=pg_catalog

The intended behaviour will be:

- if a table list (--table) is specified, include / exclude schema
will be ignored ( potentially with a warning or may be an error if
more knowledgeable people steer me that way ).

- if both include and exclude are specified, include will take
preference (with warning or error ? ).

- if not the already existing code used to enumerate tables for
paralell vacuum will be augmented to either use only tables in the
include schemas or not use tables in the exclude schemas. If, after
enumeration, the table list becomes empty an error will be produced (
think on --include-schema=oops-I-mistyped-it ). The filtering will be
done client side and will be case sensitive against the schema name
name returned by the queries to the catalogs ( no case folding, to
make it simple ).

- After that vacuumdb will proceed as it would if the table list where
given in the command line, either in serial or paralell mode.

This does not intend to solve the paralell vacuum deadlocks, but it
can let people avoid them by just doing

vacuumdb -f --exclude-schema=pg_catalog -j 20
vacuumdb -f --include-schema=pg_catalog

And vaccuuming catalogs is not going to benefit that much from
paralellism as it is normally fast.

Any comment ?

Francisco Olarte.