v4-0003-Add-pg_export_stats-pg_import_stats.patch
text/x-patch
Filename: v4-0003-Add-pg_export_stats-pg_import_stats.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 v4-0003
Subject: Add pg_export_stats, pg_import_stats
| File | + | − |
|---|---|---|
| src/bin/scripts/.gitignore | 2 | 0 |
| src/bin/scripts/Makefile | 3 | 1 |
| src/bin/scripts/pg_export_stats.c | 1012 | 0 |
| src/bin/scripts/pg_import_stats.c | 477 | 0 |
From 101c6476df3b7e9d4f199b4c4a149e1dfeebd51c Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Thu, 1 Feb 2024 20:45:05 -0500
Subject: [PATCH v4 3/3] Add pg_export_stats, pg_import_stats
These are reference programs designed to aid the testing of the
functions pg_import_rel_stats() and pg_import_ext_stats(), bringing in
statistics from older versions of postgresql.
The ultimate goal is to move the queries into pg_dump.
---
src/bin/scripts/.gitignore | 2 +
src/bin/scripts/Makefile | 4 +-
src/bin/scripts/pg_export_stats.c | 1012 +++++++++++++++++++++++++++++
src/bin/scripts/pg_import_stats.c | 477 ++++++++++++++
4 files changed, 1494 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/.gitignore b/src/bin/scripts/.gitignore
index 0f23fe0004..1b9addb339 100644
--- a/src/bin/scripts/.gitignore
+++ b/src/bin/scripts/.gitignore
@@ -6,5 +6,7 @@
/reindexdb
/vacuumdb
/pg_isready
+/pg_export_stats
+/pg_import_stats
/tmp_check/
diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile
index 9633c99136..7550c69d81 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)
@@ -41,6 +41,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..c07450d1a9
--- /dev/null
+++ b/src/bin/scripts/pg_export_stats.c
@@ -0,0 +1,1012 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_export_stats
+ *
+ * Portions Copyright (c) 1996-2024, 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);
+
+/*
+ * Versions 12+ have the same rel stats layout
+ */
+const char *export_rel_query_v12 =
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS relname, "
+ " NULL::text AS ext_stats_name, "
+ " current_setting('server_version_num')::integer AS server_version_num, "
+ " 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_type AS t "
+ " JOIN pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM 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_collation AS c "
+ " JOIN pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " UNION "
+ " SELECT u.collid "
+ " FROM 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_operator AS o "
+ " JOIN pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.oid "
+ " FROM 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_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_statistic AS s "
+ " WHERE s.starelid = r.oid "
+ " ) AS sr "
+ " ) "
+ " ) AS stats_json "
+ "FROM pg_class AS r "
+ "JOIN pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') "
+ "AND r.relpersistence = 'p' "
+ "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ";
+
+/*
+ * Versions 10-11 are missing the pg_statistic.stacollN columns
+ */
+const char *export_rel_query_v10 =
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS relname, "
+ " NULL::text AS ext_stats_name, "
+ " current_setting('server_version_num')::integer AS server_version_num, "
+ " jsonb_build_object( "
+ " 'types', "
+ " ( "
+ " SELECT array_agg(tr ORDER BY tr.oid) "
+ " FROM ( "
+ " SELECT "
+ " t.oid, "
+ " t.typname, "
+ " n.nspname "
+ " FROM pg_type AS t "
+ " JOIN pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM 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_collation AS c "
+ " JOIN pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM 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_operator AS o "
+ " JOIN pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.oid "
+ " FROM 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_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_statistic AS s "
+ " WHERE s.starelid = r.oid "
+ " ) AS sr "
+ " ) "
+ " ) AS stats_json "
+ "FROM pg_class AS r "
+ "JOIN pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') "
+ "AND r.relpersistence = 'p' "
+ "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ";
+
+
+const char *export_ext_query_v15 =
+/* v15+ have the same format */
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS relname, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " 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_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_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_type AS t "
+ " JOIN pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM 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_collation AS c "
+ " JOIN pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " UNION "
+ " SELECT u.collid "
+ " FROM 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_operator AS o "
+ " JOIN pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.opid "
+ " FROM 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_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_class r "
+ "JOIN pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') "
+ "AND r.relpersistence = 'p' "
+ "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ";
+
+/* v14 is like v15, but lacks stxdinherit on pg_statistic_ext_data */
+const char *export_ext_query_v14 =
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS relname, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " 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_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_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_type as t "
+ " join pg_namespace as n on n.oid = t.typnamespace "
+ " where t.oid in ( "
+ " select a.atttypid "
+ " from 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_collation AS c "
+ " JOIN pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM pg_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " UNION "
+ " SELECT u.collid "
+ " FROM 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_operator AS o "
+ " JOIN pg_namespace AS n ON n.oid = o.oprnamespace "
+ " WHERE o.oid IN ( "
+ " SELECT u.opid "
+ " FROM 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_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_class r "
+ "JOIN pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') "
+ "AND r.relpersistence = 'p' "
+ "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ";
+
+/* v12-v13 are like v14, but lack stxdexpr on pg_statistic_ext_data */
+const char *export_ext_query_v12 =
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS relname, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " 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_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ) AS stxdmcv "
+ " FROM 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_type AS t "
+ " JOIN pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM 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_collation AS c "
+ " JOIN pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM 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_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_class r "
+ "JOIN pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') "
+ "AND r.relpersistence = 'p' "
+ "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ";
+
+/*
+ * 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 *export_ext_query_v10 =
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS relname, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " 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_type AS t "
+ " JOIN pg_namespace AS n ON n.oid = t.typnamespace "
+ " WHERE t.oid IN ( "
+ " SELECT a.atttypid "
+ " FROM 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_collation AS c "
+ " JOIN pg_namespace AS n ON n.oid = c.collnamespace "
+ " WHERE c.oid IN ( "
+ " SELECT a.attcollation AS oid "
+ " FROM 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_attribute AS a "
+ " WHERE a.attrelid = r.oid "
+ " AND NOT a.attisdropped "
+ " AND a.attnum > 0 "
+ " ) AS ar "
+ " ) "
+ " ) AS ext_stats_json "
+ "FROM pg_class r "
+ "JOIN pg_statistic_ext AS e ON e.stxrelid = r.oid "
+ "JOIN pg_namespace AS en ON en.oid = e.stxnamespace "
+ "JOIN pg_namespace AS n ON n.oid = r.relnamespace "
+ "WHERE r.relkind IN ('r', 'm', 'f', 'p', 'i') "
+ "AND r.relpersistence = 'p' "
+ "AND n.nspname NOT IN ('pg_catalog', 'pg_toast', '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;
+ int server_version_num;
+
+ 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);
+
+ server_version_num = PQserverVersion(conn);
+
+ initPQExpBuffer(&sql);
+
+ appendPQExpBufferStr(&sql, "COPY (");
+
+ if (server_version_num >= 120000)
+ appendPQExpBufferStr(&sql, export_rel_query_v12);
+ else if (server_version_num >= 100000)
+ appendPQExpBufferStr(&sql, export_rel_query_v10);
+ else
+ pg_fatal("exporting statistics from databases prior to version 10 not supported");
+
+ appendPQExpBufferStr(&sql, " UNION ALL ");
+
+ if (server_version_num >= 150000)
+ appendPQExpBufferStr(&sql, export_ext_query_v15);
+ else if (server_version_num >= 140000)
+ appendPQExpBufferStr(&sql, export_ext_query_v14);
+ else if (server_version_num >= 120000)
+ appendPQExpBufferStr(&sql, export_ext_query_v12);
+ else if (server_version_num >= 100000)
+ appendPQExpBufferStr(&sql, export_ext_query_v10);
+ else
+ pg_fatal("exporting statistics from databases prior to version 10 not supported");
+
+ appendPQExpBufferStr(&sql, " ORDER BY 1, 2, 3) TO STDOUT");
+
+ /* printf("%s\n", sql.data); */
+ result = PQexec(conn, sql.data);
+ result_status = PQresultStatus(result);
+
+ if (result_status != PGRES_COPY_OUT)
+ pg_fatal("malformed copy command: %s", PQerrorMessage(conn));
+
+ 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..96a7252fec
--- /dev/null
+++ b/src/bin/scripts/pg_import_stats.c
@@ -0,0 +1,477 @@
+/*-------------------------------------------------------------------------
+ *
+ * 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;
+ int numextstats;
+
+ 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 */
+
+ /*
+ * Create a table that can received the COPY-ed file which is a mix
+ * of relation statistics and extended statistics.
+ */
+ result = PQexec(conn,
+ "CREATE TEMPORARY TABLE import_stats ( "
+ "schemaname text, "
+ "relname text, "
+ "ext_stats_name text, "
+ "server_version_num integer, "
+ "stats jsonb )");
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("could not create temporary table: %s", PQerrorMessage(conn));
+
+ PQclear(result);
+
+ /*
+ * Create a table just for the relation statistics
+ */
+ result = PQexec(conn,
+ "CREATE TEMPORARY TABLE import_rel_stats ( "
+ "id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, "
+ "schemaname text, "
+ "relname text, "
+ "server_version_num integer, "
+ "stats jsonb )");
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("could not create temporary table: %s", PQerrorMessage(conn));
+
+
+ PQclear(result);
+
+ /*
+ * Create a table just for extended statistics
+ */
+ result = PQexec(conn,
+ "CREATE TEMPORARY TABLE import_ext_stats ( "
+ "id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, "
+ "schemaname text, "
+ "relname text, "
+ "ext_stats_name text, "
+ "server_version_num integer, "
+ "stats jsonb )");
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("could not create temporary table: %s", PQerrorMessage(conn));
+
+ PQclear(result);
+
+ /*
+ * Copy input data into combined table.
+ */
+ result = PQexec(conn,
+ "COPY import_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));
+
+ PQclear(result);
+
+ /*
+ * Insert rel stats into their own table with numbering.
+ */
+ result = PQexec(conn,
+ "INSERT INTO import_rel_stats(schemaname, relname, server_version_num, "
+ "stats) "
+ "SELECT schemaname, relname, server_version_num, stats "
+ "FROM import_stats "
+ "WHERE ext_stats_name IS NULL ");
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("relation stats insert error: %s", PQerrorMessage(conn));
+
+ numtables = atol(PQcmdTuples(result));
+
+ PQclear(result);
+
+ /*
+ * Insert extended stats into their own table with numbering.
+ */
+ result = PQexec(conn,
+ "INSERT INTO import_ext_stats(schemaname, relname, ext_stats_name, "
+ "server_version_num, stats) "
+ "SELECT schemaname, relname, ext_stats_name, server_version_num, "
+ "stats "
+ "FROM import_stats "
+ "WHERE ext_stats_name IS NOT NULL ");
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("relation stats insert error: %s", PQerrorMessage(conn));
+
+ numextstats = atol(PQcmdTuples(result));
+
+ PQclear(result);
+
+ if (numtables > 0)
+ {
+
+ result = PQprepare(conn, "import_rel",
+ "SELECT pg_import_rel_stats(c.oid, s.stats, true, true) AS import_result "
+ "FROM import_rel_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_rel",
+ "SELECT s.schemaname, s.relname "
+ "FROM import_rel_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_rel", 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_rel", 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);
+ }
+ }
+
+ if (numextstats > 0)
+ {
+
+ result = PQprepare(conn, "import_ext",
+ "SELECT pg_import_ext_stats(e.oid, s.stats, true, true) AS import_result "
+ "FROM import_ext_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 "
+ "JOIN pg_statistic_ext AS e ON e.stxrelid = c.oid "
+ " AND e.stxname = s.ext_stats_name "
+ "WHERE s.id = $1::bigint "
+ "AND false ", /* remove when we enable extended stats */
+ 1, NULL);
+
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("error in PREPARE: %s", PQerrorMessage(conn));
+
+ PQclear(result);
+
+ if (!quiet)
+ {
+ result = PQprepare(conn, "echo_ext",
+ "SELECT s.schemaname, s.relname, s.ext_stats_name "
+ "FROM import_ext_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 <= numextstats; i++)
+ {
+ char istr[32];
+ char *schema = NULL;
+ char *table = NULL;
+ char *stat = NULL;
+
+ const char *const values[] = {istr};
+
+ snprintf(istr, 32, "%d", i);
+
+ if (!quiet)
+ {
+ result = PQexecPrepared(conn, "echo_ext", 1, values, NULL, NULL, 0);
+ schema = pg_strdup(PQgetvalue(result, 0, 0));
+ table = pg_strdup(PQgetvalue(result, 0, 1));
+ stat = pg_strdup(PQgetvalue(result, 0, 2));
+ }
+
+ PQclear(result);
+
+ result = PQexecPrepared(conn, "import_ext", 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 on %s.%s: imported\n", stat, schema, table);
+ else
+ printf("%s on %s.%s: failed\n", stat, schema, table);
+ }
+ else if (rows == 0)
+ printf("%s on %s.%s: not found\n", stat, schema, table);
+ else
+ pg_fatal("import function must return 0 or 1 rows");
+ }
+ else
+ printf("%s on %s.%s: error: %s\n", stat, schema, table, PQerrorMessage(conn));
+
+ if (schema != NULL)
+ pfree(schema);
+
+ if (table != NULL)
+ pfree(table);
+
+ if (stat != NULL)
+ pfree(stat);
+
+ 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.43.0