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

On 19.09.2019 16:21, Robert Haas wrote:
> On Thu, Sep 19, 2019 at 12:43 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>> It seems to me that it would be good to keep the patch as simple as
>> possible for its first version, and split it into two if you would
>> like to add this new option instead of bundling both together. This
>> makes the review of one and the other more simple. Anyway, regarding
>> the grammar, is SET TABLESPACE really our best choice here? What
>> about:
>> - TABLESPACE = foo, in parenthesis only?
>> - Only using TABLESPACE, without SET at the end of the query?
>>
>> SET is used in ALTER TABLE per the set of subqueries available there,
>> but that's not the case of REINDEX.
> So, earlier in this thread, I suggested making this part of ALTER
> TABLE, and several people seemed to like that idea. Did we have a
> reason for dropping that approach?

If we add this option to REINDEX, then for 'ALTER TABLE tb_name action1,
REINDEX SET TABLESPACE tbsp_name, action3' action2 will be just a direct
alias to 'REINDEX TABLE tb_name SET TABLESPACE tbsp_name'. So it seems
practical to do this for REINDEX first.

The only one concern I have against adding REINDEX to ALTER TABLE in
this context is that it will allow user to write such a chimera:

ALTER TABLE tb_name REINDEX SET TABLESPACE tbsp_name, SET TABLESPACE
tbsp_name;

when they want to move both table and all the indexes. Because simple

ALTER TABLE tb_name REINDEX, SET TABLESPACE tbsp_name;

looks ambiguous. Should it change tablespace of table, indexes or both?

--
Alexey Kondratov

Postgres Professional https://www.postgrespro.com
Russian Postgres Company