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 1pB2pH-0007Sq-DX for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Dec 2022 00:00:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pB2pF-0003tD-TZ for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Dec 2022 00:00:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pB2pF-0003t3-9D for pgsql-hackers@lists.postgresql.org; Fri, 30 Dec 2022 00:00:41 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pB2p7-0007tu-Ro for pgsql-hackers@postgresql.org; Fri, 30 Dec 2022 00:00:40 +0000 Received: by mail-pj1-x102c.google.com with SMTP id hd14-20020a17090b458e00b0021909875bccso19546411pjb.1 for ; Thu, 29 Dec 2022 16:00:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:content-disposition:mime-version :message-id:subject:to:from:date:from:to:cc:subject:date:message-id :reply-to; bh=0j2sd6RxWdrTbJ0TZ5PmjIWjxOZOV+MJIz/c996jpkk=; b=VFDF0bXgBp7QJ3CnFVhkGex1hQoeCCNpJ71A3n61qbl0BBDs1emxbm31uOCtsG363/ H5myJqgWD7OYDBMzIY7yeLokQKSWYQ98nM9x5T4skcEo0lcjfezNSlQdF+pr2GmEyLnn EtULGrpbrJIPWzN8WV1jLCPtE0+4Z3XmfDdeJ+hm98dfLpGAspX8/WUm6o1kXyqAfcLN Rq9SZ6YQXZOctryWgfuQ5zl8C0a7SLa0xcIaM1LP/vLUtJvfrlNGsdfXpYxOBUQ8P0ro Ru66mP3GIUty3aVabvRkbkIROtvnO6xDWltjNBDd7UB7l8aSiBEC5rTsqAiv70EE/giP LT6w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:content-disposition:mime-version :message-id:subject:to:from:date:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=0j2sd6RxWdrTbJ0TZ5PmjIWjxOZOV+MJIz/c996jpkk=; b=iFcyjMUH5oO0ATbAncyJ8ZSljYK43XvUPoy5A6r4VAEEVT9ghChYMaWE7EjqDTjI9M 34KK7UE7FhXyfNoPFDRmuoufZEVTu9DoPZVJcK3AuvJeXqEfWYsL2iw+WeFgy63j4W5R pVF8HHgRBKICEkaZWjsouxcC4dwZuAHfu23nW8UZO0qp4rjMFcc2WFu/LPkae+01I2+3 0Bj7/onBjO2rnKrjyPaolTSexH1mqMaSlN3t+ff3Zb9GgcvwJFEnUEt5DfQ4gSc11UhS ofeDHMeCEtxJNcfe9ivNh27ubPVpaW4ezHnDsLcngUhE1fVCB8Jk2L+2LiaM9DsXg6Gs mdMw== X-Gm-Message-State: AFqh2kqATGwCB4N79vnggn/v1NSGNhpQYxe5Fuh/nR9kqlUCx/75V5zm JWG8WrMCId2Lsl7pxqtABDFJpjGHPDc= X-Google-Smtp-Source: AMrXdXuKrK7OHBaOcvVBAUf3aFmY/P7Yww+rBwSHT5WGtNvhEwOm/UdPwtWNth4QPacTmcOjVMowtQ== X-Received: by 2002:a17:902:ccc4:b0:186:e434:6265 with SMTP id z4-20020a170902ccc400b00186e4346265mr37285790ple.2.1672358432383; Thu, 29 Dec 2022 16:00:32 -0800 (PST) Received: from nathanxps13 ([50.47.162.83]) by smtp.gmail.com with ESMTPSA id s16-20020a17090302d000b00186b138706fsm13538102plk.13.2022.12.29.16.00.31 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 29 Dec 2022 16:00:31 -0800 (PST) Date: Thu, 29 Dec 2022 16:00:28 -0800 From: Nathan Bossart To: pgsql-hackers@postgresql.org Subject: add PROCESS_MAIN to VACUUM Message-ID: <20221230000028.GA435655@nathanxps13> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="IJpNTDwzlM2Ie8A6" Content-Disposition: inline Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --IJpNTDwzlM2Ie8A6 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit Hi hackers, I originally suggested $Š…UBJECT as part of the thread that ultimately led to the addition of PROCESS_TOAST [0], but we decided not to proceed with it. Recently, this idea came up again [1], so I thought I'd give it another try. The motivation for adding this option is to make it easier to VACUUM only a relation's TOAST table. At the moment, you need to find the TOAST table by examining a relation's reltoastrelid, and you need USAGE on the pg_toast schema. This option could also help make it possible to call only vac_update_datfrozenxid() without processing any relations, as discussed elsewhere [2]. The demand for all these niche VACUUM options is likely limited, but it does seem like there are some useful applications. If a new option is out of the question, perhaps this functionality could be added to the existing PROCESS_TOAST option. [0] https://postgr.es/m/BA8951E9-1524-48C5-94AF-73B1F0D7857F%40amazon.com [1] https://postgr.es/m/20221215191246.GA252861%40nathanxps13 [2] https://postgr.es/m/20221229213719.GA301584%40nathanxps13 -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --IJpNTDwzlM2Ie8A6 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v1-0001-add-PROCESS_MAIN-to-VACUUM.patch" From b123ddaf560a647536f5a7e52993401755da8650 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Thu, 29 Dec 2022 15:31:49 -0800 Subject: [PATCH v1 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 | 24 +++++++++++++++++++----- src/backend/postmaster/autovacuum.c | 1 + 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 | 1 + src/test/regress/expected/vacuum.out | 4 ++++ src/test/regress/sql/vacuum.sql | 5 +++++ 10 files changed, 91 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index e14ead8826..bb634ae569 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 @@ -236,6 +237,18 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ defname, "process_toast") == 0) process_toast = defGetBoolean(opt); else if (strcmp(opt->defname, "truncate") == 0) @@ -216,7 +219,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) (freeze ? VACOPT_FREEZE : 0) | (full ? VACOPT_FULL : 0) | (disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) | - (process_toast ? VACOPT_PROCESS_TOAST : 0); + (process_toast ? VACOPT_PROCESS_TOAST : 0) | + (process_main ? VACOPT_PROCESS_MAIN : 0); /* sanity checks on options */ Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE)); @@ -1994,10 +1998,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) /* * 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; @@ -2016,7 +2022,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) /* * 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}; @@ -2030,7 +2037,7 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) /* 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 */ @@ -2057,7 +2064,14 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) * totally unimportant for toast relations. */ if (toast_relid != InvalidOid) + { + /* we force VACOPT_PROCESS_MAIN so vacuum_rel() processes it */ + bool force_opt = ((params->options & VACOPT_PROCESS_MAIN) == 0); + + params->options |= force_opt ? VACOPT_PROCESS_MAIN : 0; vacuum_rel(toast_relid, NULL, params); + params->options &= force_opt ? ~VACOPT_PROCESS_MAIN : ~0; + } /* * 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 0746d80224..65c6fb6a27 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2856,6 +2856,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, /* Note that this skips toast relations */ tab->at_params.options = (dovacuum ? VACOPT_VACUUM : 0) | + (dovacuum ? VACOPT_PROCESS_MAIN : 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 2a3921937c..ce4c16c8a3 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -4551,9 +4551,9 @@ 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"); - else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_TOAST|TRUNCATE")) + else if (TailMatches("FULL|FREEZE|ANALYZE|VERBOSE|DISABLE_PAGE_SKIPPING|SKIP_LOCKED|PROCESS_MAIN|PROCESS_TOAST|TRUNCATE")) 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 e5343774fe..fd7cb944cb 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\).*;/, + '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\).*;/, diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 272e37d290..7424e75668 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; } vacuumingOptions; @@ -120,6 +121,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} }; @@ -147,6 +149,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]); @@ -259,6 +262,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); @@ -311,6 +317,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"); @@ -507,6 +516,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); @@ -950,6 +966,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 */ @@ -1057,6 +1080,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 2f274f2bec..80fca14619 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -188,6 +188,7 @@ typedef struct VacAttrStats #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_PROCESS_MAIN 0x100 /* process main relation */ /* * 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 0035d158b7..d3880f67b5 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -282,6 +282,10 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; VACUUM (PROCESS_TOAST FALSE) vactst; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; ERROR: PROCESS_TOAST required with VACUUM FULL +-- PROCESS_MAIN option +VACUUM (PROCESS_MAIN FALSE) vactst; +VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst; +VACUUM (PROCESS_MAIN FALSE, FULL) vactst; 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 9faa8a34a6..d2893e0c37 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -237,6 +237,11 @@ ALTER TABLE vactst ALTER COLUMN t SET STORAGE EXTERNAL; VACUUM (PROCESS_TOAST FALSE) vactst; VACUUM (PROCESS_TOAST FALSE, FULL) vactst; +-- PROCESS_MAIN option +VACUUM (PROCESS_MAIN FALSE) vactst; +VACUUM (PROCESS_MAIN FALSE, PROCESS_TOAST FALSE) vactst; +VACUUM (PROCESS_MAIN FALSE, FULL) vactst; + DROP TABLE vaccluster; DROP TABLE vactst; DROP TABLE vacparted; -- 2.25.1 --IJpNTDwzlM2Ie8A6--