v6-0003-Add-common-functions-for-exporting-statistics.patch
text/x-patch
Filename: v6-0003-Add-common-functions-for-exporting-statistics.patch
Type: text/x-patch
Part: 2
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 v6-0003
Subject: Add common functions for exporting statistics.
| File | + | − |
|---|---|---|
| src/fe_utils/Makefile | 1 | 0 |
| src/fe_utils/meson.build | 1 | 0 |
| src/fe_utils/stats_export.c | 880 | 0 |
| src/include/fe_utils/stats_export.h | 35 | 0 |
From 78c35f045bb3194bf03b8236b2ac951e4082e2a1 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Sat, 17 Feb 2024 14:20:55 -0500
Subject: [PATCH v6 3/4] Add common functions for exporting statistics.
Creates functions that aid the caller in generating the
version-appropriate function for extracting pg_statistic and
pg_statistic_ext data, and forming the SQL command to import those same
statistics.
These functions will be used by pg_export_stats and pg_dump.
---
src/include/fe_utils/stats_export.h | 35 ++
src/fe_utils/Makefile | 1 +
src/fe_utils/meson.build | 1 +
src/fe_utils/stats_export.c | 880 ++++++++++++++++++++++++++++
4 files changed, 917 insertions(+)
create mode 100644 src/include/fe_utils/stats_export.h
create mode 100644 src/fe_utils/stats_export.c
diff --git a/src/include/fe_utils/stats_export.h b/src/include/fe_utils/stats_export.h
new file mode 100644
index 0000000000..4f80d110cf
--- /dev/null
+++ b/src/include/fe_utils/stats_export.h
@@ -0,0 +1,35 @@
+/*-------------------------------------------------------------------------
+ *
+ * stats_export.h
+ * Queries to export statistics from current and past versions.
+ *
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1995, Regents of the University of California
+ *
+ * src/include/varatt.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#ifndef STATS_EXPORT_H
+#define STATS_EXPORT_H
+
+extern const char *stats_export_rel_query(int server_version_num);
+extern const char *stats_export_ext_query(int server_version_num);
+
+extern void stats_export_print_rel_import(FILE *outf,
+ const char *nspname_literal,
+ const char *relname_literal,
+ const char *stats_json_literal,
+ bool validate,
+ bool require_match);
+extern void stats_export_print_ext_import(FILE *outf,
+ const char *nspname_literal,
+ const char *relname_literal,
+ const char *stxname_literal,
+ const char *ext_stats_json_literal,
+ bool validate,
+ bool require_match);
+
+#endif
diff --git a/src/fe_utils/Makefile b/src/fe_utils/Makefile
index 946c05258f..c734f9f6d3 100644
--- a/src/fe_utils/Makefile
+++ b/src/fe_utils/Makefile
@@ -32,6 +32,7 @@ OBJS = \
query_utils.o \
recovery_gen.o \
simple_list.o \
+ stats_export.o \
string_utils.o
ifeq ($(PORTNAME), win32)
diff --git a/src/fe_utils/meson.build b/src/fe_utils/meson.build
index 14d0482a2c..fce503f641 100644
--- a/src/fe_utils/meson.build
+++ b/src/fe_utils/meson.build
@@ -12,6 +12,7 @@ fe_utils_sources = files(
'query_utils.c',
'recovery_gen.c',
'simple_list.c',
+ 'stats_export.c',
'string_utils.c',
)
diff --git a/src/fe_utils/stats_export.c b/src/fe_utils/stats_export.c
new file mode 100644
index 0000000000..1815793c9d
--- /dev/null
+++ b/src/fe_utils/stats_export.c
@@ -0,0 +1,880 @@
+/*-------------------------------------------------------------------------
+ *
+ * Utility functions for extracting object statistics for frontend code
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/fe_utils/stats_export.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres_fe.h"
+
+#include "fe_utils/stats_export.h"
+
+/*
+ * The following are query fragments that build one or more JSONB objects
+ * in the format expected by pg_import_rel_stats(). They cannot be used
+ * unmodified, as they are missing the SELECT clause. In addition, they are
+ * missing a WHERE clause to filter results. The expectation is that the
+ * the caller will do one of the following:
+ * - Prepend "SELECT stats_json " and append a WHERE clause to the query
+ * that identifies a single object.
+ * - Similar to the previous option, but make it a correlated subquery in
+ * a larger query.
+ * - Prepend a SELECT list to the query and append a WHERE clause that
+ * refines the objects selected.
+ */
+
+/*
+ * pg_statitic on versions 12+ have the same rel stats layout
+ */
+const char *stats_export_rel_query_v12 =
+ " jsonb_build_object( "
+ " 'server_version_num', current_setting('server_version_num'), "
+ " 'relname', r.relname, "
+ " 'nspname', n.nspname, "
+ " 'reltuples', r.reltuples, "
+ " 'relpages', r.relpages, "
+ " 'types', "
+ " ( "
+ " SELECT array_agg(tr ORDER BY tr.oid) "
+ " FROM ( "
+ " SELECT "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_type AS t "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS tr "
+ " ), "
+ " 'collations', "
+ " ( "
+ " SELECT array_agg(cr ORDER BY cr.oid) "
+ " FROM ( "
+ " SELECT "
+ " c.oid, "
+ " c.collname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_collation AS c "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " UNION "
+ " SELECT u.collid "
+ " FROM pg_catalog.pg_statistic AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.stacoll1, s.stacoll2, "
+ " s.stacoll3, s.stacoll4, "
+ " s.stacoll5]) AS u(collid) "
+ " WHERE s.starelid = r.oid "
+ " ) "
+ " ) AS cr "
+ " ), "
+ " 'operators', "
+ " ( "
+ " SELECT array_agg(p ORDER BY p.oid) "
+ " FROM ( "
+ " SELECT "
+ " o.oid, "
+ " o.oprname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_operator AS o "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.oid "
+ " FROM pg_catalog.pg_statistic AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.staop1, s.staop2, "
+ " s.staop3, s.staop4, "
+ " s.staop5]) AS u(opid) "
+ " WHERE s.starelid = r.oid "
+ " ) "
+ " ) AS p "
+ " ), "
+ " 'attributes', "
+ " ( "
+ " SELECT array_agg(ar ORDER BY ar.attnum) "
+ " FROM ( "
+ " SELECT "
+ " a.attnum, "
+ " a.attname, "
+ " a.atttypid, "
+ " a.attcollation "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ), "
+ " 'statistics', "
+ " ( "
+ " SELECT array_agg(sr ORDER BY sr.stainherit, sr.staattnum) "
+ " FROM ( "
+ " SELECT "
+ " s.staattnum, "
+ " s.stainherit, "
+ " s.stanullfrac, "
+ " s.stawidth, "
+ " s.stadistinct, "
+ " s.stakind1, "
+ " s.stakind2, "
+ " s.stakind3, "
+ " s.stakind4, "
+ " s.stakind5, "
+ " s.staop1, "
+ " s.staop2, "
+ " s.staop3, "
+ " s.staop4, "
+ " s.staop5, "
+ " s.stacoll1, "
+ " s.stacoll2, "
+ " s.stacoll3, "
+ " s.stacoll4, "
+ " s.stacoll5, "
+ " s.stanumbers1::text AS stanumbers1, "
+ " s.stanumbers2::text AS stanumbers2, "
+ " s.stanumbers3::text AS stanumbers3, "
+ " s.stanumbers4::text AS stanumbers4, "
+ " s.stanumbers5::text AS stanumbers5, "
+ " s.stavalues1::text AS stavalues1, "
+ " s.stavalues2::text AS stavalues2, "
+ " s.stavalues3::text AS stavalues3, "
+ " s.stavalues4::text AS stavalues4, "
+ " s.stavalues5::text AS stavalues5 "
+ " FROM pg_catalog.pg_statistic AS s "
+ " WHERE s.starelid = r.oid "
+ " ) AS sr "
+ " ) "
+ " ) AS stats_json "
+ "FROM pg_catalog.pg_class AS r "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace ";
+
+/*
+ * pg_statitic on versions 10-11 are missing the pg_statistic.stacollN columns
+ */
+const char *stats_export_rel_query_v10 =
+ " jsonb_build_object( "
+ " 'types', "
+ " ( "
+ " SELECT array_agg(tr ORDER BY tr.oid) "
+ " FROM ( "
+ " SELECT "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_type AS t "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS tr "
+ " ), "
+ " 'collations', "
+ " ( "
+ " SELECT array_agg(cr ORDER BY cr.oid) "
+ " FROM ( "
+ " SELECT "
+ " c.oid, "
+ " c.collname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_collation AS c "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS cr "
+ " ), "
+ " 'operators', "
+ " ( "
+ " SELECT array_agg(p ORDER BY p.oid) "
+ " FROM ( "
+ " SELECT "
+ " o.oid, "
+ " o.oprname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_operator AS o "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.oid "
+ " FROM pg_catalog.pg_statistic AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.staop1, s.staop2, "
+ " s.staop3, s.staop4, "
+ " s.staop5]) AS u(opid) "
+ " WHERE s.starelid = r.oid "
+ " ) "
+ " ) AS p "
+ " ), "
+ " 'attributes', "
+ " ( "
+ " SELECT array_agg(ar ORDER BY ar.attnum) "
+ " FROM ( "
+ " SELECT "
+ " a.attnum, "
+ " a.attname, "
+ " a.atttypid, "
+ " a.attcollation "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ), "
+ " 'statistics', "
+ " ( "
+ " SELECT array_agg(sr ORDER BY sr.stainherit, sr.staattnum) "
+ " FROM ( "
+ " SELECT "
+ " s.staattnum, "
+ " s.stainherit, "
+ " s.stanullfrac, "
+ " s.stawidth, "
+ " s.stadistinct, "
+ " s.stakind1, "
+ " s.stakind2, "
+ " s.stakind3, "
+ " s.stakind4, "
+ " s.stakind5, "
+ " s.staop1, "
+ " s.staop2, "
+ " s.staop3, "
+ " s.staop4, "
+ " s.staop5, "
+ " s.stanumbers1::text AS stanumbers1, "
+ " s.stanumbers2::text AS stanumbers2, "
+ " s.stanumbers3::text AS stanumbers3, "
+ " s.stanumbers4::text AS stanumbers4, "
+ " s.stanumbers5::text AS stanumbers5, "
+ " s.stavalues1::text AS stavalues1, "
+ " s.stavalues2::text AS stavalues2, "
+ " s.stavalues3::text AS stavalues3, "
+ " s.stavalues4::text AS stavalues4, "
+ " s.stavalues5::text AS stavalues5 "
+ " FROM pg_catalog.pg_statistic AS s "
+ " WHERE s.starelid = r.oid "
+ " ) AS sr "
+ " ) "
+ " ) AS stats_json "
+ "FROM pg_catalog.pg_class AS r "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace ";
+
+/*
+ * The following are query fragments that build one or more JSONB objects
+ * in the format expected by pg_import_rel_stats(), and it is expected that
+ * they will be utilized in the same ways.
+ */
+
+const char *stats_export_ext_query_v15 =
+/* v15+ have the same format */
+ " jsonb_build_object( "
+ " 'server_version_num', current_setting('server_version_num'), "
+ " 'stxoid', e.oid, "
+ " 'reloid', r.oid, "
+ " 'stxname', e.stxname, "
+ " 'stxnspname', en.nspname, "
+ " 'relname', r.relname, "
+ " 'nspname', n.nspname, "
+ " 'stxkeys', e.stxkeys::text, "
+ " 'stxkind', e.stxkind::text, "
+ " 'data', "
+ " ( "
+ " SELECT array_agg(dr ORDER by dr.stxdinherit) "
+ " FROM ( "
+ " SELECT "
+ " sd.stxdinherit, "
+ " sd.stxdndistinct::text AS stxdndistinct, "
+ " sd.stxddependencies::text AS stxddependencies, "
+ " ( "
+ " SELECT array_agg(mcvl) "
+ " FROM pg_catalog.pg_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ) AS stxdmcv, "
+ " ( "
+ " SELECT array_agg(sr ORDER BY sr.stainherit, sr.staattnum) "
+ " FROM ( "
+ " SELECT "
+ " s.staattnum, "
+ " s.stainherit, "
+ " s.stanullfrac, "
+ " s.stawidth, "
+ " s.stadistinct, "
+ " s.stakind1, "
+ " s.stakind2, "
+ " s.stakind3, "
+ " s.stakind4, "
+ " s.stakind5, "
+ " s.staop1, "
+ " s.staop2, "
+ " s.staop3, "
+ " s.staop4, "
+ " s.staop5, "
+ " s.stacoll1, "
+ " s.stacoll2, "
+ " s.stacoll3, "
+ " s.stacoll4, "
+ " s.stacoll5, "
+ " s.stanumbers1::text AS stanumbers1, "
+ " s.stanumbers2::text AS stanumbers2, "
+ " s.stanumbers3::text AS stanumbers3, "
+ " s.stanumbers4::text AS stanumbers4, "
+ " s.stanumbers5::text AS stanumbers5, "
+ " s.stavalues1::text AS stavalues1, "
+ " s.stavalues2::text AS stavalues2, "
+ " s.stavalues3::text AS stavalues3, "
+ " s.stavalues4::text AS stavalues4, "
+ " s.stavalues5::text AS stavalues5 "
+ " FROM unnest(sd.stxdexpr) AS s "
+ " WHERE sd.stxdexpr IS NOT NULL "
+ " ) AS sr "
+ " ) AS stxdexpr "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " WHERE sd.stxoid = e.oid "
+ " ) AS dr "
+ " ), "
+ " 'types', "
+ " ( "
+ " SELECT array_agg(tr ORDER BY tr.oid) "
+ " FROM ( "
+ " SELECT "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_type AS t "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS tr "
+ " ), "
+ " 'collations', "
+ " ( "
+ " SELECT array_agg(cr ORDER BY cr.oid) "
+ " FROM ( "
+ " SELECT "
+ " c.oid, "
+ " c.collname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_collation AS c "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " UNION "
+ " SELECT u.collid "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " CROSS JOIN LATERAL unnest(sd.stxdexpr) AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.stacoll1, s.stacoll2, "
+ " s.stacoll3, s.stacoll4, "
+ " s.stacoll5]) AS u(collid) "
+ " WHERE sd.stxoid = e.oid "
+ " AND sd.stxdexpr IS NOT NULL "
+ " ) "
+ " ) AS cr "
+ " ), "
+ " 'operators', "
+ " ( "
+ " SELECT array_agg(p ORDER BY p.oid) "
+ " FROM ( "
+ " SELECT "
+ " o.oid, "
+ " o.oprname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_operator AS o "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.opid "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " CROSS JOIN LATERAL unnest(sd.stxdexpr) AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.staop1, s.staop2, "
+ " s.staop3, s.staop4, "
+ " s.staop5]) AS u(opid) "
+ " WHERE sd.stxoid = e.oid "
+ " AND sd.stxdexpr IS NOT NULL "
+ " ) "
+ " ) AS p "
+ " ), "
+ " 'attributes', "
+ " ( "
+ " SELECT array_agg(ar ORDER BY ar.attnum) "
+ " FROM ( "
+ " SELECT "
+ " a.attnum, "
+ " a.attname, "
+ " a.atttypid, "
+ " a.attcollation "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_catalog.pg_class r "
+ "JOIN pg_catalog.pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_catalog.pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace ";
+
+/* v14 is like v15, but lacks stxdinherit on pg_statistic_ext_data */
+const char *stats_export_ext_query_v14 =
+ " jsonb_build_object( "
+ " 'server_version_num', current_setting('server_version_num'), "
+ " 'stxoid', e.oid, "
+ " 'reloid', r.oid, "
+ " 'stxname', e.stxname, "
+ " 'stxnspname', en.nspname, "
+ " 'relname', r.relname, "
+ " 'nspname', n.nspname, "
+ " 'stxkeys', e.stxkeys::text, "
+ " 'stxkind', e.stxkind::text, "
+ " 'data', "
+ " ( "
+ " SELECT array_agg(dr) "
+ " FROM ( "
+ " SELECT "
+ " sd.stxdndistinct::text AS stxdndistinct, "
+ " sd.stxddependencies::text AS stxddependencies, "
+ " ( "
+ " SELECT array_agg(mcvl) "
+ " FROM pg_catalog.pg_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ) AS stxdmcv, "
+ " ( "
+ " SELECT array_agg(sr ORDER BY sr.staattnum) "
+ " FROM ( "
+ " SELECT "
+ " s.staattnum, "
+ " s.stanullfrac, "
+ " s.stawidth, "
+ " s.stadistinct, "
+ " s.stakind1, "
+ " s.stakind2, "
+ " s.stakind3, "
+ " s.stakind4, "
+ " s.stakind5, "
+ " s.staop1, "
+ " s.staop2, "
+ " s.staop3, "
+ " s.staop4, "
+ " s.staop5, "
+ " s.stacoll1, "
+ " s.stacoll2, "
+ " s.stacoll3, "
+ " s.stacoll4, "
+ " s.stacoll5, "
+ " s.stanumbers1::text AS stanumbers1, "
+ " s.stanumbers2::text AS stanumbers2, "
+ " s.stanumbers3::text AS stanumbers3, "
+ " s.stanumbers4::text AS stanumbers4, "
+ " s.stanumbers5::text AS stanumbers5, "
+ " s.stavalues1::text AS stavalues1, "
+ " s.stavalues2::text AS stavalues2, "
+ " s.stavalues3::text AS stavalues3, "
+ " s.stavalues4::text AS stavalues4, "
+ " s.stavalues5::text AS stavalues5 "
+ " FROM unnest(sd.stxdexpr) AS s "
+ " WHERE sd.stxdexpr IS NOT NULL "
+ " ) AS sr "
+ " ) AS stxdexpr "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " WHERE sd.stxoid = e.oid "
+ " ) dr "
+ " ), "
+ " 'types', "
+ " ( "
+ " select array_agg(tr ORDER BY tr.oid) "
+ " from ( "
+ " select "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " from pg_catalog.pg_type as t "
+ " join pg_catalog.pg_namespace as n on n.oid = t.typnamespace "
+ " where t.oid in ( "
+ " select a.atttypid "
+ " from pg_catalog.pg_attribute as a "
+ " where a.attrelid = r.oid "
+ " and not a.attisdropped "
+ " and a.attnum > 0 "
+ " ) "
+ " ) as tr "
+ " ), "
+ " 'collations', "
+ " ( "
+ " SELECT array_agg(cr ORDER BY cr.oid) "
+ " FROM ( "
+ " SELECT "
+ " c.oid, "
+ " c.collname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_collation AS c "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " UNION "
+ " SELECT u.collid "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " CROSS JOIN LATERAL unnest(sd.stxdexpr) AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.stacoll1, s.stacoll2, "
+ " s.stacoll3, s.stacoll4, "
+ " s.stacoll5]) AS u(collid) "
+ " WHERE sd.stxoid = e.oid "
+ " AND sd.stxdexpr IS NOT NULL "
+ " ) "
+ " ) AS cr "
+ " ), "
+ " 'operators', "
+ " ( "
+ " SELECT array_agg(p ORDER BY p.oid) "
+ " FROM ( "
+ " SELECT "
+ " o.oid, "
+ " o.oprname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_operator AS o "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.opid "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " CROSS JOIN LATERAL unnest(sd.stxdexpr) AS s "
+ " CROSS JOIN LATERAL unnest(ARRAY[ "
+ " s.staop1, s.staop2, "
+ " s.staop3, s.staop4, "
+ " s.staop5]) AS u(opid) "
+ " WHERE sd.stxoid = e.oid "
+ " AND sd.stxdexpr IS NOT NULL "
+ " ) "
+ " ) AS p "
+ " ), "
+ " 'attributes', "
+ " ( "
+ " SELECT array_agg(ar ORDER BY ar.attnum) "
+ " FROM ( "
+ " SELECT "
+ " a.attnum, "
+ " a.attname, "
+ " a.atttypid, "
+ " a.attcollation "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_catalog.pg_class r "
+ "JOIN pg_catalog.pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_catalog.pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace ";
+
+/* v12-v13 are like v14, but lack stxdexpr on pg_statistic_ext_data */
+const char *stats_export_ext_query_v12 =
+ " jsonb_build_object( "
+ " 'server_version_num', current_setting('server_version_num'), "
+ " 'stxoid', e.oid, "
+ " 'reloid', r.oid, "
+ " 'stxname', e.stxname, "
+ " 'stxnspname', en.nspname, "
+ " 'relname', r.relname, "
+ " 'nspname', n.nspname, "
+ " 'stxkeys', e.stxkeys::text, "
+ " 'stxkind', e.stxkind::text, "
+ " 'data', "
+ " ( "
+ " SELECT array_agg(r) "
+ " FROM ( "
+ " SELECT "
+ " sd.stxdndistinct::text AS stxdndistinct, "
+ " sd.stxddependencies::text AS stxddependencies, "
+ " ( "
+ " SELECT array_agg(mcvl) "
+ " FROM pg_catalog.pg_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ) AS stxdmcv "
+ " FROM pg_catalog.pg_statistic_ext_data AS sd "
+ " WHERE sd.stxoid = e.oid "
+ " ) r "
+ " ), "
+ " 'types', "
+ " ( "
+ " SELECT array_agg(tr ORDER BY tr.oid) "
+ " FROM ( "
+ " SELECT "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_type AS t "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS tr "
+ " ), "
+ " 'collations', "
+ " ( "
+ " SELECT array_agg(cr ORDER BY cr.oid) "
+ " FROM ( "
+ " SELECT "
+ " c.oid, "
+ " c.collname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_collation AS c "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS cr "
+ " ), "
+ " 'attributes', "
+ " ( "
+ " SELECT array_agg(ar ORDER BY ar.attnum) "
+ " FROM ( "
+ " SELECT "
+ " a.attnum, "
+ " a.attname, "
+ " a.atttypid, "
+ " a.attcollation "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_catalog.pg_class r "
+ "JOIN pg_catalog.pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_catalog.pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace ";
+
+/*
+ * v10-v11 are like v12, but:
+ * - MCV is gone
+ * - remaining stats are stored on pg_statistic_ext
+ * - pg_statistic_ext_data is gone
+ */
+
+const char *stats_export_ext_query_v10 =
+ " jsonb_build_object( "
+ " 'server_version_num', current_setting('server_version_num'), "
+ " 'stxoid', e.oid, "
+ " 'reloid', r.oid, "
+ " 'stxname', e.stxname, "
+ " 'stxnspname', en.nspname, "
+ " 'relname', r.relname, "
+ " 'nspname', n.nspname, "
+ " 'stxkeys', e.stxkeys::text, "
+ " 'stxkind', e.stxkind::text, "
+ " 'stxndistinct', e.stxndistinct::text, "
+ " 'stxdependencies', e.stxdependencies::text, "
+ " 'types', "
+ " ( "
+ " SELECT array_agg(tr ORDER BY tr.oid) "
+ " FROM ( "
+ " SELECT "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_type AS t "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS tr "
+ " ), "
+ " 'collations', "
+ " ( "
+ " SELECT array_agg(cr ORDER BY cr.oid) "
+ " FROM ( "
+ " SELECT "
+ " c.oid, "
+ " c.collname, "
+ " n.nspname "
+ " FROM pg_catalog.pg_collation AS c "
+ " JOIN pg_catalog.pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) "
+ " ) AS cr "
+ " ), "
+ " 'attributes', "
+ " ( "
+ " SELECT array_agg(ar ORDER BY r.attnum) "
+ " FROM ( "
+ " SELECT "
+ " a.attnum, "
+ " a.attname, "
+ " a.atttypid, "
+ " a.attcollation "
+ " FROM pg_catalog.pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_catalog.pg_class r "
+ "JOIN pg_catalog.pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_catalog.pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace ";
+
+/*
+ * Return the pg_statitic export query fragment appropriate for the
+ * given server_version_num.
+ */
+const char *
+stats_export_rel_query(int server_version_num)
+{
+ if (server_version_num >= 120000)
+ return stats_export_rel_query_v12;
+ else if (server_version_num >= 100000)
+ return stats_export_rel_query_v10;
+ else
+ return NULL;
+}
+
+/*
+ * Return the pg_statitic_ext export query fragment appropriate for the
+ * given server_version_num.
+ */
+const char *
+stats_export_ext_query(int server_version_num)
+{
+ if (server_version_num >= 150000)
+ return stats_export_ext_query_v15;
+ else if (server_version_num >= 140000)
+ return stats_export_ext_query_v14;
+ else if (server_version_num >= 120000)
+ return stats_export_ext_query_v12;
+ else if (server_version_num >= 100000)
+ return stats_export_ext_query_v10;
+ else
+ return NULL;
+}
+
+/*
+ * Names cannot be null, must be regular strings
+ */
+#define is_quoted_name(s) \
+ (s != NULL) && \
+ ((s[0] == '\'')) && \
+ (s[strlen(s)-1] == '\'')
+
+/*
+ * JSON strings can be NULL, and may extended format E'...' strings.
+ */
+#define is_quoted_json(s) \
+ (s == NULL) || \
+ (((s[0] == '\'') || ((s[0] == 'E' && s[1] == '\''))) && \
+ (s[strlen(s)-1] == '\''))
+
+/*
+ * Print the SQL statement to import statistics to a given relation.
+ */
+void
+stats_export_print_rel_import(FILE *outf,
+ const char *nspname_literal,
+ const char *relname_literal,
+ const char *stats_json_literal,
+ bool validate,
+ bool require_match)
+{
+ Assert(is_quoted_name(nspname_literal));
+ Assert(is_quoted_name(relname_literal));
+ Assert(is_quoted_json(stats_json_literal));
+
+ fprintf(outf,
+ "SELECT pg_catalog.pg_import_rel_stats(r.oid, %s::jsonb, %s, %s) "
+ "FROM pg_catalog.pg_catalog.pg_class AS r "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE n.nspname = %s "
+ "AND r.relname = %s;\n\n",
+ (stats_json_literal != NULL) ? stats_json_literal : "NULL",
+ (validate) ? "true" : "false",
+ (require_match) ? "true" : "false",
+ nspname_literal,
+ relname_literal);
+}
+
+/*
+ * Print the SQL statement to import statistics to a given statistics object.
+ */
+void
+stats_export_print_ext_import(FILE *outf,
+ const char *nspname_literal,
+ const char *relname_literal,
+ const char *stxname_literal,
+ const char *ext_stats_json_literal,
+ bool validate,
+ bool require_match)
+{
+ Assert(is_quoted_name(nspname_literal));
+ Assert(is_quoted_name(relname_literal));
+ Assert(is_quoted_name(stxname_literal));
+ Assert(is_quoted_json(ext_stats_json_literal));
+
+ fprintf(outf,
+ "SELECT pg_catalog.pg_import_ext_stats(e.oid, %s::jsonb, %s, %s) "
+ "FROM pg_catalog.pg_class r "
+ "JOIN pg_catalog.pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_catalog.pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE n.nspname = %s "
+ "AND r.relname = %s "
+ "AND e.stxname = %s;\n\n",
+ (ext_stats_json_literal != NULL) ? ext_stats_json_literal : "NULL",
+ (validate) ? "true" : "false",
+ (require_match) ? "true" : "false",
+ nspname_literal,
+ relname_literal,
+ stxname_literal);
+}
--
2.43.2