Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXcpM-0006ip-S7 for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Mar 2023 06:54:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pXcpK-0006RZ-MZ for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Mar 2023 06:54:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pXcpK-0006R9-8e for pgsql-hackers@lists.postgresql.org; Thu, 02 Mar 2023 06:54:06 +0000 Received: from mail-pl1-x62d.google.com ([2607:f8b0:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pXcpH-00032b-Tc for pgsql-hackers@postgresql.org; Thu, 02 Mar 2023 06:54:05 +0000 Received: by mail-pl1-x62d.google.com with SMTP id v11so13051854plz.8 for ; Wed, 01 Mar 2023 22:54:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=5gpkaQLD+uh3ou1phM9NN+cMx1KSbspoF/+V3F+eIRE=; b=arkHNvoPrCtK94dWsbH2E5EqO4avpVYT2r+81eod3AMeCO4tKOYx6XtLT53EgIpRwG BCXiYjdhBrpYjozxUqX4DSA0l2M1uC13OLvIVn1uaWYpVGaLWVzC1CIBwbWm3GHklAXy rgYRtEkwRJcLM8kMbu19jZgTrtxFjZyxjy513t0CqwYyfwG+ibdrbKff5wGtuenTYPzx w7VcplyS8iLB1r9y7XDiYyOd5BtR8hMThWxZ/igiLQSxf1RzeSbAbFJOPLbsMcrRc9PA BMVabAIi7tD9R2gSyU5KjONZJu8tWC9NDoSf9f9ZWjqxnLZzPU6EwOku6jH0rrzgwELb Ig5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=5gpkaQLD+uh3ou1phM9NN+cMx1KSbspoF/+V3F+eIRE=; b=1bdKyNi10ZVsLVZTLBYtoKWam0FswlhiiCnwtbIAT5H58mE3jvT0nnJkNcZJROII9o RTtaNbQwO4KTIZ30EgVUnQQzVIfj50bWfAx/XXZoHUaBTL7LvJp+J3knhydlwN67Z2Uf G5UMrw5NBNkpbIoEJhS2TX9NUlbGCON/J7myboIm+reste8hM+TE2CyId17dpnWfrZQh PBxJ7jc4No+Y6kRGMngnjaj3A2jYbeXV1tovsjd02+KgsqoYiCQXL3smgCCMLETPc3PY 3UPAcAP+tgjnhHKiaaXvmS/QNl2S7A9gU5PU+p7ZWzQdnjbMzzdm8vlyjRFCdwB3nehG sHWA== X-Gm-Message-State: AO0yUKXzk9bohxbP3aCLknArhDDXMxl2TGMzqcpO45t9JKvSWK1I1OoY qS9Zu01Z7IicixhWmi7XepU= X-Google-Smtp-Source: AK7set8Oh6/uWOKuyhSZEu1K9d3tjDdI5AyLRd/wcWc3cV1G3/B4LIOWQbCDfLZ9DFH0eD3aRwxPxw== X-Received: by 2002:a17:903:2487:b0:19e:78b7:2f4e with SMTP id p7-20020a170903248700b0019e78b72f4emr129492plw.46.1677740041525; Wed, 01 Mar 2023 22:54:01 -0800 (PST) Received: from nathanxps13 ([50.47.162.83]) by smtp.gmail.com with ESMTPSA id a2-20020a170902710200b0019a7d6a9a76sm9489424pll.111.2023.03.01.22.54.00 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 01 Mar 2023 22:54:00 -0800 (PST) Date: Wed, 1 Mar 2023 22:53:59 -0800 From: Nathan Bossart To: Michael Paquier Cc: vignesh C , pgsql-hackers@postgresql.org Subject: Re: add PROCESS_MAIN to VACUUM Message-ID: <20230302065359.GA2140777@nathanxps13> References: <20221230000028.GA435655@nathanxps13> <20230107050724.GA914213@nathanxps13> <20230119190807.GA4062411@nathanxps13> <20230301172637.GA1456203@nathanxps13> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="tKW2IUtsqtDRztdT" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --tKW2IUtsqtDRztdT Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Thu, Mar 02, 2023 at 02:53:02PM +0900, Michael Paquier wrote: > - if (params->options & VACOPT_FULL) > + if (params->options & VACOPT_FULL && > + params->options & VACOPT_PROCESS_MAIN) > Perhaps this is a bit under-parenthesized, while reading through it > once again.. fixed > > + { > + /* we force VACOPT_PROCESS_MAIN so vacuum_rel() processes it */ > + bool force_opt = ((params->options & VACOPT_PROCESS_MAIN) == 0); > + > + params->options |= VACOPT_PROCESS_MAIN; > vacuum_rel(toast_relid, NULL, params, true); > + if (force_opt) > + params->options &= ~VACOPT_PROCESS_MAIN; > Zigzagging with the centralized VacuumParams is a bit inelegant. > Perhaps it would be neater to copy VacuumParams and append > VACOPT_PROCESS_MAIN on the copy? done > An extra question: should we check the behavior of the commands when > applying a list of relations to VACUUM? I don't feel a strong need for that, especially now that we aren't modifying params anymore. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --tKW2IUtsqtDRztdT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0001-add-PROCESS_MAIN-to-VACUUM.patch" From 0a4563a536fc4aa044d6d1b663ce28b6ef0c36f7 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Thu, 29 Dec 2022 15:31:49 -0800 Subject: [PATCH v6 1/1] add PROCESS_MAIN to VACUUM --- doc/src/sgml/ref/vacuum.sgml | 13 ++++++ doc/src/sgml/ref/vacuumdb.sgml | 15 +++++++ src/backend/commands/vacuum.c | 30 ++++++++++---- src/backend/postmaster/autovacuum.c | 4 +- src/bin/psql/tab-complete.c | 4 +- src/bin/scripts/t/100_vacuumdb.pl | 7 ++++ src/bin/scripts/vacuumdb.c | 24 +++++++++++ src/include/commands/vacuum.h | 9 ++-- src/test/regress/expected/vacuum.out | 61 ++++++++++++++++++++++++++++ src/test/regress/sql/vacuum.sql | 25 ++++++++++++ 10 files changed, 178 insertions(+), 14 deletions(-) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 545b23b54f..b6d30b5764 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -33,6 +33,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ boolean ] SKIP_LOCKED [ boolean ] INDEX_CLEANUP { AUTO | ON | OFF } + PROCESS_MAIN [ boolean ] PROCESS_TOAST [ boolean ] TRUNCATE [ boolean ] PARALLEL integer @@ -238,6 +239,18 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ defname, "process_toast") == 0) process_toast = defGetBoolean(opt); else if (strcmp(opt->defname, "truncate") == 0) @@ -226,7 +229,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) (disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) | (process_toast ? VACOPT_PROCESS_TOAST : 0) | (skip_database_stats ? VACOPT_SKIP_DATABASE_STATS : 0) | - (only_database_stats ? VACOPT_ONLY_DATABASE_STATS : 0); + (only_database_stats ? VACOPT_ONLY_DATABASE_STATS : 0) | + (process_main ? VACOPT_PROCESS_MAIN : 0); /* sanity checks on options */ Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE)); @@ -367,9 +371,10 @@ vacuum(List *relations, VacuumParams *params, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("ONLY_DATABASE_STATS cannot be specified with a list of tables"))); - /* don't require people to turn off PROCESS_TOAST explicitly */ + /* don't require people to turn off PROCESS_TOAST/MAIN explicitly */ if (params->options & ~(VACOPT_VACUUM | VACOPT_VERBOSE | + VACOPT_PROCESS_MAIN | VACOPT_PROCESS_TOAST | VACOPT_ONLY_DATABASE_STATS)) ereport(ERROR, @@ -2031,10 +2036,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs) /* * Remember the relation's TOAST relation for later, if the caller asked * us to process it. In VACUUM FULL, though, the toast table is - * automatically rebuilt by cluster_rel so we shouldn't recurse to it. + * automatically rebuilt by cluster_rel so we shouldn't recurse to it + * unless PROCESS_MAIN is disabled. */ if ((params->options & VACOPT_PROCESS_TOAST) != 0 && - (params->options & VACOPT_FULL) == 0) + ((params->options & VACOPT_FULL) == 0 || + (params->options & VACOPT_PROCESS_MAIN) == 0)) toast_relid = rel->rd_rel->reltoastrelid; else toast_relid = InvalidOid; @@ -2053,7 +2060,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs) /* * Do the actual work --- either FULL or "lazy" vacuum */ - if (params->options & VACOPT_FULL) + if ((params->options & VACOPT_FULL) && + (params->options & VACOPT_PROCESS_MAIN)) { ClusterParams cluster_params = {0}; @@ -2067,7 +2075,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs) /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, &cluster_params); } - else + else if (params->options & VACOPT_PROCESS_MAIN) table_relation_vacuum(rel, params, vac_strategy); /* Roll back any GUC changes executed by index functions */ @@ -2094,7 +2102,15 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params, bool skip_privs) * totally unimportant for toast relations. */ if (toast_relid != InvalidOid) - vacuum_rel(toast_relid, NULL, params, true); + { + VacuumParams toast_params; + + /* force VACOPT_PROCESS_MAIN so vacuum_rel() processes it */ + memcpy(&toast_params, params, sizeof(VacuumParams)); + toast_params.options |= VACOPT_PROCESS_MAIN; + + vacuum_rel(toast_relid, NULL, &toast_params, true); + } /* * Now release the session-level lock on the main table. diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index ff6149a179..c0e2e00a7e 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2860,7 +2860,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, * skip vac_update_datfrozenxid(); we'll do that separately. */ tab->at_params.options = - (dovacuum ? (VACOPT_VACUUM | VACOPT_SKIP_DATABASE_STATS) : 0) | + (dovacuum ? (VACOPT_VACUUM | + VACOPT_PROCESS_MAIN | + VACOPT_SKIP_DATABASE_STATS) : 0) | (doanalyze ? VACOPT_ANALYZE : 0) | (!wraparound ? VACOPT_SKIP_LOCKED : 0); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5e1882eaea..8f12af799b 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -4618,10 +4618,10 @@ psql_completion(const char *text, int start, int end) if (ends_with(prev_wd, '(') || ends_with(prev_wd, ',')) COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE", "DISABLE_PAGE_SKIPPING", "SKIP_LOCKED", - "INDEX_CLEANUP", "PROCESS_TOAST", + "INDEX_CLEANUP", "PROCESS_MAIN", "PROCESS_TOAST", "TRUNCATE", "PARALLEL", "SKIP_DATABASE_STATS", "ONLY_DATABASE_STATS"); - else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS")) + else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_MAIN|PROCESS_TOAST|TRUNCATE|SKIP_DATABASE_STATS|ONLY_DATABASE_STATS")) COMPLETE_WITH("ON", "OFF"); else if (TailMatches("INDEX_CLEANUP")) COMPLETE_WITH("AUTO", "ON", "OFF"); diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index 3cfbaaec0d..46101899ae 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -65,6 +65,13 @@ $node->issues_sql_like( $node->command_fails( [ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ], '--analyze-only and --no-truncate specified together'); +$node->issues_sql_like( + [ 'vacuumdb', '--no-process-main', 'postgres' ], + qr/statement: VACUUM \(PROCESS_MAIN FALSE, SKIP_DATABASE_STATS\).*;/, + 'vacuumdb --no-process-main'); +$node->command_fails( + [ 'vacuumdb', '--analyze-only', '--no-process-main', 'postgres' ], + '--analyze-only and --no-process_main specified together'); $node->issues_sql_like( [ 'vacuumdb', '--no-process-toast', 'postgres' ], qr/statement: VACUUM \(PROCESS_TOAST FALSE, SKIP_DATABASE_STATS\).*;/, diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 58b894216b..39be265b5b 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -43,6 +43,7 @@ typedef struct vacuumingOptions bool no_index_cleanup; bool force_index_cleanup; bool do_truncate; + bool process_main; bool process_toast; bool skip_database_stats; } vacuumingOptions; @@ -121,6 +122,7 @@ main(int argc, char *argv[]) {"force-index-cleanup", no_argument, NULL, 9}, {"no-truncate", no_argument, NULL, 10}, {"no-process-toast", no_argument, NULL, 11}, + {"no-process-main", no_argument, NULL, 12}, {NULL, 0, NULL, 0} }; @@ -148,6 +150,7 @@ main(int argc, char *argv[]) vacopts.no_index_cleanup = false; vacopts.force_index_cleanup = false; vacopts.do_truncate = true; + vacopts.process_main = true; vacopts.process_toast = true; pg_logging_init(argv[0]); @@ -260,6 +263,9 @@ main(int argc, char *argv[]) case 11: vacopts.process_toast = false; break; + case 12: + vacopts.process_main = false; + break; default: /* getopt_long already emitted a complaint */ pg_log_error_hint("Try \"%s --help\" for more information.", progname); @@ -312,6 +318,9 @@ main(int argc, char *argv[]) if (!vacopts.do_truncate) pg_fatal("cannot use the \"%s\" option when performing only analyze", "no-truncate"); + if (!vacopts.process_main) + pg_fatal("cannot use the \"%s\" option when performing only analyze", + "no-process-main"); if (!vacopts.process_toast) pg_fatal("cannot use the \"%s\" option when performing only analyze", "no-process-toast"); @@ -508,6 +517,13 @@ vacuum_one_database(ConnParams *cparams, "no-truncate", "12"); } + if (!vacopts->process_main && PQserverVersion(conn) < 160000) + { + PQfinish(conn); + pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s", + "no-process-main", "16"); + } + if (!vacopts->process_toast && PQserverVersion(conn) < 140000) { PQfinish(conn); @@ -976,6 +992,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion, appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep); sep = comma; } + if (!vacopts->process_main) + { + /* PROCESS_MAIN is supported since v16 */ + Assert(serverVersion >= 160000); + appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep); + sep = comma; + } if (!vacopts->process_toast) { /* PROCESS_TOAST is supported since v14 */ @@ -1090,6 +1113,7 @@ help(const char *progname) printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n")); printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n")); printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n")); + printf(_(" --no-process-main skip the main relation\n")); printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n")); printf(_(" --no-truncate don't truncate empty pages at the end of the table\n")); printf(_(" -n, --schema=PATTERN vacuum tables in the specified schema(s) only\n")); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 689dbb7702..bdfd96cfec 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -186,10 +186,11 @@ typedef struct VacAttrStats #define VACOPT_FREEZE 0x08 /* FREEZE option */ #define VACOPT_FULL 0x10 /* FULL (non-concurrent) vacuum */ #define VACOPT_SKIP_LOCKED 0x20 /* skip if cannot get lock */ -#define VACOPT_PROCESS_TOAST 0x40 /* process the TOAST table, if any */ -#define VACOPT_DISABLE_PAGE_SKIPPING 0x80 /* don't skip any pages */ -#define VACOPT_SKIP_DATABASE_STATS 0x100 /* skip vac_update_datfrozenxid() */ -#define VACOPT_ONLY_DATABASE_STATS 0x200 /* only vac_update_datfrozenxid() */ +#define VACOPT_PROCESS_MAIN 0x40 /* process main relation */ +#define VACOPT_PROCESS_TOAST 0x80 /* process the TOAST table, if any */ +#define VACOPT_DISABLE_PAGE_SKIPPING 0x100 /* don't skip any pages */ +#define VACOPT_SKIP_DATABASE_STATS 0x200 /* skip vac_update_datfrozenxid() */ +#define VACOPT_ONLY_DATABASE_STATS 0x400 /* only vac_update_datfrozenxid() */ /* * Values used by index_cleanup and truncate params. diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 458adee7f8..50c41984da 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -276,10 +276,30 @@ RESET default_transaction_isolation; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; ANALYZE vactst; COMMIT; +CREATE VIEW vactst_vacuum_counts AS + SELECT CASE WHEN c.relname IS NULL THEN 'main' ELSE 'toast' END as rel, + s.vacuum_count + FROM pg_stat_all_tables s + LEFT JOIN pg_class c ON s.relid = c.reltoastrelid + WHERE c.relname = 'vactst' OR s.relname = 'vactst' + ORDER BY rel; -- PROCESS_TOAST option ALTER TABLE vactst ADD COLUMN t TEXT; ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; +SELECT pg_stat_reset(); + pg_stat_reset +--------------- + +(1 row) + VACUUM (PROCESS_TOAST FALSE) vactst; +SELECT * FROM vactst_vacuum_counts; + rel | vacuum_count +-------+-------------- + main | 1 + toast | 0 +(2 rows) + VACUUM (PROCESS_TOAST FALSE, FULL) vactst; ERROR: PROCESS_TOAST required with VACUUM FULL -- SKIP_DATABASE_STATS option @@ -288,6 +308,47 @@ VACUUM (SKIP_DATABASE_STATS) vactst; VACUUM (ONLY_DATABASE_STATS); VACUUM (ONLY_DATABASE_STATS) vactst; -- error ERROR: ONLY_DATABASE_STATS cannot be specified with a list of tables +-- PROCESS_MAIN option +SELECT pg_stat_reset(); + pg_stat_reset +--------------- + +(1 row) + +VACUUM (PROCESS_MAIN FALSE) vactst; +SELECT * FROM vactst_vacuum_counts; + rel | vacuum_count +-------+-------------- + main | 0 + toast | 1 +(2 rows) + +VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst; +SELECT * FROM vactst_vacuum_counts; + rel | vacuum_count +-------+-------------- + main | 0 + toast | 1 +(2 rows) + +SELECT relfilenode AS main_filenode FROM pg_class WHERE relname = 'vactst' \gset +SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t + WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst' \gset +VACUUM (PROCESS_MAIN FALSE, FULL) vactst; +SELECT relfilenode = :main_filenode FROM pg_class WHERE relname = 'vactst'; + ?column? +---------- + t +(1 row) + +SELECT t.relfilenode = :toast_filenode FROM pg_class c, pg_class t + WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst'; + ?column? +---------- + f +(1 row) + +DROP VIEW vactst_vacuum_counts; DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 9da8f3e830..f1329c0db9 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -231,10 +231,20 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; ANALYZE vactst; COMMIT; +CREATE VIEW vactst_vacuum_counts AS + SELECT CASE WHEN c.relname IS NULL THEN 'main' ELSE 'toast' END as rel, + s.vacuum_count + FROM pg_stat_all_tables s + LEFT JOIN pg_class c ON s.relid = c.reltoastrelid + WHERE c.relname = 'vactst' OR s.relname = 'vactst' + ORDER BY rel; + -- PROCESS_TOAST option ALTER TABLE vactst ADD COLUMN t TEXT; ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; +SELECT pg_stat_reset(); VACUUM (PROCESS_TOAST FALSE) vactst; +SELECT * FROM vactst_vacuum_counts; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; -- SKIP_DATABASE_STATS option @@ -244,6 +254,21 @@ VACUUM (SKIP_DATABASE_STATS) vactst; VACUUM (ONLY_DATABASE_STATS); VACUUM (ONLY_DATABASE_STATS) vactst; -- error +-- PROCESS_MAIN option +SELECT pg_stat_reset(); +VACUUM (PROCESS_MAIN FALSE) vactst; +SELECT * FROM vactst_vacuum_counts; +VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst; +SELECT * FROM vactst_vacuum_counts; +SELECT relfilenode AS main_filenode FROM pg_class WHERE relname = 'vactst' \gset +SELECT t.relfilenode AS toast_filenode FROM pg_class c, pg_class t + WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst' \gset +VACUUM (PROCESS_MAIN FALSE, FULL) vactst; +SELECT relfilenode = :main_filenode FROM pg_class WHERE relname = 'vactst'; +SELECT t.relfilenode = :toast_filenode FROM pg_class c, pg_class t + WHERE c.reltoastrelid = t.oid AND c.relname = 'vactst'; + +DROP VIEW vactst_vacuum_counts; DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; -- 2.25.1 --tKW2IUtsqtDRztdT--