From aeffb52e6c744f624ea32b3ef587f5e9d0b5b85b Mon Sep 17 00:00:00 2001 From: Corey Huinker 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