From bedf3ff9be5f26ed435f90e6db8405a5e7e32e67 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Tue, 12 Dec 2023 22:25:51 -0500 Subject: [PATCH v3 4/9] Add pg_export_stats, pg_import_stats. pg_export_stats is used to export stats from databases as far back as v10. The output is currently only to stdout and should be redirected to a file in most use cases. pg_import_stats is used to import stats to any version that has the function pg_import_rel_stats(). --- src/bin/scripts/.gitignore | 2 + src/bin/scripts/Makefile | 6 +- src/bin/scripts/pg_export_stats.c | 298 +++++++++++++++++++++++++++++ src/bin/scripts/pg_import_stats.c | 303 ++++++++++++++++++++++++++++++ 4 files changed, 608 insertions(+), 1 deletion(-) create mode 100644 src/bin/scripts/pg_export_stats.c create mode 100644 src/bin/scripts/pg_import_stats.c diff --git a/src/bin/scripts/.gitignore b/src/bin/scripts/.gitignore index 0f23fe0004..1b9addb339 100644 --- a/src/bin/scripts/.gitignore +++ b/src/bin/scripts/.gitignore @@ -6,5 +6,7 @@ /reindexdb /vacuumdb /pg_isready +/pg_export_stats +/pg_import_stats /tmp_check/ diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile index 20db40b103..a019894d84 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 pg_import_stats override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) LDFLAGS_INTERNAL += -L$(top_builddir)/src/fe_utils -lpgfeutils $(libpq_pgport) @@ -31,6 +31,8 @@ clusterdb: clusterdb.o common.o $(WIN32RES) | submake-libpq submake-libpgport su vacuumdb: vacuumdb.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils reindexdb: reindexdb.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils pg_isready: pg_isready.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils +pg_export_stats: pg_export_stats.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils +pg_import_stats: pg_import_stats.o common.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils install: all installdirs $(INSTALL_PROGRAM) createdb$(X) '$(DESTDIR)$(bindir)'/createdb$(X) @@ -41,6 +43,8 @@ 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) + $(INSTALL_PROGRAM) pg_import_stats$(X) '$(DESTDIR)$(bindir)'/pg_import_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..abb3659e20 --- /dev/null +++ b/src/bin/scripts/pg_export_stats.c @@ -0,0 +1,298 @@ +/*------------------------------------------------------------------------- + * + * pg_export_stats + * + * Portions Copyright (c) 1996-2023, 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/string_utils.h" + +static void help(const char *progname); + +/* view definition introduced in 17 */ +const char *export_query_v17 = + "SELECT schemaname, relname, server_version_num, n_tuples, " + "n_pages, stats FROM pg_statistic_export "; + +/* + * Versions 10-16 have the same stats layout, but lack the view definition, + * so extracting the view definition ad using it as-is will work. + */ +const char *export_query_v10 = + "SELECT " + " n.nspname AS schemaname, " + " r.relname AS relname, " + " current_setting('server_version_num')::integer AS server_version_num, " + " r.reltuples::float4 AS n_tuples, " + " r.relpages::integer AS n_pages, " + " ( " + " SELECT " + " jsonb_object_agg( " + " CASE " + " WHEN a.stainherit THEN 'inherited' " + " ELSE 'regular' " + " END, " + " a.stats " + " ) " + " FROM " + " ( " + " SELECT " + " s.stainherit, " + " jsonb_object_agg( " + " a.attname, " + " jsonb_build_object( " + " 'stanullfrac', s.stanullfrac::text, " + " 'stawidth', s.stawidth::text, " + " 'stadistinct', s.stadistinct::text, " + " 'stakinds', " + " ( " + " SELECT " + " jsonb_agg( " + " CASE kind.kind " + " WHEN 0 THEN 'TRIVIAL' " + " WHEN 1 THEN 'MCV' " + " WHEN 2 THEN 'HISTOGRAM' " + " WHEN 3 THEN 'CORRELATION' " + " WHEN 4 THEN 'MCELEM' " + " WHEN 5 THEN 'DECHIST' " + " WHEN 6 THEN 'RANGE_LENGTH_HISTOGRAM' " + " WHEN 7 THEN 'BOUNDS_HISTOGRAM' " + " END::text " + " ORDER BY kind.ord) " + " FROM unnest(ARRAY[s.stakind1, s.stakind2, " + " s.stakind3, stakind4, " + " s.stakind5]) " + " WITH ORDINALITY AS kind(kind, ord) " + " ), " + " 'stanumbers', " + " jsonb_build_array( " + " s.stanumbers1::text, " + " s.stanumbers2::text, " + " s.stanumbers3::text, " + " s.stanumbers4::text, " + " s.stanumbers5::text), " + " 'stavalues', " + " jsonb_build_array( " + " s.stavalues1::text, " + " s.stavalues2::text, " + " s.stavalues3::text, " + " s.stavalues4::text, " + " s.stavalues5::text) " + " ) " + " ) AS stats " + " FROM pg_attribute AS a " + " JOIN pg_statistic AS s " + " ON s.starelid = a.attrelid " + " AND s.staattnum = a.attnum " + " WHERE a.attrelid = r.oid " + " AND NOT a.attisdropped " + " AND a.attnum > 0 " + " AND has_column_privilege(a.attrelid, a.attnum, 'SELECT') " + " GROUP BY s.stainherit " + " ) AS a " + " ) AS stats " + "FROM pg_class AS r " + "JOIN pg_namespace AS n " + " ON n.oid = r.relnamespace " + "WHERE relkind IN ('r', 'm', 'f', 'p', 'i') " + "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') "; + +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'}, + {NULL, 0, NULL, 0} + }; + + const char *progname; + int optindex; + int c; + + 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; + + FILE *copystream = stdout; + + PGresult *result; + + ExecStatusType result_status; + + char *buf; + int ret; + + pg_logging_init(argv[0]); + progname = get_progname(argv[0]); + set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); + + handle_help_version_opts(argc, argv, "clusterdb", help); + + while ((c = getopt_long(argc, argv, "d:eh:p:U:wW", 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 'p': + port = pg_strdup(optarg); + break; + case 'U': + username = pg_strdup(optarg); + 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); + + initPQExpBuffer(&sql); + + appendPQExpBufferStr(&sql, "COPY ("); + + if (PQserverVersion(conn) >= 170000) + appendPQExpBufferStr(&sql, export_query_v17); + else if (PQserverVersion(conn) >= 100000) + appendPQExpBufferStr(&sql, export_query_v10); + else + pg_fatal("exporting statistics from databases prior to version 10 not supported"); + + appendPQExpBufferStr(&sql, ") TO STDOUT"); + + result = PQexec(conn, sql.data); + result_status = PQresultStatus(result); + + if (result_status != PGRES_COPY_OUT) + pg_fatal("malformed copy command"); + + for (;;) + { + ret = PQgetCopyData(conn, &buf, 0); + + if (ret < 0) + break; /* done or server/connection error */ + + if (buf) + { + if (copystream && fwrite(buf, 1, ret, copystream) != ret) + pg_fatal("could not write COPY data: %m"); + PQfreemem(buf); + } + } + + if (copystream && fflush(copystream)) + pg_fatal("could not write COPY data: %m"); + + if (ret == -2) + pg_fatal("COPY data transfer failed: %s", PQerrorMessage(conn)); + + PQfinish(conn); + termPQExpBuffer(&sql); + exit(0); +} + + +static void +help(const char *progname) +{ + printf(_("%s clusters all previously clustered tables in a database.\n\n"), progname); + printf(_("Usage:\n")); + printf(_(" %s [OPTION]... [DBNAME]\n"), progname); + printf(_("\nOptions:\n")); + printf(_(" -d, --dbname=DBNAME database to cluster\n")); + printf(_(" -e, --echo show the commands being sent to the server\n")); + printf(_(" -t, --table=TABLE cluster specific table(s) only\n")); + printf(_(" -V, --version output version information, then exit\n")); + printf(_(" -?, --help show this help, then exit\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(_("\nRead the description of the SQL command CLUSTER for details.\n")); + printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT); + printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL); +} diff --git a/src/bin/scripts/pg_import_stats.c b/src/bin/scripts/pg_import_stats.c new file mode 100644 index 0000000000..122afc0971 --- /dev/null +++ b/src/bin/scripts/pg_import_stats.c @@ -0,0 +1,303 @@ +/*------------------------------------------------------------------------- + * + * pg_import_stats + * + * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/bin/scripts/pg_import_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/string_utils.h" + +#define COPY_BUF_LEN 8192 + +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'}, + {"quiet", no_argument, NULL, 'q'}, + {"dbname", required_argument, NULL, 'd'}, + {NULL, 0, NULL, 0} + }; + + const char *progname; + int optindex; + int c; + + const char *dbname = NULL; + char *host = NULL; + char *port = NULL; + char *username = NULL; + enum trivalue prompt_password = TRI_DEFAULT; + ConnParams cparams; + bool quiet = false; + + PGconn *conn; + + FILE *copysrc= stdin; + + PGresult *result; + + int i; + int numtables; + + pg_logging_init(argv[0]); + progname = get_progname(argv[0]); + set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); + + handle_help_version_opts(argc, argv, "clusterdb", help); + + while ((c = getopt_long(argc, argv, "d:h:p:qU:wW", long_options, &optindex)) != -1) + { + switch (c) + { + case 'd': + dbname = pg_strdup(optarg); + break; + case 'h': + host = pg_strdup(optarg); + break; + case 'p': + port = pg_strdup(optarg); + break; + case 'q': + quiet = true; + break; + case 'U': + username = pg_strdup(optarg); + 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, false, false, true); + + /* open file */ + + /* iterate over records */ + + + result = PQexec(conn, + "CREATE TEMPORARY TABLE import_stats ( " + "id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " + "schemaname text, relname text, server_version_num integer, " + "n_tuples float4, n_pages integer, stats jsonb )"); + + if (PQresultStatus(result) != PGRES_COMMAND_OK) + pg_fatal("could not create temporary file: %s", PQerrorMessage(conn)); + + PQclear(result); + + result = PQexec(conn, + "COPY import_stats(schemaname, relname, server_version_num, n_tuples, " + "n_pages, stats) FROM STDIN"); + + if (PQresultStatus(result) != PGRES_COPY_IN) + pg_fatal("error copying data to import_stats: %s", PQerrorMessage(conn)); + + for (;;) + { + char copybuf[COPY_BUF_LEN]; + + int numread = fread(copybuf, 1, COPY_BUF_LEN, copysrc); + + if (ferror(copysrc)) + pg_fatal("error reading from source"); + + if (numread == 0) + break; + + if (PQputCopyData(conn, copybuf, numread) == -1) + pg_fatal("eror during copy: %s", PQerrorMessage(conn)); + } + + if (PQputCopyEnd(conn, NULL) == -1) + pg_fatal("eror during copy: %s", PQerrorMessage(conn)); + fclose(copysrc); + + result = PQgetResult(conn); + + if (PQresultStatus(result) != PGRES_COMMAND_OK) + pg_fatal("error copying data to import_stats: %s", PQerrorMessage(conn)); + + numtables = atol(PQcmdTuples(result)); + + PQclear(result); + + result = PQprepare(conn, "import", + "SELECT pg_import_rel_stats(c.oid, s.server_version_num, " + " s.n_tuples, s.n_pages, s.stats) as import_result " + "FROM import_stats AS s " + "JOIN pg_namespace AS n ON n.nspname = s.schemaname " + "JOIN pg_class AS c ON c.relnamespace = n.oid " + " AND c.relname = s.relname " + "WHERE s.id = $1::bigint ", + 1, NULL); + + if (PQresultStatus(result) != PGRES_COMMAND_OK) + pg_fatal("error in PREPARE: %s", PQerrorMessage(conn)); + + PQclear(result); + + if (!quiet) + { + result = PQprepare(conn, "echo", + "SELECT s.schemaname, s.relname " + "FROM import_stats AS s " + "WHERE s.id = $1::bigint ", + 1, NULL); + + if (PQresultStatus(result) != PGRES_COMMAND_OK) + pg_fatal("error in PREPARE: %s", PQerrorMessage(conn)); + + PQclear(result); + } + + for (i = 1; i <= numtables; i++) + { + char istr[32]; + char *schema = NULL; + char *table = NULL; + + const char *const values[] = {istr}; + + snprintf(istr, 32, "%d", i); + + if (!quiet) + { + result = PQexecPrepared(conn, "echo", 1, values, NULL, NULL, 0); + schema = pg_strdup(PQgetvalue(result, 0, 0)); + table = pg_strdup(PQgetvalue(result, 0, 1)); + } + + PQclear(result); + + result = PQexecPrepared(conn, "import", 1, values, NULL, NULL, 0); + + if (quiet) + { + PQclear(result); + continue; + } + + if (PQresultStatus(result) == PGRES_TUPLES_OK) + { + int rows = PQntuples(result); + + if (rows == 1) + { + char *retval = PQgetvalue(result, 0, 0); + if (*retval == 't') + printf("%s.%s: imported\n", schema, table); + else + printf("%s.%s: failed\n", schema, table); + } + else if (rows == 0) + printf("%s.%s: not found\n", schema, table); + else + pg_fatal("import function must return 0 or 1 rows"); + } + else + printf("%s.%s: error: %s\n", schema, table, PQerrorMessage(conn)); + + if (schema != NULL) + pfree(schema); + + if (table != NULL) + pfree(table); + + PQclear(result); + } + + exit(0); +} + + +static void +help(const char *progname) +{ + printf(_("%s clusters all previously clustered tables in a database.\n\n"), progname); + printf(_("Usage:\n")); + printf(_(" %s [OPTION]... [DBNAME]\n"), progname); + printf(_("\nOptions:\n")); + printf(_(" -d, --dbname=DBNAME database to cluster\n")); + printf(_(" -q, --quiet don't write any messages\n")); + printf(_(" -t, --table=TABLE cluster specific table(s) only\n")); + printf(_(" -V, --version output version information, then exit\n")); + printf(_(" -?, --help show this help, then exit\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(_("\nRead the description of the SQL command CLUSTER for details.\n")); + printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT); + printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL); +} -- 2.43.0