v3-0002-Add-system-view-pg_statistic_export.patch
text/x-patch
Filename: v3-0002-Add-system-view-pg_statistic_export.patch
Type: text/x-patch
Part: 0
Message:
Re: Statistics Import and Export
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v3-0002
Subject: Add system view pg_statistic_export.
| File | + | − |
|---|---|---|
| doc/src/sgml/system-views.sgml | 5 | 0 |
| src/backend/catalog/system_views.sql | 84 | 0 |
| src/test/regress/expected/rules.out | 31 | 0 |
From 4ae4ac484bcaf4b74fef1284d63e6e6d4d6a236f Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Tue, 12 Dec 2023 20:48:42 -0500
Subject: [PATCH v3 2/9] Add system view pg_statistic_export.
This view is designed to aid in the export (and re-import) of table
statistics, mostly for upgrade/restore situations.
---
src/backend/catalog/system_views.sql | 84 ++++++++++++++++++++++++++++
src/test/regress/expected/rules.out | 31 ++++++++++
doc/src/sgml/system-views.sgml | 5 ++
3 files changed, 120 insertions(+)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 11d18ed9dd..7655bf7458 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -274,6 +274,90 @@ 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,
+ (
+ SELECT
+ jsonb_object_agg(
+ CASE
+ WHEN a.stainherit THEN 'inherited'
+ ELSE 'regular'
+ END,
+ a.stats
+ )
+ FROM
+ (
+ SELECT
+ s.stainherit,
+ jsonb_object_agg(
+ a.attname,
+ jsonb_build_object(
+ 'stanullfrac', s.stanullfrac::text,
+ 'stawidth', s.stawidth::text,
+ 'stadistinct', s.stadistinct::text,
+ 'stakinds',
+ (
+ SELECT
+ jsonb_agg(
+ CASE kind.kind
+ WHEN 0 THEN 'TRIVIAL'
+ WHEN 1 THEN 'MCV'
+ WHEN 2 THEN 'HISTOGRAM'
+ WHEN 3 THEN 'CORRELATION'
+ WHEN 4 THEN 'MCELEM'
+ WHEN 5 THEN 'DECHIST'
+ WHEN 6 THEN 'RANGE_LENGTH_HISTOGRAM'
+ WHEN 7 THEN 'BOUNDS_HISTOGRAM'
+ END::text
+ ORDER BY kind.ord)
+ FROM unnest(ARRAY[s.stakind1, s.stakind2,
+ s.stakind3, stakind4,
+ s.stakind5])
+ WITH ORDINALITY AS kind(kind, ord)
+ ),
+ 'stanumbers',
+ jsonb_build_array(
+ s.stanumbers1::text,
+ s.stanumbers2::text,
+ s.stanumbers3::text,
+ s.stanumbers4::text,
+ s.stanumbers5::text),
+ 'stavalues',
+ jsonb_build_array(
+ -- casting to text makes it easier to import using array_in()
+ s.stavalues1::text,
+ s.stavalues2::text,
+ s.stavalues3::text,
+ s.stavalues4::text,
+ s.stavalues5::text)
+ )
+ ) AS stats
+ FROM pg_attribute AS a
+ JOIN pg_statistic AS s
+ ON s.starelid = a.attrelid
+ AND s.staattnum = a.attnum
+ WHERE a.attrelid = r.oid
+ AND NOT a.attisdropped
+ AND a.attnum > 0
+ AND has_column_privilege(a.attrelid, a.attnum, 'SELECT')
+ GROUP BY s.stainherit
+ ) AS a
+ ) AS stats
+ FROM pg_class AS r
+ JOIN pg_namespace AS n
+ ON n.oid = r.relnamespace
+ WHERE relkind IN ('r', 'm', 'f', 'p', 'i')
+ AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema');
+
+
+
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 05070393b9..f2b059af5e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2404,6 +2404,37 @@ 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,
+ ( SELECT jsonb_object_agg(
+ CASE
+ WHEN a.stainherit THEN 'inherited'::text
+ ELSE 'regular'::text
+ END, a.stats) AS jsonb_object_agg
+ FROM ( SELECT s.stainherit,
+ jsonb_object_agg(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, (s.stanumbers2)::text, (s.stanumbers3)::text, (s.stanumbers4)::text, (s.stanumbers5)::text), 'stavalues', jsonb_build_array((s.stavalues1)::text, (s.stavalues2)::text, (s.stavalues3)::text, (s.stavalues4)::text, (s.stavalues5)::text))) AS stats
+ FROM (pg_attribute 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))
+ GROUP BY s.stainherit) a) 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", 'i'::"char"])) AND (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'pg_toast'::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 0ef1745631..91b3ab22fb 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.43.0