From b2c9936a7156926ace70b89ffb9cfbe1e3b9a559 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 29 Dec 2023 10:57:11 -0500 Subject: [PATCH] In pg_dump, don't dump a stats object unless dumping underlying table. If the underlying table isn't being dumped, it's useless to dump an extended statistics object; it'll just cause errors at restore. We have always applied similar policies to, say, indexes. (When and if we get cross-table stats objects, it might be profitable to think a little harder about what to do with them. But for now there seems no point in considering a stats object as anything but an appendage of its table.) Rian McGuire and Tom Lane, per report from Rian McGuire. Back-patch to supported branches. Discussion: https://postgr.es/m/7075d3aa-3f05-44a5-b68f-47dc6a8a0550@buildkite.com --- src/bin/pg_dump/pg_dump.c | 30 +++++++++++++++++++++++++++--- src/bin/pg_dump/pg_dump.h | 3 ++- src/bin/pg_dump/t/002_pg_dump.pl | 5 +++-- 3 files changed, 32 insertions(+), 6 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 129dde9bbf..6e1b070e6f 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -2030,6 +2030,26 @@ selectDumpablePublicationObject(DumpableObject *dobj, Archive *fout) } /* + * selectDumpableStatisticsObject: policy-setting subroutine + * Mark an extended statistics object as to be dumped or not + * + * We dump an extended statistics object if the schema it's in and the table + * it's for are being dumped. (This'll need more thought if statistics + * objects ever support cross-table stats.) + */ +static void +selectDumpableStatisticsObject(StatsExtInfo *sobj, Archive *fout) +{ + if (checkExtensionMembership(&sobj->dobj, fout)) + return; /* extension membership overrides all else */ + + sobj->dobj.dump = sobj->dobj.namespace->dobj.dump_contains; + if (sobj->stattable == NULL || + !(sobj->stattable->dobj.dump & DUMP_COMPONENT_DEFINITION)) + sobj->dobj.dump = DUMP_COMPONENT_NONE; +} + +/* * selectDumpableObject: policy-setting subroutine * Mark a generic dumpable object as to be dumped or not * @@ -7269,6 +7289,7 @@ getExtendedStatistics(Archive *fout) int i_stxname; int i_stxnamespace; int i_stxowner; + int i_stxrelid; int i_stattarget; int i; @@ -7280,11 +7301,11 @@ getExtendedStatistics(Archive *fout) if (fout->remoteVersion < 130000) appendPQExpBufferStr(query, "SELECT tableoid, oid, stxname, " - "stxnamespace, stxowner, (-1) AS stxstattarget " + "stxnamespace, stxowner, stxrelid, (-1) AS stxstattarget " "FROM pg_catalog.pg_statistic_ext"); else appendPQExpBufferStr(query, "SELECT tableoid, oid, stxname, " - "stxnamespace, stxowner, stxstattarget " + "stxnamespace, stxowner, stxrelid, stxstattarget " "FROM pg_catalog.pg_statistic_ext"); res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); @@ -7296,6 +7317,7 @@ getExtendedStatistics(Archive *fout) i_stxname = PQfnumber(res, "stxname"); i_stxnamespace = PQfnumber(res, "stxnamespace"); i_stxowner = PQfnumber(res, "stxowner"); + i_stxrelid = PQfnumber(res, "stxrelid"); i_stattarget = PQfnumber(res, "stxstattarget"); statsextinfo = (StatsExtInfo *) pg_malloc(ntups * sizeof(StatsExtInfo)); @@ -7310,10 +7332,12 @@ getExtendedStatistics(Archive *fout) statsextinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_stxnamespace))); statsextinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_stxowner)); + statsextinfo[i].stattable = + findTableByOid(atooid(PQgetvalue(res, i, i_stxrelid))); statsextinfo[i].stattarget = atoi(PQgetvalue(res, i, i_stattarget)); /* Decide whether we want to dump it */ - selectDumpableObject(&(statsextinfo[i].dobj), fout); + selectDumpableStatisticsObject(&(statsextinfo[i]), fout); } PQclear(res); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index f73132bab4..f9878ebd16 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -418,7 +418,8 @@ typedef struct _indexAttachInfo typedef struct _statsExtInfo { DumpableObject dobj; - const char *rolname; + const char *rolname; /* owner */ + TableInfo *stattable; /* link to table the stats are for */ int stattarget; /* statistics target */ } StatsExtInfo; diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index d9c117f068..b633b95f0d 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3731,14 +3731,15 @@ my %tests = ( 'CREATE STATISTICS extended_stats_no_options' => { create_order => 97, create_sql => 'CREATE STATISTICS dump_test.test_ext_stats_no_options - ON col1, col2 FROM dump_test.test_fifth_table', + ON col1, col2 FROM dump_test.test_table', regexp => qr/^ - \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON col1, col2 FROM dump_test.test_fifth_table;\E + \QCREATE STATISTICS dump_test.test_ext_stats_no_options ON col1, col2 FROM dump_test.test_table;\E /xms, like => { %full_runs, %dump_test_schema_runs, section_post_data => 1, }, unlike => { exclude_dump_test_schema => 1, + exclude_test_table => 1, only_dump_measurement => 1, }, }, -- 2.11.4.GIT