v3-0009-Enable-pg_export_stats-pg_import_stats-to-use-ext.patch
text/x-patch
Filename: v3-0009-Enable-pg_export_stats-pg_import_stats-to-use-ext.patch
Type: text/x-patch
Part: 8
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-0009
Subject: Enable pg_export_stats, pg_import_stats to use extended statistics.
| File | + | − |
|---|---|---|
| src/bin/scripts/pg_export_stats.c | 437 | 11 |
| src/bin/scripts/pg_import_stats.c | 235 | 54 |
From aeffb52e6c744f624ea32b3ef587f5e9d0b5b85b Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 13 Dec 2023 05:21:00 -0500
Subject: [PATCH v3 9/9] Enable pg_export_stats, pg_import_stats to use
extended statistics.
Both programs still use a single data stream. As a result, the columns
COPY-ed are a superset of both export tables, and on import the data
must first be loaded into a superset temporary table before being
separated into rel-specific and extended-specific tables.
While relation stats format is stable back to v10, the formats of
pg_statistic_ext and pg_statistic_ext_data change every version or two.
Extraction queries are provided back to v10, but currently only tested
back to v15. That's probably ok given that these programs primarily
serve as a reference and their functionality will most likely be moved
to pg_upgrade and pg_dump+pg_restore.
---
src/bin/scripts/pg_export_stats.c | 448 +++++++++++++++++++++++++++++-
src/bin/scripts/pg_import_stats.c | 289 +++++++++++++++----
2 files changed, 672 insertions(+), 65 deletions(-)
diff --git a/src/bin/scripts/pg_export_stats.c b/src/bin/scripts/pg_export_stats.c
index abb3659e20..4aaafc729e 100644
--- a/src/bin/scripts/pg_export_stats.c
+++ b/src/bin/scripts/pg_export_stats.c
@@ -22,18 +22,20 @@
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 ";
+const char *export_rel_query_v17 =
+ "SELECT schemaname, relname, NULL::text AS ext_stats_name, "
+ "server_version_num, n_tuples, n_pages, stats "
+ "FROM pg_statistic_export ";
/*
- * Versions 10-16 have the same stats layout, but lack the view definition,
- * so extracting the view definition ad using it as-is will work.
+ * Versions 10-16 have the same rel stats layout, but lack the view
+ * definition, so extracting the view definition ad using it as-is will work.
*/
-const char *export_query_v10 =
+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, "
" r.reltuples::float4 AS n_tuples, "
" r.relpages::integer AS n_pages, "
@@ -109,6 +111,412 @@ const char *export_query_v10 =
"WHERE relkind IN ('r', 'm', 'f', 'p', 'i') "
"AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema') ";
+/* view definition introduced in 17 */
+const char *export_ext_query_v17 =
+ "SELECT schemaname, relname, ext_stats_name, server_version_num, "
+ "NULL::float4 AS n_tuples, NULL::integer AS n_pages, stats "
+ "FROM pg_statistic_ext_export ";
+
+/* v15-v16 have the same extended stats layout, but lack the view definition */
+const char *export_ext_query_v15 =
+ "SELECT "
+ " n.nspname AS schemaname, "
+ " r.relname AS tablename, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " NULL::float4 AS n_tuples, "
+ " NULL::integer AS n_pages, "
+ " jsonb_object_agg( "
+ " CASE sd.stxdinherit "
+ " WHEN true THEN 'inherited' "
+ " ELSE 'regular' "
+ " END, "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array(nd.attnums, ', '::text), "
+ " 'ndistinct', "
+ " nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(sd.stxdndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " WHERE sd.stxdndistinct IS NOT NULL "
+ " ), "
+ " 'stxdndependencies', "
+ " ( "
+ " 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) "
+ " WHERE sd.stxddependencies IS NOT NULL "
+ " ), "
+ " 'stxdmcv', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'index', "
+ " mcvl.index::text, "
+ " 'frequency', "
+ " mcvl.frequency::text, "
+ " 'base_frequency', "
+ " mcvl.base_frequency::text, "
+ " 'values', "
+ " mcvl.values, "
+ " 'nulls', "
+ " mcvl.nulls "
+ " ) "
+ " ) "
+ " FROM pg_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ), "
+ " 'stxdexprs', "
+ " ( "
+ " 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' "
+ " ELSE NULL "
+ " END "
+ " ORDER BY kind.ord "
+ " ) "
+ " 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) "
+ " ) "
+ " ORDER BY s.ordinality "
+ " ) "
+ " FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s "
+ " WHERE sd.stxdexpr IS NOT NULL "
+ " ) "
+ " ) "
+ " ) AS stats "
+ "FROM pg_class r "
+ "JOIN pg_namespace n ON n.oid = r.relnamespace "
+ "JOIN pg_statistic_ext e ON e.stxrelid = r.oid "
+ "JOIN pg_statistic_ext_data sd ON sd.stxoid = e.oid "
+ "GROUP BY schemaname, tablename, ext_stats_name, server_version_num ";
+
+/* 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 tablename, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " NULL::float4 AS n_tuples, "
+ " NULL::integer AS n_pages, "
+ " jsonb_object_agg( "
+ " 'regular', "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array(nd.attnums, ', '::text), "
+ " 'ndistinct', "
+ " nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(sd.stxdndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " WHERE sd.stxdndistinct IS NOT NULL "
+ " ), "
+ " 'stxdndependencies', "
+ " ( "
+ " 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) "
+ " WHERE sd.stxddependencies IS NOT NULL "
+ " ), "
+ " 'stxdmcv', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'index', "
+ " mcvl.index::text, "
+ " 'frequency', "
+ " mcvl.frequency::text, "
+ " 'base_frequency', "
+ " mcvl.base_frequency::text, "
+ " 'values', "
+ " mcvl.values, "
+ " 'nulls', "
+ " mcvl.nulls "
+ " ) "
+ " ) "
+ " FROM pg_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ), "
+ " 'stxdexprs', "
+ " ( "
+ " 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' "
+ " ELSE NULL "
+ " END "
+ " ORDER BY kind.ord "
+ " ) "
+ " 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) "
+ " ) "
+ " ORDER BY s.ordinality "
+ " ) "
+ " FROM unnest(sd.stxdexpr) WITH ORDINALITY AS s "
+ " WHERE sd.stxdexpr IS NOT NULL "
+ " ) "
+ " ) "
+ " ) AS stats "
+ "FROM pg_class r "
+ "JOIN pg_namespace n ON n.oid = r.relnamespace "
+ "JOIN pg_statistic_ext e ON e.stxrelid = r.oid "
+ "JOIN pg_statistic_ext_data sd ON sd.stxoid = e.oid "
+ "GROUP BY schemaname, tablename, ext_stats_name, server_version_num ";
+
+/* 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 tablename, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " NULL::float4 AS n_tuples, "
+ " NULL::integer AS n_pages, "
+ " jsonb_object_agg( "
+ " 'regular', "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array(nd.attnums, ', '::text), "
+ " 'ndistinct', "
+ " nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(sd.stxdndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " WHERE sd.stxdndistinct IS NOT NULL "
+ " ), "
+ " 'stxdndependencies', "
+ " ( "
+ " 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) "
+ " WHERE sd.stxddependencies IS NOT NULL "
+ " ), "
+ " 'stxdmcv', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'index', "
+ " mcvl.index::text, "
+ " 'frequency', "
+ " mcvl.frequency::text, "
+ " 'base_frequency', "
+ " mcvl.base_frequency::text, "
+ " 'values', "
+ " mcvl.values, "
+ " 'nulls', "
+ " mcvl.nulls "
+ " ) "
+ " ) "
+ " FROM pg_mcv_list_items(sd.stxdmcv) AS mcvl "
+ " WHERE sd.stxdmcv IS NOT NULL "
+ " ) "
+ " ) "
+ " ) AS stats "
+ "FROM pg_class r "
+ "JOIN pg_namespace n ON n.oid = r.relnamespace "
+ "JOIN pg_statistic_ext e ON e.stxrelid = r.oid "
+ "JOIN pg_statistic_ext_data sd ON sd.stxoid = e.oid "
+ "GROUP BY schemaname, tablename, ext_stats_name, server_version_num ";
+
+/*
+ * 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 tablename, "
+ " e.stxname AS ext_stats_name, "
+ " (current_setting('server_version_num'::text))::integer AS server_version_num, "
+ " NULL::float4 AS n_tuples, "
+ " NULL::integer AS n_pages, "
+ " jsonb_object_agg( "
+ " 'regular', "
+ " jsonb_build_object( "
+ " 'stxkinds', "
+ " to_jsonb(e.stxkind), "
+ " 'stxdndistinct', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array(nd.attnums, ', '::text), "
+ " 'ndistinct', "
+ " nd.ndistinct "
+ " ) "
+ " ORDER BY nd.ord "
+ " ) "
+ " FROM json_each_text(e.stxndistinct::text::json) "
+ " WITH ORDINALITY AS nd(attnums, ndistinct, ord) "
+ " WHERE e.stxndistinct IS NOT NULL "
+ " ), "
+ " 'stxdndependencies', "
+ " ( "
+ " SELECT "
+ " jsonb_agg( "
+ " jsonb_build_object( "
+ " 'attnums', "
+ " string_to_array( "
+ " replace(dep.attrs, ' => ', ', '), ', ' "
+ " ), "
+ " 'degree', "
+ " dep.degree "
+ " ) "
+ " ORDER BY dep.ord "
+ " ) "
+ " FROM json_each_text(e.stxdependencies::text::json) "
+ " WITH ORDINALITY AS dep(attrs, degree, ord) "
+ " WHERE e.stxdependencies IS NOT NULL "
+ " ) "
+ " ) "
+ " ) AS stats "
+ "FROM pg_class r "
+ "JOIN pg_namespace n ON n.oid = r.relnamespace "
+ "JOIN pg_statistic_ext e ON e.stxrelid = r.oid "
+ "GROUP BY schemaname, tablename, ext_stats_name, server_version_num ";
+
int
main(int argc, char *argv[])
{
@@ -138,6 +546,7 @@ main(int argc, char *argv[])
PQExpBufferData sql;
PGconn *conn;
+ int server_version_num;
FILE *copystream = stdout;
@@ -227,14 +636,31 @@ main(int argc, char *argv[])
conn = connectDatabase(&cparams, progname, echo, false, true);
+ server_version_num = PQserverVersion(conn);
+
initPQExpBuffer(&sql);
appendPQExpBufferStr(&sql, "COPY (");
- if (PQserverVersion(conn) >= 170000)
- appendPQExpBufferStr(&sql, export_query_v17);
- else if (PQserverVersion(conn) >= 100000)
- appendPQExpBufferStr(&sql, export_query_v10);
+ if (server_version_num >= 170000)
+ appendPQExpBufferStr(&sql, export_rel_query_v17);
+ 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 >= 170000)
+ appendPQExpBufferStr(&sql, export_ext_query_v17);
+ else 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");
@@ -244,7 +670,7 @@ main(int argc, char *argv[])
result_status = PQresultStatus(result);
if (result_status != PGRES_COPY_OUT)
- pg_fatal("malformed copy command");
+ pg_fatal("malformed copy command: %s", PQerrorMessage(conn));
for (;;)
{
diff --git a/src/bin/scripts/pg_import_stats.c b/src/bin/scripts/pg_import_stats.c
index 122afc0971..a8b6f9c701 100644
--- a/src/bin/scripts/pg_import_stats.c
+++ b/src/bin/scripts/pg_import_stats.c
@@ -57,6 +57,7 @@ main(int argc, char *argv[])
int i;
int numtables;
+ int numextstats;
pg_logging_init(argv[0]);
progname = get_progname(argv[0]);
@@ -141,21 +142,69 @@ main(int argc, char *argv[])
/* 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, "
+ "n_tuples float4, "
+ "n_pages 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, "
+ "n_tuples float4, "
+ "n_pages 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, server_version_num integer, "
- "n_tuples float4, n_pages integer, stats jsonb )");
+ "schemaname text, "
+ "relname text, "
+ "ext_stats_name 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));
+ pg_fatal("could not create temporary table: %s", PQerrorMessage(conn));
PQclear(result);
+ /*
+ * Copy input data into combined table.
+ */
result = PQexec(conn,
- "COPY import_stats(schemaname, relname, server_version_num, n_tuples, "
- "n_pages, stats) FROM STDIN");
+ "COPY import_stats(schemaname, relname, ext_stats_name, "
+ "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));
@@ -185,30 +234,52 @@ main(int argc, char *argv[])
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, n_tuples, n_pages, stats) "
+ "SELECT schemaname, relname, server_version_num, "
+ "n_tuples, n_pages, 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);
- 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);
+ /*
+ * 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("error in PREPARE: %s", PQerrorMessage(conn));
+ pg_fatal("relation stats insert error: %s", PQerrorMessage(conn));
+
+ numextstats = atol(PQcmdTuples(result));
PQclear(result);
- if (!quiet)
+ if (numtables > 0)
{
- result = PQprepare(conn, "echo",
- "SELECT s.schemaname, s.relname "
- "FROM import_stats AS s "
+
+ result = PQprepare(conn, "import_rel",
+ "SELECT pg_import_rel_stats(c.oid, s.server_version_num, "
+ " s.n_tuples, s.n_pages, s.stats) 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);
@@ -216,62 +287,172 @@ main(int argc, char *argv[])
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;
+ 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);
- const char *const values[] = {istr};
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("error in PREPARE: %s", PQerrorMessage(conn));
- snprintf(istr, 32, "%d", i);
+ PQclear(result);
+ }
- if (!quiet)
+ for (i = 1; i <= numtables; i++)
{
- result = PQexecPrepared(conn, "echo", 1, values, NULL, NULL, 0);
- schema = pg_strdup(PQgetvalue(result, 0, 0));
- table = pg_strdup(PQgetvalue(result, 0, 1));
+ 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);
}
+ }
- PQclear(result);
+ if (numextstats > 0)
+ {
+
+ result = PQprepare(conn, "import_ext",
+ "SELECT pg_import_ext_stats(e.oid, s.server_version_num, "
+ " s.stats) 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 ",
+ 1, NULL);
- result = PQexecPrepared(conn, "import", 1, values, NULL, NULL, 0);
+ if (PQresultStatus(result) != PGRES_COMMAND_OK)
+ pg_fatal("error in PREPARE: %s", PQerrorMessage(conn));
- if (quiet)
+ 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);
- continue;
}
- if (PQresultStatus(result) == PGRES_TUPLES_OK)
+ for (i = 1; i <= numextstats; i++)
{
- int rows = PQntuples(result);
+ char istr[32];
+ char *schema = NULL;
+ char *table = NULL;
+ char *stat = NULL;
+
+ const char *const values[] = {istr};
+
+ snprintf(istr, 32, "%d", i);
- if (rows == 1)
+ if (!quiet)
{
- char *retval = PQgetvalue(result, 0, 0);
- if (*retval == 't')
- printf("%s.%s: imported\n", schema, table);
+ 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
- printf("%s.%s: failed\n", schema, table);
+ pg_fatal("import function must return 0 or 1 rows");
}
- 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));
+ printf("%s on %s.%s: error: %s\n", stat, schema, table, PQerrorMessage(conn));
- if (schema != NULL)
- pfree(schema);
+ if (schema != NULL)
+ pfree(schema);
- if (table != NULL)
- pfree(table);
+ if (table != NULL)
+ pfree(table);
- PQclear(result);
+ if (stat != NULL)
+ pfree(stat);
+
+ PQclear(result);
+ }
}
exit(0);
--
2.43.0