From c70f5cb4ef5d5f919036725331ff89a31234d215 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Sat, 16 Mar 2024 17:21:10 -0400 Subject: [PATCH v14 2/2] Enable dumping of table/index stats in pg_dump. For each table/matview/index dumped, it will also generate a statement that calls all of the pg_set_relation_stats() and pg_set_attribute_stats() calls necessary to restore the statistics of the current system onto the destination system. As is the pattern with pg_dump options, this can be disabled with --no-statistics. --- src/bin/pg_dump/pg_backup.h | 2 + src/bin/pg_dump/pg_backup_archiver.c | 5 + src/bin/pg_dump/pg_dump.c | 326 ++++++++++++++++++++++++++- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/pg_dumpall.c | 5 + src/bin/pg_dump/pg_restore.c | 3 + 6 files changed, 340 insertions(+), 2 deletions(-) diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index 9ef2f2017e..1db5cf52eb 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -112,6 +112,7 @@ typedef struct _restoreOptions int no_publications; /* Skip publication entries */ int no_security_labels; /* Skip security label entries */ int no_subscriptions; /* Skip subscription entries */ + int no_statistics; /* Skip statistics import */ int strict_names; const char *filename; @@ -179,6 +180,7 @@ typedef struct _dumpOptions int no_security_labels; int no_publications; int no_subscriptions; + int no_statistics; int no_toast_compression; int no_unlogged_table_data; int serializable_deferrable; diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index d97ebaff5b..d5f61399d9 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -2833,6 +2833,10 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) if (ropt->no_subscriptions && strcmp(te->desc, "SUBSCRIPTION") == 0) return 0; + /* If it's a stats dump, maybe ignore it */ + if (ropt->no_statistics && strcmp(te->desc, "STATISTICS") == 0) + return 0; + /* Ignore it if section is not to be dumped/restored */ switch (curSection) { @@ -2862,6 +2866,7 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) */ if (strcmp(te->desc, "ACL") == 0 || strcmp(te->desc, "COMMENT") == 0 || + strcmp(te->desc, "STATISTICS") == 0 || strcmp(te->desc, "SECURITY LABEL") == 0) { /* Database properties react to createDB, not selectivity options. */ diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index b1c4c3ec7f..29029909e9 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -428,6 +428,7 @@ main(int argc, char **argv) {"no-comments", no_argument, &dopt.no_comments, 1}, {"no-publications", no_argument, &dopt.no_publications, 1}, {"no-security-labels", no_argument, &dopt.no_security_labels, 1}, + {"no-statistics", no_argument, &dopt.no_statistics, 1}, {"no-subscriptions", no_argument, &dopt.no_subscriptions, 1}, {"no-toast-compression", no_argument, &dopt.no_toast_compression, 1}, {"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1}, @@ -1144,6 +1145,7 @@ help(const char *progname) printf(_(" --no-comments do not dump comments\n")); printf(_(" --no-publications do not dump publications\n")); printf(_(" --no-security-labels do not dump security label assignments\n")); + printf(_(" --no-statistics do not dump statistics\n")); printf(_(" --no-subscriptions do not dump subscriptions\n")); printf(_(" --no-table-access-method do not dump table access methods\n")); printf(_(" --no-tablespaces do not dump tablespace assignments\n")); @@ -7001,6 +7003,7 @@ getTables(Archive *fout, int *numTables) /* Tables have data */ tblinfo[i].dobj.components |= DUMP_COMPONENT_DATA; + tblinfo[i].dobj.components |= DUMP_COMPONENT_STATISTICS; /* Mark whether table has an ACL */ if (!PQgetisnull(res, i, i_relacl)) @@ -7498,6 +7501,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid)); indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid)); AssignDumpId(&indxinfo[j].dobj); + indxinfo[j].dobj.components |= DUMP_COMPONENT_STATISTICS; indxinfo[j].dobj.dump = tbinfo->dobj.dump; indxinfo[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_indexname)); indxinfo[j].dobj.namespace = tbinfo->dobj.namespace; @@ -10247,6 +10251,309 @@ dumpComment(Archive *fout, const char *type, catalogId, subid, dumpId, NULL); } +/* + * Convenience routine for constructing parameters of the form: + * paraname => 'value'::type + */ +static void +appendNamedCastedParam(PQExpBuffer str, const char *param, const char *value, + const char *type, Archive *fout) +{ + appendPQExpBuffer(str, "%s => ", param); + appendStringLiteralAH(str, value, fout); + appendPQExpBuffer(str, "::%s", type); +} + +/* + * Append a formatted pg_set_relation_stats statement. + */ +static void +appendSetRelationStats(PQExpBuffer str, const char *qualrelname, + PGresult *res, Archive *fout) +{ + int i_relpages = PQfnumber(res, "relpages"); + int i_reltuples = PQfnumber(res, "relpages"); + int i_relallvisible = PQfnumber(res, "relallvisible"); + char *val; + + if (PQgetisnull(res, 0, i_relpages)) + pg_fatal("Unexpected NULL found in %s", "relpages"); + if (PQgetisnull(res, 0, i_reltuples)) + pg_fatal("Unexpected NULL found in %s", "reltuples"); + if (PQgetisnull(res, 0, i_relallvisible)) + pg_fatal("Unexpected NULL found in %s", "relallvisible"); + + appendPQExpBufferStr(str, "SELECT pg_catalog.pg_set_relation_stats(\n"); + appendNamedCastedParam(str, "relation", + qualrelname, "regclass", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, 0, i_relpages); + appendNamedCastedParam(str, "relpages", val, "integer", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, 0, i_reltuples); + appendNamedCastedParam(str, "reltuples", val, "real", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, 0, i_relallvisible); + appendNamedCastedParam(str, "relallvisible", val, "integer", fout); + appendPQExpBufferStr(str, ");\n"); +} + +/* + * Append a series of formatted pg_set_attribute_stats statements. + */ +static void +appendSetAttributeStats(PQExpBuffer str, const char *qualrelname, + PGresult *res, Archive *fout) +{ + /* these are required */ + int i_attname = PQfnumber(res, "attname"); + int i_inherited = PQfnumber(res, "inherited"); + int i_null_frac = PQfnumber(res, "null_frac"); + int i_avg_width = PQfnumber(res, "avg_width"); + int i_n_distinct = PQfnumber(res, "n_distinct"); + + /* these are optional, can be NULL */ + int i_most_common_vals = PQfnumber(res, "most_common_vals"); + int i_most_common_freqs = PQfnumber(res, "most_common_freqs"); + int i_histogram_bounds = PQfnumber(res, "histogram_bounds"); + int i_correlation = PQfnumber(res, "correlation"); + int i_most_common_elems = PQfnumber(res, "most_common_elems"); + int i_most_common_elem_freqs = PQfnumber(res, "most_common_elem_freqs"); + int i_elem_count_histogram = PQfnumber(res, "elem_count_histogram"); + int i_range_length_histogram = PQfnumber(res, "range_length_histogram"); + int i_range_empty_frac = PQfnumber(res, "range_empty_frac"); + int i_range_bounds_histogram = PQfnumber(res, "range_bounds_histogram"); + + int ntups = PQntuples(res); + + for (int i = 0; i < ntups; i++) + { + char *val; + + if (PQgetisnull(res, i, i_attname)) + pg_fatal("Unexpected NULL found in %s", "attname"); + if (PQgetisnull(res, i, i_inherited)) + pg_fatal("Unexpected NULL found in %s", "inherited"); + if (PQgetisnull(res, i, i_null_frac)) + pg_fatal("Unexpected NULL found in %s", "null_frac"); + if (PQgetisnull(res, i, i_avg_width)) + pg_fatal("Unexpected NULL found in %s", "avg_width"); + if (PQgetisnull(res, i, i_n_distinct)) + pg_fatal("Unexpected NULL found in %s", "n_distinct"); + + appendPQExpBufferStr(str, + "SELECT pg_catalog.pg_set_attribute_stats(\n"); + appendNamedCastedParam(str, "relation", qualrelname, "regclass", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_attname); + appendNamedCastedParam(str, "attname", val, "name", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_inherited); + appendNamedCastedParam(str, "inherited", val, "boolean", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_null_frac); + appendNamedCastedParam(str, "null_frac", val, "real", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_avg_width); + appendNamedCastedParam(str, "avg_width", val, "integer", fout); + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_n_distinct), + appendNamedCastedParam(str, "n_distinct", val, "real", fout); + + /* Optional parameters */ + if (!PQgetisnull(res, i, i_most_common_vals)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_most_common_vals); + appendNamedCastedParam(str, "most_common_vals", val, "text", fout); + } + + if (!PQgetisnull(res, i, i_most_common_freqs)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_most_common_freqs); + appendNamedCastedParam(str, "most_common_freqs", val, "real[]", fout); + } + + if (!PQgetisnull(res, i, i_histogram_bounds)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_histogram_bounds); + appendNamedCastedParam(str, "histogram_bounds", val, "text", fout); + } + + if (!PQgetisnull(res, i, i_correlation)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_correlation); + appendNamedCastedParam(str, "correlation", val, "real", fout); + } + + if (!PQgetisnull(res, i, i_most_common_elems)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_most_common_elems); + appendNamedCastedParam(str, "most_common_elems", val, "text", fout); + } + + if (!PQgetisnull(res, i, i_most_common_elem_freqs)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_most_common_elem_freqs); + appendNamedCastedParam(str, "most_common_elem_freqs", val, "real[]", fout); + } + + if (!PQgetisnull(res, i, i_elem_count_histogram)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_elem_count_histogram); + appendNamedCastedParam(str, "elem_count_histogram", val, "real[]", fout); + } + + if (!PQgetisnull(res, i, i_range_length_histogram)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_range_length_histogram); + appendNamedCastedParam(str, "range_length_histogram", val, "text", fout); + } + + if (!PQgetisnull(res, i, i_range_empty_frac)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_range_empty_frac); + appendNamedCastedParam(str, "range_empty_frac", val, "real", fout); + } + + if (!PQgetisnull(res, i, i_range_bounds_histogram)) + { + appendPQExpBufferStr(str, ",\n"); + val = PQgetvalue(res, i, i_range_bounds_histogram); + appendNamedCastedParam(str, "range_bounds_histogram", val, "text", fout); + } + appendPQExpBufferStr(str, ");\n"); + } +} + +/* + * dumpRelationStats -- + * + * Dump command to import stats into the relation on the new database. + */ +static void +dumpRelationStats(Archive *fout, const DumpableObject *dobj, + const char *reltypename, DumpId dumpid) +{ + const char *nspname = dobj->namespace->dobj.name; + const char *relname = dobj->name; + + static bool prepared = false; + + char *qualrelname = pg_strdup(fmtQualifiedId(nspname, relname)); + PQExpBuffer query = createPQExpBuffer(); /* the extract queries */ + PQExpBuffer out = createPQExpBuffer(); /* the dumped statements */ + PGresult *res; + PQExpBuffer tag = createPQExpBuffer(); + + /* do nothing, if --no-statistics is supplied */ + if (fout->dopt->no_statistics) + return; + + if (!prepared) + { + ArchiveHandle *AH = (ArchiveHandle *) fout; + int ver = PQserverVersion(AH->connection); + + /* Prepare getRelStats */ + appendPQExpBufferStr(query, + "PREPARE getRelStats(pg_catalog.oid) AS\n"); + + if (ver >= fout->minRemoteVersion) + appendPQExpBufferStr(query, + "SELECT c.relpages, c.reltuples, " + "c.relallvisible\n" + "FROM pg_catalog.pg_class AS c\n"); + else + pg_fatal("could not prepare stats export query for " + "server version %d", ver); + + appendPQExpBufferStr(query, "WHERE c.oid = $1"); + + ExecuteSqlStatement(fout, query->data); + + resetPQExpBuffer(query); + + /* Prepare getAttrStats */ + appendPQExpBufferStr(query, + "PREPARE getAttrStats(pg_catalog.oid) AS\n" + "SELECT s.attname, s.inherited, s.null_frac, " + "s.avg_width, s.n_distinct, s.most_common_vals,\n" + "s.most_common_freqs, s.histogram_bounds, " + "s.correlation, s.most_common_elems,\n" + "s.most_common_elem_freqs, " + "s.elem_count_histogram,\n"); + + if (ver >= 170000) + appendPQExpBufferStr(query, + "s.range_length_histogram, " + "s.range_empty_frac, " + "s.range_bounds_histogram\n"); + else if (ver >= fout->minRemoteVersion) + appendPQExpBufferStr(query, + "NULL::text AS range_length_histogram, " + "NULL::real AS range_empty_frac, " + "NULL::text AS range_bounds_histogram\n"); + else + pg_fatal("could not prepare stats export query for " + "server version %d", ver); + + appendPQExpBuffer(query, + "FROM pg_catalog.pg_class AS c\n" + "JOIN pg_catalog.pg_namespace AS n " + "ON n.oid = c.relnamespace\n" + "JOIN pg_catalog.pg_stats AS s " + "ON s.schemaname = n.nspname " + "AND s.tablename = c.relname\n" + "WHERE c.oid = $1"); + + ExecuteSqlStatement(fout, query->data); + + resetPQExpBuffer(query); + + prepared = true; + } + + printfPQExpBuffer(query, "EXECUTE getRelStats('%u')", dobj->catId.oid); + + res = ExecuteSqlQueryForSingleRow(fout, query->data); + + appendSetRelationStats(out, qualrelname, res, fout); + + PQclear(res); + + printfPQExpBuffer(query, "EXECUTE getAttrStats('%u')", dobj->catId.oid); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + appendSetAttributeStats(out, qualrelname, res, fout); + + PQclear(res); + + appendPQExpBuffer(tag, "%s %s", reltypename, fmtId(dobj->name)); + + ArchiveEntry(fout, nilCatalogId, createDumpId(), + ARCHIVE_OPTS(.tag = tag->data, + .namespace = dobj->namespace->dobj.name, + .description = "STATISTICS DATA", + .section = SECTION_NONE, + .createStmt = out->data, + .deps = &dumpid, + .nDeps = 1)); + + destroyPQExpBuffer(query); + destroyPQExpBuffer(out); + destroyPQExpBuffer(tag); +} + /* * dumpTableComment -- * @@ -16681,6 +16988,13 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) if (tbinfo->dobj.dump & DUMP_COMPONENT_SECLABEL) dumpTableSecLabel(fout, tbinfo, reltypename); + /* Statistics are dependent on the definition, not the data */ + /* Views don't have stats */ + if ((tbinfo->dobj.dump & DUMP_COMPONENT_STATISTICS) && + (tbinfo->relkind != RELKIND_VIEW)) + dumpRelationStats(fout, &tbinfo->dobj, reltypename, + tbinfo->dobj.dumpId); + /* Dump comments on inlined table constraints */ for (j = 0; j < tbinfo->ncheck; j++) { @@ -16882,6 +17196,7 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo) PQExpBuffer delq; char *qindxname; char *qqindxname; + DumpId dumpid; /* Do nothing in data-only dump */ if (dopt->dataOnly) @@ -16994,14 +17309,21 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo) free(indstatvalsarray); } + /* Comments and stats share same .dep */ + dumpid = is_constraint ? indxinfo->indexconstraint : + indxinfo->dobj.dumpId; + /* Dump Index Comments */ if (indxinfo->dobj.dump & DUMP_COMPONENT_COMMENT) dumpComment(fout, "INDEX", qindxname, tbinfo->dobj.namespace->dobj.name, tbinfo->rolname, indxinfo->dobj.catId, 0, - is_constraint ? indxinfo->indexconstraint : - indxinfo->dobj.dumpId); + dumpid); + + /* Dump Index Stats */ + if (indxinfo->dobj.dump & DUMP_COMPONENT_STATISTICS) + dumpRelationStats(fout, &indxinfo->dobj, "INDEX", dumpid); destroyPQExpBuffer(q); destroyPQExpBuffer(delq); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 9bc93520b4..d6a071ec28 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -101,6 +101,7 @@ typedef uint32 DumpComponents; #define DUMP_COMPONENT_ACL (1 << 4) #define DUMP_COMPONENT_POLICY (1 << 5) #define DUMP_COMPONENT_USERMAP (1 << 6) +#define DUMP_COMPONENT_STATISTICS (1 << 7) #define DUMP_COMPONENT_ALL (0xFFFF) /* diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 046c0dc3b3..69652aa205 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -105,6 +105,7 @@ static int use_setsessauth = 0; static int no_comments = 0; static int no_publications = 0; static int no_security_labels = 0; +static int no_statistics = 0; static int no_subscriptions = 0; static int no_toast_compression = 0; static int no_unlogged_table_data = 0; @@ -174,6 +175,7 @@ main(int argc, char *argv[]) {"no-role-passwords", no_argument, &no_role_passwords, 1}, {"no-security-labels", no_argument, &no_security_labels, 1}, {"no-subscriptions", no_argument, &no_subscriptions, 1}, + {"no-statistics", no_argument, &no_statistics, 1}, {"no-sync", no_argument, NULL, 4}, {"no-toast-compression", no_argument, &no_toast_compression, 1}, {"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1}, @@ -453,6 +455,8 @@ main(int argc, char *argv[]) appendPQExpBufferStr(pgdumpopts, " --no-publications"); if (no_security_labels) appendPQExpBufferStr(pgdumpopts, " --no-security-labels"); + if (no_statistics) + appendPQExpBufferStr(pgdumpopts, " --no-statistics"); if (no_subscriptions) appendPQExpBufferStr(pgdumpopts, " --no-subscriptions"); if (no_toast_compression) @@ -668,6 +672,7 @@ help(void) printf(_(" --no-publications do not dump publications\n")); printf(_(" --no-role-passwords do not dump passwords for roles\n")); printf(_(" --no-security-labels do not dump security label assignments\n")); + printf(_(" --no-statistics do not dump statistics\n")); printf(_(" --no-subscriptions do not dump subscriptions\n")); printf(_(" --no-sync do not wait for changes to be written safely to disk\n")); printf(_(" --no-table-access-method do not dump table access methods\n")); diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index c3beacdec1..2d326dec72 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -75,6 +75,7 @@ main(int argc, char **argv) static int no_publications = 0; static int no_security_labels = 0; static int no_subscriptions = 0; + static int no_statistics = 0; static int strict_names = 0; struct option cmdopts[] = { @@ -126,6 +127,7 @@ main(int argc, char **argv) {"no-security-labels", no_argument, &no_security_labels, 1}, {"no-subscriptions", no_argument, &no_subscriptions, 1}, {"filter", required_argument, NULL, 4}, + {"no-statistics", no_argument, &no_statistics, 1}, {NULL, 0, NULL, 0} }; @@ -358,6 +360,7 @@ main(int argc, char **argv) opts->no_publications = no_publications; opts->no_security_labels = no_security_labels; opts->no_subscriptions = no_subscriptions; + opts->no_statistics = no_statistics; if (if_exists && !opts->dropSchema) pg_fatal("option --if-exists requires option -c/--clean"); -- 2.44.0