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

On 20.09.2019 19:38, Alvaro Herrera wrote:
> On 2019-Sep-19, Robert Haas wrote:
>
>> 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?
> Hmm, my own reading of that was to add tablespace changing abilities to
> ALTER TABLE *in addition* to this patch, not instead of it.

That was my understanding too.

On 20.09.2019 11:26, Jose Luis Tallon wrote:
> On 20/9/19 4:06, Michael Paquier wrote:
>> Personally, I don't find this idea very attractive as ALTER TABLE is
>> already complicated enough with all the subqueries we already support
>> in the command, all the logic we need to maintain to make combinations
>> of those subqueries in a minimum number of steps, and also the number
>> of bugs we have seen because of the amount of complication present.
>
> Yes, but please keep the other options: At it is, cluster, vacuum full
> and reindex already rewrite the table in full; Being able to write the
> result to a different tablespace than the original object was stored
> in enables a whole world of very interesting possibilities....
> including a quick way out of a "so little disk space available that
> vacuum won't work properly" situation --- which I'm sure MANY users
> will appreciate, including me

Yes, sure, that was my main motivation. The first message in the thread
contains a patch, which adds SET TABLESPACE support to all of CLUSTER,
VACUUM FULL and REINDEX. However, there came up an idea to integrate
CLUSTER/VACUUM FULL with ALTER TABLE and do their work + all the ALTER
TABLE stuff in a single table rewrite. I've dig a little bit into this
and ended up with some architectural questions and concerns [1]. So I
decided to start with a simple REINDEX patch.

Anyway, I've followed Michael's advice and split the last patch into two:

1) Adds all the main functionality, but with simplified 'REINDEX INDEX [
CONCURRENTLY ] ... [ TABLESPACE ... ]' grammar;

2) Adds a more sophisticated syntax with '[ SET TABLESPACE ... [ NOWAIT
] ]'.

Patch 1 contains all the docs and tests and may be applied/committed
separately or together with 2, which is fully optional.

Recent merge conflicts and reindex_index validations order are also
fixed in the attached version.

[1]
https://www.postgresql.org/message-id/6b2a5c4de19f111ef24b63428033bb67%40postgrespro.ru

Regards

--
Alexey Kondratov

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