pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nathan Bossart <nathandbossart@gmail.com>
Subject: [PATCH v1 3/3] update datfrozenxid/datminmxid once per database in vacuumdb
Date: Tue, 27 Dec 2022 16:59:31 -0800
---
src/bin/scripts/t/100_vacuumdb.pl | 26 ++++++++--------
src/bin/scripts/vacuumdb.c | 52 +++++++++++++++++++++++++++++++
src/fe_utils/parallel_slot.c | 4 +++
3 files changed, 69 insertions(+), 13 deletions(-)
inline patch 1: src/bin/scripts/t/100_vacuumdb.pl, src/bin/scripts/vacuumdb.c, src/fe_utils/parallel_slot.c (+69/-13 lines) download inline-1.patch diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index e5343774fe..39eaae4e8e 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -22,15 +22,15 @@ $node->issues_sql_like(
'SQL VACUUM run');
$node->issues_sql_like(
[ 'vacuumdb', '-f', 'postgres' ],
- qr/statement: VACUUM \(FULL\).*;/,
+ qr/statement: VACUUM \(FULL, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb -f');
$node->issues_sql_like(
[ 'vacuumdb', '-F', 'postgres' ],
- qr/statement: VACUUM \(FREEZE\).*;/,
+ qr/statement: VACUUM \(FREEZE, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb -F');
$node->issues_sql_like(
[ 'vacuumdb', '-zj2', 'postgres' ],
- qr/statement: VACUUM \(ANALYZE\).*;/,
+ qr/statement: VACUUM \(ANALYZE, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb -zj2');
$node->issues_sql_like(
[ 'vacuumdb', '-Z', 'postgres' ],
@@ -38,11 +38,11 @@ $node->issues_sql_like(
'vacuumdb -Z');
$node->issues_sql_like(
[ 'vacuumdb', '--disable-page-skipping', 'postgres' ],
- qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\).*;/,
+ qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb --disable-page-skipping');
$node->issues_sql_like(
[ 'vacuumdb', '--skip-locked', 'postgres' ],
- qr/statement: VACUUM \(SKIP_LOCKED\).*;/,
+ qr/statement: VACUUM \(SKIP_LOCKED, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb --skip-locked');
$node->issues_sql_like(
[ 'vacuumdb', '--skip-locked', '--analyze-only', 'postgres' ],
@@ -53,32 +53,32 @@ $node->command_fails(
'--analyze-only and --disable-page-skipping specified together');
$node->issues_sql_like(
[ 'vacuumdb', '--no-index-cleanup', 'postgres' ],
- qr/statement: VACUUM \(INDEX_CLEANUP FALSE\).*;/,
+ qr/statement: VACUUM \(INDEX_CLEANUP FALSE, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb --no-index-cleanup');
$node->command_fails(
[ 'vacuumdb', '--analyze-only', '--no-index-cleanup', 'postgres' ],
'--analyze-only and --no-index-cleanup specified together');
$node->issues_sql_like(
[ 'vacuumdb', '--no-truncate', 'postgres' ],
- qr/statement: VACUUM \(TRUNCATE FALSE\).*;/,
+ qr/statement: VACUUM \(TRUNCATE FALSE, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb --no-truncate');
$node->command_fails(
[ 'vacuumdb', '--analyze-only', '--no-truncate', 'postgres' ],
'--analyze-only and --no-truncate specified together');
$node->issues_sql_like(
[ 'vacuumdb', '--no-process-toast', 'postgres' ],
- qr/statement: VACUUM \(PROCESS_TOAST FALSE\).*;/,
+ qr/statement: VACUUM \(PROCESS_TOAST FALSE, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb --no-process-toast');
$node->command_fails(
[ 'vacuumdb', '--analyze-only', '--no-process-toast', 'postgres' ],
'--analyze-only and --no-process-toast specified together');
$node->issues_sql_like(
[ 'vacuumdb', '-P', 2, 'postgres' ],
- qr/statement: VACUUM \(PARALLEL 2\).*;/,
+ qr/statement: VACUUM \(PARALLEL 2, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb -P 2');
$node->issues_sql_like(
[ 'vacuumdb', '-P', 0, 'postgres' ],
- qr/statement: VACUUM \(PARALLEL 0\).*;/,
+ qr/statement: VACUUM \(PARALLEL 0, UPDATE_DATFROZENXID FALSE\).*;/,
'vacuumdb -P 0');
$node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
'vacuumdb with connection string');
@@ -119,7 +119,7 @@ $node->command_fails([ 'vacuumdb', '-P', -1, 'postgres' ],
'negative parallel degree');
$node->issues_sql_like(
[ 'vacuumdb', '--analyze', '--table', 'vactable(a, b)', 'postgres' ],
- qr/statement: VACUUM \(ANALYZE\) public.vactable\(a, b\);/,
+ qr/statement: VACUUM \(ANALYZE, UPDATE_DATFROZENXID FALSE\) public.vactable\(a, b\);/,
'vacuumdb --analyze with complete column list');
$node->issues_sql_like(
[ 'vacuumdb', '--analyze-only', '--table', 'vactable(b)', 'postgres' ],
@@ -150,11 +150,11 @@ $node->issues_sql_like(
'vacuumdb --table --min-xid-age');
$node->issues_sql_like(
[ 'vacuumdb', '--schema', '"Foo"', 'postgres' ],
- qr/VACUUM "Foo".bar/,
+ qr/VACUUM \(UPDATE_DATFROZENXID FALSE\) "Foo".bar/,
'vacuumdb --schema');
$node->issues_sql_like(
[ 'vacuumdb', '--exclude-schema', '"Foo"', 'postgres' ],
- qr/(?:(?!VACUUM "Foo".bar).)*/,
+ qr/(?:(?!VACUUM \(UPDATE_DATFROZENXID FALSE\) "Foo".bar).)*/,
'vacuumdb --exclude-schema');
$node->command_fails_like(
[ 'vacuumdb', '-N', 'pg_catalog', '-t', 'pg_class', 'postgres', ],
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 272e37d290..3ba1a37049 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -80,6 +80,8 @@ static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
static void help(const char *progname);
+static bool UpdateDatfrozenxidHandler(PGresult *res, PGconn *conn, void *context);
+
void check_objfilter(void);
/* For analyze-in-stages mode */
@@ -791,6 +793,29 @@ vacuum_one_database(ConnParams *cparams,
if (!ParallelSlotsWaitCompletion(sa))
failed = true;
+ else if (!vacopts->analyze_only && PQserverVersion(conn) >= 160000)
+ {
+ /* v16 and later updates datfrozenxid/datminmxid at the end */
+ const char *cmd = "SELECT pg_catalog.pg_update_datfrozenxid()";
+ ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
+
+ if (!free_slot)
+ {
+ failed = true;
+ goto finish;
+ }
+
+ ParallelSlotSetHandler(free_slot, UpdateDatfrozenxidHandler, NULL);
+
+ if (echo)
+ printf("%s\n", cmd);
+
+ if (PQsendQuery(free_slot->connection, cmd) != 1)
+ pg_log_error("\"pg_update_datfrozenxid()\" failed: %s",
+ PQerrorMessage(free_slot->connection));
+
+ failed = !ParallelSlotsWaitCompletion(sa);
+ }
finish:
ParallelSlotsTerminate(sa);
@@ -802,6 +827,27 @@ finish:
exit(1);
}
+/*
+ * UpdateDatfrozenxidHandler
+ *
+ * ParallelSlotResultHandler for result of pg_update_datfrozenxid(). Requires
+ * that the result status is PGRES_TUPLES_OK. Otherwise, logs an error and
+ * terminates further processing.
+ *
+ * res: PGresult from the query executed on the slot's connection
+ * conn: connection belonging to the slot
+ * context: unused
+ */
+static bool
+UpdateDatfrozenxidHandler(PGresult *res, PGconn *conn, void *context)
+{
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pg_log_error("\"pg_update_datfrozenxid()\" failed: %s",
+ PQerrorMessage(conn));
+
+ return (PQresultStatus(res) == PGRES_TUPLES_OK);
+}
+
/*
* Vacuum/analyze all connectable databases.
*
@@ -992,6 +1038,12 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
vacopts->parallel_workers);
sep = comma;
}
+ if (serverVersion >= 160000)
+ {
+ /* v16 and later calls pg_update_datfrozenxid() at the end */
+ appendPQExpBuffer(sql, "%sUPDATE_DATFROZENXID FALSE", sep);
+ sep = comma;
+ }
if (sep != paren)
appendPQExpBufferChar(sql, ')');
}
diff --git a/src/fe_utils/parallel_slot.c b/src/fe_utils/parallel_slot.c
index 767256757f..aed9e40608 100644
--- a/src/fe_utils/parallel_slot.c
+++ b/src/fe_utils/parallel_slot.c
@@ -475,6 +475,10 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
continue;
if (!consumeQueryResult(&sa->slots[i]))
return false;
+
+ /* mark slot as idle so it can be reused */
+ sa->slots[i].inUse = false;
+ ParallelSlotClearHandler(&sa->slots[i]);
}
return true;
--
2.25.1
Message-ID: <no-message-id-13145@localhost>
Permalink: ../no-message-id-13145@localhost/
Also on: postgresql.org/message-id/no-message-id-13145@localhost
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: nathandbossart@gmail.com
Subject: Re: [PATCH v1 3/3] update datfrozenxid/datminmxid once per database in vacuumdb
In-Reply-To: <no-message-id-13145@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox