public inbox for [email protected]
help / color / mirror / Atom feedFrom: Corey Huinker <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: [email protected]
Subject: Re: Statistics Import and Export
Date: Tue, 31 Oct 2023 03:25:17 -0400
Message-ID: <CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com> (raw)
In-Reply-To: <CADkLM=c_iuAN19_94ikG5VeR_Z3LDkR6fKyeOz=ZKr44qweYjg@mail.gmail.com>
References: <CADkLM=cB0rF3p_FuWRTMSV0983ihTRpsH+OCpNyiqE7Wk0vUWA@mail.gmail.com>
<CAExHW5uRNe856LPYH1eZFyeASv4Xs4-9d1Gqj+rzS+3StVGLxQ@mail.gmail.com>
<CADkLM=c_iuAN19_94ikG5VeR_Z3LDkR6fKyeOz=ZKr44qweYjg@mail.gmail.com>
>
>
> Yeah, that use makes sense as well, and if so then postgres_fdw would
> likely need to be aware of the appropriate query for several versions back
> - they change, not by much, but they do change. So now we'd have each query
> text in three places: a system view, postgres_fdw, and the bin/scripts
> pre-upgrade program. So I probably should consider the best way to share
> those in the codebase.
>
>
Attached is v2 of this patch.
New features:
* imports index statistics. This is not strictly accurate: it re-computes
index statistics the same as ANALYZE does, which is to say it derives those
stats entirely from table column stats, which are imported, so in that
sense we're getting index stats without touching the heap.
* now support extended statistics except for MCV, which is currently
serialized as an difficult-to-decompose bytea field.
* bare-bones CLI script pg_export_stats, which extracts stats on databases
back to v12 (tested) and could work back to v10.
* bare-bones CLI script pg_import_stats, which obviously only works on
current devel dbs, but can take exports from older versions.
Attachments:
[text/x-patch] v2-0001-Additional-internal-jsonb-access-functions.patch (2.4K, ../CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com/3-v2-0001-Additional-internal-jsonb-access-functions.patch)
download | inline diff:
From 9bd1618db9e40caad773334921f0a43d9d63e73a Mon Sep 17 00:00:00 2001
From: coreyhuinker <[email protected]>
Date: Mon, 30 Oct 2023 16:21:30 -0400
Subject: [PATCH v2 1/4] Additional internal jsonb access functions.
Make JsonbContainerTypeName externally visible.
Add JsonbStringValueToCString.
---
src/include/utils/jsonb.h | 4 ++++
src/backend/utils/adt/jsonb.c | 2 +-
src/backend/utils/adt/jsonb_util.c | 15 +++++++++++++++
3 files changed, 20 insertions(+), 1 deletion(-)
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index addc9b608e..b3c1e104f2 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -424,6 +424,8 @@ extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
int estimated_len);
extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res);
extern const char *JsonbTypeName(JsonbValue *val);
+extern const char *JsonbContainerTypeName(JsonbContainer *jbc);
+
extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
JsonbValue *newval);
@@ -436,4 +438,6 @@ extern Datum jsonb_build_object_worker(int nargs, const Datum *args, const bool
extern Datum jsonb_build_array_worker(int nargs, const Datum *args, const bool *nulls,
const Oid *types, bool absent_on_null);
+extern char *JsonbStringValueToCString(JsonbValue *j);
+
#endif /* __JSONB_H__ */
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 6f445f5c2b..0ad4e81d89 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -160,7 +160,7 @@ jsonb_from_text(text *js, bool unique_keys)
/*
* Get the type name of a jsonb container.
*/
-static const char *
+const char *
JsonbContainerTypeName(JsonbContainer *jbc)
{
JsonbValue scalar;
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 9cc95b773d..ae311b38ba 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -1992,3 +1992,18 @@ uniqueifyJsonbObject(JsonbValue *object, bool unique_keys, bool skip_nulls)
}
}
}
+
+/*
+ * Extract a JsonbValue as a cstring.
+ */
+char *JsonbStringValueToCString(JsonbValue *j)
+{
+ char *s;
+
+ Assert(j->type == jbvString);
+ /* make a string that we are sure is null-terminated */
+ s = palloc(j->val.string.len + 1);
+ memcpy(s, j->val.string.val, j->val.string.len);
+ s[j->val.string.len] = '\0';
+ return s;
+}
--
2.41.0
[text/x-patch] v2-0002-Add-system-view-pg_statistic_export.patch (18.2K, ../CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com/4-v2-0002-Add-system-view-pg_statistic_export.patch)
download | inline diff:
From 920d546032a330fe1ec4c8f6a35c48a47ccd08b2 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Tue, 31 Oct 2023 02:27:17 -0400
Subject: [PATCH v2 2/4] Add system view pg_statistic_export.
This view is designed to aid in the export (and re-import) of table
statistics and extended statistics, mostly for upgrade/restore
situations.
---
src/backend/catalog/system_views.sql | 215 +++++++++++++++++++++++++++
src/test/regress/expected/rules.out | 71 +++++++++
doc/src/sgml/system-views.sgml | 5 +
3 files changed, 291 insertions(+)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b65f6b5249..11a1037ceb 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -253,6 +253,221 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
REVOKE ALL ON pg_statistic FROM public;
+
+
+
+CREATE VIEW pg_statistic_export WITH (security_barrier) AS
+ 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,
+ (
+ WITH per_column_stats AS
+ (
+ SELECT
+ s.stainherit,
+ 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::text[],
+ s.stanumbers2::text::text[],
+ s.stanumbers3::text::text[],
+ s.stanumbers4::text::text[],
+ s.stanumbers5::text::text[]),
+ 'stavalues',
+ jsonb_build_array(
+ s.stavalues1::text::text[],
+ s.stavalues2::text::text[],
+ s.stavalues3::text::text[],
+ s.stavalues4::text::text[],
+ s.stavalues5::text::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')
+ ),
+ attagg AS
+ (
+ SELECT
+ pcs.stainherit,
+ jsonb_build_object(
+ 'columns',
+ jsonb_object_agg(
+ pcs.attname,
+ pcs.stats
+ )
+ ) AS stats
+ FROM per_column_stats AS pcs
+ GROUP BY pcs.stainherit
+ ),
+ extended_object_stats AS
+ (
+ SELECT
+ sd.stxdinherit,
+ e.stxname,
+ jsonb_build_object(
+ 'stxkinds',
+ to_jsonb(e.stxkind),
+ 'stxdndistinct',
+ ndist.stxdndistinct,
+ 'stxdndependencies',
+ ndep.stxdndependencies,
+ 'stxdmcv',
+ mcv.stxdmcv,
+ 'stxdexprs',
+ x.stdxdexprs
+ ) AS stats
+ FROM pg_statistic_ext AS e
+ JOIN pg_statistic_ext_data AS sd
+ ON sd.stxoid = e.oid
+ LEFT JOIN LATERAL
+ (
+ -- att1 [, att2..]: ndistinct
+ SELECT
+ jsonb_agg(
+ jsonb_build_object(
+ 'attnums', string_to_array(nd.attnums, ', '),
+ 'ndistinct', nd.ndistinct
+ )
+ ORDER BY nd.ord
+ )
+ -- jsonb does not preserve parsed order so use json
+ FROM json_each_text(sd.stxdndistinct::text::json)
+ WITH ORDINALITY AS nd(attnums, ndistinct, ord)
+ ) AS ndist(stxdndistinct) ON sd.stxdndistinct IS NOT NULL
+ LEFT JOIN LATERAL
+ (
+ -- att1, [, att2 ...] => attN: degree
+ SELECT
+ jsonb_agg(
+ jsonb_build_object(
+ 'attnums',
+ string_to_array( replace(dep.attrs, ' => ', ', '), ', '),
+ 'degree',
+ dep.degree
+ )
+ ORDER BY dep.ord
+ )
+ -- jsonb does not preserve parsed order so use json
+ FROM json_each_text(sd.stxddependencies::text::json)
+ WITH ORDINALITY AS dep(attrs, degree, ord)
+ ) AS ndep(stxdndependencies) ON sd.stxddependencies IS NOT NULL
+ LEFT JOIN LATERAL
+ (
+ -- TODO SELECT sd.stxdmcv
+ SELECT NULL AS stxdmcv
+ ) AS mcv(stxdmcv) ON sd.stxdmcv IS NOT NULL
+ LEFT JOIN LATERAL
+ (
+ SELECT
+ jsonb_agg(
+ 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::text[],
+ s.stanumbers2::text::text[],
+ s.stanumbers3::text::text[],
+ s.stanumbers4::text::text[],
+ s.stanumbers5::text::text[]),
+ 'stavalues',
+ jsonb_build_array(
+ s.stavalues1::text::text[],
+ s.stavalues2::text::text[],
+ s.stavalues3::text::text[],
+ s.stavalues4::text::text[],
+ s.stavalues5::text::text[])
+ )
+ ORDER BY s.ordinality
+ )
+ FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s
+ ) AS x(stdxdexprs) ON sd.stxdexpr IS NOT NULL
+ WHERE e.stxrelid = r.oid
+ ),
+ extagg AS
+ (
+ SELECT
+ eos.stxdinherit,
+ jsonb_build_object(
+ 'extended',
+ jsonb_object_agg(
+ eos.stxname,
+ eos.stats
+ )
+ ) AS stats
+ FROM extended_object_stats AS eos
+ GROUP BY eos.stxdinherit
+ )
+ SELECT
+ jsonb_object_agg(
+ CASE coalesce(a.stainherit, e.stxdinherit)
+ WHEN TRUE THEN 'inherited'
+ ELSE 'regular'
+ END,
+ coalesce(a.stats, '{}'::jsonb) || coalesce(e.stats, '{}'::jsonb)
+ )
+ FROM attagg AS a
+ FULL OUTER JOIN extagg e ON a.stainherit = e.stxdinherit
+ ) AS stats
+ FROM pg_class AS r
+ JOIN pg_namespace AS n
+ ON n.oid = r.relnamespace
+ WHERE relkind IN ('r', 'm', 'f', 'p')
+ AND n.nspname NOT IN ('pg_catalog', 'information_schema');
+
+
CREATE VIEW pg_stats_ext WITH (security_barrier) AS
SELECT cn.nspname AS schemaname,
c.relname AS tablename,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 1442c43d9c..20ab4da385 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2404,6 +2404,77 @@ pg_statio_user_tables| SELECT relid,
tidx_blks_hit
FROM pg_statio_all_tables
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
+pg_statistic_export| SELECT n.nspname AS schemaname,
+ r.relname,
+ (current_setting('server_version_num'::text))::integer AS server_version_num,
+ r.reltuples AS n_tuples,
+ r.relpages AS n_pages,
+ ( WITH per_column_stats AS (
+ SELECT s.stainherit,
+ a_1.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'::text
+ WHEN 1 THEN 'MCV'::text
+ WHEN 2 THEN 'HISTOGRAM'::text
+ WHEN 3 THEN 'CORRELATION'::text
+ WHEN 4 THEN 'MCELEM'::text
+ WHEN 5 THEN 'DECHIST'::text
+ WHEN 6 THEN 'RANGE_LENGTH_HISTOGRAM'::text
+ WHEN 7 THEN 'BOUNDS_HISTOGRAM'::text
+ ELSE NULL::text
+ END ORDER BY kind.ord) AS jsonb_agg
+ FROM unnest(ARRAY[s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5]) WITH ORDINALITY kind(kind, ord)), 'stanumbers', jsonb_build_array(((s.stanumbers1)::text)::text[], ((s.stanumbers2)::text)::text[], ((s.stanumbers3)::text)::text[], ((s.stanumbers4)::text)::text[], ((s.stanumbers5)::text)::text[]), 'stavalues', jsonb_build_array(((s.stavalues1)::text)::text[], ((s.stavalues2)::text)::text[], ((s.stavalues3)::text)::text[], ((s.stavalues4)::text)::text[], ((s.stavalues5)::text)::text[])) AS stats
+ FROM (pg_attribute a_1
+ JOIN pg_statistic s ON (((s.starelid = a_1.attrelid) AND (s.staattnum = a_1.attnum))))
+ WHERE ((a_1.attrelid = r.oid) AND (NOT a_1.attisdropped) AND (a_1.attnum > 0) AND has_column_privilege(a_1.attrelid, a_1.attnum, 'SELECT'::text))
+ ), attagg AS (
+ SELECT pcs.stainherit,
+ jsonb_build_object('columns', jsonb_object_agg(pcs.attname, pcs.stats)) AS stats
+ FROM per_column_stats pcs
+ GROUP BY pcs.stainherit
+ ), extended_object_stats AS (
+ SELECT sd.stxdinherit,
+ e_1.stxname,
+ jsonb_build_object('stxkinds', to_jsonb(e_1.stxkind), 'stxdndistinct', ndist.stxdndistinct, 'stxdndependencies', ndep.stxdndependencies, 'stxdmcv', mcv.stxdmcv, 'stxdexprs', x.stdxdexprs) AS stats
+ FROM (((((pg_statistic_ext e_1
+ JOIN pg_statistic_ext_data sd ON ((sd.stxoid = e_1.oid)))
+ LEFT JOIN LATERAL ( SELECT jsonb_agg(jsonb_build_object('attnums', string_to_array(nd.attnums, ', '::text), 'ndistinct', nd.ndistinct) ORDER BY nd.ord) AS jsonb_agg
+ FROM json_each_text(((sd.stxdndistinct)::text)::json) WITH ORDINALITY nd(attnums, ndistinct, ord)) ndist(stxdndistinct) ON ((sd.stxdndistinct IS NOT NULL)))
+ LEFT JOIN LATERAL ( SELECT jsonb_agg(jsonb_build_object('attnums', string_to_array(replace(dep.attrs, ' => '::text, ', '::text), ', '::text), 'degree', dep.degree) ORDER BY dep.ord) AS jsonb_agg
+ FROM json_each_text(((sd.stxddependencies)::text)::json) WITH ORDINALITY dep(attrs, degree, ord)) ndep(stxdndependencies) ON ((sd.stxddependencies IS NOT NULL)))
+ LEFT JOIN LATERAL ( SELECT NULL::text AS stxdmcv) mcv(stxdmcv) ON ((sd.stxdmcv IS NOT NULL)))
+ LEFT JOIN LATERAL ( SELECT jsonb_agg(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'::text
+ WHEN 1 THEN 'MCV'::text
+ WHEN 2 THEN 'HISTOGRAM'::text
+ WHEN 3 THEN 'CORRELATION'::text
+ WHEN 4 THEN 'MCELEM'::text
+ WHEN 5 THEN 'DECHIST'::text
+ WHEN 6 THEN 'RANGE_LENGTH_HISTOGRAM'::text
+ WHEN 7 THEN 'BOUNDS_HISTOGRAM'::text
+ ELSE NULL::text
+END ORDER BY kind.ord) AS jsonb_agg
+ FROM unnest(ARRAY[s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5]) WITH ORDINALITY kind(kind, ord)), 'stanumbers', jsonb_build_array(((s.stanumbers1)::text)::text[], ((s.stanumbers2)::text)::text[], ((s.stanumbers3)::text)::text[], ((s.stanumbers4)::text)::text[], ((s.stanumbers5)::text)::text[]), 'stavalues', jsonb_build_array(((s.stavalues1)::text)::text[], ((s.stavalues2)::text)::text[], ((s.stavalues3)::text)::text[], ((s.stavalues4)::text)::text[], ((s.stavalues5)::text)::text[])) ORDER BY s.ordinality) AS jsonb_agg
+ FROM unnest(sd.stxdexpr) WITH ORDINALITY s(starelid, staattnum, stainherit, stanullfrac, stawidth, stadistinct, stakind1, stakind2, stakind3, stakind4, stakind5, staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, stavalues1, stavalues2, stavalues3, stavalues4, stavalues5, ordinality)) x(stdxdexprs) ON ((sd.stxdexpr IS NOT NULL)))
+ WHERE (e_1.stxrelid = r.oid)
+ ), extagg AS (
+ SELECT eos.stxdinherit,
+ jsonb_build_object('extended', jsonb_object_agg(eos.stxname, eos.stats)) AS stats
+ FROM extended_object_stats eos
+ GROUP BY eos.stxdinherit
+ )
+ SELECT jsonb_object_agg(
+ CASE COALESCE(a.stainherit, e.stxdinherit)
+ WHEN true THEN 'inherited'::text
+ ELSE 'regular'::text
+ END, (COALESCE(a.stats, '{}'::jsonb) || COALESCE(e.stats, '{}'::jsonb))) AS jsonb_object_agg
+ FROM (attagg a
+ FULL JOIN extagg e ON ((a.stainherit = e.stxdinherit)))) AS stats
+ FROM (pg_class r
+ JOIN pg_namespace n ON ((n.oid = r.relnamespace)))
+ WHERE ((r.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])));
pg_stats| SELECT n.nspname AS schemaname,
c.relname AS tablename,
a.attname,
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 7078491c4c..24b44ab388 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -191,6 +191,11 @@
<entry>extended planner statistics for expressions</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-stats"><structname>pg_stats_export</structname></link></entry>
+ <entry>planner statistics for export/upgrade purposes</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-tables"><structname>pg_tables</structname></link></entry>
<entry>tables</entry>
--
2.41.0
[text/x-patch] v2-0003-Add-pg_import_rel_stats.patch (58.8K, ../CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com/5-v2-0003-Add-pg_import_rel_stats.patch)
download | inline diff:
From 05b0a3537765a85fda8b59912b658eec1e6b5a81 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Tue, 31 Oct 2023 03:12:45 -0400
Subject: [PATCH v2 3/4] Add pg_import_rel_stats().
The function pg_import_rel_stats imports rowcount, pagecount, and column
statistics for a given table, as well as column statistics for the
underlying indexes and extended statistics for any named statistics
objects for that table.
The most likely application of this function is to quickly apply stats
to a newly upgraded database faster than could be accomplished by
vacuumdb --analyze-in-stages.
The function takes a best-effort approach, skipping statistics that are
expected but omitted, skipping object that are specified but do not
exist on the target system. The goal is to get better-than-empty
statistics into the table quickly, so that business operations can
resume sooner.
It should be noted that index statistics _are_ rebuilt as normal, but
use the imported column statistics from the table, so it is not
necessary for the indexes on the target table to match the indexes on
the extracted source table.
The statistics applied are not locked in any way, and will be
overwritten by the next analyze, either explicit or via autovacuum.
Extended statistics are identified by name, so those names on the target
system must match the source. If not, they are skipped.
While the column, index column, and extended statistics are applied
transactionally, the changes to pg_class (reltuples and relpages) are
not. This decision was made to avoid bloat of pg_class and is in line
with the behavior of VACUUM.
The medium of exchange is jsonb, the format of which is specified in the
view pg_statistic_export. Obviously this view does not exist in older
versions of the database, but the view definition can be extracted and
adapted to older versions.
This function also allows for tweaking of table statistics in-place,
allowing the user to inflate rowcounts, skew histograms, etc, to see
what those changes will evoke from the query planner.
---
src/include/catalog/pg_proc.dat | 5 +
src/include/commands/vacuum.h | 4 +
.../statistics/extended_stats_internal.h | 3 +
src/include/statistics/statistics.h | 10 +
src/backend/commands/analyze.c | 709 ++++++++++++++++--
src/backend/statistics/dependencies.c | 133 ++++
src/backend/statistics/extended_stats.c | 259 +++++++
src/backend/statistics/mcv.c | 6 +
src/backend/statistics/mvdistinct.c | 120 +++
src/test/regress/expected/vacuum.out | 153 ++++
src/test/regress/sql/vacuum.sql | 146 ++++
doc/src/sgml/func.sgml | 42 ++
12 files changed, 1527 insertions(+), 63 deletions(-)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 568aa80d92..01aeb54fe3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5639,6 +5639,11 @@
proname => 'pg_stat_get_db_checksum_last_failure', provolatile => 's',
proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_get_db_checksum_last_failure' },
+{ oid => '3813',
+ descr => 'statistics: import to relation',
+ proname => 'pg_import_rel_stats', provolatile => 'v', proisstrict => 'f',
+ proparallel => 'u', prorettype => 'bool', proargtypes => 'oid int4 float4 int4 jsonb',
+ prosrc => 'pg_import_rel_stats' },
{ oid => '3074', descr => 'statistics: last reset for a database',
proname => 'pg_stat_get_db_stat_reset_time', provolatile => 's',
proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid',
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 4af02940c5..72c31eb882 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -24,6 +24,7 @@
#include "storage/buf.h"
#include "storage/lock.h"
#include "utils/relcache.h"
+#include "utils/jsonb.h"
/*
* Flags for amparallelvacuumoptions to control the participation of bulkdelete
@@ -386,4 +387,7 @@ extern double anl_random_fract(void);
extern double anl_init_selection_state(int n);
extern double anl_get_next_S(double t, int n, double *stateptr);
+extern Datum pg_import_rel_stats(PG_FUNCTION_ARGS);
+
+
#endif /* VACUUM_H */
diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h
index 7b55eb8ffa..fdefaa28f5 100644
--- a/src/include/statistics/extended_stats_internal.h
+++ b/src/include/statistics/extended_stats_internal.h
@@ -72,15 +72,18 @@ typedef struct StatsBuildData
extern MVNDistinct *statext_ndistinct_build(double totalrows, StatsBuildData *data);
extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct);
extern MVNDistinct *statext_ndistinct_deserialize(bytea *data);
+extern MVNDistinct *import_ndistinct(JsonbContainer *cont);
extern MVDependencies *statext_dependencies_build(StatsBuildData *data);
extern bytea *statext_dependencies_serialize(MVDependencies *dependencies);
extern MVDependencies *statext_dependencies_deserialize(bytea *data);
+extern MVDependencies * import_dependencies(JsonbContainer *cont);
extern MCVList *statext_mcv_build(StatsBuildData *data,
double totalrows, int stattarget);
extern bytea *statext_mcv_serialize(MCVList *mcvlist, VacAttrStats **stats);
extern MCVList *statext_mcv_deserialize(bytea *data);
+extern MCVList *import_mcv(JsonbContainer *cont);
extern MultiSortSupport multi_sort_init(int ndims);
extern void multi_sort_add_dimension(MultiSortSupport mss, int sortdim,
diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h
index 5e538fec32..2b37e77887 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -101,6 +101,7 @@ extern MCVList *statext_mcv_load(Oid mvoid, bool inh);
extern void BuildRelationExtStatistics(Relation onerel, bool inh, double totalrows,
int numrows, HeapTuple *rows,
int natts, VacAttrStats **vacattrstats);
+
extern int ComputeExtStatisticsRows(Relation onerel,
int natts, VacAttrStats **vacattrstats);
extern bool statext_is_kind_built(HeapTuple htup, char type);
@@ -127,4 +128,13 @@ extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind,
int nclauses);
extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx);
+extern void ImportVacAttrStats( VacAttrStats *stats, JsonbContainer *cont);
+extern void ImportRelationExtStatistics(Relation onerel, bool inh, int natts,
+ VacAttrStats **vacattrstats,
+ JsonbContainer *cont);
+
+extern char *key_lookup_cstring(JsonbContainer *cont, const char *key);
+extern JsonbContainer *key_lookup_object(JsonbContainer *cont, const char *key);
+extern JsonbContainer *key_lookup_array(JsonbContainer *cont, const char *key);
+
#endif /* STATISTICS_H */
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 206d1689ef..a9715c3eb3 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -18,6 +18,7 @@
#include "access/detoast.h"
#include "access/genam.h"
+#include "access/heapam.h"
#include "access/multixact.h"
#include "access/relation.h"
#include "access/sysattr.h"
@@ -33,6 +34,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
#include "commands/dbcommands.h"
#include "commands/progress.h"
@@ -40,6 +42,7 @@
#include "commands/vacuum.h"
#include "common/pg_prng.h"
#include "executor/executor.h"
+#include "fmgr.h"
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -57,16 +60,20 @@
#include "utils/attoptcache.h"
#include "utils/builtins.h"
#include "utils/datum.h"
+#include "utils/float.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
+#include "utils/jsonb.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+#include "utils/numeric.h"
#include "utils/pg_rusage.h"
#include "utils/sampling.h"
#include "utils/sortsupport.h"
#include "utils/spccache.h"
#include "utils/syscache.h"
#include "utils/timestamp.h"
+#include "utils/typcache.h"
/* Per-index data for ANALYZE */
@@ -109,6 +116,12 @@ static void update_attstats(Oid relid, bool inh,
static Datum std_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
+static AnlIndexData *build_indexdata(Relation onerel, Relation *Irel,
+ int nindexes, bool all_columns);
+static VacAttrStats **examine_rel_attributes(Relation onerel, int *attr_cnt);
+
+static
+void import_pg_statistics(Relation onerel, bool inh, JsonbContainer *cont);
/*
* analyze_rel() -- analyze one relation
@@ -403,29 +416,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
attr_cnt = tcnt;
}
else
- {
- attr_cnt = onerel->rd_att->natts;
- vacattrstats = (VacAttrStats **)
- palloc(attr_cnt * sizeof(VacAttrStats *));
- tcnt = 0;
- for (i = 1; i <= attr_cnt; i++)
- {
- vacattrstats[tcnt] = examine_attribute(onerel, i, NULL);
- if (vacattrstats[tcnt] != NULL)
- tcnt++;
- }
- attr_cnt = tcnt;
- }
+ vacattrstats = examine_rel_attributes(onerel, &attr_cnt);
- /*
- * Open all indexes of the relation, and see if there are any analyzable
- * columns in the indexes. We do not analyze index columns if there was
- * an explicit column list in the ANALYZE command, however.
- *
- * If we are doing a recursive scan, we don't want to touch the parent's
- * indexes at all. If we're processing a partitioned table, we need to
- * know if there are any indexes, but we don't want to process them.
- */
if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
List *idxs = RelationGetIndexList(onerel);
@@ -446,48 +438,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
nindexes = 0;
hasindex = false;
}
- indexdata = NULL;
- if (nindexes > 0)
- {
- indexdata = (AnlIndexData *) palloc0(nindexes * sizeof(AnlIndexData));
- for (ind = 0; ind < nindexes; ind++)
- {
- AnlIndexData *thisdata = &indexdata[ind];
- IndexInfo *indexInfo;
- thisdata->indexInfo = indexInfo = BuildIndexInfo(Irel[ind]);
- thisdata->tupleFract = 1.0; /* fix later if partial */
- if (indexInfo->ii_Expressions != NIL && va_cols == NIL)
- {
- ListCell *indexpr_item = list_head(indexInfo->ii_Expressions);
-
- thisdata->vacattrstats = (VacAttrStats **)
- palloc(indexInfo->ii_NumIndexAttrs * sizeof(VacAttrStats *));
- tcnt = 0;
- for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
- {
- int keycol = indexInfo->ii_IndexAttrNumbers[i];
-
- if (keycol == 0)
- {
- /* Found an index expression */
- Node *indexkey;
-
- if (indexpr_item == NULL) /* shouldn't happen */
- elog(ERROR, "too few entries in indexprs list");
- indexkey = (Node *) lfirst(indexpr_item);
- indexpr_item = lnext(indexInfo->ii_Expressions,
- indexpr_item);
- thisdata->vacattrstats[tcnt] =
- examine_attribute(Irel[ind], i + 1, indexkey);
- if (thisdata->vacattrstats[tcnt] != NULL)
- tcnt++;
- }
- }
- thisdata->attr_cnt = tcnt;
- }
- }
- }
+ indexdata = build_indexdata(onerel, Irel, nindexes, (va_cols == NIL));
/*
* Determine how many rows we need to sample, using the worst case from
@@ -823,6 +775,92 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
anl_context = NULL;
}
+
+/*
+ * Create VacAttrStats entries for all attributes in a relation.
+ */
+static
+VacAttrStats **examine_rel_attributes(Relation onerel, int *attr_cnt)
+{
+ int natts = onerel->rd_att->natts;
+ int nfound = 0;
+ int i;
+ VacAttrStats **attrstats;
+
+ *attr_cnt = natts;
+ attrstats = (VacAttrStats **) palloc(natts * sizeof(VacAttrStats *));
+ for (i = 1; i <= natts; i++)
+ {
+ attrstats[nfound] = examine_attribute(onerel, i, NULL);
+ if (attrstats[nfound] != NULL)
+ nfound++;
+ }
+ *attr_cnt = nfound;
+ return attrstats;
+}
+
+/*
+ * Open all indexes of the relation, and see if there are any analyzable
+ * columns in the indexes. We do not analyze index columns if there was
+ * an explicit column list in the ANALYZE command, however.
+ *
+ * If we are doing a recursive scan, we don't want to touch the parent's
+ * indexes at all. If we're processing a partitioned table, we need to
+ * know if there are any indexes, but we don't want to process them.
+ */
+static
+AnlIndexData *build_indexdata(Relation onerel, Relation *Irel, int nindexes, bool all_columns)
+{
+ AnlIndexData *indexdata;
+ int ind,
+ tcnt,
+ i;
+
+ if (nindexes == 0)
+ return NULL;
+
+ indexdata = (AnlIndexData *) palloc0(nindexes * sizeof(AnlIndexData));
+
+ for (ind = 0; ind < nindexes; ind++)
+ {
+ AnlIndexData *thisdata = &indexdata[ind];
+ IndexInfo *indexInfo;
+
+ thisdata->indexInfo = indexInfo = BuildIndexInfo(Irel[ind]);
+ thisdata->tupleFract = 1.0; /* fix later if partial */
+ if (indexInfo->ii_Expressions != NIL && all_columns)
+ {
+ ListCell *indexpr_item = list_head(indexInfo->ii_Expressions);
+
+ thisdata->vacattrstats = (VacAttrStats **)
+ palloc(indexInfo->ii_NumIndexAttrs * sizeof(VacAttrStats *));
+ tcnt = 0;
+ for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
+ {
+ int keycol = indexInfo->ii_IndexAttrNumbers[i];
+
+ if (keycol == 0)
+ {
+ /* Found an index expression */
+ Node *indexkey;
+
+ if (indexpr_item == NULL) /* shouldn't happen */
+ elog(ERROR, "too few entries in indexprs list");
+ indexkey = (Node *) lfirst(indexpr_item);
+ indexpr_item = lnext(indexInfo->ii_Expressions,
+ indexpr_item);
+ thisdata->vacattrstats[tcnt] =
+ examine_attribute(Irel[ind], i + 1, indexkey);
+ if (thisdata->vacattrstats[tcnt] != NULL)
+ tcnt++;
+ }
+ }
+ thisdata->attr_cnt = tcnt;
+ }
+ }
+ return indexdata;
+}
+
/*
* Compute statistics about indexes of a relation
*/
@@ -3073,3 +3111,548 @@ analyze_mcv_list(int *mcv_counts,
}
return num_mcv;
}
+
+/*
+ * Get a JsonbValue from a JsonbContainer and ensure that it is a string,
+ * and return the cstring.
+ */
+char *key_lookup_cstring(JsonbContainer *cont, const char *key)
+{
+ JsonbValue j;
+
+ if (!getKeyJsonValueFromContainer(cont, key, strlen(key), &j))
+ return NULL;
+
+ if (j.type != jbvString)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, %s must be a string but is type %s",
+ key, JsonbTypeName(&j))));
+
+ return JsonbStringValueToCString(&j);
+}
+
+/*
+ * Get a JsonbContainer from a JsonbContainer and ensure that it is a object
+ */
+JsonbContainer *key_lookup_object(JsonbContainer *cont, const char *key)
+{
+ JsonbValue j;
+
+ if (!getKeyJsonValueFromContainer(cont, key, strlen(key), &j))
+ return NULL;
+
+ if (j.type != jbvBinary)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, %s must be an object but is type %s",
+ key, JsonbTypeName(&j))));
+
+ if (!JsonContainerIsObject(j.val.binary.data))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, %s must be an object but is type %s",
+ key, JsonbContainerTypeName(j.val.binary.data))));
+
+ return j.val.binary.data;
+}
+
+/*
+ * Get a JsonbContainer from a JsonbContainer and ensure that it is an array
+ */
+JsonbContainer *key_lookup_array(JsonbContainer *cont, const char *key)
+{
+ JsonbValue j;
+
+ if (!getKeyJsonValueFromContainer(cont, key, strlen(key), &j))
+ return NULL;
+
+ if (j.type != jbvBinary)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, %s must be an array but is type %s",
+ key, JsonbTypeName(&j))));
+
+ if (!JsonContainerIsArray(j.val.binary.data))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, %s must be an array but is type %s",
+ key, JsonbContainerTypeName(j.val.binary.data))));
+
+ return j.val.binary.data;
+}
+
+/*
+ * Import statistics from JSONB export into relation
+ *
+ * Format is:
+ *
+ * {
+ * "columns": { "colname1": ... },
+ * "extended": { "statname1": ...}
+ * }
+ */
+Datum
+pg_import_rel_stats(PG_FUNCTION_ARGS)
+{
+ Oid relid;
+ int32 stats_version_num;
+ Jsonb *jb;
+ Relation onerel;
+ Oid save_userid;
+ int save_sec_context;
+ int save_nestlevel;
+
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relation cannot be NULL")));
+ relid = PG_GETARG_OID(0);
+
+ if (PG_ARGISNULL(1))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("server_version_number cannot be NULL")));
+ stats_version_num = PG_GETARG_INT32(1);
+
+ if (stats_version_num < 80000)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics version: %d is earlier than earliest supported version",
+ stats_version_num)));
+
+ if (PG_ARGISNULL(4))
+ jb = NULL;
+ else
+ {
+ jb = PG_GETARG_JSONB_P(4);
+ if (!JB_ROOT_IS_OBJECT(jb))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("columns must be jsonb object at root")));
+ }
+
+ onerel = vacuum_open_relation(relid, NULL, VACOPT_ANALYZE, true,
+ ShareUpdateExclusiveLock);
+
+ if (onerel == NULL)
+ PG_RETURN_BOOL(false);
+
+ if (!vacuum_is_relation_owner(RelationGetRelid(onerel),
+ onerel->rd_rel,
+ VACOPT_ANALYZE))
+ {
+ relation_close(onerel, ShareUpdateExclusiveLock);
+ PG_RETURN_BOOL(false);
+ }
+
+ /*
+ * Switch to the table owner's userid, so that any index functions are run
+ * as that user. Also lock down security-restricted operations and
+ * arrange to make GUC variable changes local to this command.
+ */
+ GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ SetUserIdAndSecContext(onerel->rd_rel->relowner,
+ save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ save_nestlevel = NewGUCNestLevel();
+
+ /*
+ * Apply statistical updates, if any, to copied tuple.
+ *
+ * Format is:
+ * {
+ * "regular": { "columns": ..., "extended": ...},
+ * "inherited": { "columns": ..., "extended": ...}
+ * }
+ *
+ */
+ if (jb != NULL)
+ {
+ JsonbContainer *cont;
+
+ cont = key_lookup_object(&jb->root, "regular");
+ import_pg_statistics(onerel, false, cont);
+
+ if (onerel->rd_rel->relhassubclass)
+ {
+ cont = key_lookup_object(&jb->root, "inherited");
+ import_pg_statistics(onerel, true, cont);
+ }
+ }
+
+ /* only modify pg_class row if changes are to be made */
+ if ( ! PG_ARGISNULL(2) || ! PG_ARGISNULL(3) )
+ {
+ Relation pg_class_rel;
+ HeapTuple ctup;
+ Form_pg_class pgcform;
+
+ /*
+ * Open the relation, getting ShareUpdateExclusiveLock to ensure that no
+ * other stat-setting operation can run on it concurrently.
+ */
+ pg_class_rel = table_open(RelationRelationId, ShareUpdateExclusiveLock);
+
+ /* leave if relation could not be opened or locked */
+ if (!pg_class_rel)
+ PG_RETURN_BOOL(false);
+
+ ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(ctup))
+ elog(ERROR, "pg_class entry for relid %u vanished during statistics import",
+ relid);
+ pgcform = (Form_pg_class) GETSTRUCT(ctup);
+
+ /* leave un-set values alone */
+ if (! PG_ARGISNULL(2))
+ pgcform->reltuples = PG_GETARG_FLOAT4(2);
+ if (! PG_ARGISNULL(3))
+ pgcform->relpages = PG_GETARG_INT32(3);
+
+ heap_inplace_update(pg_class_rel, ctup);
+ table_close(pg_class_rel, ShareUpdateExclusiveLock);
+ }
+
+ /* relation_close(onerel, ShareUpdateExclusiveLock); */
+ relation_close(onerel, NoLock);
+
+ /* Roll back any GUC changes executed by index functions */
+ AtEOXact_GUC(false, save_nestlevel);
+
+ /* Restore userid and security context */
+ SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ PG_RETURN_BOOL(true);
+}
+
+/*
+ * Convert the STATISTICS_KIND strings defined in pg_statistic_export
+ * back to their defined enum values.
+ */
+static int16
+decode_stakind_string(char *s)
+{
+ if (strcmp(s,"MCV") == 0)
+ return STATISTIC_KIND_MCV;
+ if (strcmp(s,"HISTOGRAM") == 0)
+ return STATISTIC_KIND_HISTOGRAM;
+ if (strcmp(s,"CORRELATION") == 0)
+ return STATISTIC_KIND_CORRELATION;
+ if (strcmp(s,"MCELEM") == 0)
+ return STATISTIC_KIND_MCELEM;
+ if (strcmp(s,"DECHIST") == 0)
+ return STATISTIC_KIND_DECHIST;
+ if (strcmp(s,"RANGE_LENGTH_HISTOGRAM") == 0)
+ return STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM;
+ if (strcmp(s,"BOUNDS_HISTOGRAM") == 0)
+ return STATISTIC_KIND_BOUNDS_HISTOGRAM;
+ if (strcmp(s,"TRIVIAL") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unknown statistics kind: %s", s)));
+
+ return 0;
+}
+
+/*
+ *
+ * Format is:
+ *
+ * {
+ * "regular":
+ * {
+ * "columns": { "colname1": ... }},
+ * "extended": { "statname1": ...}
+ * },
+ * "inherited":
+ * {
+ * "columns": { "colname1": ... }},
+ * "extended": { "statname1": ...}
+ * },
+ * }
+ */
+static
+void import_pg_statistics(Relation onerel, bool inh, JsonbContainer *cont)
+{
+ VacAttrStats **vacattrstats;
+ JsonbContainer *colscont;
+ JsonbContainer *extscont;
+ int natts;
+ AnlIndexData *indexdata = NULL;
+ int nindexes = 0;
+ int i,
+ ind;
+ Relation *Irel = NULL;
+
+ /* skip if no statistics of this inheritance type available */
+ if (cont == NULL)
+ return;
+
+ vacattrstats = examine_rel_attributes(onerel, &natts);
+
+ if ((!inh) &&
+ (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE))
+ {
+ vac_open_indexes(onerel, AccessShareLock, &nindexes, &Irel);
+ if (nindexes > 0)
+ indexdata = build_indexdata(onerel, Irel, nindexes, true);
+ }
+
+ colscont = key_lookup_object(cont, "columns");
+ if (colscont != NULL)
+ {
+ for (i = 0; i < natts; i++)
+ {
+ VacAttrStats *stats;
+ JsonbContainer *attrcont;
+ const char *name;
+
+ stats = vacattrstats[i];
+ name = NameStr(*attnumAttName(onerel, stats->tupattnum));
+
+ attrcont = key_lookup_object(colscont, name);
+ ImportVacAttrStats(stats, attrcont);
+ }
+ update_attstats(RelationGetRelid(onerel), inh, natts, vacattrstats);
+
+ /* now compute the index stats based on imported table stats */
+ for (ind = 0; ind < nindexes; ind++)
+ {
+ AnlIndexData *thisdata = &indexdata[ind];
+
+ update_attstats(RelationGetRelid(Irel[ind]), false,
+ thisdata->attr_cnt, thisdata->vacattrstats);
+ }
+ }
+
+ extscont = key_lookup_object(cont, "extended");
+
+ if (extscont != NULL)
+ {
+ /* Build extended statistics (if there are any). */
+
+ ImportRelationExtStatistics(onerel, false, natts, vacattrstats, extscont);
+
+ if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ImportRelationExtStatistics(onerel, true, natts, vacattrstats, extscont);
+ }
+
+ if ((!inh) &&
+ (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE))
+ vac_close_indexes(nindexes, Irel, NoLock);
+}
+
+/*
+ * Apply all found statistics within a JSON structure to a pre-examined
+ * VacAttrStats.
+ */
+void
+ImportVacAttrStats( VacAttrStats *stats, JsonbContainer *cont)
+{
+ JsonbContainer *arraycont;
+ char *s;
+ int k;
+
+ /* nothing to import, skip */
+ if (cont == NULL)
+ return;
+
+ stats->stats_valid = true;
+
+ s = key_lookup_cstring(cont, "stanullfrac");
+ if (s != NULL)
+ {
+ stats->stanullfrac = float4in_internal(s, NULL, "real", s, NULL);
+ pfree(s);
+ }
+ s = key_lookup_cstring(cont, "stawidth");
+ if (s != NULL)
+ {
+ stats->stawidth = pg_strtoint32(s);
+ pfree(s);
+ }
+
+ s = key_lookup_cstring(cont, "stadistinct");
+ if (s != NULL)
+ {
+ stats->stadistinct = float4in_internal(s, NULL, "real", s, NULL);
+ pfree(s);
+ }
+
+ arraycont = key_lookup_array(cont, "stakinds");
+ if (arraycont != NULL)
+ {
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ StdAnalyzeData *mystats;
+ JsonbValue *e;
+ char *stakindstr;
+
+ mystats = (StdAnalyzeData *) stats->extra_data;
+ e = getIthJsonbValueFromContainer(arraycont, k);
+
+ if (e == NULL || (e->type != jbvString))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid format: stakind elements must be strings")));
+
+ stakindstr = JsonbStringValueToCString(e);
+ stats->stakind[k] = decode_stakind_string(stakindstr);
+ pfree(stakindstr);
+ pfree(e);
+
+ switch(stats->stakind[k])
+ {
+ case STATISTIC_KIND_MCV:
+ case STATISTIC_KIND_DECHIST:
+ stats->staop[k] = mystats->eqopr;
+ stats->stacoll[k] = stats->attrcollid;
+ break;
+
+ case STATISTIC_KIND_HISTOGRAM:
+ case STATISTIC_KIND_CORRELATION:
+ stats->staop[k] = mystats->ltopr;
+ stats->stacoll[k] = stats->attrcollid;
+ break;
+
+ case STATISTIC_KIND_MCELEM:
+ stats->staop[k] = TextEqualOperator;
+ stats->stacoll[k] = DEFAULT_COLLATION_OID;
+ break;
+
+ case STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM:
+ stats->staop[k] = Float8LessOperator;
+ stats->stacoll[k] = InvalidOid;
+ break;
+
+ case STATISTIC_KIND_BOUNDS_HISTOGRAM:
+ default:
+ stats->staop[k] = InvalidOid;
+ stats->stacoll[k] = InvalidOid;
+ break;
+ }
+ }
+ }
+
+ /* stanumbers is an array of arrays of floats */
+ arraycont = key_lookup_array(cont, "stanumbers");
+ if (arraycont != NULL)
+ {
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ JsonbValue *j;
+ JsonbContainer *numarr;
+ int nelems;
+ int e;
+
+ j = getIthJsonbValueFromContainer(arraycont, k);
+
+ /* skip out-of-bounds and explicit nulls */
+ if (j == NULL)
+ continue;
+
+ if (j->type == jbvNull)
+ {
+ pfree(j);
+ continue;
+ }
+
+ if ((j->type != jbvBinary) ||
+ (!JsonContainerIsArray(j->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stanumbers must be a binary array or null")));
+
+ numarr = j->val.binary.data;
+ pfree(j);
+
+ nelems = JsonContainerSize(numarr);
+ stats->numnumbers[k] = nelems;
+ stats->stanumbers[k] = (float4 *) palloc(nelems * sizeof(float4));
+
+ for (e = 0; e < nelems; e++)
+ {
+ JsonbValue *f = getIthJsonbValueFromContainer(numarr, e);
+ float4 floatval;
+ char *fstr;
+
+ /* skip out-of-bounds and explicit nulls */
+ if (f == NULL || f->type != jbvString)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, elements of stanumbers must a string")));
+
+ fstr = JsonbStringValueToCString(f);
+ floatval = float4in_internal(fstr, NULL, "realx3", fstr, NULL);
+ stats->stanumbers[k][e] = floatval;
+ pfree(f);
+ pfree(fstr);
+ }
+ }
+ }
+
+ /* stavalues is an array of arrays of the column attr type */
+ arraycont = key_lookup_array(cont, "stavalues");
+ if (arraycont != NULL)
+ {
+ Oid in_func;
+ Oid typioparam;
+ FmgrInfo finfo;
+
+ getTypeInputInfo(stats->attrtypid, &in_func, &typioparam);
+ fmgr_info(in_func, &finfo);
+
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ JsonbValue *j;
+ JsonbContainer *attrarr;
+ int nelems;
+ int e;
+
+ j = getIthJsonbValueFromContainer(arraycont, k);
+
+ /* skip out-of-bounds and explicit nulls */
+ if (j == NULL)
+ continue;
+
+ if (j->type == jbvNull)
+ {
+ pfree(j);
+ continue;
+ }
+
+ if ((j->type != jbvBinary) ||
+ (!JsonContainerIsArray(j->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stavalues must be a binary array or null")));
+
+ attrarr = j->val.binary.data;
+ pfree(j);
+ nelems = JsonContainerSize(attrarr);
+ stats->numvalues[k] = nelems;
+ stats->stavalues[k] = (Datum *) palloc(nelems * sizeof(Datum));
+
+ for (e = 0; e < nelems; e++)
+ {
+ JsonbValue *f;
+ char *fstr;
+ Datum datum;
+
+ f = getIthJsonbValueFromContainer(attrarr, e);
+
+ /* skip out-of-bounds and explicit nulls */
+ if (f == NULL || f->type != jbvString)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, elements of stavalues must be a string")));
+
+ fstr = JsonbStringValueToCString(f);
+ datum = InputFunctionCall(&finfo, fstr, typioparam, stats->attrtypmod);
+ stats->stavalues[k][e] = datum;
+ pfree(fstr);
+ pfree(f);
+ }
+ }
+ }
+}
diff --git a/src/backend/statistics/dependencies.c b/src/backend/statistics/dependencies.c
index edb2e5347d..f410510a28 100644
--- a/src/backend/statistics/dependencies.c
+++ b/src/backend/statistics/dependencies.c
@@ -27,7 +27,9 @@
#include "parser/parsetree.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
+#include "utils/builtins.h"
#include "utils/bytea.h"
+#include "utils/float.h"
#include "utils/fmgroids.h"
#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
@@ -1829,3 +1831,134 @@ dependencies_clauselist_selectivity(PlannerInfo *root,
return s1;
}
+
+
+/*
+ * imports functional dependencies between groups of columns
+ *
+ * Generates all possible subsets of columns (variations) and computes
+ * the degree of validity for each one. For example when creating statistics
+ * on three columns (a,b,c) there are 9 possible dependencies
+ *
+ * two columns three columns
+ * ----------- -------------
+ * (a) -> b (a,b) -> c
+ * (a) -> c (a,c) -> b
+ * (b) -> a (b,c) -> a
+ * (b) -> c
+ * (c) -> a
+ * (c) -> b
+ */
+MVDependencies *
+import_dependencies(JsonbContainer *cont)
+{
+ MVDependencies *dependencies = NULL;
+
+ int numcombs;
+ int i;
+
+ /* no dependencies to import */
+ if (cont == NULL)
+ return NULL;
+
+ /*
+ *
+ * format example:
+ *
+ * "stxdndependencies": <= you are here
+ * [
+ * {
+ * "attnums": [ "2", "-1" ],
+ * "degree": "0.500"
+ * },
+ * ...
+ * ]
+ *
+ */
+ numcombs = JsonContainerSize(cont);
+
+ /* no dependencies to import */
+ if (numcombs == 0)
+ return NULL;
+
+ dependencies = palloc(offsetof(MVDependencies, deps)
+ + numcombs * sizeof(MVDependency *));
+
+ dependencies->magic = STATS_DEPS_MAGIC;
+ dependencies->type = STATS_DEPS_TYPE_BASIC;
+ dependencies->ndeps = numcombs;
+
+ for (i = 0; i < numcombs; i++)
+ {
+ MVDependency *d = dependencies->deps[i];
+ JsonbValue *elem;
+ JsonbContainer *dep;
+ JsonbValue *attnums;
+ JsonbContainer *attnumscont;
+ JsonbValue *degree;
+ char *degree_str;
+ int numattnums;
+ int j;
+
+ elem = getIthJsonbValueFromContainer(cont, i);
+
+ if ((elem->type != jbvBinary) ||
+ (!JsonContainerIsObject(elem->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxnddependencies elements must be binary objects")));
+
+ dep = elem->val.binary.data;
+ pfree(elem);
+
+ attnums = getKeyJsonValueFromContainer(dep, "attnums", strlen("attnums"), NULL);
+ if ((attnums == NULL) || (attnums->type != jbvBinary) ||
+ (!JsonContainerIsArray(attnums->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndependencies must contain attnums array")));
+
+ attnumscont = attnums->val.binary.data;
+ pfree(attnums);
+ numattnums = JsonContainerSize(attnumscont);
+
+ d = palloc(offsetof(MVDependency, attributes)
+ + numattnums * sizeof(AttrNumber));
+
+ d->nattributes = numattnums;
+
+ for (j = 0; j < numattnums; j++)
+ {
+ JsonbValue *attnum;
+ char *s;
+
+ attnum = getIthJsonbValueFromContainer(attnumscont, j);
+
+ if (attnum->type != jbvString)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndependencies attnums elements must be strings")));
+
+ s = JsonbStringValueToCString(attnum);
+ pfree(attnum);
+
+ d->attributes[j] = pg_strtoint16(s);
+ pfree(s);
+ }
+
+ degree = getKeyJsonValueFromContainer(dep, "degree", strlen("degree"), NULL);
+ if ((degree == NULL) || (degree->type != jbvString))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndependencies elements must have degree element")));
+
+ degree_str = JsonbStringValueToCString(degree);
+ pfree(degree);
+ d->degree = float8in_internal(degree_str, NULL, "double", degree_str, NULL);
+ pfree(degree_str);
+ dependencies->deps[i] = d;
+ }
+
+
+ return dependencies;
+}
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index 9f67a57724..4e6e444f4c 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -2636,3 +2636,262 @@ make_build_data(Relation rel, StatExtEntry *stat, int numrows, HeapTuple *rows,
return result;
}
+
+/* form an array of pg_statistic rows (per update_attstats) */
+static Datum
+serialize_vacattrstats(VacAttrStats **exprstats, int nexprs)
+{
+ int exprno;
+ Oid typOid;
+ Relation sd;
+
+ ArrayBuildState *astate = NULL;
+
+ sd = table_open(StatisticRelationId, RowExclusiveLock);
+
+ /* lookup OID of composite type for pg_statistic */
+ typOid = get_rel_type_id(StatisticRelationId);
+ if (!OidIsValid(typOid))
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("relation \"%s\" does not have a composite type",
+ "pg_statistic")));
+
+ for (exprno = 0; exprno < nexprs; exprno++)
+ {
+ int i,
+ k;
+ VacAttrStats *stats = exprstats[exprno];
+
+ Datum values[Natts_pg_statistic];
+ bool nulls[Natts_pg_statistic];
+ HeapTuple stup;
+
+ if (!stats->stats_valid)
+ {
+ astate = accumArrayResult(astate,
+ (Datum) 0,
+ true,
+ typOid,
+ CurrentMemoryContext);
+ continue;
+ }
+
+ /*
+ * Construct a new pg_statistic tuple
+ */
+ for (i = 0; i < Natts_pg_statistic; ++i)
+ {
+ nulls[i] = false;
+ }
+
+ values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(InvalidOid);
+ values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(InvalidAttrNumber);
+ values[Anum_pg_statistic_stainherit - 1] = BoolGetDatum(false);
+ values[Anum_pg_statistic_stanullfrac - 1] = Float4GetDatum(stats->stanullfrac);
+ values[Anum_pg_statistic_stawidth - 1] = Int32GetDatum(stats->stawidth);
+ values[Anum_pg_statistic_stadistinct - 1] = Float4GetDatum(stats->stadistinct);
+ i = Anum_pg_statistic_stakind1 - 1;
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ values[i++] = Int16GetDatum(stats->stakind[k]); /* stakindN */
+ }
+ i = Anum_pg_statistic_staop1 - 1;
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ values[i++] = ObjectIdGetDatum(stats->staop[k]); /* staopN */
+ }
+ i = Anum_pg_statistic_stacoll1 - 1;
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ values[i++] = ObjectIdGetDatum(stats->stacoll[k]); /* stacollN */
+ }
+ i = Anum_pg_statistic_stanumbers1 - 1;
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ int nnum = stats->numnumbers[k];
+
+ if (nnum > 0)
+ {
+ int n;
+ Datum *numdatums = (Datum *) palloc(nnum * sizeof(Datum));
+ ArrayType *arry;
+
+ for (n = 0; n < nnum; n++)
+ numdatums[n] = Float4GetDatum(stats->stanumbers[k][n]);
+ arry = construct_array_builtin(numdatums, nnum, FLOAT4OID);
+ values[i++] = PointerGetDatum(arry); /* stanumbersN */
+ }
+ else
+ {
+ nulls[i] = true;
+ values[i++] = (Datum) 0;
+ }
+ }
+ i = Anum_pg_statistic_stavalues1 - 1;
+ for (k = 0; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ if (stats->numvalues[k] > 0)
+ {
+ ArrayType *arry;
+
+ arry = construct_array(stats->stavalues[k],
+ stats->numvalues[k],
+ stats->statypid[k],
+ stats->statyplen[k],
+ stats->statypbyval[k],
+ stats->statypalign[k]);
+ values[i++] = PointerGetDatum(arry); /* stavaluesN */
+ }
+ else
+ {
+ nulls[i] = true;
+ values[i++] = (Datum) 0;
+ }
+ }
+
+ stup = heap_form_tuple(RelationGetDescr(sd), values, nulls);
+
+ astate = accumArrayResult(astate,
+ heap_copy_tuple_as_datum(stup, RelationGetDescr(sd)),
+ false,
+ typOid,
+ CurrentMemoryContext);
+ }
+
+ table_close(sd, RowExclusiveLock);
+
+ return makeArrayResult(astate, CurrentMemoryContext);
+}
+
+/*
+ * Import requested extended stats, using the pre-computed (single-column)
+ * stats.
+ *
+ * This fetches a list of stats types from pg_statistic_ext, extracts the
+ * requested stats from the jsonb, and serializes them back into the catalog.
+ */
+void
+ImportRelationExtStatistics(Relation onerel, bool inh,
+ int natts,
+ VacAttrStats **vacattrstats,
+ JsonbContainer *cont)
+{
+ Relation pg_stext;
+ ListCell *lc;
+ List *statslist;
+
+ /* Do nothing if there are no columns to analyze. */
+ if (!natts)
+ return;
+
+ /* Do nothing if there are no stats to import */
+ if (cont == NULL)
+ return;
+
+ /* the list of stats has to be allocated outside the memory context */
+ pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock);
+ statslist = fetch_statentries_for_relation(pg_stext, RelationGetRelid(onerel));
+
+ /*
+ * format:
+ *
+ * { <= you are here
+ * <stat-name>:
+ * {
+ * "stxkinds": array of single characters (up to 3?),
+ * "stxdndistinct": [ {ndistinct}, ... ],
+ * "stxdndependencies": [ {dependency}, ... ]
+ * "stxdmcv": [ {mcv}, ... ]
+ * "stxdexprs" : [ {pg_statistic}, ... ]
+ * },
+ * ...
+ * }
+ */
+
+ foreach(lc, statslist)
+ {
+ StatExtEntry *stat = (StatExtEntry *) lfirst(lc);
+ MVNDistinct *ndistinct = NULL;
+ MVDependencies *dependencies = NULL;
+ MCVList *mcv = NULL;
+ Datum exprstats = (Datum) 0;
+ VacAttrStats **stats;
+ JsonbValue *exprval;
+ JsonbContainer *attrcont,
+ *ndistcont,
+ *depcont,
+ *exprcont;
+
+ /*
+ * Check if we can build these stats based on the column analyzed. If
+ * not, report this fact (except in autovacuum) and move on.
+ */
+ stats = lookup_var_attr_stats(onerel, stat->columns, stat->exprs,
+ natts, vacattrstats);
+ if (!stats)
+ continue;
+
+ attrcont = key_lookup_object(cont, stat->name);
+
+ /* staname not found, skip */
+ if (attrcont == NULL)
+ continue;
+
+ ndistcont = key_lookup_array(attrcont, "stxdndistinct");
+
+ if (ndistcont != NULL)
+ ndistinct = import_ndistinct(ndistcont);
+
+ depcont = key_lookup_array(attrcont, "stxdndependencies");
+ if (depcont != NULL)
+ dependencies = import_dependencies(depcont);
+
+ /* TODO mcvcont for "stxdmcv" and import_mcv() */
+
+ exprval = getKeyJsonValueFromContainer(attrcont, "stxdexprs", strlen("stxdexprs"), NULL);
+ if (exprval != NULL)
+ {
+ if (exprval->type != jbvNull)
+ {
+ int nexprs;
+ int k;
+
+ if (!JsonContainerIsArray(exprval->val.binary.data))
+ errmsg("invalid statistics format, stxndeprs must be array or null");
+
+ exprcont = exprval->val.binary.data;
+
+ nexprs = JsonContainerSize(exprcont);
+
+ for (k = 0; k < nexprs; k++)
+ {
+ JsonbValue *statelem;
+
+ statelem = getIthJsonbValueFromContainer(exprcont, k);
+ if ((statelem->type != jbvBinary) ||
+ (!JsonContainerIsObject(statelem->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxdexprs elements must be binary objects")));
+
+ ImportVacAttrStats(stats[k], statelem->val.binary.data);
+ pfree(statelem);
+ }
+
+ exprstats = serialize_vacattrstats(stats, nexprs);
+
+ }
+ pfree(exprval);
+ }
+
+
+ /* store the statistics in the catalog */
+ statext_store(stat->statOid, inh,
+ ndistinct, dependencies, mcv, exprstats, stats);
+ }
+
+ list_free(statslist);
+
+ table_close(pg_stext, RowExclusiveLock);
+}
diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c
index 03b9f04bb5..8fbb388028 100644
--- a/src/backend/statistics/mcv.c
+++ b/src/backend/statistics/mcv.c
@@ -2177,3 +2177,9 @@ mcv_clause_selectivity_or(PlannerInfo *root, StatisticExtInfo *stat,
return s;
}
+
+MCVList *import_mcv(JsonbContainer *cont)
+{
+ return NULL;
+}
+
diff --git a/src/backend/statistics/mvdistinct.c b/src/backend/statistics/mvdistinct.c
index 6d25c14644..8489a758a6 100644
--- a/src/backend/statistics/mvdistinct.c
+++ b/src/backend/statistics/mvdistinct.c
@@ -31,6 +31,8 @@
#include "lib/stringinfo.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
+#include "utils/builtins.h"
+#include "utils/float.h"
#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
@@ -698,3 +700,121 @@ generate_combinations(CombinationGenerator *state)
pfree(current);
}
+
+
+/*
+ * Import ndistinct values from JsonB.
+ *
+ * To handle expressions easily, we treat them as system attributes with
+ * negative attnums, and offset everything by number of expressions to
+ * allow using Bitmapsets.
+ */
+MVNDistinct *
+import_ndistinct(JsonbContainer *cont)
+{
+ MVNDistinct *result;
+ int numcombs;
+ int i;
+
+ /* no ndistinct to import */
+ if (cont == NULL)
+ return NULL;
+
+ /* each row of the JSON is a combination */
+ numcombs = JsonContainerSize(cont);
+
+ /* no ndistinct to import */
+ if (numcombs == 0)
+ return NULL;
+
+ result = palloc(offsetof(MVNDistinct, items) +
+ numcombs * sizeof(MVNDistinctItem));
+ result->magic = STATS_NDISTINCT_MAGIC;
+ result->type = STATS_NDISTINCT_TYPE_BASIC;
+ result->nitems = numcombs;
+
+ /*
+ * format example:
+ *
+ * "stxdndistinct": [
+ * {
+ * "attnums": [ "2", "1" ],
+ * "ndistinct": "4"
+ * },
+ * ...
+ * ]
+ *
+ */
+ for (i = 0; i < numcombs; i++)
+ {
+ MVNDistinctItem *item;
+ JsonbValue *elem;
+ JsonbContainer *combo;
+ JsonbValue *attnums;
+ JsonbContainer *attnumscont;
+ JsonbValue *ndistinct;
+ char *ndist_str;
+ int numattnums;
+ int j;
+
+ item = &result->items[i];
+
+ elem = getIthJsonbValueFromContainer(cont, i);
+
+ if ((elem->type != jbvBinary) ||
+ (!JsonContainerIsObject(elem->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndistinct elements must be a binary objects")));
+
+ combo = elem->val.binary.data;
+
+ attnums = getKeyJsonValueFromContainer(combo, "attnums", strlen("attnums"), NULL);
+ if ((attnums == NULL) || (attnums->type != jbvBinary) ||
+ (!JsonContainerIsArray(attnums->val.binary.data)))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndistinct must contain attnums array")));
+
+ attnumscont = attnums->val.binary.data;
+ numattnums = JsonContainerSize(attnumscont);
+
+ item->attributes = palloc(sizeof(AttrNumber) * numattnums);
+ item->nattributes = numattnums;
+
+ for (j = 0; j < numattnums; j++)
+ {
+ JsonbValue *attnum;
+ char *s;
+ attnum = getIthJsonbValueFromContainer(attnumscont, j);
+
+ if (attnum->type != jbvString)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndistinct attnums elements must be strings, but one is %s",
+ JsonbTypeName(attnum))));
+
+ s = JsonbStringValueToCString(attnum);
+
+ item->attributes[j] = pg_strtoint16(s);
+ pfree(s);
+ pfree(attnum);
+ }
+
+ ndistinct = getKeyJsonValueFromContainer(combo, "ndistinct", strlen("ndistinct"), NULL);
+ if ((ndistinct == NULL) || (ndistinct->type != jbvString))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid statistics format, stxndistinct elements must have ndistinct element")));
+
+ ndist_str = JsonbStringValueToCString(ndistinct);
+ item->ndistinct = float8in_internal(ndist_str, NULL, "double", ndist_str, NULL);
+
+ pfree(ndist_str);
+ pfree(ndistinct);
+ pfree(attnums);
+ pfree(elem);
+ }
+
+ return result;
+}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 4def90b805..4e609ca3b6 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -508,3 +508,156 @@ RESET ROLE;
DROP TABLE vacowned;
DROP TABLE vacowned_parted;
DROP ROLE regress_vacuum;
+CREATE TYPE stats_import_complex_type AS (
+ a integer,
+ b float,
+ c text,
+ d date,
+ e jsonb);
+CREATE TABLE stats_import_test(
+ id INTEGER PRIMARY KEY,
+ name text,
+ comp stats_import_complex_type
+);
+INSERT INTO stats_import_test
+SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import_complex_type
+UNION ALL
+SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import_complex_type
+UNION ALL
+SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import_complex_type
+UNION ALL
+SELECT 4, 'four', NULL;
+CREATE INDEX is_odd ON stats_import_test(((comp).a % 2 = 1));
+CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_test;
+ANALYZE stats_import_test;
+CREATE TABLE stats_export AS
+SELECT e.*
+FROM pg_catalog.pg_statistic_export AS e
+WHERE e.schemaname = 'public'
+AND e.relname = 'stats_import_test';
+SELECT c.reltuples AS before_tuples, c.relpages AS before_pages
+FROM pg_class AS c
+WHERE oid = 'stats_import_test'::regclass;
+ before_tuples | before_pages
+---------------+--------------
+ 4 | 1
+(1 row)
+
+-- test settting tuples and pages but no columns
+SELECT pg_import_rel_stats(c.oid, current_setting('server_version_num')::integer,
+ 1000.0, 200, NULL::jsonb)
+FROM pg_class AS c
+WHERE oid = 'stats_import_test'::regclass;
+ pg_import_rel_stats
+---------------------
+ t
+(1 row)
+
+SELECT c.reltuples AS after_tuples, c.relpages AS after_pages
+FROM pg_class AS c
+WHERE oid = 'stats_import_test'::regclass;
+ after_tuples | after_pages
+--------------+-------------
+ 1000 | 200
+(1 row)
+
+CREATE TEMPORARY TABLE orig_stats
+AS
+SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct,
+ stakind1, stakind2, stakind3, stakind4, stakind5,
+ staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
+ stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3,
+ stavalues4::text AS sv4, stavalues5::text AS sv5
+FROM pg_statistic AS s
+WHERE s.starelid = 'stats_import_test'::regclass;
+CREATE TEMPORARY TABLE orig_export
+AS
+SELECT e.*
+FROM stats_export AS e
+WHERE e.schemaname = 'public'
+AND e.relname = 'stats_import_test';
+-- create a table just like stats_import_test
+CREATE TABLE stats_import_clone ( LIKE stats_import_test );
+-- create an index that does not exist on stats_import_test
+CREATE INDEX is_even ON stats_import_clone(((comp).a % 2 = 0));
+-- rename statistics object to allow for a same-named one on the clone
+ALTER STATISTICS oddness RENAME TO oddness_original;
+CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_clone;
+-- copy table stats to clone table
+SELECT pg_import_rel_stats(c.oid, e.server_version_num,
+ e.n_tuples, e.n_pages, e.stats)
+FROM pg_class AS c
+JOIN pg_namespace AS n
+ON n.oid = c.relnamespace
+JOIN orig_export AS e
+ON e.schemaname = 'public'
+AND e.relname = 'stats_import_test'
+WHERE c.oid = 'stats_import_clone'::regclass;
+ pg_import_rel_stats
+---------------------
+ t
+(1 row)
+
+-- stats should match
+SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct,
+ stakind1, stakind2, stakind3, stakind4, stakind5,
+ staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
+ stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3,
+ stavalues4::text AS sv4, stavalues5::text AS sv5
+FROM pg_statistic AS s
+WHERE s.starelid = 'stats_import_test'::regclass
+EXCEPT
+SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct,
+ stakind1, stakind2, stakind3, stakind4, stakind5,
+ staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
+ stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3,
+ stavalues4::text AS sv4, stavalues5::text AS sv5
+FROM pg_statistic AS s
+WHERE s.starelid = 'stats_import_clone'::regclass;
+ staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5
+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----
+(0 rows)
+
+-- compare all except stxdmcv and oid-related stxdexprs
+SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr
+FROM pg_statistic_ext AS e
+JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid
+LEFT JOIN LATERAL (
+ SELECT
+ array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth,
+ x.stadistinct, x.stakind1, x.stakind2, x.stakind3,
+ x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2,
+ x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1,
+ x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5
+ )::text ORDER BY x.ordinality)
+ FROM unnest(d.stxdexpr) WITH ORDINALITY AS x
+) AS xpr(xpr) ON d.stxdexpr IS NOT NULL
+WHERE e.stxrelid = 'stats_import_test'::regclass
+AND e.stxname = 'oddness_original'
+EXCEPT
+SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr
+FROM pg_statistic_ext AS e
+JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid
+LEFT JOIN LATERAL (
+ SELECT
+ array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth,
+ x.stadistinct, x.stakind1, x.stakind2, x.stakind3,
+ x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2,
+ x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1,
+ x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5
+ )::text ORDER BY x.ordinality)
+ FROM unnest(d.stxdexpr) WITH ORDINALITY AS x
+) AS xpr(xpr) ON d.stxdexpr IS NOT NULL
+WHERE e.stxrelid = 'stats_import_clone'::regclass
+AND e.stxname = 'oddness';
+ stxdinherit | stxdndistinct | stxddependencies | xpr
+-------------+---------------+------------------+-----
+(0 rows)
+
+DROP TABLE stats_export;
+DROP TABLE stats_import_clone;
+DROP TABLE stats_import_test;
+DROP TYPE stats_import_complex_type;
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 51d7b1fecc..b42f35a8e3 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -377,3 +377,149 @@ RESET ROLE;
DROP TABLE vacowned;
DROP TABLE vacowned_parted;
DROP ROLE regress_vacuum;
+
+
+CREATE TYPE stats_import_complex_type AS (
+ a integer,
+ b float,
+ c text,
+ d date,
+ e jsonb);
+
+CREATE TABLE stats_import_test(
+ id INTEGER PRIMARY KEY,
+ name text,
+ comp stats_import_complex_type
+);
+
+INSERT INTO stats_import_test
+SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import_complex_type
+UNION ALL
+SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import_complex_type
+UNION ALL
+SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import_complex_type
+UNION ALL
+SELECT 4, 'four', NULL;
+
+CREATE INDEX is_odd ON stats_import_test(((comp).a % 2 = 1));
+
+CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_test;
+
+ANALYZE stats_import_test;
+
+CREATE TABLE stats_export AS
+SELECT e.*
+FROM pg_catalog.pg_statistic_export AS e
+WHERE e.schemaname = 'public'
+AND e.relname = 'stats_import_test';
+
+SELECT c.reltuples AS before_tuples, c.relpages AS before_pages
+FROM pg_class AS c
+WHERE oid = 'stats_import_test'::regclass;
+
+-- test settting tuples and pages but no columns
+SELECT pg_import_rel_stats(c.oid, current_setting('server_version_num')::integer,
+ 1000.0, 200, NULL::jsonb)
+FROM pg_class AS c
+WHERE oid = 'stats_import_test'::regclass;
+
+SELECT c.reltuples AS after_tuples, c.relpages AS after_pages
+FROM pg_class AS c
+WHERE oid = 'stats_import_test'::regclass;
+
+CREATE TEMPORARY TABLE orig_stats
+AS
+SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct,
+ stakind1, stakind2, stakind3, stakind4, stakind5,
+ staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
+ stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3,
+ stavalues4::text AS sv4, stavalues5::text AS sv5
+FROM pg_statistic AS s
+WHERE s.starelid = 'stats_import_test'::regclass;
+
+CREATE TEMPORARY TABLE orig_export
+AS
+SELECT e.*
+FROM stats_export AS e
+WHERE e.schemaname = 'public'
+AND e.relname = 'stats_import_test';
+
+-- create a table just like stats_import_test
+CREATE TABLE stats_import_clone ( LIKE stats_import_test );
+
+-- create an index that does not exist on stats_import_test
+CREATE INDEX is_even ON stats_import_clone(((comp).a % 2 = 0));
+
+-- rename statistics object to allow for a same-named one on the clone
+ALTER STATISTICS oddness RENAME TO oddness_original;
+
+CREATE STATISTICS oddness ON name, ((comp).a % 2 = 1) FROM stats_import_clone;
+
+-- copy table stats to clone table
+SELECT pg_import_rel_stats(c.oid, e.server_version_num,
+ e.n_tuples, e.n_pages, e.stats)
+FROM pg_class AS c
+JOIN pg_namespace AS n
+ON n.oid = c.relnamespace
+JOIN orig_export AS e
+ON e.schemaname = 'public'
+AND e.relname = 'stats_import_test'
+WHERE c.oid = 'stats_import_clone'::regclass;
+
+-- stats should match
+SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct,
+ stakind1, stakind2, stakind3, stakind4, stakind5,
+ staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
+ stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3,
+ stavalues4::text AS sv4, stavalues5::text AS sv5
+FROM pg_statistic AS s
+WHERE s.starelid = 'stats_import_test'::regclass
+EXCEPT
+SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct,
+ stakind1, stakind2, stakind3, stakind4, stakind5,
+ staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5,
+ stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5,
+ stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3,
+ stavalues4::text AS sv4, stavalues5::text AS sv5
+FROM pg_statistic AS s
+WHERE s.starelid = 'stats_import_clone'::regclass;
+
+-- compare all except stxdmcv and oid-related stxdexprs
+SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr
+FROM pg_statistic_ext AS e
+JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid
+LEFT JOIN LATERAL (
+ SELECT
+ array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth,
+ x.stadistinct, x.stakind1, x.stakind2, x.stakind3,
+ x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2,
+ x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1,
+ x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5
+ )::text ORDER BY x.ordinality)
+ FROM unnest(d.stxdexpr) WITH ORDINALITY AS x
+) AS xpr(xpr) ON d.stxdexpr IS NOT NULL
+WHERE e.stxrelid = 'stats_import_test'::regclass
+AND e.stxname = 'oddness_original'
+EXCEPT
+SELECT d.stxdinherit, d.stxdndistinct, d.stxddependencies, xpr.xpr
+FROM pg_statistic_ext AS e
+JOIN pg_statistic_ext_data AS d ON d.stxoid = e.oid
+LEFT JOIN LATERAL (
+ SELECT
+ array_agg(ROW(x.staattnum, x.stainherit, x.stanullfrac, x.stawidth,
+ x.stadistinct, x.stakind1, x.stakind2, x.stakind3,
+ x.stakind4, x.stakind5, x.stanumbers1, x.stanumbers2,
+ x.stanumbers3, x.stanumbers4, x.stanumbers5, x.stavalues1,
+ x.stavalues2, x.stavalues3, x.stavalues4, x.stavalues5
+ )::text ORDER BY x.ordinality)
+ FROM unnest(d.stxdexpr) WITH ORDINALITY AS x
+) AS xpr(xpr) ON d.stxdexpr IS NOT NULL
+WHERE e.stxrelid = 'stats_import_clone'::regclass
+AND e.stxname = 'oddness';
+
+DROP TABLE stats_export;
+DROP TABLE stats_import_clone;
+DROP TABLE stats_import_test;
+DROP TYPE stats_import_complex_type;
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c76ec52c55..61e01d1b90 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28014,6 +28014,48 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
in identifying the specific disk files associated with database objects.
</para>
+ <table id="functions-admin-statsimport">
+ <title>Database Object Statistics Import Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_import_rel_stats</primary>
+ </indexterm>
+ <function>pg_import_rel_stats</function> ( <parameter>relation</parameter> <type>regclass</type>, <parameter>server_version_num</parameter> <type>integer</type>, <parameter>num_tuples</parameter> <type>float4</type>, <parameter>num_pages</parameter> <type>integer</type>, <parameter>column_stats</parameter> <type>jsonb</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Modifies the <structname>pg_class</structname> row with the
+ <structfield>oid</structfield> matching <parameter>relation</parameter>
+ to set the <structfield>reltuples</structfield> and
+ <structfield>relpages</structfield> fields. This is done nontransactionally.
+ The <structname>pg_statistic</structname> rows for the
+ <structfield>statrelid</structfield> matching <parameter>relation</parameter>
+ are replaced with the values found in <parameter>column_stats</parameter>,
+ and this is done transactionally. The purpose of this function is to apply
+ statistics values in an upgrade situation that are "good enough" for system
+ operation until they are replaced by the next auto-analyze. This function
+ is used by <program>pg_upgrade</program> and <program>pg_restore</program>
+ to convey the statistics from the old system version into the new one.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<table id="functions-admin-dblocation">
<title>Database Object Location Functions</title>
<tgroup cols="1">
--
2.41.0
[text/x-patch] v2-0004-Add-pg_export_stats-pg_import_stats.patch (49.5K, ../CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com/6-v2-0004-Add-pg_export_stats-pg_import_stats.patch)
download | inline diff:
From da9b9a4012757a8bb08711d4479ce5ebb38e974d Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Tue, 31 Oct 2023 03:13:24 -0400
Subject: [PATCH v2 4/4] 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/Makefile | 6 +-
src/bin/scripts/pg_export_stats.c | 946 ++++++++++++++++++++++++++++++
src/bin/scripts/pg_import_stats.c | 303 ++++++++++
3 files changed, 1254 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/Makefile b/src/bin/scripts/Makefile
index a7a9d0fea5..da45fcfa6a 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..b9fc192e8f
--- /dev/null
+++ b/src/bin/scripts/pg_export_stats.c
@@ -0,0 +1,946 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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 ";
+
+/* v15-v16 have the same stats layout, but lacks view definition */
+const char *export_query_v15 =
+ "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, "
+ " ( "
+ " WITH per_column_stats AS "
+ " ( "
+ " SELECT "
+ " s.stainherit, "
+ " 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::text[], "
+ " s.stanumbers2::text::text[], "
+ " s.stanumbers3::text::text[], "
+ " s.stanumbers4::text::text[], "
+ " s.stanumbers5::text::text[]), "
+ " 'stavalues', "
+ " jsonb_build_array( "
+ " s.stavalues1::text::text[], "
+ " s.stavalues2::text::text[], "
+ " s.stavalues3::text::text[], "
+ " s.stavalues4::text::text[], "
+ " s.stavalues5::text::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') "
+ " ), "
+ " attagg AS "
+ " ( "
+ " SELECT "
+ " pcs.stainherit, "
+ " jsonb_build_object( "
+ " 'columns', "
+ " jsonb_object_agg( "
+ " pcs.attname, "
+ " pcs.stats "
+ " ) "
+ " ) AS stats "
+ " FROM per_column_stats AS pcs "
+ " GROUP BY pcs.stainherit "
+ " ), "
+ " extended_object_stats AS "
+ " ( "
+ " SELECT "
+ " sd.stxdinherit, "
+ " e.stxname, "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ndist.stxdndistinct, "
+ " 'stxdndependencies', "
+ " ndep.stxdndependencies, "
+ " 'stxdmcv', "
+ " mcv.stxdmcv, "
+ " 'stxdexprs', "
+ " x.stdxdexprs "
+ " ) AS stats "
+ " FROM pg_statistic_ext AS e "
+ " JOIN pg_statistic_ext_data AS sd "
+ " ON sd.stxoid = e.oid "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', string_to_array(nd.attnums, ', '), "
+ " 'ndistinct', nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(sd.stxdndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " ) AS ndist(stxdndistinct) ON sd.stxdndistinct IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array( replace(dep.attrs, ' => ', ', '), ', '), "
+ " 'degree', "
+ " dep.degree "
+ " ) "
+ " ORDER BY dep.ord "
+ " ) "
+ " FROM json_each_text(sd.stxddependencies::text::json) "
+ " WITH ORDINALITY AS dep(attrs, degree, ord) "
+ " ) AS ndep(stxdndependencies) ON sd.stxddependencies IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT NULL AS stxdmcv "
+ " ) AS mcv(stxdmcv) ON sd.stxdmcv IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " 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::text[], "
+ " s.stanumbers2::text::text[], "
+ " s.stanumbers3::text::text[], "
+ " s.stanumbers4::text::text[], "
+ " s.stanumbers5::text::text[]), "
+ " 'stavalues', "
+ " jsonb_build_array( "
+ " s.stavalues1::text::text[], "
+ " s.stavalues2::text::text[], "
+ " s.stavalues3::text::text[], "
+ " s.stavalues4::text::text[], "
+ " s.stavalues5::text::text[]) "
+ " ) "
+ " ORDER BY s.ordinality "
+ " ) "
+ " FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s "
+ " ) AS x(stdxdexprs) ON sd.stxdexpr IS NOT NULL "
+ " WHERE e.stxrelid = r.oid "
+ " ), "
+ " extagg AS "
+ " ( "
+ " SELECT "
+ " eos.stxdinherit, "
+ " jsonb_build_object( "
+ " 'extended', "
+ " jsonb_object_agg( "
+ " eos.stxname, "
+ " eos.stats "
+ " ) "
+ " ) AS stats "
+ " FROM extended_object_stats AS eos "
+ " GROUP BY eos.stxdinherit "
+ " ) "
+ " SELECT "
+ " jsonb_object_agg( "
+ " CASE coalesce(a.stainherit, e.stxdinherit) "
+ " WHEN TRUE THEN 'inherited' "
+ " ELSE 'regular' "
+ " END, "
+ " coalesce(a.stats, '{}'::jsonb) || coalesce(e.stats, '{}'::jsonb) "
+ " ) "
+ " FROM attagg AS a "
+ " FULL OUTER JOIN extagg e ON a.stainherit = e.stxdinherit "
+ " ) AS stats "
+ "FROM pg_class AS r "
+ "JOIN pg_namespace AS n "
+ " ON n.oid = r.relnamespace "
+ "WHERE relkind IN ('r', 'm', 'f', 'p') "
+ "AND n.nspname NOT IN ('pg_catalog', 'information_schema')";
+
+/* v14 is like v15, but lacks stxdinherit on ext_data */
+const char *export_query_v14 =
+ "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, "
+ " ( "
+ " WITH per_column_stats AS "
+ " ( "
+ " SELECT "
+ " s.stainherit, "
+ " 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::text[], "
+ " s.stanumbers2::text::text[], "
+ " s.stanumbers3::text::text[], "
+ " s.stanumbers4::text::text[], "
+ " s.stanumbers5::text::text[]), "
+ " 'stavalues', "
+ " jsonb_build_array( "
+ " s.stavalues1::text::text[], "
+ " s.stavalues2::text::text[], "
+ " s.stavalues3::text::text[], "
+ " s.stavalues4::text::text[], "
+ " s.stavalues5::text::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') "
+ " ), "
+ " attagg AS "
+ " ( "
+ " SELECT "
+ " pcs.stainherit, "
+ " jsonb_build_object( "
+ " 'columns', "
+ " jsonb_object_agg( "
+ " pcs.attname, "
+ " pcs.stats "
+ " ) "
+ " ) AS stats "
+ " FROM per_column_stats AS pcs "
+ " GROUP BY pcs.stainherit "
+ " ), "
+ " extended_object_stats AS "
+ " ( "
+ " SELECT "
+ " false AS stxdinherit, "
+ " e.stxname, "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ndist.stxdndistinct, "
+ " 'stxdndependencies', "
+ " ndep.stxdndependencies, "
+ " 'stxdmcv', "
+ " mcv.stxdmcv, "
+ " 'stxdexprs', "
+ " x.stdxdexprs "
+ " ) AS stats "
+ " FROM pg_statistic_ext AS e "
+ " JOIN pg_statistic_ext_data AS sd "
+ " ON sd.stxoid = e.oid "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', string_to_array(nd.attnums, ', '), "
+ " 'ndistinct', nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(sd.stxdndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " ) AS ndist(stxdndistinct) ON sd.stxdndistinct IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array( replace(dep.attrs, ' => ', ', '), ', '), "
+ " 'degree', "
+ " dep.degree "
+ " ) "
+ " ORDER BY dep.ord "
+ " ) "
+ " FROM json_each_text(sd.stxddependencies::text::json) "
+ " WITH ORDINALITY AS dep(attrs, degree, ord) "
+ " ) AS ndep(stxdndependencies) ON sd.stxddependencies IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT NULL AS stxdmcv "
+ " ) AS mcv(stxdmcv) ON sd.stxdmcv IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " 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::text[], "
+ " s.stanumbers2::text::text[], "
+ " s.stanumbers3::text::text[], "
+ " s.stanumbers4::text::text[], "
+ " s.stanumbers5::text::text[]), "
+ " 'stavalues', "
+ " jsonb_build_array( "
+ " s.stavalues1::text::text[], "
+ " s.stavalues2::text::text[], "
+ " s.stavalues3::text::text[], "
+ " s.stavalues4::text::text[], "
+ " s.stavalues5::text::text[]) "
+ " ) "
+ " ORDER BY s.ordinality "
+ " ) "
+ " FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s "
+ " ) AS x(stdxdexprs) ON sd.stxdexpr IS NOT NULL "
+ " WHERE e.stxrelid = r.oid "
+ " ), "
+ " extagg AS "
+ " ( "
+ " SELECT "
+ " eos.stxdinherit, "
+ " jsonb_build_object( "
+ " 'extended', "
+ " jsonb_object_agg( "
+ " eos.stxname, "
+ " eos.stats "
+ " ) "
+ " ) AS stats "
+ " FROM extended_object_stats AS eos "
+ " GROUP BY eos.stxdinherit "
+ " ) "
+ " SELECT "
+ " jsonb_object_agg( "
+ " CASE coalesce(a.stainherit, e.stxdinherit) "
+ " WHEN TRUE THEN 'inherited' "
+ " ELSE 'regular' "
+ " END, "
+ " coalesce(a.stats, '{}'::jsonb) || coalesce(e.stats, '{}'::jsonb) "
+ " ) "
+ " FROM attagg AS a "
+ " FULL OUTER JOIN extagg e ON a.stainherit = e.stxdinherit "
+ " ) AS stats "
+ "FROM pg_class AS r "
+ "JOIN pg_namespace AS n "
+ " ON n.oid = r.relnamespace "
+ "WHERE relkind IN ('r', 'm', 'f', 'p') "
+ "AND n.nspname NOT IN ('pg_catalog', 'information_schema')";
+
+/* v12-v13 are like v14, but lacks stxdexpr on ext_data */
+const char *export_query_v12 =
+ "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, "
+ " ( "
+ " WITH per_column_stats AS "
+ " ( "
+ " SELECT "
+ " s.stainherit, "
+ " 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::text[], "
+ " s.stanumbers2::text::text[], "
+ " s.stanumbers3::text::text[], "
+ " s.stanumbers4::text::text[], "
+ " s.stanumbers5::text::text[]), "
+ " 'stavalues', "
+ " jsonb_build_array( "
+ " s.stavalues1::text::text[], "
+ " s.stavalues2::text::text[], "
+ " s.stavalues3::text::text[], "
+ " s.stavalues4::text::text[], "
+ " s.stavalues5::text::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') "
+ " ), "
+ " attagg AS "
+ " ( "
+ " SELECT "
+ " pcs.stainherit, "
+ " jsonb_build_object( "
+ " 'columns', "
+ " jsonb_object_agg( "
+ " pcs.attname, "
+ " pcs.stats "
+ " ) "
+ " ) AS stats "
+ " FROM per_column_stats AS pcs "
+ " GROUP BY pcs.stainherit "
+ " ), "
+ " extended_object_stats AS "
+ " ( "
+ " SELECT "
+ " false AS stxdinherit, "
+ " e.stxname, "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ndist.stxdndistinct, "
+ " 'stxdndependencies', "
+ " ndep.stxdndependencies, "
+ " 'stxdmcv', "
+ " mcv.stxdmcv, "
+ " ) AS stats "
+ " FROM pg_statistic_ext AS e "
+ " JOIN pg_statistic_ext_data AS sd "
+ " ON sd.stxoid = e.oid "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', string_to_array(nd.attnums, ', '), "
+ " 'ndistinct', nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(sd.stxdndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " ) AS ndist(stxdndistinct) ON sd.stxdndistinct IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array( replace(dep.attrs, ' => ', ', '), ', '), "
+ " 'degree', "
+ " dep.degree "
+ " ) "
+ " ORDER BY dep.ord "
+ " ) "
+ " FROM json_each_text(sd.stxddependencies::text::json) "
+ " WITH ORDINALITY AS dep(attrs, degree, ord) "
+ " ) AS ndep(stxdndependencies) ON sd.stxddependencies IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT NULL AS stxdmcv "
+ " ) AS mcv(stxdmcv) ON sd.stxdmcv IS NOT NULL "
+ " WHERE e.stxrelid = r.oid "
+ " ), "
+ " extagg AS "
+ " ( "
+ " SELECT "
+ " eos.stxdinherit, "
+ " jsonb_build_object( "
+ " 'extended', "
+ " jsonb_object_agg( "
+ " eos.stxname, "
+ " eos.stats "
+ " ) "
+ " ) AS stats "
+ " FROM extended_object_stats AS eos "
+ " GROUP BY eos.stxdinherit "
+ " ) "
+ " SELECT "
+ " jsonb_object_agg( "
+ " CASE coalesce(a.stainherit, e.stxdinherit) "
+ " WHEN TRUE THEN 'inherited' "
+ " ELSE 'regular' "
+ " END, "
+ " coalesce(a.stats, '{}'::jsonb) || coalesce(e.stats, '{}'::jsonb) "
+ " ) "
+ " FROM attagg AS a "
+ " FULL OUTER JOIN extagg e ON a.stainherit = e.stxdinherit "
+ " ) AS stats "
+ "FROM pg_class AS r "
+ "JOIN pg_namespace AS n "
+ " ON n.oid = r.relnamespace "
+ "WHERE relkind IN ('r', 'm', 'f', 'p') "
+ "AND n.nspname NOT IN ('pg_catalog', 'information_schema')";
+
+/* v10-v11 are like v12, but ext_data is gone and ndistinct and dependencies are on ext */
+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, "
+ " ( "
+ " WITH per_column_stats AS "
+ " ( "
+ " SELECT "
+ " s.stainherit, "
+ " 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::text[], "
+ " s.stanumbers2::text::text[], "
+ " s.stanumbers3::text::text[], "
+ " s.stanumbers4::text::text[], "
+ " s.stanumbers5::text::text[]), "
+ " 'stavalues', "
+ " jsonb_build_array( "
+ " s.stavalues1::text::text[], "
+ " s.stavalues2::text::text[], "
+ " s.stavalues3::text::text[], "
+ " s.stavalues4::text::text[], "
+ " s.stavalues5::text::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') "
+ " ), "
+ " attagg AS "
+ " ( "
+ " SELECT "
+ " pcs.stainherit, "
+ " jsonb_build_object( "
+ " 'columns', "
+ " jsonb_object_agg( "
+ " pcs.attname, "
+ " pcs.stats "
+ " ) "
+ " ) AS stats "
+ " FROM per_column_stats AS pcs "
+ " GROUP BY pcs.stainherit "
+ " ), "
+ " extended_object_stats AS "
+ " ( "
+ " SELECT "
+ " false AS stxdinherit, "
+ " e.stxname, "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ndist.stxdndistinct, "
+ " 'stxdndependencies', "
+ " ndep.stxdndependencies, "
+ " ) AS stats "
+ " FROM pg_statistic_ext AS e "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', string_to_array(nd.attnums, ', '), "
+ " 'ndistinct', nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(e.stxndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " ) AS ndist(stxdndistinct) ON e.stxndistinct IS NOT NULL "
+ " LEFT JOIN LATERAL "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array( replace(dep.attrs, ' => ', ', '), ', '), "
+ " 'degree', "
+ " dep.degree "
+ " ) "
+ " ORDER BY dep.ord "
+ " ) "
+ " FROM json_each_text(.stxdependencies::text::json) "
+ " WITH ORDINALITY AS dep(attrs, degree, ord) "
+ " ) AS ndep(stxdndependencies) ON e.stxdependencies IS NOT NULL "
+ " WHERE e.stxrelid = r.oid "
+ " ), "
+ " extagg AS "
+ " ( "
+ " SELECT "
+ " eos.stxdinherit, "
+ " jsonb_build_object( "
+ " 'extended', "
+ " jsonb_object_agg( "
+ " eos.stxname, "
+ " eos.stats "
+ " ) "
+ " ) AS stats "
+ " FROM extended_object_stats AS eos "
+ " GROUP BY eos.stxdinherit "
+ " ) "
+ " SELECT "
+ " jsonb_object_agg( "
+ " CASE coalesce(a.stainherit, e.stxdinherit) "
+ " WHEN TRUE THEN 'inherited' "
+ " ELSE 'regular' "
+ " END, "
+ " coalesce(a.stats, '{}'::jsonb) || coalesce(e.stats, '{}'::jsonb) "
+ " ) "
+ " FROM attagg AS a "
+ " FULL OUTER JOIN extagg e ON a.stainherit = e.stxdinherit "
+ " ) AS stats "
+ "FROM pg_class AS r "
+ "JOIN pg_namespace AS n "
+ " ON n.oid = r.relnamespace "
+ "WHERE relkind IN ('r', 'm', 'f', 'p') "
+ "AND n.nspname NOT IN ('pg_catalog', '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) >= 150000)
+ appendPQExpBufferStr(&sql, export_query_v15);
+ else if (PQserverVersion(conn) >= 140000)
+ appendPQExpBufferStr(&sql, export_query_v14);
+ else if (PQserverVersion(conn) >= 120000)
+ appendPQExpBufferStr(&sql, export_query_v12);
+ 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..cfcadff769
--- /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.41.0
view thread (21+ 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]
Subject: Re: Statistics Import and Export
In-Reply-To: <CADkLM=dTHVSrcGBAstjshoZBXKJgWjzN3Vj53KQ9fORqvkaN5Q@mail.gmail.com>
* 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