Subject: Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on
 the fly
To: Robert Haas <robertmhaas@gmail.com>, Michael Paquier <michael@paquier.xyz>
Cc: Surafel Temesgen <surafel3000@gmail.com>,
 Alexander Korotkov <a.korotkov@postgrespro.ru>,
 Masahiko Sawada <sawada.mshk@gmail.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
References: <f3255cfc-a395-133a-17d1-5fca873e7b05@postgrespro.ru>
 <20181227132417.xe3oagawina7775b@alvherre.pgsql>
 <CAD21AoCOsHnu2wLOaHgcZ4yJp7kaQkWfyhLzD4tjh6k4k4nhAA@mail.gmail.com>
 <CAPpHfdunJ4o5w9ndtOeAEt_h_H+XoZpgopK+NCsA30xeZTE8kw@mail.gmail.com>
 <6b2a5c4de19f111ef24b63428033bb67@postgrespro.ru>
 <CALAY4q-0oU4ORb07C4BxvGXgetBswqS1V5e2JP1wTV5q+O7-tA@mail.gmail.com>
 <34493046-cbda-3262-2ff9-4189bccfb862@postgrespro.ru>
 <20190919044300.GB21144@paquier.xyz>
 <CA+Tgmoae6pTLmz-oEHXFbEXGAQm6FCP9D5fF2KXLpbg2u0SJbg@mail.gmail.com>
From: Alexey Kondratov <a.kondratov@postgrespro.ru>
Message-ID: <f4881081-14cd-46d2-8e81-75003a7a7c83@postgrespro.ru>
Date: Thu, 19 Sep 2019 17:40:41 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+Tgmoae6pTLmz-oEHXFbEXGAQm6FCP9D5fF2KXLpbg2u0SJbg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

On 19.09.2019 16:21, Robert Haas wrote:
> On Thu, Sep 19, 2019 at 12:43 AM Michael Paquier <michael@paquier.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