Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly

On Mon, Mar 30, 2020 at 09:02:22PM +0300, Alexey Kondratov wrote:
> Hmm, I went through the well known to me SQL commands in Postgres and a bit
> more. Parenthesized options list is mostly used in two common cases:

There's also ANALYZE(VERBOSE), REINDEX(VERBOSE).
There was debate a year ago [0] as to whether to make "reindex CONCURRENTLY" a
separate command, or to use parenthesized syntax "REINDEX (CONCURRENTLY)". I
would propose to support that now (and implemented that locally).

..and explain(...)

> - In the beginning for boolean options only, e.g. VACUUM

You're right that those are currently boolean, but note that explain(FORMAT ..)
is not boolean.

> Putting it into the WITH (...) options list looks like an option to me.
> However, doing it only for VACUUM will ruin the consistency, while doing it
> for CLUSTER and REINDEX is not necessary, so I do not like it either.

It's not necessary but I think it's a more flexible way to add new
functionality (requiring no changes to the grammar for vacuum, and for
REINDEX/CLUSTER it would allow future options to avoid changing the grammar).

If we use parenthesized syntax for vacuum, my proposal is to do it for REINDEX, and
consider adding parenthesized syntax for cluster, too.

> To summarize, currently I see only 2 + 1 extra options:
>
> 1) Keep everything with syntax as it is in 0001-0002
> 2) Implement tail syntax for VACUUM, but with limitation for VACUUM FULL of
> the entire database + TABLESPACE change
> 3) Change TABLESPACE to a fully reserved word

+ 4) Use parenthesized syntax for all three.

Note, I mentioned that maybe VACUUM/CLUSTER should support not only "TABLESPACE
foo" but also "INDEX TABLESPACE bar" (I would use that, too). I think that
would be easy to implement, and for sure it would suggest using () for both.
(For sure we don't want to implement "VACUUM t TABLESPACE foo" now, and then
later implement "INDEX TABLESPACE bar" and realize that for consistency we
cannot parenthesize it.

Michael ? Alvaro ? Robert ?

--
Justin