agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH 6/8] Add default_toast_compression GUC 2+ messages / 2 participants [nested] [flat]
* [PATCH 6/8] Add default_toast_compression GUC @ 2021-03-10 08:35 Dilip Kumar <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Dilip Kumar @ 2021-03-10 08:35 UTC (permalink / raw) Justin Pryzby and Dilip Kumar --- src/backend/access/common/toast_compression.c | 46 +++++++++++++++++++ src/backend/access/common/tupdesc.c | 2 +- src/backend/bootstrap/bootstrap.c | 2 +- src/backend/commands/tablecmds.c | 8 ++-- src/backend/utils/misc/guc.c | 12 +++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/access/toast_compression.h | 22 ++++++++- src/test/regress/expected/compression.out | 16 +++++++ src/test/regress/expected/compression_1.out | 19 ++++++++ src/test/regress/sql/compression.sql | 8 ++++ 10 files changed, 128 insertions(+), 8 deletions(-) diff --git a/src/backend/access/common/toast_compression.c b/src/backend/access/common/toast_compression.c index 3463b42438..e33f92687c 100644 --- a/src/backend/access/common/toast_compression.c +++ b/src/backend/access/common/toast_compression.c @@ -55,6 +55,9 @@ const CompressionRoutine toast_compression[] = } }; +/* Compile-time default */ +char *default_toast_compression = DEFAULT_TOAST_COMPRESSION; + /* * pglz_cmcompress - compression routine for pglz compression method * @@ -306,3 +309,46 @@ GetCompressionRoutines(char method) { return &toast_compression[CompressionMethodToId(method)]; } + +/* check_hook: validate new default_toast_compression */ +bool +check_default_toast_compression(char **newval, void **extra, GucSource source) +{ + if (**newval == '\0') + { + GUC_check_errdetail("%s cannot be empty.", + "default_toast_compression"); + return false; + } + + if (strlen(*newval) >= NAMEDATALEN) + { + GUC_check_errdetail("%s is too long (maximum %d characters).", + "default_toast_compression", NAMEDATALEN - 1); + return false; + } + + if (!CompressionMethodIsValid(CompressionNameToMethod(*newval))) + { + /* + * When source == PGC_S_TEST, don't throw a hard error for a + * nonexistent compression method, only a NOTICE. See comments in + * guc.h. + */ + if (source == PGC_S_TEST) + { + ereport(NOTICE, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("compression method \"%s\" does not exist", + *newval))); + } + else + { + GUC_check_errdetail("Compression method \"%s\" does not exist.", + *newval); + return false; + } + } + + return true; +} diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index a66d1113db..362a371a6c 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -668,7 +668,7 @@ TupleDescInitEntry(TupleDesc desc, att->attcollation = typeForm->typcollation; if (IsStorageCompressible(typeForm->typstorage)) - att->attcompression = DefaultCompressionMethod; + att->attcompression = GetDefaultToastCompression(); else att->attcompression = InvalidCompressionMethod; diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index af0c0aa77e..9be0841d08 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -733,7 +733,7 @@ DefineAttr(char *name, char *type, int attnum, int nullness) attrtypes[attnum]->atttypmod = -1; attrtypes[attnum]->attislocal = true; if (IsStorageCompressible(attrtypes[attnum]->attstorage)) - attrtypes[attnum]->attcompression = DefaultCompressionMethod; + attrtypes[attnum]->attcompression = GetDefaultToastCompression(); else attrtypes[attnum]->attcompression = InvalidCompressionMethod; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index e20dd5f63c..a0d2af0cde 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11971,7 +11971,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, if (!IsStorageCompressible(tform->typstorage)) attTup->attcompression = InvalidCompressionMethod; else if (!CompressionMethodIsValid(attTup->attcompression)) - attTup->attcompression = DefaultCompressionMethod; + attTup->attcompression = GetDefaultToastCompression(); } else attTup->attcompression = InvalidCompressionMethod; @@ -17847,9 +17847,9 @@ GetAttributeCompression(Form_pg_attribute att, char *compression) /* fallback to default compression if it's not specified */ if (compression == NULL) - return DefaultCompressionMethod; - - cmethod = CompressionNameToMethod(compression); + cmethod = GetDefaultToastCompression(); + else + cmethod = CompressionNameToMethod(compression); return cmethod; } diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 953837085f..e59ed5b214 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -33,6 +33,7 @@ #include "access/gin.h" #include "access/rmgr.h" #include "access/tableam.h" +#include "access/toast_compression.h" #include "access/transam.h" #include "access/twophase.h" #include "access/xact.h" @@ -3915,6 +3916,17 @@ static struct config_string ConfigureNamesString[] = check_default_table_access_method, NULL, NULL }, + { + {"default_toast_compression", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Sets the default compression for new columns."), + NULL, + GUC_IS_NAME + }, + &default_toast_compression, + DEFAULT_TOAST_COMPRESSION, + check_default_toast_compression, NULL, NULL + }, + { {"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT, gettext_noop("Sets the default tablespace to create tables and indexes in."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index f46c2dd7a8..7d7a433dcc 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -659,6 +659,7 @@ #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace #default_table_access_method = 'heap' +#default_toast_compression = 'pglz' # 'pglz' or 'lz4' #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off diff --git a/src/include/access/toast_compression.h b/src/include/access/toast_compression.h index 38800cb97a..e9d9ce5634 100644 --- a/src/include/access/toast_compression.h +++ b/src/include/access/toast_compression.h @@ -15,6 +15,14 @@ #include "postgres.h" +#include "utils/guc.h" + +/* default compression method if not specified. */ +#define DEFAULT_TOAST_COMPRESSION "pglz" + +/* GUCs */ +extern char *default_toast_compression; + /* * Built-in compression methods. pg_attribute will store this in the * attcompression column. @@ -36,8 +44,6 @@ typedef enum CompressionId LZ4_COMPRESSION_ID = 1 } CompressionId; -/* use default compression method if it is not specified. */ -#define DefaultCompressionMethod PGLZ_COMPRESSION #define IsValidCompression(cm) ((cm) != InvalidCompressionMethod) #define IsStorageCompressible(storage) ((storage) != TYPSTORAGE_PLAIN && \ @@ -67,6 +73,8 @@ typedef struct CompressionRoutine extern char CompressionNameToMethod(char *compression); extern const CompressionRoutine *GetCompressionRoutines(char method); +extern bool check_default_toast_compression(char **newval, void **extra, + GucSource source); /* * CompressionMethodToId - Convert compression method to compression id. @@ -115,5 +123,15 @@ GetCompressionMethodName(char method) return GetCompressionRoutines(method)->cmname; } +/* + * GetDefaultToastCompression -- get the current toast compression + * + * This exists to hide the use of the default_toast_compression GUC variable. + */ +static inline char +GetDefaultToastCompression(void) +{ + return CompressionNameToMethod(default_toast_compression); +} #endif /* TOAST_COMPRESSION_H */ diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out index 6981d580dc..9ddffc8b18 100644 --- a/src/test/regress/expected/compression.out +++ b/src/test/regress/expected/compression.out @@ -231,6 +231,22 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); NOTICE: merging column "f1" with inherited definition ERROR: column "f1" has a compression method conflict DETAIL: pglz versus lz4 +-- test default_toast_compression GUC +SET default_toast_compression = ''; +ERROR: invalid value for parameter "default_toast_compression": "" +DETAIL: default_toast_compression cannot be empty. +SET default_toast_compression = 'I do not exist compression'; +ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression" +DETAIL: Compression method "I do not exist compression" does not exist. +SET default_toast_compression = 'lz4'; +DROP TABLE cmdata2; +CREATE TABLE cmdata2 (f1 text); +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | lz4 | | + -- check data is ok SELECT length(f1) FROM cmdata; length diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out index 893c18c7fa..03a7f46554 100644 --- a/src/test/regress/expected/compression_1.out +++ b/src/test/regress/expected/compression_1.out @@ -237,6 +237,25 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); NOTICE: merging column "f1" with inherited definition ERROR: column "f1" has a compression method conflict DETAIL: pglz versus lz4 +-- test default_toast_compression GUC +SET default_toast_compression = ''; +ERROR: invalid value for parameter "default_toast_compression": "" +DETAIL: default_toast_compression cannot be empty. +SET default_toast_compression = 'I do not exist compression'; +ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression" +DETAIL: Compression method "I do not exist compression" does not exist. +SET default_toast_compression = 'lz4'; +ERROR: unsupported LZ4 compression method +DETAIL: This functionality requires the server to be built with lz4 support. +HINT: You need to rebuild PostgreSQL using --with-lz4. +DROP TABLE cmdata2; +CREATE TABLE cmdata2 (f1 text); +\d+ cmdata2 + Table "public.cmdata2" + Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description +--------+------+-----------+----------+---------+----------+-------------+--------------+------------- + f1 | text | | | | extended | pglz | | + -- check data is ok SELECT length(f1) FROM cmdata; length diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql index b085c9fbd9..6c9b24f1f7 100644 --- a/src/test/regress/sql/compression.sql +++ b/src/test/regress/sql/compression.sql @@ -103,6 +103,14 @@ SELECT pg_column_compression(f1) FROM cmpart; CREATE TABLE cminh() INHERITS(cmdata, cmdata1); CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata); +-- test default_toast_compression GUC +SET default_toast_compression = ''; +SET default_toast_compression = 'I do not exist compression'; +SET default_toast_compression = 'lz4'; +DROP TABLE cmdata2; +CREATE TABLE cmdata2 (f1 text); +\d+ cmdata2 + -- check data is ok SELECT length(f1) FROM cmdata; SELECT length(f1) FROM cmdata1; -- 2.17.0 --cvVnyQ+4j833TQvp Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0007-Alter-table-set-compression.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
* [PATCH v33 1/5] Add REPACK command @ 2026-01-27 10:48 Antonin Houska <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Antonin Houska @ 2026-01-27 10:48 UTC (permalink / raw) REPACK absorbs the functionality of VACUUM FULL and CLUSTER in a single command. Because this functionality is completely different from regular VACUUM, having it separate from VACUUM makes it easier for users to understand; as for CLUSTER, the term is heavily overloaded in the IT world and even in Postgres itself, so it's good that we can avoid it. Author: Antonin Houska <[email protected]> Co-authored-by: Álvaro Herrera <[email protected]> Reviewed-by: Mihail Nikalayeu <[email protected]> Reviewed-by: Robert Treat <[email protected]> Reviewed-by: Euler Taveira <[email protected]> Reviewed-by: Matheus Alcantara <[email protected]> Reviewed-by: Junwang Zhao <[email protected]> Reviewed-by: jian he <[email protected]> Discussion: https://postgr.es/m/82651.1720540558@antos Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/monitoring.sgml | 223 +++++- doc/src/sgml/ref/allfiles.sgml | 1 + doc/src/sgml/ref/cluster.sgml | 97 +-- doc/src/sgml/ref/repack.sgml | 328 +++++++++ doc/src/sgml/ref/vacuum.sgml | 33 +- doc/src/sgml/reference.sgml | 1 + src/backend/access/heap/heapam_handler.c | 32 +- src/backend/catalog/index.c | 2 +- src/backend/catalog/system_views.sql | 29 +- src/backend/commands/cluster.c | 848 +++++++++++++++-------- src/backend/commands/vacuum.c | 6 +- src/backend/parser/gram.y | 86 ++- src/backend/tcop/utility.c | 23 +- src/backend/utils/adt/pgstatfuncs.c | 4 +- src/bin/psql/tab-complete.in.c | 42 +- src/include/commands/cluster.h | 8 +- src/include/commands/progress.h | 48 +- src/include/nodes/parsenodes.h | 35 +- src/include/parser/kwlist.h | 1 + src/include/tcop/cmdtaglist.h | 1 + src/include/utils/backend_progress.h | 2 +- src/test/regress/expected/cluster.out | 134 +++- src/test/regress/expected/rules.out | 72 +- src/test/regress/sql/cluster.sql | 70 +- src/tools/pgindent/typedefs.list | 2 + 25 files changed, 1598 insertions(+), 530 deletions(-) create mode 100644 doc/src/sgml/ref/repack.sgml diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b77d189a500..71c92ed53ef 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -405,6 +405,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser </entry> </row> + <row> + <entry><structname>pg_stat_progress_repack</structname><indexterm><primary>pg_stat_progress_repack</primary></indexterm></entry> + <entry>One row for each backend running + <command>REPACK</command>, showing current progress. See + <xref linkend="repack-progress-reporting"/>. + </entry> + </row> + <row> <entry><structname>pg_stat_progress_basebackup</structname><indexterm><primary>pg_stat_progress_basebackup</primary></indexterm></entry> <entry>One row for each WAL sender process streaming a base backup, @@ -5646,7 +5654,8 @@ FROM pg_stat_get_backend_idset() AS backendid; certain commands during command execution. Currently, the only commands which support progress reporting are <command>ANALYZE</command>, <command>CLUSTER</command>, - <command>CREATE INDEX</command>, <command>VACUUM</command>, + <command>CREATE INDEX</command>, <command>REPACK</command>, + <command>VACUUM</command>, <command>COPY</command>, and <xref linkend="protocol-replication-base-backup"/> (i.e., replication command that <xref linkend="app-pgbasebackup"/> issues to take @@ -6130,6 +6139,218 @@ FROM pg_stat_get_backend_idset() AS backendid; </table> </sect2> + <sect2 id="repack-progress-reporting"> + <title>REPACK Progress Reporting</title> + + <indexterm> + <primary>pg_stat_progress_repack</primary> + </indexterm> + + <para> + Whenever <command>REPACK</command> is running, + the <structname>pg_stat_progress_repack</structname> view will contain a + row for each backend that is currently running the command. The tables + below describe the information that will be reported and provide + information about how to interpret it. + </para> + + <table id="pg-stat-progress-repack-view" xreflabel="pg_stat_progress_repack"> + <title><structname>pg_stat_progress_repack</structname> View</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>pid</structfield> <type>integer</type> + </para> + <para> + Process ID of backend. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>datid</structfield> <type>oid</type> + </para> + <para> + OID of the database to which this backend is connected. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>datname</structfield> <type>name</type> + </para> + <para> + Name of the database to which this backend is connected. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>relid</structfield> <type>oid</type> + </para> + <para> + OID of the table being repacked. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>phase</structfield> <type>text</type> + </para> + <para> + Current processing phase. See <xref linkend="repack-phases"/>. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>repack_index_relid</structfield> <type>oid</type> + </para> + <para> + If the table is being scanned using an index, this is the OID of the + index being used; otherwise, it is zero. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>heap_tuples_scanned</structfield> <type>bigint</type> + </para> + <para> + Number of heap tuples scanned. + This counter only advances when the phase is + <literal>seq scanning heap</literal>, + <literal>index scanning heap</literal> + or <literal>writing new heap</literal>. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>heap_tuples_written</structfield> <type>bigint</type> + </para> + <para> + Number of heap tuples written. + This counter only advances when the phase is + <literal>seq scanning heap</literal>, + <literal>index scanning heap</literal> + or <literal>writing new heap</literal>. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>heap_blks_total</structfield> <type>bigint</type> + </para> + <para> + Total number of heap blocks in the table. This number is reported + as of the beginning of <literal>seq scanning heap</literal>. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>heap_blks_scanned</structfield> <type>bigint</type> + </para> + <para> + Number of heap blocks scanned. This counter only advances when the + phase is <literal>seq scanning heap</literal>. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>index_rebuild_count</structfield> <type>bigint</type> + </para> + <para> + Number of indexes rebuilt. This counter only advances when the phase + is <literal>rebuilding index</literal>. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <table id="repack-phases"> + <title>REPACK Phases</title> + <tgroup cols="2"> + <colspec colname="col1" colwidth="1*"/> + <colspec colname="col2" colwidth="2*"/> + <thead> + <row> + <entry>Phase</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>initializing</literal></entry> + <entry> + The command is preparing to begin scanning the heap. This phase is + expected to be very brief. + </entry> + </row> + <row> + <entry><literal>seq scanning heap</literal></entry> + <entry> + The command is currently scanning the table using a sequential scan. + </entry> + </row> + <row> + <entry><literal>index scanning heap</literal></entry> + <entry> + <command>REPACK</command> is currently scanning the table using an index scan. + </entry> + </row> + <row> + <entry><literal>sorting tuples</literal></entry> + <entry> + <command>REPACK</command> is currently sorting tuples. + </entry> + </row> + <row> + <entry><literal>writing new heap</literal></entry> + <entry> + <command>REPACK</command> is currently writing the new heap. + </entry> + </row> + <row> + <entry><literal>swapping relation files</literal></entry> + <entry> + The command is currently swapping newly-built files into place. + </entry> + </row> + <row> + <entry><literal>rebuilding index</literal></entry> + <entry> + The command is currently rebuilding an index. + </entry> + </row> + <row> + <entry><literal>performing final cleanup</literal></entry> + <entry> + The command is performing final cleanup. When this phase is + completed, <command>REPACK</command> will end. + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect2> + <sect2 id="copy-progress-reporting"> <title>COPY Progress Reporting</title> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index e167406c744..141ada9c50a 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -167,6 +167,7 @@ Complete list of usable sgml source files in this directory. <!ENTITY refreshMaterializedView SYSTEM "refresh_materialized_view.sgml"> <!ENTITY reindex SYSTEM "reindex.sgml"> <!ENTITY releaseSavepoint SYSTEM "release_savepoint.sgml"> +<!ENTITY repack SYSTEM "repack.sgml"> <!ENTITY reset SYSTEM "reset.sgml"> <!ENTITY revoke SYSTEM "revoke.sgml"> <!ENTITY rollback SYSTEM "rollback.sgml"> diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml index 0b47460080b..2cda711bc9f 100644 --- a/doc/src/sgml/ref/cluster.sgml +++ b/doc/src/sgml/ref/cluster.sgml @@ -33,51 +33,13 @@ CLUSTER [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r <title>Description</title> <para> - <command>CLUSTER</command> instructs <productname>PostgreSQL</productname> - to cluster the table specified - by <replaceable class="parameter">table_name</replaceable> - based on the index specified by - <replaceable class="parameter">index_name</replaceable>. The index must - already have been defined on - <replaceable class="parameter">table_name</replaceable>. + The <command>CLUSTER</command> command is equivalent to + <xref linkend="sql-repack"/> with an <literal>USING INDEX</literal> + clause. See there for more details. </para> - <para> - When a table is clustered, it is physically reordered - based on the index information. Clustering is a one-time operation: - when the table is subsequently updated, the changes are - not clustered. That is, no attempt is made to store new or - updated rows according to their index order. (If one wishes, one can - periodically recluster by issuing the command again. Also, setting - the table's <literal>fillfactor</literal> storage parameter to less than - 100% can aid in preserving cluster ordering during updates, since updated - rows are kept on the same page if enough space is available there.) - </para> +<!-- Do we need to describe exactly which options map to what? They seem obvious to me. --> - <para> - When a table is clustered, <productname>PostgreSQL</productname> - remembers which index it was clustered by. The form - <command>CLUSTER <replaceable class="parameter">table_name</replaceable></command> - reclusters the table using the same index as before. You can also - use the <literal>CLUSTER</literal> or <literal>SET WITHOUT CLUSTER</literal> - forms of <link linkend="sql-altertable"><command>ALTER TABLE</command></link> to set the index to be used for - future cluster operations, or to clear any previous setting. - </para> - - <para> - <command>CLUSTER</command> without a - <replaceable class="parameter">table_name</replaceable> reclusters all the - previously-clustered tables in the current database that the calling user - has privileges for. This form of <command>CLUSTER</command> cannot be - executed inside a transaction block. - </para> - - <para> - When a table is being clustered, an <literal>ACCESS - EXCLUSIVE</literal> lock is acquired on it. This prevents any other - database operations (both reads and writes) from operating on the - table until the <command>CLUSTER</command> is finished. - </para> </refsect1> <refsect1> @@ -136,63 +98,12 @@ CLUSTER [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r on the table. </para> - <para> - In cases where you are accessing single rows randomly - within a table, the actual order of the data in the - table is unimportant. However, if you tend to access some - data more than others, and there is an index that groups - them together, you will benefit from using <command>CLUSTER</command>. - If you are requesting a range of indexed values from a table, or a - single indexed value that has multiple rows that match, - <command>CLUSTER</command> will help because once the index identifies the - table page for the first row that matches, all other rows - that match are probably already on the same table page, - and so you save disk accesses and speed up the query. - </para> - - <para> - <command>CLUSTER</command> can re-sort the table using either an index scan - on the specified index, or (if the index is a b-tree) a sequential - scan followed by sorting. It will attempt to choose the method that - will be faster, based on planner cost parameters and available statistical - information. - </para> - <para> While <command>CLUSTER</command> is running, the <xref linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog, pg_temp</literal>. </para> - <para> - When an index scan is used, a temporary copy of the table is created that - contains the table data in the index order. Temporary copies of each - index on the table are created as well. Therefore, you need free space on - disk at least equal to the sum of the table size and the index sizes. - </para> - - <para> - When a sequential scan and sort is used, a temporary sort file is - also created, so that the peak temporary space requirement is as much - as double the table size, plus the index sizes. This method is often - faster than the index scan method, but if the disk space requirement is - intolerable, you can disable this choice by temporarily setting <xref - linkend="guc-enable-sort"/> to <literal>off</literal>. - </para> - - <para> - It is advisable to set <xref linkend="guc-maintenance-work-mem"/> to - a reasonably large value (but not more than the amount of RAM you can - dedicate to the <command>CLUSTER</command> operation) before clustering. - </para> - - <para> - Because the planner records statistics about the ordering of - tables, it is advisable to run <link linkend="sql-analyze"><command>ANALYZE</command></link> - on the newly clustered table. - Otherwise, the planner might make poor choices of query plans. - </para> - <para> Because <command>CLUSTER</command> remembers which indexes are clustered, one can cluster the tables one wants clustered manually the first time, diff --git a/doc/src/sgml/ref/repack.sgml b/doc/src/sgml/ref/repack.sgml new file mode 100644 index 00000000000..61d5c2cdef1 --- /dev/null +++ b/doc/src/sgml/ref/repack.sgml @@ -0,0 +1,328 @@ +<!-- +doc/src/sgml/ref/repack.sgml +PostgreSQL documentation +--> + +<refentry id="sql-repack"> + <indexterm zone="sql-repack"> + <primary>REPACK</primary> + </indexterm> + + <refmeta> + <refentrytitle>REPACK</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>REPACK</refname> + <refpurpose>rewrite a table to reclaim disk space</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +REPACK [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [ USING INDEX [ <replaceable class="parameter">index_name</replaceable> ] ] ] +REPACK [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] USING INDEX + +<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> + + VERBOSE [ <replaceable class="parameter">boolean</replaceable> ] + ANALYZE [ <replaceable class="parameter">boolean</replaceable> ] + +<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase> + + <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>REPACK</command> reclaims storage occupied by dead + tuples. Unlike <command>VACUUM</command>, it does so by rewriting the + entire contents of the table specified + by <replaceable class="parameter">table_name</replaceable> into a new disk + file with no extra space (except for the space guaranteed by + the <literal>fillfactor</literal> storage parameter), allowing unused space + to be returned to the operating system. + </para> + + <para> + Without + a <replaceable class="parameter">table_name</replaceable>, <command>REPACK</command> + processes every table and materialized view in the current database that + the current user has the <literal>MAINTAIN</literal> privilege on. This + form of <command>REPACK</command> cannot be executed inside a transaction + block. + </para> + + <para> + If a <literal>USING INDEX</literal> clause is specified, the rows are + physically reordered based on information from an index. Please see the + notes on clustering below. + </para> + + <para> + When a table is being repacked, an <literal>ACCESS EXCLUSIVE</literal> lock + is acquired on it. This prevents any other database operations (both reads + and writes) from operating on the table until the <command>REPACK</command> + is finished. + </para> + + <refsect2 id="sql-repack-notes-on-clustering" xreflabel="Notes on Clustering"> + <title>Notes on Clustering</title> + + <para> + If the <literal>USING INDEX</literal> clause is specified, the rows in + the table are physically reordered following an index: if an index name + is specified in the command, then that index is used; if no index name + is specified, then the index that has been configured as the index to + cluster on. If no index has been configured in this way, an error is + thrown. The index given in the <literal>USING INDEX</literal> clause + is configured as the index to cluster on, as well as an index given + to the <command>CLUSTER</command> command. An index can be set + manually using <command>ALTER TABLE ... CLUSTER ON</command>, and reset + with <command>ALTER TABLE ... SET WITHOUT CLUSTER</command>. + </para> + + <para> + If no table name is specified in <command>REPACK USING INDEX</command>, + all tables which have a clustering index defined and which the calling + user has privileges for are processed. + </para> + + <para> + Clustering is a one-time operation: when the table is + subsequently updated, the changes are not clustered. That is, no attempt + is made to store new or updated rows according to their index order. (If + one wishes, one can periodically recluster by issuing the command again. + Also, setting the table's <literal>fillfactor</literal> storage parameter + to less than 100% can aid in preserving cluster ordering during updates, + since updated rows are kept on the same page if enough space is available + there.) + </para> + + <para> + In cases where you are accessing single rows randomly within a table, the + actual order of the data in the table is unimportant. However, if you tend + to access some data more than others, and there is an index that groups + them together, you will benefit from using clustering. If + you are requesting a range of indexed values from a table, or a single + indexed value that has multiple rows that match, + <command>REPACK</command> will help because once the index identifies the + table page for the first row that matches, all other rows that match are + probably already on the same table page, and so you save disk accesses and + speed up the query. + </para> + + <para> + <command>REPACK</command> can re-sort the table using either an index scan + on the specified index (if the index is a b-tree), or a sequential scan + followed by sorting. It will attempt to choose the method that will be + faster, based on planner cost parameters and available statistical + information. + </para> + + <para> + Because the planner records statistics about the ordering of tables, it is + advisable to + run <link linkend="sql-analyze"><command>ANALYZE</command></link> on the + newly repacked table. Otherwise, the planner might make poor choices of + query plans. + </para> + </refsect2> + + <refsect2 id="sql-repack-notes-on-resources" xreflabel="Notes on Resources"> + <title>Notes on Resources</title> + + <para> + When an index scan or a sequential scan without sort is used, a temporary + copy of the table is created that contains the table data in the index + order. Temporary copies of each index on the table are created as well. + Therefore, you need free space on disk at least equal to the sum of the + table size and the index sizes. + </para> + + <para> + When a sequential scan and sort is used, a temporary sort file is also + created, so that the peak temporary space requirement is as much as double + the table size, plus the index sizes. This method is often faster than + the index scan method, but if the disk space requirement is intolerable, + you can disable this choice by temporarily setting + <xref linkend="guc-enable-sort"/> to <literal>off</literal>. + </para> + + <para> + It is advisable to set <xref linkend="guc-maintenance-work-mem"/> to a + reasonably large value (but not more than the amount of RAM you can + dedicate to the <command>REPACK</command> operation) before repacking. + </para> + </refsect2> + + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">table_name</replaceable></term> + <listitem> + <para> + The name (possibly schema-qualified) of a table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">column_name</replaceable></term> + <listitem> + <para> + The name of a specific column to analyze. Defaults to all columns. + If a column list is specific, <literal>ANALYZE</literal> must also + be specified. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">index_name</replaceable></term> + <listitem> + <para> + The name of an index. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>VERBOSE</literal></term> + <listitem> + <para> + Prints a progress report as each table is repacked + at <literal>INFO</literal> level. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ANALYZE</literal></term> + <term><literal>ANALYSE</literal></term> + <listitem> + <para> + Applies <xref linkend="sql-analyze"/> on the table after repacking. This is + currently only supported when a single (non-partitioned) table is specified. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">boolean</replaceable></term> + <listitem> + <para> + Specifies whether the selected option should be turned on or off. + You can write <literal>TRUE</literal>, <literal>ON</literal>, or + <literal>1</literal> to enable the option, and <literal>FALSE</literal>, + <literal>OFF</literal>, or <literal>0</literal> to disable it. The + <replaceable class="parameter">boolean</replaceable> value can also + be omitted, in which case <literal>TRUE</literal> is assumed. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + To repack a table, one must have the <literal>MAINTAIN</literal> privilege + on the table. + </para> + + <para> + While <command>REPACK</command> is running, the <xref + linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog, + pg_temp</literal>. + </para> + + <para> + Each backend running <command>REPACK</command> will report its progress + in the <structname>pg_stat_progress_repack</structname> view. See + <xref linkend="repack-progress-reporting"/> for details. + </para> + + <para> + Repacking a partitioned table repacks each of its partitions. If an index + is specified, each partition is repacked using the partition of that + index. <command>REPACK</command> on a partitioned table cannot be executed + inside a transaction block. + </para> + + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Repack the table <literal>employees</literal>: +<programlisting> +REPACK employees; +</programlisting> + </para> + + <para> + Repack the table <literal>employees</literal> on the basis of its + index <literal>employees_ind</literal> (Since index is used here, this is + effectively clustering): +<programlisting> +REPACK employees USING INDEX employees_ind; +</programlisting> + </para> + + <para> + Repack the table <literal>cases</literal> on physical ordering, + running an <command>ANALYZE</command> on the given columns once + repacking is done, showing informational messages: +<programlisting> +REPACK (ANALYZE, VERBOSE) cases (district, case_nr); +</programlisting> + </para> + + <para> + Repack all tables in the database on which you have + the <literal>MAINTAIN</literal> privilege: +<programlisting> +REPACK; +</programlisting> + </para> + + <para> + Repack all tables for which a clustering index has previously been + configured on which you have the <literal>MAINTAIN</literal> privilege, + showing informational messages: +<programlisting> +REPACK (VERBOSE) USING INDEX; +</programlisting> + </para> + + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + There is no <command>REPACK</command> statement in the SQL standard. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="app-pgrepackdb"/></member> + <member><xref linkend="repack-progress-reporting"/></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 6d0fdd43cfb..ac5d083d468 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -25,7 +25,6 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> - FULL [ <replaceable class="parameter">boolean</replaceable> ] FREEZE [ <replaceable class="parameter">boolean</replaceable> ] VERBOSE [ <replaceable class="parameter">boolean</replaceable> ] ANALYZE [ <replaceable class="parameter">boolean</replaceable> ] @@ -39,6 +38,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ] ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ] BUFFER_USAGE_LIMIT <replaceable class="parameter">size</replaceable> + FULL [ <replaceable class="parameter">boolean</replaceable> ] <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase> @@ -95,20 +95,6 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re <title>Parameters</title> <variablelist> - <varlistentry> - <term><literal>FULL</literal></term> - <listitem> - <para> - Selects <quote>full</quote> vacuum, which can reclaim more - space, but takes much longer and exclusively locks the table. - This method also requires extra disk space, since it writes a - new copy of the table and doesn't release the old copy until - the operation is complete. Usually this should only be used when a - significant amount of space needs to be reclaimed from within the table. - </para> - </listitem> - </varlistentry> - <varlistentry> <term><literal>FREEZE</literal></term> <listitem> @@ -362,6 +348,23 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re </listitem> </varlistentry> + <varlistentry> + <term><literal>FULL</literal></term> + <listitem> + <para> + This option, which is deprecated, makes <command>VACUUM</command> + behave like <command>REPACK</command> without a + <literal>USING INDEX</literal> clause. + This method of compacting the table takes much longer than + <command>VACUUM</command> and exclusively locks the table. + This method also requires extra disk space, since it writes a + new copy of the table and doesn't release the old copy until + the operation is complete. Usually this should only be used when a + significant amount of space needs to be reclaimed from within the table. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><replaceable class="parameter">boolean</replaceable></term> <listitem> diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 2cf02c37b17..d9fdbb5d254 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -195,6 +195,7 @@ &refreshMaterializedView; &reindex; &releaseSavepoint; + &repack; &reset; &revoke; &rollback; diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index cbef73e5d4b..7d4b48e5a97 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -741,13 +741,13 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, if (OldIndex != NULL && !use_sort) { const int ci_index[] = { - PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_INDEX_RELID + PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_INDEX_RELID }; int64 ci_val[2]; /* Set phase and OIDOldIndex to columns */ - ci_val[0] = PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP; + ci_val[0] = PROGRESS_REPACK_PHASE_INDEX_SCAN_HEAP; ci_val[1] = RelationGetRelid(OldIndex); pgstat_progress_update_multi_param(2, ci_index, ci_val); @@ -759,15 +759,15 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, else { /* In scan-and-sort mode and also VACUUM FULL, set phase */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_SEQ_SCAN_HEAP); tableScan = table_beginscan(OldHeap, SnapshotAny, 0, (ScanKey) NULL); heapScan = (HeapScanDesc) tableScan; indexScan = NULL; /* Set total heap blocks */ - pgstat_progress_update_param(PROGRESS_CLUSTER_TOTAL_HEAP_BLKS, + pgstat_progress_update_param(PROGRESS_REPACK_TOTAL_HEAP_BLKS, heapScan->rs_nblocks); } @@ -809,7 +809,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, * is manually updated to the correct value when the table * scan finishes. */ - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_BLKS_SCANNED, heapScan->rs_nblocks); break; } @@ -825,7 +825,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, */ if (prev_cblock != heapScan->rs_cblock) { - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_BLKS_SCANNED, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_BLKS_SCANNED, (heapScan->rs_cblock + heapScan->rs_nblocks - heapScan->rs_startblock @@ -926,14 +926,14 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, * In scan-and-sort mode, report increase in number of tuples * scanned */ - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_TUPLES_SCANNED, *num_tuples); } else { const int ct_index[] = { - PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED, - PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN + PROGRESS_REPACK_HEAP_TUPLES_SCANNED, + PROGRESS_REPACK_HEAP_TUPLES_WRITTEN }; int64 ct_val[2]; @@ -966,14 +966,14 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, double n_tuples = 0; /* Report that we are now sorting tuples */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_SORT_TUPLES); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_SORT_TUPLES); tuplesort_performsort(tuplesort); /* Report that we are now writing new heap */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_WRITE_NEW_HEAP); for (;;) { @@ -991,7 +991,7 @@ heapam_relation_copy_for_cluster(Relation OldHeap, Relation NewHeap, values, isnull, rwstate); /* Report n_tuples */ - pgstat_progress_update_param(PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN, + pgstat_progress_update_param(PROGRESS_REPACK_HEAP_TUPLES_WRITTEN, n_tuples); } diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 43de42ce39e..5ee6389d39c 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -4077,7 +4077,7 @@ reindex_relation(const ReindexStmt *stmt, Oid relid, int flags, Assert(!ReindexIsProcessingIndex(indexOid)); /* Set index rebuild count */ - pgstat_progress_update_param(PROGRESS_CLUSTER_INDEX_REBUILD_COUNT, + pgstat_progress_update_param(PROGRESS_REPACK_INDEX_REBUILD_COUNT, i); i++; } diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 7553f31fef0..3f05ba3083a 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1283,14 +1283,15 @@ CREATE VIEW pg_stat_progress_vacuum AS FROM pg_stat_get_progress_info('VACUUM') AS S LEFT JOIN pg_database D ON S.datid = D.oid; -CREATE VIEW pg_stat_progress_cluster AS +CREATE VIEW pg_stat_progress_repack AS SELECT S.pid AS pid, S.datid AS datid, D.datname AS datname, S.relid AS relid, CASE S.param1 WHEN 1 THEN 'CLUSTER' - WHEN 2 THEN 'VACUUM FULL' + WHEN 2 THEN 'REPACK' + WHEN 3 THEN 'VACUUM FULL' END AS command, CASE S.param2 WHEN 0 THEN 'initializing' WHEN 1 THEN 'seq scanning heap' @@ -1301,15 +1302,35 @@ CREATE VIEW pg_stat_progress_cluster AS WHEN 6 THEN 'rebuilding index' WHEN 7 THEN 'performing final cleanup' END AS phase, - CAST(S.param3 AS oid) AS cluster_index_relid, + CAST(S.param3 AS oid) AS repack_index_relid, S.param4 AS heap_tuples_scanned, S.param5 AS heap_tuples_written, S.param6 AS heap_blks_total, S.param7 AS heap_blks_scanned, S.param8 AS index_rebuild_count - FROM pg_stat_get_progress_info('CLUSTER') AS S + FROM pg_stat_get_progress_info('REPACK') AS S LEFT JOIN pg_database D ON S.datid = D.oid; +-- This view is as the one above, except for renaming a column and avoiding +-- 'REPACK' as a command name to report. +CREATE VIEW pg_stat_progress_cluster AS + SELECT + pid, + datid, + datname, + relid, + CASE WHEN command IN ('CLUSTER', 'VACUUM FULL') THEN command + WHEN repack_index_relid = 0 THEN 'VACUUM FULL' + ELSE 'CLUSTER' END AS command, + phase, + repack_index_relid AS cluster_index_relid, + heap_tuples_scanned, + heap_tuples_written, + heap_blks_total, + heap_blks_scanned, + index_rebuild_count + FROM pg_stat_progress_repack; + CREATE VIEW pg_stat_progress_create_index AS SELECT S.pid AS pid, S.datid AS datid, D.datname AS datname, diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 60a4617a585..e19675a6d05 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -1,7 +1,8 @@ /*------------------------------------------------------------------------- * * cluster.c - * CLUSTER a table on an index. This is now also used for VACUUM FULL. + * CLUSTER a table on an index. This is now also used for VACUUM FULL and + * REPACK. * * There is hardly anything left of Paul Brown's original implementation... * @@ -67,27 +68,35 @@ typedef struct Oid indexOid; } RelToCluster; - -static void cluster_multiple_rels(List *rtcs, ClusterParams *params); +static bool cluster_rel_recheck(RepackCommand cmd, Relation OldHeap, + Oid indexOid, Oid userid, int options); static void rebuild_relation(Relation OldHeap, Relation index, bool verbose); static void copy_table_data(Relation NewHeap, Relation OldHeap, Relation OldIndex, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); -static List *get_tables_to_cluster(MemoryContext cluster_context); -static List *get_tables_to_cluster_partitioned(MemoryContext cluster_context, - Oid indexOid); -static bool cluster_is_permitted_for_relation(Oid relid, Oid userid); +static List *get_tables_to_repack(RepackCommand cmd, bool usingindex, + MemoryContext permcxt); +static List *get_tables_to_repack_partitioned(RepackCommand cmd, + Oid relid, bool rel_is_index, + MemoryContext permcxt); +static bool cluster_is_permitted_for_relation(RepackCommand cmd, + Oid relid, Oid userid); +static Relation process_single_relation(RepackStmt *stmt, + ClusterParams *params); +static Oid determine_clustered_index(Relation rel, bool usingindex, + const char *indexname); +static const char *RepackCommandAsString(RepackCommand cmd); -/*--------------------------------------------------------------------------- - * This cluster code allows for clustering multiple tables at once. Because +/* + * The repack code allows for processing multiple tables at once. Because * of this, we cannot just run everything on a single transaction, or we * would be forced to acquire exclusive locks on all the tables being * clustered, simultaneously --- very likely leading to deadlock. * - * To solve this we follow a similar strategy to VACUUM code, - * clustering each relation in a separate transaction. For this to work, - * we need to: + * To solve this we follow a similar strategy to VACUUM code, processing each + * relation in a separate transaction. For this to work, we need to: + * * - provide a separate memory context so that we can pass information in * a way that survives across transactions * - start a new transaction every time a new relation is clustered @@ -98,197 +107,166 @@ static bool cluster_is_permitted_for_relation(Oid relid, Oid userid); * * The single-relation case does not have any such overhead. * - * We also allow a relation to be specified without index. In that case, - * the indisclustered bit will be looked up, and an ERROR will be thrown - * if there is no index with the bit set. - *--------------------------------------------------------------------------- + * We also allow a relation to be repacked following an index, but without + * naming a specific one. In that case, the indisclustered bit will be + * looked up, and an ERROR will be thrown if no so-marked index is found. */ void -cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel) +ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel) { - ListCell *lc; ClusterParams params = {0}; - bool verbose = false; Relation rel = NULL; - Oid indexOid = InvalidOid; - MemoryContext cluster_context; + MemoryContext repack_context; List *rtcs; /* Parse option list */ - foreach(lc, stmt->params) + foreach_node(DefElem, opt, stmt->params) { - DefElem *opt = (DefElem *) lfirst(lc); - if (strcmp(opt->defname, "verbose") == 0) - verbose = defGetBoolean(opt); + params.options |= defGetBoolean(opt) ? CLUOPT_VERBOSE : 0; + else if (strcmp(opt->defname, "analyze") == 0 || + strcmp(opt->defname, "analyse") == 0) + params.options |= defGetBoolean(opt) ? CLUOPT_ANALYZE : 0; else ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("unrecognized %s option \"%s\"", - "CLUSTER", opt->defname), - parser_errposition(pstate, opt->location))); + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("unrecognized %s option \"%s\"", + RepackCommandAsString(stmt->command), + opt->defname), + parser_errposition(pstate, opt->location)); } - params.options = (verbose ? CLUOPT_VERBOSE : 0); - + /* + * If a single relation is specified, process it and we're done ... unless + * the relation is a partitioned table, in which case we fall through. + */ if (stmt->relation != NULL) { - /* This is the single-relation case. */ - Oid tableOid; - - /* - * Find, lock, and check permissions on the table. We obtain - * AccessExclusiveLock right away to avoid lock-upgrade hazard in the - * single-transaction case. - */ - tableOid = RangeVarGetRelidExtended(stmt->relation, - AccessExclusiveLock, - 0, - RangeVarCallbackMaintainsTable, - NULL); - rel = table_open(tableOid, NoLock); - - /* - * Reject clustering a remote temp table ... their local buffer - * manager is not going to cope. - */ - if (RELATION_IS_OTHER_TEMP(rel)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster temporary tables of other sessions"))); - - if (stmt->indexname == NULL) - { - ListCell *index; - - /* We need to find the index that has indisclustered set. */ - foreach(index, RelationGetIndexList(rel)) - { - indexOid = lfirst_oid(index); - if (get_index_isclustered(indexOid)) - break; - indexOid = InvalidOid; - } - - if (!OidIsValid(indexOid)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("there is no previously clustered index for table \"%s\"", - stmt->relation->relname))); - } - else - { - /* - * The index is expected to be in the same namespace as the - * relation. - */ - indexOid = get_relname_relid(stmt->indexname, - rel->rd_rel->relnamespace); - if (!OidIsValid(indexOid)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("index \"%s\" for table \"%s\" does not exist", - stmt->indexname, stmt->relation->relname))); - } - - /* For non-partitioned tables, do what we came here to do. */ - if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE) - { - cluster_rel(rel, indexOid, ¶ms); - /* cluster_rel closes the relation, but keeps lock */ - - return; - } + rel = process_single_relation(stmt, ¶ms); + if (rel == NULL) + return; /* all done */ } + /* + * Don't allow ANALYZE in the multiple-relation case for now. Maybe we + * can add support for this later. + */ + if (params.options & CLUOPT_ANALYZE) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot %s multiple tables", "REPACK (ANALYZE)")); + /* * By here, we know we are in a multi-table situation. In order to avoid * holding locks for too long, we want to process each table in its own * transaction. This forces us to disallow running inside a user * transaction block. */ - PreventInTransactionBlock(isTopLevel, "CLUSTER"); + PreventInTransactionBlock(isTopLevel, RepackCommandAsString(stmt->command)); /* Also, we need a memory context to hold our list of relations */ - cluster_context = AllocSetContextCreate(PortalContext, - "Cluster", - ALLOCSET_DEFAULT_SIZES); + repack_context = AllocSetContextCreate(PortalContext, + "Repack", + ALLOCSET_DEFAULT_SIZES); + + params.options |= CLUOPT_RECHECK; /* - * Either we're processing a partitioned table, or we were not given any - * table name at all. In either case, obtain a list of relations to - * process. - * - * In the former case, an index name must have been given, so we don't - * need to recheck its "indisclustered" bit, but we have to check that it - * is an index that we can cluster on. In the latter case, we set the - * option bit to have indisclustered verified. - * - * Rechecking the relation itself is necessary here in all cases. + * If we don't have a relation yet, determine a relation list. If we do, + * then it must be a partitioned table, and we want to process its + * partitions. */ - params.options |= CLUOPT_RECHECK; - if (rel != NULL) + if (rel == NULL) { - Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); - check_index_is_clusterable(rel, indexOid, AccessShareLock); - rtcs = get_tables_to_cluster_partitioned(cluster_context, indexOid); - - /* close relation, releasing lock on parent table */ - table_close(rel, AccessExclusiveLock); + Assert(stmt->indexname == NULL); + rtcs = get_tables_to_repack(stmt->command, stmt->usingindex, + repack_context); + params.options |= CLUOPT_RECHECK_ISCLUSTERED; } else { - rtcs = get_tables_to_cluster(cluster_context); - params.options |= CLUOPT_RECHECK_ISCLUSTERED; + Oid relid; + bool rel_is_index; + + Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + + /* + * If USING INDEX was specified, resolve the index name now and pass + * it down. + */ + if (stmt->usingindex) + { + /* + * If no index name was specified when repacking a partitioned + * table, punt for now. Maybe we can improve this later. + */ + if (!stmt->indexname) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("there is no previously clustered index for table \"%s\"", + RelationGetRelationName(rel))); + + relid = determine_clustered_index(rel, stmt->usingindex, + stmt->indexname); + if (!OidIsValid(relid)) + elog(ERROR, "unable to determine index to cluster on"); + /* XXX is this the right place for this check? */ + check_index_is_clusterable(rel, relid, AccessExclusiveLock); + rel_is_index = true; + } + else + { + relid = RelationGetRelid(rel); + rel_is_index = false; + } + + rtcs = get_tables_to_repack_partitioned(stmt->command, + relid, rel_is_index, + repack_context); + + /* close parent relation, releasing lock on it */ + table_close(rel, AccessExclusiveLock); + rel = NULL; } - /* Do the job. */ - cluster_multiple_rels(rtcs, ¶ms); - - /* Start a new transaction for the cleanup work. */ - StartTransactionCommand(); - - /* Clean up working storage */ - MemoryContextDelete(cluster_context); -} - -/* - * Given a list of relations to cluster, process each of them in a separate - * transaction. - * - * We expect to be in a transaction at start, but there isn't one when we - * return. - */ -static void -cluster_multiple_rels(List *rtcs, ClusterParams *params) -{ - ListCell *lc; - /* Commit to get out of starting transaction */ PopActiveSnapshot(); CommitTransactionCommand(); /* Cluster the tables, each in a separate transaction */ - foreach(lc, rtcs) + Assert(rel == NULL); + foreach_ptr(RelToCluster, rtc, rtcs) { - RelToCluster *rtc = (RelToCluster *) lfirst(lc); - Relation rel; - /* Start a new transaction for each relation. */ StartTransactionCommand(); + /* + * Open the target table, coping with the case where it has been + * dropped. + */ + rel = try_table_open(rtc->tableOid, AccessExclusiveLock); + if (rel == NULL) + { + CommitTransactionCommand(); + continue; + } + /* functions in indexes may want a snapshot set */ PushActiveSnapshot(GetTransactionSnapshot()); - rel = table_open(rtc->tableOid, AccessExclusiveLock); - /* Process this table */ - cluster_rel(rel, rtc->indexOid, params); + cluster_rel(stmt->command, rel, rtc->indexOid, ¶ms); /* cluster_rel closes the relation, but keeps lock */ PopActiveSnapshot(); CommitTransactionCommand(); } + + /* Start a new transaction for the cleanup work. */ + StartTransactionCommand(); + + /* Clean up working storage */ + MemoryContextDelete(repack_context); } /* @@ -304,11 +282,14 @@ cluster_multiple_rels(List *rtcs, ClusterParams *params) * them incrementally while we load the table. * * If indexOid is InvalidOid, the table will be rewritten in physical order - * instead of index order. This is the new implementation of VACUUM FULL, - * and error messages should refer to the operation as VACUUM not CLUSTER. + * instead of index order. + * + * 'cmd' indicates which command is being executed, to be used for error + * messages. */ void -cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params) +cluster_rel(RepackCommand cmd, Relation OldHeap, Oid indexOid, + ClusterParams *params) { Oid tableOid = RelationGetRelid(OldHeap); Oid save_userid; @@ -323,13 +304,8 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params) /* Check for user-requested abort. */ CHECK_FOR_INTERRUPTS(); - pgstat_progress_start_command(PROGRESS_COMMAND_CLUSTER, tableOid); - if (OidIsValid(indexOid)) - pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND, - PROGRESS_CLUSTER_COMMAND_CLUSTER); - else - pgstat_progress_update_param(PROGRESS_CLUSTER_COMMAND, - PROGRESS_CLUSTER_COMMAND_VACUUM_FULL); + pgstat_progress_start_command(PROGRESS_COMMAND_REPACK, tableOid); + pgstat_progress_update_param(PROGRESS_REPACK_COMMAND, cmd); /* * Switch to the table owner's userid, so that any index functions are run @@ -350,86 +326,38 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params) * *must* skip the one on indisclustered since it would reject an attempt * to cluster a not-previously-clustered index. */ - if (recheck) - { - /* Check that the user still has privileges for the relation */ - if (!cluster_is_permitted_for_relation(tableOid, save_userid)) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - - /* - * Silently skip a temp table for a remote session. Only doing this - * check in the "recheck" case is appropriate (which currently means - * somebody is executing a database-wide CLUSTER or on a partitioned - * table), because there is another check in cluster() which will stop - * any attempt to cluster remote temp tables by name. There is - * another check in cluster_rel which is redundant, but we leave it - * for extra safety. - */ - if (RELATION_IS_OTHER_TEMP(OldHeap)) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - - if (OidIsValid(indexOid)) - { - /* - * Check that the index still exists - */ - if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(indexOid))) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - - /* - * Check that the index is still the one with indisclustered set, - * if needed. - */ - if ((params->options & CLUOPT_RECHECK_ISCLUSTERED) != 0 && - !get_index_isclustered(indexOid)) - { - relation_close(OldHeap, AccessExclusiveLock); - goto out; - } - } - } + if (recheck && + !cluster_rel_recheck(cmd, OldHeap, indexOid, save_userid, + params->options)) + goto out; /* - * We allow VACUUM FULL, but not CLUSTER, on shared catalogs. CLUSTER - * would work in most respects, but the index would only get marked as - * indisclustered in the current database, leading to unexpected behavior - * if CLUSTER were later invoked in another database. + * We allow repacking shared catalogs only when not using an index. It + * would work to use an index in most respects, but the index would only + * get marked as indisclustered in the current database, leading to + * unexpected behavior if CLUSTER were later invoked in another database. */ if (OidIsValid(indexOid) && OldHeap->rd_rel->relisshared) ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster a shared catalog"))); + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot run %s on a shared catalog", + RepackCommandAsString(cmd))); /* * Don't process temp tables of other backends ... their local buffer * manager is not going to cope. */ if (RELATION_IS_OTHER_TEMP(OldHeap)) - { - if (OidIsValid(indexOid)) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot cluster temporary tables of other sessions"))); - else - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot vacuum temporary tables of other sessions"))); - } + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot run %s on temporary tables of other sessions", + RepackCommandAsString(cmd))); /* * Also check for active uses of the relation in the current transaction, * including open scans and pending AFTER trigger events. */ - CheckTableNotInUse(OldHeap, OidIsValid(indexOid) ? "CLUSTER" : "VACUUM"); + CheckTableNotInUse(OldHeap, RepackCommandAsString(cmd)); /* Check heap and index are valid to cluster on */ if (OidIsValid(indexOid)) @@ -442,6 +370,24 @@ cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params) else index = NULL; + /* + * When allow_system_table_mods is turned off, we disallow repacking a + * catalog on a particular index unless that's already the clustered index + * for that catalog. + * + * XXX We don't check for this in CLUSTER, because it's historically been + * allowed. + */ + if (cmd != REPACK_COMMAND_CLUSTER && + !allowSystemTableMods && OidIsValid(indexOid) && + IsCatalogRelation(OldHeap) && !index->rd_index->indisclustered) + ereport(ERROR, + errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied: \"%s\" is a system catalog", + RelationGetRelationName(OldHeap)), + errdetail("System catalogs can only be clustered by the index they're already clustered on, if any, unless \"%s\" is enabled.", + "allow_system_table_mods")); + /* * Quietly ignore the request if this is a materialized view which has not * been populated from its query. No harm is done because there is no data @@ -482,6 +428,63 @@ out: pgstat_progress_end_command(); } +/* + * Check if the table (and its index) still meets the requirements of + * cluster_rel(). + */ +static bool +cluster_rel_recheck(RepackCommand cmd, Relation OldHeap, Oid indexOid, + Oid userid, int options) +{ + Oid tableOid = RelationGetRelid(OldHeap); + + /* Check that the user still has privileges for the relation */ + if (!cluster_is_permitted_for_relation(cmd, tableOid, userid)) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + + /* + * Silently skip a temp table for a remote session. Only doing this check + * in the "recheck" case is appropriate (which currently means somebody is + * executing a database-wide CLUSTER or on a partitioned table), because + * there is another check in cluster() which will stop any attempt to + * cluster remote temp tables by name. There is another check in + * cluster_rel which is redundant, but we leave it for extra safety. + */ + if (RELATION_IS_OTHER_TEMP(OldHeap)) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + + if (OidIsValid(indexOid)) + { + /* + * Check that the index still exists + */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(indexOid))) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + + /* + * Check that the index is still the one with indisclustered set, if + * needed. + */ + if ((options & CLUOPT_RECHECK_ISCLUSTERED) != 0 && + !get_index_isclustered(indexOid)) + { + relation_close(OldHeap, AccessExclusiveLock); + return false; + } + } + + return true; +} + /* * Verify that the specified heap and index are valid to cluster on * @@ -642,8 +645,8 @@ rebuild_relation(Relation OldHeap, Relation index, bool verbose) Assert(CheckRelationLockedByMe(OldHeap, AccessExclusiveLock, false) && (index == NULL || CheckRelationLockedByMe(index, AccessExclusiveLock, false))); - if (index) - /* Mark the correct index as clustered */ + /* for CLUSTER or REPACK USING INDEX, mark the index as the one to use */ + if (index != NULL) mark_index_clustered(OldHeap, RelationGetRelid(index), true); /* Remember info about rel before closing OldHeap */ @@ -958,20 +961,20 @@ copy_table_data(Relation NewHeap, Relation OldHeap, Relation OldIndex, bool verb /* Log what we're doing */ if (OldIndex != NULL && !use_sort) ereport(elevel, - (errmsg("clustering \"%s.%s\" using index scan on \"%s\"", - nspname, - RelationGetRelationName(OldHeap), - RelationGetRelationName(OldIndex)))); + errmsg("repacking \"%s.%s\" using index scan on \"%s\"", + nspname, + RelationGetRelationName(OldHeap), + RelationGetRelationName(OldIndex))); else if (use_sort) ereport(elevel, - (errmsg("clustering \"%s.%s\" using sequential scan and sort", - nspname, - RelationGetRelationName(OldHeap)))); + errmsg("repacking \"%s.%s\" using sequential scan and sort", + nspname, + RelationGetRelationName(OldHeap))); else ereport(elevel, - (errmsg("vacuuming \"%s.%s\"", - nspname, - RelationGetRelationName(OldHeap)))); + errmsg("repacking \"%s.%s\" in physical order", + nspname, + RelationGetRelationName(OldHeap))); /* * Hand off the actual copying to AM specific function, the generic code @@ -1458,8 +1461,8 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, int i; /* Report that we are now swapping relation files */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_SWAP_REL_FILES); /* Zero out possible results from swapped_relation_files */ memset(mapped_tables, 0, sizeof(mapped_tables)); @@ -1509,14 +1512,14 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, reindex_flags |= REINDEX_REL_FORCE_INDEXES_PERMANENT; /* Report that we are now reindexing relations */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_REBUILD_INDEX); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_REBUILD_INDEX); reindex_relation(NULL, OIDOldHeap, reindex_flags, &reindex_params); /* Report that we are now doing clean up */ - pgstat_progress_update_param(PROGRESS_CLUSTER_PHASE, - PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP); + pgstat_progress_update_param(PROGRESS_REPACK_PHASE, + PROGRESS_REPACK_PHASE_FINAL_CLEANUP); /* * If the relation being rebuilt is pg_class, swap_relation_files() @@ -1632,106 +1635,191 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap, } } - /* - * Get a list of tables that the current user has privileges on and - * have indisclustered set. Return the list in a List * of RelToCluster - * (stored in the specified memory context), each one giving the tableOid - * and the indexOid on which the table is already clustered. + * Determine which relations to process, when REPACK/CLUSTER is called + * without specifying a table name. The exact process depends on whether + * USING INDEX was given or not, and in any case we only return tables and + * materialized views that the current user has privileges to repack/cluster. + * + * If USING INDEX was given, we scan pg_index to find those that have + * indisclustered set; if it was not given, scan pg_class and return all + * tables. + * + * Return it as a list of RelToCluster in the given memory context. */ static List * -get_tables_to_cluster(MemoryContext cluster_context) +get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt) { - Relation indRelation; + Relation catalog; TableScanDesc scan; - ScanKeyData entry; - HeapTuple indexTuple; - Form_pg_index index; - MemoryContext old_context; + HeapTuple tuple; List *rtcs = NIL; - /* - * Get all indexes that have indisclustered set and that the current user - * has the appropriate privileges for. - */ - indRelation = table_open(IndexRelationId, AccessShareLock); - ScanKeyInit(&entry, - Anum_pg_index_indisclustered, - BTEqualStrategyNumber, F_BOOLEQ, - BoolGetDatum(true)); - scan = table_beginscan_catalog(indRelation, 1, &entry); - while ((indexTuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + if (usingindex) { - RelToCluster *rtc; + ScanKeyData entry; - index = (Form_pg_index) GETSTRUCT(indexTuple); + catalog = table_open(IndexRelationId, AccessShareLock); + ScanKeyInit(&entry, + Anum_pg_index_indisclustered, + BTEqualStrategyNumber, F_BOOLEQ, + BoolGetDatum(true)); + scan = table_beginscan_catalog(catalog, 1, &entry); + while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + RelToCluster *rtc; + Form_pg_index index; + MemoryContext oldcxt; - if (!cluster_is_permitted_for_relation(index->indrelid, GetUserId())) - continue; + index = (Form_pg_index) GETSTRUCT(tuple); - /* Use a permanent memory context for the result list */ - old_context = MemoryContextSwitchTo(cluster_context); + /* + * Try to obtain a light lock on the index's table, to ensure it + * doesn't go away while we collect the list. If we cannot, just + * disregard it. + */ + if (!ConditionalLockRelationOid(index->indrelid, AccessShareLock)) + continue; - rtc = palloc_object(RelToCluster); - rtc->tableOid = index->indrelid; - rtc->indexOid = index->indexrelid; - rtcs = lappend(rtcs, rtc); + /* Verify that the table still exists */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(index->indrelid))) + { + /* Release useless lock */ + UnlockRelationOid(index->indrelid, AccessShareLock); + continue; + } - MemoryContextSwitchTo(old_context); + if (!cluster_is_permitted_for_relation(cmd, index->indrelid, + GetUserId())) + continue; + + /* Use a permanent memory context for the result list */ + oldcxt = MemoryContextSwitchTo(permcxt); + rtc = palloc_object(RelToCluster); + rtc->tableOid = index->indrelid; + rtc->indexOid = index->indexrelid; + rtcs = lappend(rtcs, rtc); + MemoryContextSwitchTo(oldcxt); + } } - table_endscan(scan); + else + { + catalog = table_open(RelationRelationId, AccessShareLock); + scan = table_beginscan_catalog(catalog, 0, NULL); - relation_close(indRelation, AccessShareLock); + while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + RelToCluster *rtc; + Form_pg_class class; + MemoryContext oldcxt; + + class = (Form_pg_class) GETSTRUCT(tuple); + + /* + * Try to obtain a light lock on the table, to ensure it doesn't + * go away while we collect the list. If we cannot, just + * disregard the table. + */ + if (!ConditionalLockRelationOid(class->oid, AccessShareLock)) + continue; + + /* Verify that the table still exists */ + if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(class->oid))) + { + /* Release useless lock */ + UnlockRelationOid(class->oid, AccessShareLock); + continue; + } + + /* Can only process plain tables and matviews */ + if (class->relkind != RELKIND_RELATION && + class->relkind != RELKIND_MATVIEW) + continue; + + /* noisily skip rels which the user can't process */ + if (!cluster_is_permitted_for_relation(cmd, class->oid, + GetUserId())) + continue; + + /* Use a permanent memory context for the result list */ + oldcxt = MemoryContextSwitchTo(permcxt); + rtc = palloc_object(RelToCluster); + rtc->tableOid = class->oid; + rtc->indexOid = InvalidOid; + rtcs = lappend(rtcs, rtc); + MemoryContextSwitchTo(oldcxt); + } + } + + table_endscan(scan); + relation_close(catalog, AccessShareLock); return rtcs; } /* - * Given an index on a partitioned table, return a list of RelToCluster for + * Given a partitioned table or its index, return a list of RelToCluster for * all the children leaves tables/indexes. * * Like expand_vacuum_rel, but here caller must hold AccessExclusiveLock * on the table containing the index. + * + * 'rel_is_index' tells whether 'relid' is that of an index (true) or of the + * owning relation. */ static List * -get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid) +get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid, + bool rel_is_index, MemoryContext permcxt) { List *inhoids; - ListCell *lc; List *rtcs = NIL; - MemoryContext old_context; - /* Do not lock the children until they're processed */ - inhoids = find_all_inheritors(indexOid, NoLock, NULL); - - foreach(lc, inhoids) + /* + * Do not lock the children until they're processed. Note that we do hold + * a lock on the parent partitioned table. + */ + inhoids = find_all_inheritors(relid, NoLock, NULL); + foreach_oid(child_oid, inhoids) { - Oid indexrelid = lfirst_oid(lc); - Oid relid = IndexGetRelation(indexrelid, false); + Oid table_oid, + index_oid; RelToCluster *rtc; + MemoryContext oldcxt; - /* consider only leaf indexes */ - if (get_rel_relkind(indexrelid) != RELKIND_INDEX) - continue; + if (rel_is_index) + { + /* consider only leaf indexes */ + if (get_rel_relkind(child_oid) != RELKIND_INDEX) + continue; + + table_oid = IndexGetRelation(child_oid, false); + index_oid = child_oid; + } + else + { + /* consider only leaf relations */ + if (get_rel_relkind(child_oid) != RELKIND_RELATION) + continue; + + table_oid = child_oid; + index_oid = InvalidOid; + } /* * It's possible that the user does not have privileges to CLUSTER the - * leaf partition despite having such privileges on the partitioned - * table. We skip any partitions which the user is not permitted to - * CLUSTER. + * leaf partition despite having them on the partitioned table. Skip + * if so. */ - if (!cluster_is_permitted_for_relation(relid, GetUserId())) + if (!cluster_is_permitted_for_relation(cmd, table_oid, GetUserId())) continue; /* Use a permanent memory context for the result list */ - old_context = MemoryContextSwitchTo(cluster_context); - + oldcxt = MemoryContextSwitchTo(permcxt); rtc = palloc_object(RelToCluster); - rtc->tableOid = relid; - rtc->indexOid = indexrelid; + rtc->tableOid = table_oid; + rtc->indexOid = index_oid; rtcs = lappend(rtcs, rtc); - - MemoryContextSwitchTo(old_context); + MemoryContextSwitchTo(oldcxt); } return rtcs; @@ -1742,13 +1830,167 @@ get_tables_to_cluster_partitioned(MemoryContext cluster_context, Oid indexOid) * function emits a WARNING. */ static bool -cluster_is_permitted_for_relation(Oid relid, Oid userid) +cluster_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid) { + Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK); + if (pg_class_aclcheck(relid, userid, ACL_MAINTAIN) == ACLCHECK_OK) return true; ereport(WARNING, - (errmsg("permission denied to cluster \"%s\", skipping it", - get_rel_name(relid)))); + errmsg("permission denied to execute %s on \"%s\", skipping it", + RepackCommandAsString(cmd), + get_rel_name(relid))); + return false; } + + +/* + * Given a RepackStmt with an indicated relation name, resolve the relation + * name, obtain lock on it, then determine what to do based on the relation + * type: if it's table and not partitioned, repack it as indicated (using an + * existing clustered index, or following the given one), and return NULL. + * + * On the other hand, if the table is partitioned, do nothing further and + * instead return the opened and locked relcache entry, so that caller can + * process the partitions using the multiple-table handling code. In this + * case, if an index name is given, it's up to the caller to resolve it. + */ +static Relation +process_single_relation(RepackStmt *stmt, ClusterParams *params) +{ + Relation rel; + Oid tableOid; + + Assert(stmt->relation != NULL); + Assert(stmt->command == REPACK_COMMAND_CLUSTER || + stmt->command == REPACK_COMMAND_REPACK); + + /* + * Find, lock, and check permissions on the table. We obtain + * AccessExclusiveLock right away to avoid lock-upgrade hazard in the + * single-transaction case. + */ + tableOid = RangeVarGetRelidExtended(stmt->relation->relation, + AccessExclusiveLock, + 0, + RangeVarCallbackMaintainsTable, + NULL); + rel = table_open(tableOid, NoLock); + + /* + * Reject clustering a remote temp table ... their local buffer manager is + * not going to cope. + */ + if (RELATION_IS_OTHER_TEMP(rel)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot execute %s on temporary tables of other sessions", + RepackCommandAsString(stmt->command))); + + /* + * Make sure ANALYZE is specified if a column list is present. + */ + if ((params->options & CLUOPT_ANALYZE) == 0 && stmt->relation->va_cols != NIL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ANALYZE option must be specified when a column list is provided")); + + /* + * For partitioned tables, let caller handle this. Otherwise, process it + * here and we're done. + */ + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + return rel; + else + { + Oid indexOid; + + indexOid = determine_clustered_index(rel, stmt->usingindex, + stmt->indexname); + if (OidIsValid(indexOid)) + check_index_is_clusterable(rel, indexOid, AccessExclusiveLock); + cluster_rel(stmt->command, rel, indexOid, params); + + /* Do an analyze, if requested */ + if (params->options & CLUOPT_ANALYZE) + { + VacuumParams vac_params = {0}; + + vac_params.options |= VACOPT_ANALYZE; + if (params->options & CLUOPT_VERBOSE) + vac_params.options |= VACOPT_VERBOSE; + analyze_rel(tableOid, NULL, vac_params, + stmt->relation->va_cols, true, NULL); + } + + return NULL; + } +} + +/* + * Given a relation and the usingindex/indexname options in a + * REPACK USING INDEX or CLUSTER command, return the OID of the + * index to use for clustering the table. + * + * Caller must hold lock on the relation so that the set of indexes + * doesn't change, and must call check_index_is_clusterable. + */ +static Oid +determine_clustered_index(Relation rel, bool usingindex, const char *indexname) +{ + Oid indexOid; + + if (indexname == NULL && usingindex) + { + /* + * If USING INDEX with no name is given, find a clustered index, or + * error out if none. + */ + indexOid = InvalidOid; + foreach_oid(idxoid, RelationGetIndexList(rel)) + { + if (get_index_isclustered(idxoid)) + { + indexOid = idxoid; + break; + } + } + + if (!OidIsValid(indexOid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("there is no previously clustered index for table \"%s\"", + RelationGetRelationName(rel))); + } + else if (indexname != NULL) + { + /* An index was specified; obtain its OID. */ + indexOid = get_relname_relid(indexname, rel->rd_rel->relnamespace); + if (!OidIsValid(indexOid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("index \"%s\" for table \"%s\" does not exist", + indexname, RelationGetRelationName(rel))); + } + else + indexOid = InvalidOid; + + return indexOid; +} + +static const char * +RepackCommandAsString(RepackCommand cmd) +{ + switch (cmd) + { + case REPACK_COMMAND_REPACK: + return "REPACK"; + case REPACK_COMMAND_VACUUMFULL: + return "VACUUM"; + case REPACK_COMMAND_CLUSTER: + return "CLUSTER"; + } + return "???"; /* keep compiler quiet */ +} diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 03932f45c8a..aea998260e1 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -351,7 +351,6 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) } } - /* * Sanity check DISABLE_PAGE_SKIPPING option. */ @@ -2289,8 +2288,9 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, if ((params.options & VACOPT_VERBOSE) != 0) cluster_params.options |= CLUOPT_VERBOSE; - /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ - cluster_rel(rel, InvalidOid, &cluster_params); + /* VACUUM FULL is a variant of REPACK; see cluster.c */ + cluster_rel(REPACK_COMMAND_VACUUMFULL, rel, InvalidOid, + &cluster_params); /* cluster_rel closes the relation, but keeps lock */ rel = NULL; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 713ee5c10a2..54d37c10447 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -287,7 +287,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); AlterCompositeTypeStmt AlterUserMappingStmt AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt AlterStatsStmt AlterDefaultPrivilegesStmt DefACLAction - AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt + AnalyzeStmt CallStmt ClosePortalStmt CommentStmt ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt @@ -304,7 +304,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt - RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRoleStmt + RemoveFuncStmt RemoveOperStmt RenameStmt RepackStmt ReturnStmt RevokeStmt RevokeRoleStmt RuleActionStmt RuleActionStmtOrEmpty RuleStmt SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy TruncateStmt UnlistenStmt UpdateStmt VacuumStmt @@ -323,7 +323,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <str> opt_single_name %type <list> opt_qualified_name -%type <boolean> opt_concurrently +%type <boolean> opt_concurrently opt_usingindex %type <dbehavior> opt_drop_behavior %type <list> opt_utility_option_list %type <list> opt_wait_with_clause @@ -773,7 +773,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); QUOTE QUOTES RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING - REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA + REFRESH REINDEX RELATIVE_P RELEASE RENAME REPACK REPEATABLE REPLACE REPLICA RESET RESPECT_P RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE @@ -1035,7 +1035,6 @@ stmt: | CallStmt | CheckPointStmt | ClosePortalStmt - | ClusterStmt | CommentStmt | ConstraintsSetStmt | CopyStmt @@ -1109,6 +1108,7 @@ stmt: | RemoveFuncStmt | RemoveOperStmt | RenameStmt + | RepackStmt | RevokeStmt | RevokeRoleStmt | RuleStmt @@ -1146,6 +1146,11 @@ opt_concurrently: | /*EMPTY*/ { $$ = false; } ; +opt_usingindex: + USING INDEX { $$ = true; } + | /* EMPTY */ { $$ = false; } + ; + opt_drop_behavior: CASCADE { $$ = DROP_CASCADE; } | RESTRICT { $$ = DROP_RESTRICT; } @@ -12036,38 +12041,82 @@ CreateConversionStmt: /***************************************************************************** * * QUERY: + * REPACK [ (options) ] [ <qualified_name> [ <name_list> ] [ USING INDEX <index_name> ] ] + * + * obsolete variants: * CLUSTER (options) [ <qualified_name> [ USING <index_name> ] ] * CLUSTER [VERBOSE] [ <qualified_name> [ USING <index_name> ] ] * CLUSTER [VERBOSE] <index_name> ON <qualified_name> (for pre-8.3) * *****************************************************************************/ -ClusterStmt: - CLUSTER '(' utility_option_list ')' qualified_name cluster_index_specification +RepackStmt: + REPACK opt_utility_option_list vacuum_relation USING INDEX name { - ClusterStmt *n = makeNode(ClusterStmt); + RepackStmt *n = makeNode(RepackStmt); - n->relation = $5; + n->command = REPACK_COMMAND_REPACK; + n->relation = (VacuumRelation *) $3; n->indexname = $6; + n->usingindex = true; + n->params = $2; + $$ = (Node *) n; + } + | REPACK opt_utility_option_list vacuum_relation opt_usingindex + { + RepackStmt *n = makeNode(RepackStmt); + + n->command = REPACK_COMMAND_REPACK; + n->relation = (VacuumRelation *) $3; + n->indexname = NULL; + n->usingindex = $4; + n->params = $2; + $$ = (Node *) n; + } + | REPACK opt_utility_option_list opt_usingindex + { + RepackStmt *n = makeNode(RepackStmt); + + n->command = REPACK_COMMAND_REPACK; + n->relation = NULL; + n->indexname = NULL; + n->usingindex = $3; + n->params = $2; + $$ = (Node *) n; + } + | CLUSTER '(' utility_option_list ')' qualified_name cluster_index_specification + { + RepackStmt *n = makeNode(RepackStmt); + + n->command = REPACK_COMMAND_CLUSTER; + n->relation = makeNode(VacuumRelation); + n->relation->relation = $5; + n->indexname = $6; + n->usingindex = true; n->params = $3; $$ = (Node *) n; } | CLUSTER opt_utility_option_list { - ClusterStmt *n = makeNode(ClusterStmt); + RepackStmt *n = makeNode(RepackStmt); + n->command = REPACK_COMMAND_CLUSTER; n->relation = NULL; n->indexname = NULL; + n->usingindex = true; n->params = $2; $$ = (Node *) n; } /* unparenthesized VERBOSE kept for pre-14 compatibility */ | CLUSTER opt_verbose qualified_name cluster_index_specification { - ClusterStmt *n = makeNode(ClusterStmt); + RepackStmt *n = makeNode(RepackStmt); - n->relation = $3; + n->command = REPACK_COMMAND_CLUSTER; + n->relation = makeNode(VacuumRelation); + n->relation->relation = $3; n->indexname = $4; + n->usingindex = true; if ($2) n->params = list_make1(makeDefElem("verbose", NULL, @2)); $$ = (Node *) n; @@ -12075,20 +12124,25 @@ ClusterStmt: /* unparenthesized VERBOSE kept for pre-17 compatibility */ | CLUSTER VERBOSE { - ClusterStmt *n = makeNode(ClusterStmt); + RepackStmt *n = makeNode(RepackStmt); + n->command = REPACK_COMMAND_CLUSTER; n->relation = NULL; n->indexname = NULL; + n->usingindex = true; n->params = list_make1(makeDefElem("verbose", NULL, @2)); $$ = (Node *) n; } /* kept for pre-8.3 compatibility */ | CLUSTER opt_verbose name ON qualified_name { - ClusterStmt *n = makeNode(ClusterStmt); + RepackStmt *n = makeNode(RepackStmt); - n->relation = $5; + n->command = REPACK_COMMAND_CLUSTER; + n->relation = makeNode(VacuumRelation); + n->relation->relation = $5; n->indexname = $3; + n->usingindex = true; if ($2) n->params = list_make1(makeDefElem("verbose", NULL, @2)); $$ = (Node *) n; @@ -18127,6 +18181,7 @@ unreserved_keyword: | RELATIVE_P | RELEASE | RENAME + | REPACK | REPEATABLE | REPLACE | REPLICA @@ -18764,6 +18819,7 @@ bare_label_keyword: | RELATIVE_P | RELEASE | RENAME + | REPACK | REPEATABLE | REPLACE | REPLICA diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 34dd6e18df5..ca737b05115 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -279,9 +279,9 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) return COMMAND_OK_IN_RECOVERY | COMMAND_OK_IN_READ_ONLY_TXN; } - case T_ClusterStmt: case T_ReindexStmt: case T_VacuumStmt: + case T_RepackStmt: { /* * These commands write WAL, so they're not strictly @@ -856,14 +856,14 @@ standard_ProcessUtility(PlannedStmt *pstmt, ExecuteCallStmt(castNode(CallStmt, parsetree), params, isAtomicContext, dest); break; - case T_ClusterStmt: - cluster(pstate, (ClusterStmt *) parsetree, isTopLevel); - break; - case T_VacuumStmt: ExecVacuum(pstate, (VacuumStmt *) parsetree, isTopLevel); break; + case T_RepackStmt: + ExecRepack(pstate, (RepackStmt *) parsetree, isTopLevel); + break; + case T_ExplainStmt: ExplainQuery(pstate, (ExplainStmt *) parsetree, params, dest); break; @@ -2865,10 +2865,6 @@ CreateCommandTag(Node *parsetree) tag = CMDTAG_CALL; break; - case T_ClusterStmt: - tag = CMDTAG_CLUSTER; - break; - case T_VacuumStmt: if (((VacuumStmt *) parsetree)->is_vacuumcmd) tag = CMDTAG_VACUUM; @@ -2876,6 +2872,13 @@ CreateCommandTag(Node *parsetree) tag = CMDTAG_ANALYZE; break; + case T_RepackStmt: + if (((RepackStmt *) parsetree)->command == REPACK_COMMAND_CLUSTER) + tag = CMDTAG_CLUSTER; + else + tag = CMDTAG_REPACK; + break; + case T_ExplainStmt: tag = CMDTAG_EXPLAIN; break; @@ -3517,7 +3520,7 @@ GetCommandLogLevel(Node *parsetree) lev = LOGSTMT_ALL; break; - case T_ClusterStmt: + case T_RepackStmt: lev = LOGSTMT_DDL; break; diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 73ca0bb0b7f..55a69bf681d 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -287,8 +287,8 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS) cmdtype = PROGRESS_COMMAND_VACUUM; else if (pg_strcasecmp(cmd, "ANALYZE") == 0) cmdtype = PROGRESS_COMMAND_ANALYZE; - else if (pg_strcasecmp(cmd, "CLUSTER") == 0) - cmdtype = PROGRESS_COMMAND_CLUSTER; + else if (pg_strcasecmp(cmd, "REPACK") == 0) + cmdtype = PROGRESS_COMMAND_REPACK; else if (pg_strcasecmp(cmd, "CREATE INDEX") == 0) cmdtype = PROGRESS_COMMAND_CREATE_INDEX; else if (pg_strcasecmp(cmd, "BASEBACKUP") == 0) diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 8b91bc00062..2a1bb47ff03 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -1267,7 +1267,7 @@ static const char *const sql_commands[] = { "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LISTEN", "LOAD", "LOCK", "MERGE INTO", "MOVE", "NOTIFY", "PREPARE", - "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", + "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", "REPACK", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", "TABLE", "TRUNCATE", "UNLISTEN", "UPDATE", "VACUUM", "VALUES", @@ -5086,6 +5086,46 @@ match_previous_words(int pattern_id, COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); } +/* REPACK */ + else if (Matches("REPACK")) + COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_clusterables, + "(", "USING INDEX"); + else if (Matches("REPACK", "(*)")) + COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_clusterables, + "USING INDEX"); + else if (Matches("REPACK", MatchAnyExcept("("))) + COMPLETE_WITH("USING INDEX"); + else if (Matches("REPACK", "(*)", MatchAnyExcept("("))) + COMPLETE_WITH("USING INDEX"); + else if (Matches("REPACK", MatchAny, "USING", "INDEX") || + Matches("REPACK", "(*)", MatchAny, "USING", "INDEX")) + { + set_completion_reference(prev3_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_index_of_table); + } + /* + * Complete ... [ (*) ] <sth> USING INDEX, with a list of indexes for + * <sth>. + */ + else if (TailMatches(MatchAny, "USING", "INDEX")) + { + set_completion_reference(prev3_wd); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_index_of_table); + } + else if (HeadMatches("REPACK", "(*") && + !HeadMatches("REPACK", "(*)")) + { + /* + * This fires if we're in an unfinished parenthesized option list. + * get_previous_words treats a completed parenthesized option list as + * one word, so the above test is correct. + */ + if (ends_with(prev_wd, '(') || ends_with(prev_wd, ',')) + COMPLETE_WITH("ANALYZE", "VERBOSE"); + else if (TailMatches("ANALYZE", "VERBOSE")) + COMPLETE_WITH("ON", "OFF"); + } + /* SECURITY LABEL */ else if (Matches("SECURITY")) COMPLETE_WITH("LABEL"); diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index 8ea81622f9d..28741988478 100644 --- a/src/include/commands/cluster.h +++ b/src/include/commands/cluster.h @@ -24,6 +24,7 @@ #define CLUOPT_RECHECK 0x02 /* recheck relation state */ #define CLUOPT_RECHECK_ISCLUSTERED 0x04 /* recheck relation state for * indisclustered */ +#define CLUOPT_ANALYZE 0x08 /* do an ANALYZE */ /* options for CLUSTER */ typedef struct ClusterParams @@ -31,8 +32,11 @@ typedef struct ClusterParams bits32 options; /* bitmask of CLUOPT_* */ } ClusterParams; -extern void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel); -extern void cluster_rel(Relation OldHeap, Oid indexOid, ClusterParams *params); + +extern void ExecRepack(ParseState *pstate, RepackStmt *stmt, bool isTopLevel); + +extern void cluster_rel(RepackCommand command, Relation OldHeap, Oid indexOid, + ClusterParams *params); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal); diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h index 359221dc296..f00e39b937d 100644 --- a/src/include/commands/progress.h +++ b/src/include/commands/progress.h @@ -73,28 +73,34 @@ #define PROGRESS_ANALYZE_STARTED_BY_MANUAL 1 #define PROGRESS_ANALYZE_STARTED_BY_AUTOVACUUM 2 -/* Progress parameters for cluster */ -#define PROGRESS_CLUSTER_COMMAND 0 -#define PROGRESS_CLUSTER_PHASE 1 -#define PROGRESS_CLUSTER_INDEX_RELID 2 -#define PROGRESS_CLUSTER_HEAP_TUPLES_SCANNED 3 -#define PROGRESS_CLUSTER_HEAP_TUPLES_WRITTEN 4 -#define PROGRESS_CLUSTER_TOTAL_HEAP_BLKS 5 -#define PROGRESS_CLUSTER_HEAP_BLKS_SCANNED 6 -#define PROGRESS_CLUSTER_INDEX_REBUILD_COUNT 7 +/* + * Progress parameters for REPACK. + * + * Values for PROGRESS_REPACK_COMMAND are defined as in RepackCommand. + * + * Note: Since REPACK shares code with CLUSTER, these values are also + * used by CLUSTER. (CLUSTER is now deprecated, so it makes little sense to + * introduce a separate set of constants.) + */ +#define PROGRESS_REPACK_COMMAND 0 +#define PROGRESS_REPACK_PHASE 1 +#define PROGRESS_REPACK_INDEX_RELID 2 +#define PROGRESS_REPACK_HEAP_TUPLES_SCANNED 3 +#define PROGRESS_REPACK_HEAP_TUPLES_WRITTEN 4 +#define PROGRESS_REPACK_TOTAL_HEAP_BLKS 5 +#define PROGRESS_REPACK_HEAP_BLKS_SCANNED 6 +#define PROGRESS_REPACK_INDEX_REBUILD_COUNT 7 -/* Phases of cluster (as advertised via PROGRESS_CLUSTER_PHASE) */ -#define PROGRESS_CLUSTER_PHASE_SEQ_SCAN_HEAP 1 -#define PROGRESS_CLUSTER_PHASE_INDEX_SCAN_HEAP 2 -#define PROGRESS_CLUSTER_PHASE_SORT_TUPLES 3 -#define PROGRESS_CLUSTER_PHASE_WRITE_NEW_HEAP 4 -#define PROGRESS_CLUSTER_PHASE_SWAP_REL_FILES 5 -#define PROGRESS_CLUSTER_PHASE_REBUILD_INDEX 6 -#define PROGRESS_CLUSTER_PHASE_FINAL_CLEANUP 7 - -/* Commands of PROGRESS_CLUSTER */ -#define PROGRESS_CLUSTER_COMMAND_CLUSTER 1 -#define PROGRESS_CLUSTER_COMMAND_VACUUM_FULL 2 +/* + * Phases of repack (as advertised via PROGRESS_REPACK_PHASE). + */ +#define PROGRESS_REPACK_PHASE_SEQ_SCAN_HEAP 1 +#define PROGRESS_REPACK_PHASE_INDEX_SCAN_HEAP 2 +#define PROGRESS_REPACK_PHASE_SORT_TUPLES 3 +#define PROGRESS_REPACK_PHASE_WRITE_NEW_HEAP 4 +#define PROGRESS_REPACK_PHASE_SWAP_REL_FILES 5 +#define PROGRESS_REPACK_PHASE_REBUILD_INDEX 6 +#define PROGRESS_REPACK_PHASE_FINAL_CLEANUP 7 /* Progress parameters for CREATE INDEX */ /* 3, 4 and 5 reserved for "waitfor" metrics */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 646d6ced763..c15ba5e6f29 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -3980,18 +3980,6 @@ typedef struct AlterSystemStmt VariableSetStmt *setstmt; /* SET subcommand */ } AlterSystemStmt; -/* ---------------------- - * Cluster Statement (support pbrown's cluster index implementation) - * ---------------------- - */ -typedef struct ClusterStmt -{ - NodeTag type; - RangeVar *relation; /* relation being indexed, or NULL if all */ - char *indexname; /* original index defined */ - List *params; /* list of DefElem nodes */ -} ClusterStmt; - /* ---------------------- * Vacuum and Analyze Statements * @@ -4004,7 +3992,7 @@ typedef struct VacuumStmt NodeTag type; List *options; /* list of DefElem nodes */ List *rels; /* list of VacuumRelation, or NIL for all */ - bool is_vacuumcmd; /* true for VACUUM, false for ANALYZE */ + bool is_vacuumcmd; /* true for VACUUM, false otherwise */ } VacuumStmt; /* @@ -4022,6 +4010,27 @@ typedef struct VacuumRelation List *va_cols; /* list of column names, or NIL for all */ } VacuumRelation; +/* ---------------------- + * Repack Statement + * ---------------------- + */ +typedef enum RepackCommand +{ + REPACK_COMMAND_CLUSTER = 1, + REPACK_COMMAND_REPACK, + REPACK_COMMAND_VACUUMFULL, +} RepackCommand; + +typedef struct RepackStmt +{ + NodeTag type; + RepackCommand command; /* type of command being run */ + VacuumRelation *relation; /* relation being repacked */ + char *indexname; /* order tuples by this index */ + bool usingindex; /* whether USING INDEX is specified */ + List *params; /* list of DefElem nodes */ +} RepackStmt; + /* ---------------------- * Explain Statement * diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f7753c5c8a8..6f74a8c05c7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -377,6 +377,7 @@ PG_KEYWORD("reindex", REINDEX, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("relative", RELATIVE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("release", RELEASE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("rename", RENAME, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("repack", REPACK, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h index 1290c9bab68..652dc61b834 100644 --- a/src/include/tcop/cmdtaglist.h +++ b/src/include/tcop/cmdtaglist.h @@ -196,6 +196,7 @@ PG_CMDTAG(CMDTAG_REASSIGN_OWNED, "REASSIGN OWNED", false, false, false) PG_CMDTAG(CMDTAG_REFRESH_MATERIALIZED_VIEW, "REFRESH MATERIALIZED VIEW", true, false, false) PG_CMDTAG(CMDTAG_REINDEX, "REINDEX", true, false, false) PG_CMDTAG(CMDTAG_RELEASE, "RELEASE", false, false, false) +PG_CMDTAG(CMDTAG_REPACK, "REPACK", false, false, false) PG_CMDTAG(CMDTAG_RESET, "RESET", false, false, false) PG_CMDTAG(CMDTAG_REVOKE, "REVOKE", true, false, false) PG_CMDTAG(CMDTAG_REVOKE_ROLE, "REVOKE ROLE", false, false, false) diff --git a/src/include/utils/backend_progress.h b/src/include/utils/backend_progress.h index 19f63b41431..6300dbd15d5 100644 --- a/src/include/utils/backend_progress.h +++ b/src/include/utils/backend_progress.h @@ -24,10 +24,10 @@ typedef enum ProgressCommandType PROGRESS_COMMAND_INVALID, PROGRESS_COMMAND_VACUUM, PROGRESS_COMMAND_ANALYZE, - PROGRESS_COMMAND_CLUSTER, PROGRESS_COMMAND_CREATE_INDEX, PROGRESS_COMMAND_BASEBACKUP, PROGRESS_COMMAND_COPY, + PROGRESS_COMMAND_REPACK, } ProgressCommandType; #define PGSTAT_NUM_PROGRESS_PARAM 20 diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 4d40a6809ab..277854418fa 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -495,6 +495,43 @@ ALTER TABLE clstrpart SET WITHOUT CLUSTER; ERROR: cannot mark index clustered in partitioned table ALTER TABLE clstrpart CLUSTER ON clstrpart_idx; ERROR: cannot mark index clustered in partitioned table +-- Check that REPACK sets new relfilenodes: it should process exactly the same +-- tables as CLUSTER did. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +REPACK clstrpart USING INDEX clstrpart_idx; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C"; + relname | level | relkind | ?column? +-------------+-------+---------+---------- + clstrpart | 0 | p | t + clstrpart1 | 1 | p | t + clstrpart11 | 2 | r | f + clstrpart12 | 2 | p | t + clstrpart2 | 1 | r | f + clstrpart3 | 1 | p | t + clstrpart33 | 2 | r | f +(7 rows) + +-- And finally the same for REPACK w/o index. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +REPACK clstrpart; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C"; + relname | level | relkind | ?column? +-------------+-------+---------+---------- + clstrpart | 0 | p | t + clstrpart1 | 1 | p | t + clstrpart11 | 2 | r | f + clstrpart12 | 2 | p | t + clstrpart2 | 1 | r | f + clstrpart3 | 1 | p | t + clstrpart33 | 2 | r | f +(7 rows) + DROP TABLE clstrpart; -- Ownership of partitions is checked CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i); @@ -513,7 +550,7 @@ CREATE TEMP TABLE ptnowner_oldnodes AS JOIN pg_class AS c ON c.oid=tree.relid; SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; -WARNING: permission denied to cluster "ptnowner2", skipping it +WARNING: permission denied to execute CLUSTER on "ptnowner2", skipping it RESET SESSION AUTHORIZATION; SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; @@ -665,6 +702,101 @@ SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b; (4 rows) COMMIT; +---------------------------------------------------------------------- +-- +-- REPACK +-- +---------------------------------------------------------------------- +-- REPACK handles individual tables identically to CLUSTER, but it's worth +-- checking if it handles table hierarchies identically as well. +REPACK clstr_tst USING INDEX clstr_tst_c; +-- Verify that inheritance link still works +INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2'); +SELECT a,b,c,substring(d for 30), length(d) from clstr_tst; + a | b | c | substring | length +----+-----+------------------+--------------------------------+-------- + 10 | 14 | catorce | | + 18 | 5 | cinco | | + 9 | 4 | cuatro | | + 26 | 19 | diecinueve | | + 12 | 18 | dieciocho | | + 30 | 16 | dieciseis | | + 24 | 17 | diecisiete | | + 2 | 10 | diez | | + 23 | 12 | doce | | + 11 | 2 | dos | | + 25 | 9 | nueve | | + 31 | 8 | ocho | | + 1 | 11 | once | | + 28 | 15 | quince | | + 32 | 6 | seis | xyzzyxyzzyxyzzyxyzzyxyzzyxyzzy | 500000 + 29 | 7 | siete | | + 15 | 13 | trece | | + 22 | 30 | treinta | | + 17 | 32 | treinta y dos | | + 3 | 31 | treinta y uno | | + 5 | 3 | tres | | + 20 | 1 | uno | | + 6 | 20 | veinte | | + 14 | 25 | veinticinco | | + 21 | 24 | veinticuatro | | + 4 | 22 | veintidos | | + 19 | 29 | veintinueve | | + 16 | 28 | veintiocho | | + 27 | 26 | veintiseis | | + 13 | 27 | veintisiete | | + 7 | 23 | veintitres | | + 8 | 21 | veintiuno | | + 0 | 100 | in child table | | + 0 | 100 | in child table 2 | | +(34 rows) + +-- Verify that foreign key link still works +INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); +ERROR: insert or update on table "clstr_tst" violates foreign key constraint "clstr_tst_con" +DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s". +SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass +ORDER BY 1; + conname +---------------------- + clstr_tst_a_not_null + clstr_tst_con + clstr_tst_pkey +(3 rows) + +-- Verify partial analyze works +REPACK (ANALYZE) clstr_tst (a); +REPACK (ANALYZE) clstr_tst; +REPACK (VERBOSE) clstr_tst (a); +ERROR: ANALYZE option must be specified when a column list is provided +-- REPACK w/o argument performs no ordering, so we can only check which tables +-- have the relfilenode changed. +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_old AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); +SET SESSION AUTHORIZATION regress_clstr_user; +SET client_min_messages = ERROR; -- order of "skipping" warnings may vary +REPACK; +RESET client_min_messages; +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_new AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); +-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been +-- processed because there is nothing like clustering index here. +SELECT o.relname FROM relnodes_old o +JOIN relnodes_new n ON o.relname = n.relname +WHERE o.relfilenode <> n.relfilenode +ORDER BY o.relname; + relname +--------- + clstr_1 + clstr_3 +(2 rows) + -- clean up DROP TABLE clustertest; DROP TABLE clstr_1; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f4ee2bd7459..48461550636 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2002,34 +2002,23 @@ pg_stat_progress_basebackup| SELECT pid, ELSE NULL::text END AS backup_type FROM pg_stat_get_progress_info('BASEBACKUP'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20); -pg_stat_progress_cluster| SELECT s.pid, - s.datid, - d.datname, - s.relid, - CASE s.param1 - WHEN 1 THEN 'CLUSTER'::text - WHEN 2 THEN 'VACUUM FULL'::text - ELSE NULL::text +pg_stat_progress_cluster| SELECT pid, + datid, + datname, + relid, + CASE + WHEN (command = ANY (ARRAY['CLUSTER'::text, 'VACUUM FULL'::text])) THEN command + WHEN (repack_index_relid = (0)::oid) THEN 'VACUUM FULL'::text + ELSE 'CLUSTER'::text END AS command, - CASE s.param2 - WHEN 0 THEN 'initializing'::text - WHEN 1 THEN 'seq scanning heap'::text - WHEN 2 THEN 'index scanning heap'::text - WHEN 3 THEN 'sorting tuples'::text - WHEN 4 THEN 'writing new heap'::text - WHEN 5 THEN 'swapping relation files'::text - WHEN 6 THEN 'rebuilding index'::text - WHEN 7 THEN 'performing final cleanup'::text - ELSE NULL::text - END AS phase, - (s.param3)::oid AS cluster_index_relid, - s.param4 AS heap_tuples_scanned, - s.param5 AS heap_tuples_written, - s.param6 AS heap_blks_total, - s.param7 AS heap_blks_scanned, - s.param8 AS index_rebuild_count - FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) - LEFT JOIN pg_database d ON ((s.datid = d.oid))); + phase, + repack_index_relid AS cluster_index_relid, + heap_tuples_scanned, + heap_tuples_written, + heap_blks_total, + heap_blks_scanned, + index_rebuild_count + FROM pg_stat_progress_repack; pg_stat_progress_copy| SELECT s.pid, s.datid, d.datname, @@ -2089,6 +2078,35 @@ pg_stat_progress_create_index| SELECT s.pid, s.param15 AS partitions_done FROM (pg_stat_get_progress_info('CREATE INDEX'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) LEFT JOIN pg_database d ON ((s.datid = d.oid))); +pg_stat_progress_repack| SELECT s.pid, + s.datid, + d.datname, + s.relid, + CASE s.param1 + WHEN 1 THEN 'CLUSTER'::text + WHEN 2 THEN 'REPACK'::text + WHEN 3 THEN 'VACUUM FULL'::text + ELSE NULL::text + END AS command, + CASE s.param2 + WHEN 0 THEN 'initializing'::text + WHEN 1 THEN 'seq scanning heap'::text + WHEN 2 THEN 'index scanning heap'::text + WHEN 3 THEN 'sorting tuples'::text + WHEN 4 THEN 'writing new heap'::text + WHEN 5 THEN 'swapping relation files'::text + WHEN 6 THEN 'rebuilding index'::text + WHEN 7 THEN 'performing final cleanup'::text + ELSE NULL::text + END AS phase, + (s.param3)::oid AS repack_index_relid, + s.param4 AS heap_tuples_scanned, + s.param5 AS heap_tuples_written, + s.param6 AS heap_blks_total, + s.param7 AS heap_blks_scanned, + s.param8 AS index_rebuild_count + FROM (pg_stat_get_progress_info('REPACK'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) + LEFT JOIN pg_database d ON ((s.datid = d.oid))); pg_stat_progress_vacuum| SELECT s.pid, s.datid, d.datname, diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql index b7115f86104..c976823a3cb 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -76,7 +76,6 @@ INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass ORDER BY 1; - SELECT relname, relkind, EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast FROM pg_class c WHERE relname LIKE 'clstr_tst%' ORDER BY relname; @@ -229,6 +228,24 @@ SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM o CLUSTER clstrpart; ALTER TABLE clstrpart SET WITHOUT CLUSTER; ALTER TABLE clstrpart CLUSTER ON clstrpart_idx; + +-- Check that REPACK sets new relfilenodes: it should process exactly the same +-- tables as CLUSTER did. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +REPACK clstrpart USING INDEX clstrpart_idx; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C"; + +-- And finally the same for REPACK w/o index. +DROP TABLE old_cluster_info; +DROP TABLE new_cluster_info; +CREATE TEMP TABLE old_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +REPACK clstrpart; +CREATE TEMP TABLE new_cluster_info AS SELECT relname, level, relfilenode, relkind FROM pg_partition_tree('clstrpart'::regclass) AS tree JOIN pg_class c ON c.oid=tree.relid ; +SELECT relname, old.level, old.relkind, old.relfilenode = new.relfilenode FROM old_cluster_info AS old JOIN new_cluster_info AS new USING (relname) ORDER BY relname COLLATE "C"; + DROP TABLE clstrpart; -- Ownership of partitions is checked @@ -313,6 +330,57 @@ EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b; SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b; COMMIT; +---------------------------------------------------------------------- +-- +-- REPACK +-- +---------------------------------------------------------------------- + +-- REPACK handles individual tables identically to CLUSTER, but it's worth +-- checking if it handles table hierarchies identically as well. +REPACK clstr_tst USING INDEX clstr_tst_c; + +-- Verify that inheritance link still works +INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table 2'); +SELECT a,b,c,substring(d for 30), length(d) from clstr_tst; + +-- Verify that foreign key link still works +INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); + +SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass +ORDER BY 1; + +-- Verify partial analyze works +REPACK (ANALYZE) clstr_tst (a); +REPACK (ANALYZE) clstr_tst; +REPACK (VERBOSE) clstr_tst (a); + +-- REPACK w/o argument performs no ordering, so we can only check which tables +-- have the relfilenode changed. +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_old AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); + +SET SESSION AUTHORIZATION regress_clstr_user; +SET client_min_messages = ERROR; -- order of "skipping" warnings may vary +REPACK; +RESET client_min_messages; + +RESET SESSION AUTHORIZATION; +CREATE TEMP TABLE relnodes_new AS +(SELECT relname, relfilenode +FROM pg_class +WHERE relname IN ('clstr_1', 'clstr_2', 'clstr_3')); + +-- Do the actual comparison. Unlike CLUSTER, clstr_3 should have been +-- processed because there is nothing like clustering index here. +SELECT o.relname FROM relnodes_old o +JOIN relnodes_new n ON o.relname = n.relname +WHERE o.relfilenode <> n.relfilenode +ORDER BY o.relname; + -- clean up DROP TABLE clustertest; DROP TABLE clstr_1; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 9f5ee8fd482..6c4af1c210d 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2575,6 +2575,8 @@ ReorderBufferTupleCidEnt ReorderBufferTupleCidKey ReorderBufferUpdateProgressTxnCB ReorderTuple +RepackCommand +RepackStmt ReparameterizeForeignPathByChild_function ReplOriginId ReplOriginXactState -- 2.47.3 --r7amdyq3rmktb267 Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v33-0002-Refactor-index_concurrently_create_copy-for-use-.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-01-27 10:48 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-03-10 08:35 [PATCH 6/8] Add default_toast_compression GUC Dilip Kumar <[email protected]> 2026-01-27 10:48 [PATCH v33 1/5] Add REPACK command Antonin Houska <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox