From fa020383ac1811fb02997b53f0551a8d3ae26329 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 20 Feb 2024 01:14:54 -0500 Subject: [PATCH v6 4/4] Add pg_export_stats. This is a command-line utility that connects to a database and exports statistics from all user relations and user statistics objects, printing SQL statements designed to re-import those statistics into a like-named object. --- src/fe_utils/stats_export.c | 2 +- src/bin/scripts/.gitignore | 1 + src/bin/scripts/Makefile | 3 +- src/bin/scripts/pg_export_stats.c | 267 ++++++++++++++++++++++++++++++ 4 files changed, 271 insertions(+), 2 deletions(-) create mode 100644 src/bin/scripts/pg_export_stats.c diff --git a/src/fe_utils/stats_export.c b/src/fe_utils/stats_export.c index 1815793c9d..09b4eb240d 100644 --- a/src/fe_utils/stats_export.c +++ b/src/fe_utils/stats_export.c @@ -835,7 +835,7 @@ stats_export_print_rel_import(FILE *outf, fprintf(outf, "SELECT pg_catalog.pg_import_rel_stats(r.oid, %s::jsonb, %s, %s) " - "FROM pg_catalog.pg_catalog.pg_class AS r " + "FROM pg_catalog.pg_class AS r " "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace " "WHERE n.nspname = %s " "AND r.relname = %s;\n\n", diff --git a/src/bin/scripts/.gitignore b/src/bin/scripts/.gitignore index 0f23fe0004..76704a3ad4 100644 --- a/src/bin/scripts/.gitignore +++ b/src/bin/scripts/.gitignore @@ -6,5 +6,6 @@ /reindexdb /vacuumdb /pg_isready +/pg_export_stats /tmp_check/ diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile index 9633c99136..dd54faaf2a 100644 --- a/src/bin/scripts/Makefile +++ b/src/bin/scripts/Makefile @@ -16,7 +16,7 @@ subdir = src/bin/scripts top_builddir = ../../.. include $(top_builddir)/src/Makefile.global -PROGRAMS = createdb createuser dropdb dropuser clusterdb vacuumdb reindexdb pg_isready +PROGRAMS = createdb createuser dropdb dropuser clusterdb vacuumdb reindexdb pg_isready pg_export_stats override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) LDFLAGS_INTERNAL += -L$(top_builddir)/src/fe_utils -lpgfeutils $(libpq_pgport) @@ -41,6 +41,7 @@ install: all installdirs $(INSTALL_PROGRAM) vacuumdb$(X) '$(DESTDIR)$(bindir)'/vacuumdb$(X) $(INSTALL_PROGRAM) reindexdb$(X) '$(DESTDIR)$(bindir)'/reindexdb$(X) $(INSTALL_PROGRAM) pg_isready$(X) '$(DESTDIR)$(bindir)'/pg_isready$(X) + $(INSTALL_PROGRAM) pg_export_stats$(X) '$(DESTDIR)$(bindir)'/pg_export_stats$(X) installdirs: $(MKDIR_P) '$(DESTDIR)$(bindir)' diff --git a/src/bin/scripts/pg_export_stats.c b/src/bin/scripts/pg_export_stats.c new file mode 100644 index 0000000000..7fce1ef515 --- /dev/null +++ b/src/bin/scripts/pg_export_stats.c @@ -0,0 +1,267 @@ +/*------------------------------------------------------------------------- + * + * pg_export_stats + * + * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/bin/scripts/pg_export_stats.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres_fe.h" +#include "common.h" +#include "common/logging.h" +#include "fe_utils/cancel.h" +#include "fe_utils/option_utils.h" +#include "fe_utils/query_utils.h" +#include "fe_utils/simple_list.h" +#include "fe_utils/stats_export.h" +#include "fe_utils/string_utils.h" + +static void help(const char *progname); + +int +main(int argc, char *argv[]) +{ + static struct option long_options[] = { + {"host", required_argument, NULL, 'h'}, + {"port", required_argument, NULL, 'p'}, + {"username", required_argument, NULL, 'U'}, + {"no-password", no_argument, NULL, 'w'}, + {"password", no_argument, NULL, 'W'}, + {"echo", no_argument, NULL, 'e'}, + {"dbname", required_argument, NULL, 'd'}, + {"validate", no_argument, NULL, 'v'}, + {"require-match", no_argument, NULL, 'm'}, + {NULL, 0, NULL, 0} + }; + + const char *progname; + int optindex; + int c; + + bool validate = false; + bool require_match = false; + const char *dbname = NULL; + char *host = NULL; + char *port = NULL; + char *username = NULL; + enum trivalue prompt_password = TRI_DEFAULT; + ConnParams cparams; + bool echo = false; + + PQExpBufferData sql; + + PGconn *conn; + int server_version_num; + + PGresult *result; + + ExecStatusType result_status; + + pg_logging_init(argv[0]); + progname = get_progname(argv[0]); + set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); + + handle_help_version_opts(argc, argv, "pg_export_stats", help); + + while ((c = getopt_long(argc, argv, "d:eh:mp:U:vwW", long_options, &optindex)) != -1) + { + switch (c) + { + case 'd': + dbname = pg_strdup(optarg); + break; + case 'e': + echo = true; + break; + case 'h': + host = pg_strdup(optarg); + break; + case 'm': + require_match = true; + break; + case 'p': + port = pg_strdup(optarg); + break; + case 'U': + username = pg_strdup(optarg); + break; + case 'v': + validate = true; + break; + case 'w': + prompt_password = TRI_NO; + break; + case 'W': + prompt_password = TRI_YES; + break; + default: + /* getopt_long already emitted a complaint */ + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + } + + /* + * Non-option argument specifies database name as long as it wasn't + * already specified with -d / --dbname + */ + if (optind < argc && dbname == NULL) + { + dbname = argv[optind]; + optind++; + } + + if (optind < argc) + { + pg_log_error("too many command-line arguments (first is \"%s\")", + argv[optind]); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + + /* fill cparams except for dbname, which is set below */ + cparams.pghost = host; + cparams.pgport = port; + cparams.pguser = username; + cparams.prompt_password = prompt_password; + cparams.override_dbname = NULL; + + setup_cancel_handler(NULL); + + if (dbname == NULL) + { + if (getenv("PGDATABASE")) + dbname = getenv("PGDATABASE"); + else if (getenv("PGUSER")) + dbname = getenv("PGUSER"); + else + dbname = get_user_name_or_exit(progname); + } + + cparams.dbname = dbname; + + conn = connectDatabase(&cparams, progname, echo, false, true); + + server_version_num = PQserverVersion(conn); + + initPQExpBuffer(&sql); + + /* + * Query catalog for relations, export statistics from each of them, + * and generate a SQL statement to re-import those statistics. + */ + appendPQExpBufferStr(&sql, + "SELECT quote_literal(b.nspname), quote_literal(b.relname), " + " quote_literal(b.stats_json) " + "FROM ( SELECT n.nspname, r.relname, "); + + appendPQExpBufferStr(&sql, stats_export_rel_query(server_version_num)); + + appendPQExpBufferStr(&sql, + "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') " + "AND r.relpersistence = 'p' " + "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') " + "ORDER BY n.nspname, r.relname" + ") AS b"); + + if (echo) + printf("%s\n", sql.data); + + result = PQexec(conn, sql.data); + result_status = PQresultStatus(result); + + if (result_status != PGRES_TUPLES_OK) + pg_fatal("malformed catalog query: %s", PQerrorMessage(conn)); + else + { + int nrows = PQntuples(result); + int i; + + for (i = 0; i < nrows; i++) + stats_export_print_rel_import(stdout, + PQgetvalue(result, i, 0), + PQgetvalue(result, i, 1), + PQgetvalue(result, i, 2), + validate, + require_match); + } + + PQclear(result); + resetPQExpBuffer(&sql); + + /* + * Query catalog for statistics objects, export statistics from each of + * them, and generate a SQL statement to re-import those statistics. + */ + appendPQExpBufferStr(&sql, + "SELECT quote_literal(b.nspname), quote_literal(b.relname), " + " quote_literal(b.stxname), quote_literal(b.ext_stats_json) " + "FROM ( SELECT n.nspname, r.relname, e.stxname, "); + + appendPQExpBufferStr(&sql, stats_export_ext_query(server_version_num)); + + appendPQExpBufferStr(&sql, + "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') " + "AND r.relpersistence = 'p' " + "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') " + "ORDER BY n.nspname, r.relname, e.stxname " + ") AS b "); + + if (echo) + printf("%s\n", sql.data); + + result = PQexec(conn, sql.data); + result_status = PQresultStatus(result); + + if (result_status != PGRES_TUPLES_OK) + pg_fatal("malformed catalog query: %s", PQerrorMessage(conn)); + else + { + int nrows = PQntuples(result); + int i; + + for (i = 0; i < nrows; i++) + stats_export_print_ext_import(stdout, + PQgetvalue(result, i, 0), + PQgetvalue(result, i, 1), + PQgetvalue(result, i, 2), + PQgetvalue(result, i, 3), + validate, + require_match); + } + + PQclear(result); + + PQfinish(conn); + termPQExpBuffer(&sql); + exit(0); +} + + +static void +help(const char *progname) +{ + printf(_("%s export statistics for all objects in a database.\n\n"), progname); + printf(_("Usage:\n")); + printf(_(" %s [OPTION]... [DBNAME]\n"), progname); + printf(_("\nOptions:\n")); + printf(_(" -d, --dbname=DBNAME database to export\n")); + printf(_(" -e, --echo show the commands being sent to the server\n")); + printf(_(" -V, --version output version information, then exit\n")); + printf(_(" -?, --help show this help, then exit\n")); + printf(_(" -v, --validate Import calls generated should set the validate flag\n")); + printf(_(" -m, --require-match Import calls generated should set the require_match flag\n")); + printf(_("\nConnection options:\n")); + printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); + printf(_(" -p, --port=PORT database server port\n")); + printf(_(" -U, --username=USERNAME user name to connect as\n")); + printf(_(" -w, --no-password never prompt for password\n")); + printf(_(" -W, --password force password prompt\n")); + printf(_(" --maintenance-db=DBNAME alternate maintenance database\n")); + printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT); + printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL); +} -- 2.43.2