public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Davis <[email protected]>
To: Corey Huinker <[email protected]>
Cc: Robert Treat <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Nathan Bossart <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: jian he <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Matthias van de Meent <[email protected]>
Cc: Magnus Hagander <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: [email protected]
Subject: Re: Statistics Import and Export
Date: Fri, 28 Mar 2025 22:29:16 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CADkLM=desCuf3dVHasADvdUVRmb-5gO0mhMO5u9nzgv6i7U86Q@mail.gmail.com>
References: <y2d36zf6pl3n6ni6e4ctblu3a5iazi2dycknq53h2mjvp7lto4@hhww3h2qgdrw>
<[email protected]>
<CADkLM=cG8=gde_KjGiQC_wt0K3knJCy_mwvd-sW=f=3M2eSrEg@mail.gmail.com>
<rp6int5sqs5bn7g35brqz7w3t7il6tn2dgpm7tr3pev626wnc4@fi7a3enofhct>
<Z8j8HXlIIGYPA9L8@nathan>
<CADkLM=e-H5H+pY8iA58ie28PVSRJ1HBpzyy1=FWmy0Hm+s4boQ@mail.gmail.com>
<6rpmhyrtci4epuzay7y5xvd2cwdwb6zmtt6ofxrvakyawxhm7s@6grajbpr4kij>
<CADkLM=ct+i2G=Z+c_8np1+C75uvzjUK_SBsouUvgWMHRs2cnyg@mail.gmail.com>
<xe3yjkcthy7f377zjayuckxyd62z5cekgjau3j4vp6elbicson@cx2uiilwmxyy>
<CADkLM=f1n2_Vomq0gKab7xdxDHmJGgn=DE48P8fzQOp3Mrs1Qg@mail.gmail.com>
<pbjbvxnq36xo2ooufcnlfqsqnxuvggbqxbx3myjtswokshfjbl@hfihx57z4j4t>
<CA+TgmoYGuEC=E4TbomRyqO8+Uav=90G0dYXw=rA1d5UmRB76Yg@mail.gmail.com>
<[email protected]>
<CABV9wwO5v8Nu8q+xWexMdL3Z+2xS=fFJMQetBSHy3tR64wNHOA@mail.gmail.com>
<[email protected]>
<CABV9wwP_ajEbLhCzfCO2Sy0180AshYwQFViys=QhyZA7sW2_qw@mail.gmail.com>
<CADkLM=dvG_F2giHEAGJ=DGVGNmujUt0vAaXNSDkDXzbzcGQbxQ@mail.gmail.com>
<CAJSLCQ10++5GLeoRwNMQMKuaVrikEp2X6feBv=kYDtWmsVy-Pw@mail.gmail.com>
<[email protected]>
<CADkLM=c+r05srPy9w+-+nbmLEo15dKXYQ03Q_xyK+riJerigLQ@mail.gmail.com>
<CADkLM=ceOSsx_=oe73QQ-BxUFR2Cwqum7-UP_fPe22DBY0NerA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CADkLM=c0dOJZUQuWzVcE2BsJSVRGcX7HkJH8wYjo+OD83WFqcg@mail.gmail.com>
<[email protected]>
<CADkLM=f5Dy3RtGOq=2RH2z4xLXNTwSYCAu-d5ggzQFrb8nCsyg@mail.gmail.com>
<CADkLM=ftC94muGKH+z1irdBUXO2+tmBMLdqDAAxcct=H+LE1Eg@mail.gmail.com>
<CADkLM=desCuf3dVHasADvdUVRmb-5gO0mhMO5u9nzgv6i7U86Q@mail.gmail.com>
On Fri, 2025-03-28 at 21:11 -0400, Corey Huinker wrote:
> A rebase and a reordering of the commits to put the really-really-
> must-have relallfrozen ahead of the really-must-have stats batching
> and both of them head of the error->warning step-downs.
v11-0001 has a couple issues:
The first is that i_relallfrozen is undefined in versions earlier than
18. That's trivial to fix, we just add "0 AS relallfrozen," in the
earlier versions, but still refrain from outputting it.
The second is that the pg_upgrade test (when run with
olddump/oldinstall) compares the before and after dumps, and if the
"before" version is 17, then it will not have the relallfrozen argument
to pg_restore_relation_stats. We might need a filtering step in
adjust_new_dumpfile?
Attached new v11j-0001
Regards,
Jeff Davis
Attachments:
[text/x-patch] v11j-0001-Add-relallfrozen-to-pg_dump-statistics.patch (7.7K, ../[email protected]/2-v11j-0001-Add-relallfrozen-to-pg_dump-statistics.patch)
download | inline diff:
From 154b8b5c10ec330c26ccd9006c434a7db1feef04 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Sat, 15 Mar 2025 17:34:30 -0400
Subject: [PATCH v11j] Add relallfrozen to pg_dump statistics.
Author: Corey Huinker <[email protected]>
Discussion: https://postgr.es/m/CADkLM=desCuf3dVHasADvdUVRmb-5gO0mhMO5u9nzgv6i7U86Q@mail.gmail.com
---
src/bin/pg_dump/pg_dump.c | 42 +++++++++++++++----
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 3 +-
.../perl/PostgreSQL/Test/AdjustUpgrade.pm | 5 +++
4 files changed, 43 insertions(+), 8 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 84a78625820..4ca34be230c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6874,7 +6874,8 @@ getFuncs(Archive *fout)
*/
static RelStatsInfo *
getRelationStatistics(Archive *fout, DumpableObject *rel, int32 relpages,
- char *reltuples, int32 relallvisible, char relkind,
+ char *reltuples, int32 relallvisible,
+ int32 relallfrozen, char relkind,
char **indAttNames, int nindAttNames)
{
if (!fout->dopt->dumpStatistics)
@@ -6903,6 +6904,7 @@ getRelationStatistics(Archive *fout, DumpableObject *rel, int32 relpages,
info->relpages = relpages;
info->reltuples = pstrdup(reltuples);
info->relallvisible = relallvisible;
+ info->relallfrozen = relallfrozen;
info->relkind = relkind;
info->indAttNames = indAttNames;
info->nindAttNames = nindAttNames;
@@ -6967,6 +6969,7 @@ getTables(Archive *fout, int *numTables)
int i_relpages;
int i_reltuples;
int i_relallvisible;
+ int i_relallfrozen;
int i_toastpages;
int i_owning_tab;
int i_owning_col;
@@ -7017,8 +7020,15 @@ getTables(Archive *fout, int *numTables)
"c.relowner, "
"c.relchecks, "
"c.relhasindex, c.relhasrules, c.relpages, "
- "c.reltuples, c.relallvisible, c.relhastriggers, "
- "c.relpersistence, "
+ "c.reltuples, c.relallvisible, ");
+
+ if (fout->remoteVersion >= 180000)
+ appendPQExpBufferStr(query, "c.relallfrozen, ");
+ else
+ appendPQExpBufferStr(query, "0 AS relallfrozen, ");
+
+ appendPQExpBufferStr(query,
+ "c.relhastriggers, c.relpersistence, "
"c.reloftype, "
"c.relacl, "
"acldefault(CASE WHEN c.relkind = " CppAsString2(RELKIND_SEQUENCE)
@@ -7183,6 +7193,7 @@ getTables(Archive *fout, int *numTables)
i_relpages = PQfnumber(res, "relpages");
i_reltuples = PQfnumber(res, "reltuples");
i_relallvisible = PQfnumber(res, "relallvisible");
+ i_relallfrozen = PQfnumber(res, "relallfrozen");
i_toastpages = PQfnumber(res, "toastpages");
i_owning_tab = PQfnumber(res, "owning_tab");
i_owning_col = PQfnumber(res, "owning_col");
@@ -7230,6 +7241,7 @@ getTables(Archive *fout, int *numTables)
for (i = 0; i < ntups; i++)
{
int32 relallvisible = atoi(PQgetvalue(res, i, i_relallvisible));
+ int32 relallfrozen = atoi(PQgetvalue(res, i, i_relallfrozen));
tblinfo[i].dobj.objType = DO_TABLE;
tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
@@ -7336,7 +7348,7 @@ getTables(Archive *fout, int *numTables)
stats = getRelationStatistics(fout, &tblinfo[i].dobj,
tblinfo[i].relpages,
PQgetvalue(res, i, i_reltuples),
- relallvisible,
+ relallvisible, relallfrozen,
tblinfo[i].relkind, NULL, 0);
if (tblinfo[i].relkind == RELKIND_MATVIEW)
tblinfo[i].stats = stats;
@@ -7609,6 +7621,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_relpages,
i_reltuples,
i_relallvisible,
+ i_relallfrozen,
i_parentidx,
i_indexdef,
i_indnkeyatts,
@@ -7663,7 +7676,14 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
appendPQExpBufferStr(query,
"SELECT t.tableoid, t.oid, i.indrelid, "
"t.relname AS indexname, "
- "t.relpages, t.reltuples, t.relallvisible, "
+ "t.relpages, t.reltuples, t.relallvisible, ");
+
+ if (fout->remoteVersion >= 180000)
+ appendPQExpBufferStr(query, "t.relallfrozen, ");
+ else
+ appendPQExpBufferStr(query, "0 AS relallfrozen, ");
+
+ appendPQExpBufferStr(query,
"pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
@@ -7779,6 +7799,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_relpages = PQfnumber(res, "relpages");
i_reltuples = PQfnumber(res, "reltuples");
i_relallvisible = PQfnumber(res, "relallvisible");
+ i_relallfrozen = PQfnumber(res, "relallfrozen");
i_parentidx = PQfnumber(res, "parentidx");
i_indexdef = PQfnumber(res, "indexdef");
i_indnkeyatts = PQfnumber(res, "indnkeyatts");
@@ -7850,6 +7871,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
RelStatsInfo *relstats;
int32 relpages = atoi(PQgetvalue(res, j, i_relpages));
int32 relallvisible = atoi(PQgetvalue(res, j, i_relallvisible));
+ int32 relallfrozen = atoi(PQgetvalue(res, j, i_relallfrozen));
indxinfo[j].dobj.objType = DO_INDEX;
indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
@@ -7892,7 +7914,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
relstats = getRelationStatistics(fout, &indxinfo[j].dobj, relpages,
PQgetvalue(res, j, i_reltuples),
- relallvisible, indexkind,
+ relallvisible, relallfrozen, indexkind,
indAttNames, nindAttNames);
contype = *(PQgetvalue(res, j, i_contype));
@@ -10618,9 +10640,15 @@ dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo)
appendPQExpBufferStr(out, ",\n");
appendPQExpBuffer(out, "\t'relpages', '%d'::integer,\n", rsinfo->relpages);
appendPQExpBuffer(out, "\t'reltuples', '%s'::real,\n", rsinfo->reltuples);
- appendPQExpBuffer(out, "\t'relallvisible', '%d'::integer\n);\n",
+ appendPQExpBuffer(out, "\t'relallvisible', '%d'::integer",
rsinfo->relallvisible);
+ if (fout->remoteVersion >= 180000)
+ appendPQExpBuffer(out, ",\n\t'relallfrozen', '%d'::integer", rsinfo->relallfrozen);
+
+ appendPQExpBufferStr(out, "\n);\n");
+
+
/* fetch attribute stats */
appendPQExpBufferStr(query, "EXECUTE getAttributeStats(");
appendStringLiteralAH(query, dobj->namespace->dobj.name, fout);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 70f7a369e4a..e6f0f86a459 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -442,6 +442,7 @@ typedef struct _relStatsInfo
int32 relpages;
char *reltuples;
int32 relallvisible;
+ int32 relallfrozen;
char relkind; /* 'r', 'm', 'i', etc */
/*
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 51ebf8ad13c..576326daec7 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4771,7 +4771,8 @@ my %tests = (
'relname',\s'dup_test_post_data_ix',\s+
'relpages',\s'\d+'::integer,\s+
'reltuples',\s'\d+'::real,\s+
- 'relallvisible',\s'\d+'::integer\s+
+ 'relallvisible',\s'\d+'::integer,\s+
+ 'relallfrozen',\s'\d+'::integer\s+
\);\s+
\QSELECT * FROM pg_catalog.pg_restore_attribute_stats(\E\s+
'version',\s'\d+'::integer,\s+
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index 81a8f44aa9f..07550295a82 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -648,6 +648,11 @@ sub adjust_new_dumpfile
$dump =~ s {\n(\s+'version',) '\d+'::integer,$}
{$1 '000000'::integer,}mg;
+ if ($old_version < 18)
+ {
+ $dump =~ s {,\n(\s+'relallfrozen',) '\d+'::integer$}{}mg;
+ }
+
# pre-v16 dumps do not know about XMLSERIALIZE(NO INDENT).
if ($old_version < 16)
{
--
2.34.1
view thread (204+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Statistics Import and Export
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox