Hi Hackers,
I would like to propose a change, which allow CLUSTER, VACUUM FULL and
REINDEX to modify relation tablespace on the fly. Actually, all these
commands rebuild relation filenodes from the scratch, thus it seems
natural to allow specifying them a new location. It may be helpful, when
a server went out of disk, so you can attach new partition and perform
e.g. VACUUM FULL, which will free some space and move data to a new
location at the same time. Otherwise, you cannot complete VACUUM FULL
until you have up to x2 relation disk space on a single partition.
Please, find attached a patch, which extend CLUSTER, VACUUM FULL and
REINDEX with additional options:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE } name [ SET TABLESPACE
new_tablespace ]
CLUSTER [VERBOSE] table_name [ USING index_name ] [ SET TABLESPACE
new_tablespace ]
CLUSTER [VERBOSE] [ SET TABLESPACE new_tablespace ]
VACUUM ( FULL [, ...] ) [ SET TABLESPACE new_tablespace ] [
table_and_columns [, ...] ]
VACUUM FULL [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ SET TABLESPACE
new_tablespace ] [ table_and_columns [, ...] ]
Thereby I have a few questions:
1) What do you think about this concept in general?
2) Is SET TABLESPACE an appropriate syntax for this functionality? I
thought also about a plain TABLESPACE keyword, but it seems to be
misleading, and WITH (options) clause like in CREATE SUBSCRIPTION ...
WITH (options). So I preferred SET TABLESPACE, since the same syntax is
used currently in ALTER to change tablespace, but maybe someone will
have a better idea.
3) I was not able to update the lexer for VACUUM FULL to use SET
TABLESPACE after table_and_columns and completely get rid of
shift/reduce conflicts. I guess it happens, since table_and_columns is
optional and may be of variable length, but have no idea how to deal
with it. Any thoughts?
Regards
--
Alexey Kondratov
Postgres Professionalhttps://www.postgrespro.com
Russian Postgres Company