public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v5 2/2] Add a --outdated option to reindexdb
5+ messages / 4 participants
[nested] [flat]
* [PATCH v5 2/2] Add a --outdated option to reindexdb
@ 2021-02-24 17:33 Julien Rouhaud <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Julien Rouhaud @ 2021-02-24 17:33 UTC (permalink / raw)
This uses the new OUTDATED option for REINDEX. If user asks for multiple job,
the list of tables to process will be sorted by the total size of underlying
indexes that have outdated dependency.
Author: Julien Rouhaud <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
src/bin/scripts/reindexdb.c | 143 ++++++++++++++++++++++++-----
src/bin/scripts/t/090_reindexdb.pl | 34 ++++++-
2 files changed, 151 insertions(+), 26 deletions(-)
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index cf28176243..369d164e65 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -35,20 +35,23 @@ typedef enum ReindexType
static SimpleStringList *get_parallel_object_list(PGconn *conn,
ReindexType type,
SimpleStringList *user_list,
+ bool outdated,
bool echo);
static void reindex_one_database(const ConnParams *cparams, ReindexType type,
SimpleStringList *user_list,
const char *progname,
bool echo, bool verbose, bool concurrently,
- int concurrentCons, const char *tablespace);
+ int concurrentCons, const char *tablespace,
+ bool outdated);
static void reindex_all_databases(ConnParams *cparams,
const char *progname, bool echo,
bool quiet, bool verbose, bool concurrently,
- int concurrentCons, const char *tablespace);
+ int concurrentCons, const char *tablespace,
+ bool outdated);
static void run_reindex_command(PGconn *conn, ReindexType type,
const char *name, bool echo, bool verbose,
bool concurrently, bool async,
- const char *tablespace);
+ const char *tablespace, bool outdated);
static void help(const char *progname);
@@ -74,6 +77,7 @@ main(int argc, char *argv[])
{"concurrently", no_argument, NULL, 1},
{"maintenance-db", required_argument, NULL, 2},
{"tablespace", required_argument, NULL, 3},
+ {"outdated", no_argument, NULL, 4},
{NULL, 0, NULL, 0}
};
@@ -95,6 +99,7 @@ main(int argc, char *argv[])
bool quiet = false;
bool verbose = false;
bool concurrently = false;
+ bool outdated = false;
SimpleStringList indexes = {NULL, NULL};
SimpleStringList tables = {NULL, NULL};
SimpleStringList schemas = {NULL, NULL};
@@ -170,6 +175,9 @@ main(int argc, char *argv[])
case 3:
tablespace = pg_strdup(optarg);
break;
+ case 4:
+ outdated = true;
+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -234,7 +242,8 @@ main(int argc, char *argv[])
cparams.dbname = maintenance_db;
reindex_all_databases(&cparams, progname, echo, quiet, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
}
else if (syscatalog)
{
@@ -253,12 +262,17 @@ main(int argc, char *argv[])
pg_log_error("cannot reindex specific index(es) and system catalogs at the same time");
exit(1);
}
-
if (concurrentCons > 1)
{
pg_log_error("cannot use multiple jobs to reindex system catalogs");
exit(1);
}
+ if (outdated)
+ {
+ pg_log_error("cannot filter indexes having outdated dependencies "
+ "and reindex system catalogs at the same time");
+ exit(1);
+ }
if (dbname == NULL)
{
@@ -274,7 +288,7 @@ main(int argc, char *argv[])
reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
progname, echo, verbose,
- concurrently, 1, tablespace);
+ concurrently, 1, tablespace, outdated);
}
else
{
@@ -304,17 +318,20 @@ main(int argc, char *argv[])
if (schemas.head != NULL)
reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
progname, echo, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
if (indexes.head != NULL)
reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
progname, echo, verbose,
- concurrently, 1, tablespace);
+ concurrently, 1, tablespace,
+ outdated);
if (tables.head != NULL)
reindex_one_database(&cparams, REINDEX_TABLE, &tables,
progname, echo, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
/*
* reindex database only if neither index nor table nor schema is
@@ -323,7 +340,8 @@ main(int argc, char *argv[])
if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
progname, echo, verbose,
- concurrently, concurrentCons, tablespace);
+ concurrently, concurrentCons, tablespace,
+ outdated);
}
exit(0);
@@ -334,7 +352,7 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
SimpleStringList *user_list,
const char *progname, bool echo,
bool verbose, bool concurrently, int concurrentCons,
- const char *tablespace)
+ const char *tablespace, bool outdated)
{
PGconn *conn;
SimpleStringListCell *cell;
@@ -363,6 +381,14 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
exit(1);
}
+ if (outdated && PQserverVersion(conn) < 140000)
+ {
+ PQfinish(conn);
+ pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+ "outdated", "14");
+ exit(1);
+ }
+
if (!parallel)
{
switch (process_type)
@@ -399,14 +425,24 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
*/
if (concurrently)
pg_log_warning("cannot reindex system catalogs concurrently, skipping all");
+ else if (outdated)
+ {
+ /*
+ * The only supported kind of object that can be outdated
+ * is collation. No system catalog has any index that can
+ * depend on an outdated collation, so skip system
+ * catalogs.
+ */
+ }
else
run_reindex_command(conn, REINDEX_SYSTEM, PQdb(conn), echo,
verbose, concurrently, false,
- tablespace);
+ tablespace, outdated);
/* Build a list of relations from the database */
process_list = get_parallel_object_list(conn, process_type,
- user_list, echo);
+ user_list, outdated,
+ echo);
process_type = REINDEX_TABLE;
/* Bail out if nothing to process */
@@ -419,7 +455,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
/* Build a list of relations from all the schemas */
process_list = get_parallel_object_list(conn, process_type,
- user_list, echo);
+ user_list, outdated,
+ echo);
process_type = REINDEX_TABLE;
/* Bail out if nothing to process */
@@ -484,7 +521,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
run_reindex_command(free_slot->connection, process_type, objname,
- echo, verbose, concurrently, true, tablespace);
+ echo, verbose, concurrently, true, tablespace,
+ outdated);
cell = cell->next;
} while (cell != NULL);
@@ -509,7 +547,7 @@ finish:
static void
run_reindex_command(PGconn *conn, ReindexType type, const char *name,
bool echo, bool verbose, bool concurrently, bool async,
- const char *tablespace)
+ const char *tablespace, bool outdated)
{
const char *paren = "(";
const char *comma = ", ";
@@ -536,6 +574,12 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
sep = comma;
}
+ if (outdated)
+ {
+ appendPQExpBuffer(&sql, "%sOUTDATED", sep);
+ sep = comma;
+ }
+
if (sep != paren)
appendPQExpBufferStr(&sql, ") ");
@@ -641,7 +685,7 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
*/
static SimpleStringList *
get_parallel_object_list(PGconn *conn, ReindexType type,
- SimpleStringList *user_list, bool echo)
+ SimpleStringList *user_list, bool outdated, bool echo)
{
PQExpBufferData catalog_query;
PQExpBufferData buf;
@@ -660,16 +704,41 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
{
case REINDEX_DATABASE:
Assert(user_list == NULL);
+
appendPQExpBufferStr(&catalog_query,
"SELECT c.relname, ns.nspname\n"
" FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_namespace ns"
- " ON c.relnamespace = ns.oid\n"
+ " ON c.relnamespace = ns.oid\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " JOIN pg_catalog.pg_index i"
+ " ON c.oid = i.indrelid\n"
+ " JOIN pg_catalog.pg_class ci"
+ " ON i.indexrelid = ci.oid\n");
+ }
+
+ appendPQExpBufferStr(&catalog_query,
" WHERE ns.nspname != 'pg_catalog'\n"
" AND c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
- CppAsString2(RELKIND_MATVIEW) ")\n"
- " ORDER BY c.relpages DESC;");
+ CppAsString2(RELKIND_MATVIEW) ")\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " GROUP BY c.relname, ns.nspname\n"
+ " ORDER BY sum(ci.relpages)"
+ " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " ORDER BY c.relpages DESC;");
+ }
+
break;
case REINDEX_SCHEMA:
@@ -687,7 +756,18 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
"SELECT c.relname, ns.nspname\n"
" FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_namespace ns"
- " ON c.relnamespace = ns.oid\n"
+ " ON c.relnamespace = ns.oid\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " JOIN pg_catalog.pg_index i"
+ " ON c.oid = i.indrelid\n"
+ " JOIN pg_catalog.pg_class ci"
+ " ON i.indexrelid = ci.oid\n");
+ }
+
+ appendPQExpBufferStr(&catalog_query,
" WHERE c.relkind IN ("
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) ")\n"
@@ -705,8 +785,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
appendStringLiteralConn(&catalog_query, nspname, conn);
}
- appendPQExpBufferStr(&catalog_query, ")\n"
- " ORDER BY c.relpages DESC;");
+ appendPQExpBufferStr(&catalog_query, ")\n");
+
+ if (outdated)
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " GROUP BY c.relname, ns.nspname\n"
+ " ORDER BY sum(ci.relpages)"
+ " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+ }
+ else
+ {
+ appendPQExpBufferStr(&catalog_query,
+ " ORDER BY c.relpages DESC;");
+ }
}
break;
@@ -754,7 +846,7 @@ static void
reindex_all_databases(ConnParams *cparams,
const char *progname, bool echo, bool quiet, bool verbose,
bool concurrently, int concurrentCons,
- const char *tablespace)
+ const char *tablespace, bool outdated)
{
PGconn *conn;
PGresult *result;
@@ -778,7 +870,7 @@ reindex_all_databases(ConnParams *cparams,
reindex_one_database(cparams, REINDEX_DATABASE, NULL,
progname, echo, verbose, concurrently,
- concurrentCons, tablespace);
+ concurrentCons, tablespace, outdated);
}
PQclear(result);
@@ -797,6 +889,7 @@ help(const char *progname)
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
+ printf(_(" --outdated only process indexes having outdated depencies\n"));
printf(_(" -q, --quiet don't write any messages\n"));
printf(_(" -s, --system reindex system catalogs\n"));
printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl
index 159b637230..b60cac6081 100644
--- a/src/bin/scripts/t/090_reindexdb.pl
+++ b/src/bin/scripts/t/090_reindexdb.pl
@@ -3,7 +3,7 @@ use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 58;
+use Test::More tests => 70;
program_help_ok('reindexdb');
program_version_ok('reindexdb');
@@ -174,6 +174,9 @@ $node->command_fails(
$node->command_fails(
[ 'reindexdb', '-j', '2', '-i', 'i1', 'postgres' ],
'parallel reindexdb cannot process indexes');
+$node->command_fails(
+ [ 'reindexdb', '-s', '--outdated' ],
+ 'cannot reindex system catalog and filter indexes having outdated dependencies');
$node->issues_sql_like(
[ 'reindexdb', '-j', '2', 'postgres' ],
qr/statement:\ REINDEX SYSTEM postgres;
@@ -196,3 +199,32 @@ $node->command_checks_all(
qr/^reindexdb: warning: cannot reindex system catalogs concurrently, skipping all/s
],
'parallel reindexdb for system with --concurrently skips catalogs');
+
+# Temporarily downgrade client-min-message to get the no-op report
+$ENV{PGOPTIONS} = '--client-min-messages=NOTICE';
+$node->command_checks_all(
+ [ 'reindexdb', '--outdated', '-v', '-t', 's1.t1', 'postgres' ],
+ 0,
+ [qr/^$/],
+ [qr/table "t1" has no indexes to reindex/],
+ 'verbose reindexdb for outdated dependencies on a specific table reports no-op tables');
+
+$node->command_checks_all(
+ [ 'reindexdb', '--outdated', '-v', '-d', 'postgres' ],
+ 0,
+ [qr/^$/],
+ [qr/^$/],
+ 'verbose reindexdb for outdated dependencies database wide silently ignore all tables');
+$node->command_checks_all(
+ [ 'reindexdb', '--outdated', '-v', '-j', '2', '-d', 'postgres' ],
+ 0,
+ [qr/^$/],
+ [qr/table "t1" has no indexes to reindex/],
+ 'parallel verbose reindexdb for outdated dependencies database wide reports no-op tables');
+
+# Switch back to WARNING client-min-message
+$ENV{PGOPTIONS} = '--client-min-messages=WARNING';
+$node->issues_sql_like(
+ [ 'reindexdb', '--outdated', '-t', 's1.t1', 'postgres' ],
+ qr/.*statement: REINDEX \(OUTDATED\) TABLE s1\.t1;/,
+ 'reindexdb for outdated dependencies specify the OUTDATED keyword');
--
2.30.1
--oao5se3im4osqowp--
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h
@ 2026-01-20 23:04 Jacob Champion <[email protected]>
2026-01-20 23:10 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Jacob Champion @ 2026-01-20 23:04 UTC (permalink / raw)
To: pgsql-hackers
Hi,
This is a tiny followup to https://postgr.es/c/0664aa4ff8 that
enshrines the unused 3.1 protocol version as PG_PROTOCOL_RSRV31. The
patch comes from [1]; I just wanted to give people the opportunity to
bikeshed the name (or object to the move?) before it becomes part of a
public header.
Thanks,
--Jacob
[1] https://postgr.es/m/CAOYmi%2B%3DPMq5wiKjBuOF2_W6JYRnPFYbgp7P-MRa2ymFo89%3D6BQ%40mail.gmail.com
Attachments:
[application/octet-stream] 0001-pqcomm.h-Explicitly-reserve-protocol-v3.1.patch (1.7K, ../../CAOYmi+kKyw=h-5NKqqpc7HC5M30_QmzFx3kgq2AdipyNj47nUw@mail.gmail.com/2-0001-pqcomm.h-Explicitly-reserve-protocol-v3.1.patch)
download | inline diff:
From 4ed03c6a73361b01610373267a843cbef110946d Mon Sep 17 00:00:00 2001
From: Jacob Champion <[email protected]>
Date: Fri, 21 Nov 2025 09:18:40 -0800
Subject: [PATCH] pqcomm.h: Explicitly reserve protocol v3.1
Document this unused version alongside the other special protocol
numbers.
Reviewed-by: Jelte Fennema-Nio <[email protected]>
---
src/include/libpq/pqcomm.h | 6 ++++++
src/interfaces/libpq/fe-protocol3.c | 2 +-
2 files changed, 7 insertions(+), 1 deletion(-)
diff --git a/src/include/libpq/pqcomm.h b/src/include/libpq/pqcomm.h
index 6df6e0f902d..2e24083f462 100644
--- a/src/include/libpq/pqcomm.h
+++ b/src/include/libpq/pqcomm.h
@@ -98,6 +98,12 @@ is_unixsock_path(const char *path)
* Reserved protocol numbers, which have special semantics:
*/
+/*
+ * 3.1 would have collided with old pgbouncer deployments, and was skipped. We
+ * neither emit it nor accept it on the wire.
+ */
+#define PG_PROTOCOL_RSRV31 PG_PROTOCOL(3,1)
+
/*
* A client can send a cancel-current-operation request to the postmaster.
* This is uglier than sending it directly to the client's backend, but it
diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c
index 34518bbe6ea..2d67c496a3d 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -1465,7 +1465,7 @@ pqGetNegotiateProtocolVersion3(PGconn *conn)
}
/* 3.1 never existed, we went straight from 3.0 to 3.2 */
- if (their_version == PG_PROTOCOL(3, 1))
+ if (their_version == PG_PROTOCOL_RSRV31)
{
libpq_append_conn_error(conn, "received invalid protocol negotiation message: server requested downgrade to non-existent 3.1 protocol version");
goto failure;
--
2.34.1
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h
2026-01-20 23:04 [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
@ 2026-01-20 23:10 ` Tom Lane <[email protected]>
2026-01-20 23:17 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-01-20 23:10 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: pgsql-hackers
Jacob Champion <[email protected]> writes:
> This is a tiny followup to https://postgr.es/c/0664aa4ff8 that
> enshrines the unused 3.1 protocol version as PG_PROTOCOL_RSRV31. The
> patch comes from [1]; I just wanted to give people the opportunity to
> bikeshed the name (or object to the move?) before it becomes part of a
> public header.
+1 for concept, but I agree the name needs bikeshedding. "RSRV"
is unreadable, and people might well mentally expand it to
something involving "server", leading to confusion.
How about "PG_PROTOCOL_RESERVED_31" or
"PG_PROTOCOL_UNUSED_31"?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h
2026-01-20 23:04 [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
2026-01-20 23:10 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Tom Lane <[email protected]>
@ 2026-01-20 23:17 ` Jacob Champion <[email protected]>
2026-01-21 07:50 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jelte Fennema-Nio <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Jacob Champion @ 2026-01-20 23:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Tue, Jan 20, 2026 at 3:10 PM Tom Lane <[email protected]> wrote:
> How about "PG_PROTOCOL_RESERVED_31" or
> "PG_PROTOCOL_UNUSED_31"?
I'd be fine with either; slight preference for "RESERVED" I suppose?
Thanks!
--Jacob
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h
2026-01-20 23:04 [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
2026-01-20 23:10 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Tom Lane <[email protected]>
2026-01-20 23:17 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
@ 2026-01-21 07:50 ` Jelte Fennema-Nio <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Jelte Fennema-Nio @ 2026-01-21 07:50 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Wed Jan 21, 2026 at 12:17 AM CET, Jacob Champion wrote:
> I'd be fine with either; slight preference for "RESERVED" I suppose?
RESERVED seems clearer to me. And for people interested in why, the
comment above its definition describes it suffiecently.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-01-21 07:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-24 17:33 [PATCH v5 2/2] Add a --outdated option to reindexdb Julien Rouhaud <[email protected]>
2026-01-20 23:04 [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
2026-01-20 23:10 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Tom Lane <[email protected]>
2026-01-20 23:17 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jacob Champion <[email protected]>
2026-01-21 07:50 ` Re: [PATCH] Reserve protocol 3.1 explicitly in pqcomm.h Jelte Fennema-Nio <[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