v24-0003-Create-funcntions-pg_restore_relation_stats.patch
text/x-patch
Filename: v24-0003-Create-funcntions-pg_restore_relation_stats.patch
Type: text/x-patch
Part: 1
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 v24-0003
Subject: Create funcntions pg_restore_relation_stats(), pg_restore_attribute_stats().
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 209 | 0 |
| src/backend/statistics/statistics.c | 292 | 2 |
| src/include/catalog/pg_proc.dat | 19 | 1 |
| src/include/statistics/statistics.h | 3 | 1 |
| src/test/regress/expected/stats_export_import.out | 763 | 51 |
| src/test/regress/sql/stats_export_import.sql | 551 | 35 |
From 382923d7cf64d47cf882668afe68534991d22ae1 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 17 Jul 2024 23:23:23 -0400
Subject: [PATCH v24 3/5] Create funcntions pg_restore_relation_stats(),
pg_restore_attribute_stats().
These functions are variadic requivalents of the pg_set_*_stats()
functions, which have a function signature each stat getting its own
defined parameter (or parameter pair, as the case may be).
Such a rigid function signature would make future compability difficult,
and future compatibility is just what pg_dump needs.
Instead, these functions have all of the statistics put into a variable
argument list organized in name-value pairs. The leading or "name"
parameters must all be of type text and the string must exactly
correspond to the name of a statistics parameter in the corresponding
pg_set_X_stats function. The trailing or "value" parameter must be of
the type expected by the same-named parameter in the pg_set_X_stats
function. Names that do not match a parameter name and types that do not
match the expected type will emit a warning and be ignored. If that
parameter was a require parameter, the function will return false and no
stats will be modified.
The intention of these functions is to be used in pg_dump/pg_restore and
pg_upgrade to allow the user to avoid having to run vacuumdb
--analyze-in-stages after an upgrade or restore.
---
src/include/catalog/pg_proc.dat | 20 +-
src/include/statistics/statistics.h | 4 +-
src/backend/statistics/statistics.c | 294 ++++++-
.../regress/expected/stats_export_import.out | 814 ++++++++++++++++--
src/test/regress/sql/stats_export_import.sql | 586 ++++++++++++-
doc/src/sgml/func.sgml | 209 +++++
6 files changed, 1837 insertions(+), 90 deletions(-)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5e553939b7..42ee44bce6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12222,5 +12222,23 @@
proparallel => 'u', prorettype => 'bool',
proargtypes => 'oid name bool int4 float4 int4 float4 text _float4 text float4 text _float4 _float4 text float4 text',
proargnames => '{relation,attname,inherited,version,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation,most_common_elems,most_common_elem_freqs,elem_count_histogram,range_length_histogram,range_empty_frac,range_bounds_histogram}',
- prosrc => 'pg_set_attribute_stats' }
+ prosrc => 'pg_set_attribute_stats' },
+{ oid => '8050',
+ descr => 'set statistics on relation',
+ proname => 'pg_restore_relation_stats', provariadic => 'any',
+ proisstrict => 'f', provolatile => 'v',
+ proparallel => 'u', prorettype => 'bool',
+ proargtypes => 'regclass int4 any',
+ proargnames => '{relation,version,stats}',
+ proargmodes => '{i,i,v}',
+ prosrc => 'pg_restore_relation_stats' },
+{ oid => '8051',
+ descr => 'set statistics on attribute',
+ proname => 'pg_restore_attribute_stats', provariadic => 'any',
+ proisstrict => 'f', provolatile => 'v',
+ proparallel => 'u', prorettype => 'bool',
+ proargtypes => 'regclass name bool int4 any',
+ proargnames => '{relation,attname,inherited,version,stats}',
+ proargmodes => '{i,i,i,i,v}',
+ prosrc => 'pg_restore_attribute_stats' },
]
diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h
index 1dddf96576..9f558948cd 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -129,4 +129,6 @@ extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx);
extern Datum pg_set_relation_stats(PG_FUNCTION_ARGS);
extern Datum pg_set_attribute_stats(PG_FUNCTION_ARGS);
-#endif /* STATISTICS_H */
+extern Datum pg_restore_relation_stats(PG_FUNCTION_ARGS);
+extern Datum pg_restore_attribute_stats(PG_FUNCTION_ARGS);
+#endif
diff --git a/src/backend/statistics/statistics.c b/src/backend/statistics/statistics.c
index 2a43da7c41..e86b703e3a 100644
--- a/src/backend/statistics/statistics.c
+++ b/src/backend/statistics/statistics.c
@@ -24,6 +24,7 @@
#include "catalog/pg_database.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_type_d.h"
#include "fmgr.h"
#include "funcapi.h"
#include "miscadmin.h"
@@ -43,7 +44,8 @@
/*
* Names of parameters found in the functions pg_set_relation_stats and
- * pg_set_attribute_stats
+ * pg_set_attribute_stats, as well as the keyword names used in
+ * pg_restore_relation_stats and pg_restore_attribute_stats.
*/
const char *relation_name = "relation";
const char *relpages_name = "relpages";
@@ -65,6 +67,13 @@ const char *range_length_hist_name = "range_length_histogram";
const char *range_empty_frac_name = "range_empty_frac";
const char *range_bounds_hist_name = "range_bounds_histogram";
+typedef struct variadic_args {
+ int setargidx;
+ Oid typoid;
+ const char *argname;
+ const char *typname;
+} variadic_args;
+
/*
* A role has privileges to set statistics on the relation if any of the
* following are true:
@@ -93,7 +102,6 @@ can_modify_relation(Relation rel)
Datum
pg_set_relation_stats(PG_FUNCTION_ARGS)
{
-
Datum relation_datum = PG_GETARG_DATUM(0);
bool relation_isnull = PG_ARGISNULL(0);
Oid relation;
@@ -1203,3 +1211,285 @@ pg_set_attribute_stats(PG_FUNCTION_ARGS)
relation_close(rel, NoLock);
PG_RETURN_BOOL(true);
}
+
+ /*
+ * Restore statistics for a given pg_class entry.
+ *
+ * This does an in-place (i.e. non-transactional) update of pg_class, just as
+ * is done in ANALYZE.
+ *
+ * The actual modification of stats happesn in a call to pg_set_relation_stats(),
+ * which has a named parameters for each statistic type. This function serves
+ * as a way to allow stats import calls written for a previous version to work
+ * on the current version, regardless of what parameters were introduced or
+ * removed in the time in between.
+ *
+ */
+Datum
+pg_restore_relation_stats(PG_FUNCTION_ARGS)
+{
+ #define NUM_RELARGS 3
+
+ const variadic_args relation_args[NUM_RELARGS] = {
+ {2, INT4OID, relpages_name, "integer"},
+ {3, FLOAT4OID, reltuples_name, "real"},
+ {4, INT4OID, relallvisible_name, "integer"}
+ };
+
+ bool args_set[NUM_RELARGS] = {false, false, false};
+
+ Datum relation_datum = PG_GETARG_DATUM(0);
+ bool relation_isnull = PG_ARGISNULL(0);
+
+ Datum version_datum = PG_GETARG_DATUM(1);
+ bool version_isnull = PG_ARGISNULL(1);
+
+ /* Flags to indicate that a datum has been found once already */
+
+ /* build argument values to build the object */
+ Datum *args;
+ bool *argnulls;
+ Oid *types;
+ int nargs;
+
+ LOCAL_FCINFO(set_fcinfo, 5);
+ Datum result;
+
+ nargs = extract_variadic_args(fcinfo, 2, true, &args, &types, &argnulls);
+
+ /* if the pairs aren't pairs, something is malformed */
+ if (nargs % 2 == 1)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stats parameters must be in name-value pairs")));
+ PG_RETURN_BOOL(false);
+ }
+
+ InitFunctionCallInfoData(*set_fcinfo, NULL, 5, InvalidOid, NULL, NULL);
+
+ set_fcinfo->args[0].value = relation_datum;
+ set_fcinfo->args[0].isnull = relation_isnull;
+ set_fcinfo->args[1].value = version_datum;
+ set_fcinfo->args[1].isnull = version_isnull;
+
+ /* Assume an argument is NULL unless matching pair found */
+ for (int i = 2; i < 5; i++)
+ {
+ set_fcinfo->args[i].value = (Datum) 0;
+ set_fcinfo->args[i].isnull = true;
+ }
+
+ /* loop through args, matching params to their arg indexes */
+ for (int i = 0; i < nargs; i += 2)
+ {
+ char *statname;
+ int argidx = i + 1;
+ bool found = false;
+
+ if (types[i] != TEXTOID)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stat names must be of type text")));
+ continue;
+ }
+ statname = TextDatumGetCString(args[i]);
+
+ for (int j = 0; j < NUM_RELARGS; j++)
+ {
+ const variadic_args *varg = &relation_args[j];
+
+ if (strcmp(statname, varg->argname) == 0)
+ {
+ found = true;
+
+ /* we go with the first set of each arg, duplicates ignored */
+ if (args_set[j])
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stat name %s already used, subsequent values ignored",
+ statname)));
+ break;
+ }
+ else if (types[argidx] != varg->typoid)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s must be of type %s",
+ statname, varg->typname)));
+ PG_RETURN_BOOL(false);
+ }
+ else
+ {
+ args_set[j] = true;
+ set_fcinfo->args[varg->setargidx].value = args[argidx];
+ set_fcinfo->args[varg->setargidx].isnull = argnulls[argidx];
+ }
+
+ break; /* already matched one */
+ }
+ }
+ if (!found)
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stat name %s is not recognized, values ignored",
+ statname)));
+ pfree(statname);
+ }
+
+ result = (*pg_set_relation_stats) (set_fcinfo);
+
+ PG_RETURN_DATUM(result);
+}
+
+/*
+ * Import statistics for a given relation attribute.
+ *
+ * This will insert/replace a row in pg_statistic for the given combinarion
+ * of relation, attribute, and inherited flag.
+ *
+ * The version parameter is for future use in the events as future versions
+ * may change them meaning of certain parameters.
+ *
+ * The variadic parameters all represent name-value pairs, with the names
+ * corresponding to attributes in pg_stats. Unkown names will generate a
+ * warning.
+ *
+ * Parameters null_frac, avg_width, and n_distinct are required because
+ * those attributes have no default value in pg_statistic.
+ *
+ * The remaining parameters all belong to a specific stakind, and all are
+ * optional. Some stakinds have multiple parameters, and in those cases
+ * both parameters must be specified if one of them is, otherwise a
+ * warning is generated but the rest of the stats may still be imported.
+ *
+ * If there is no attribute with a matching attname in the relation, the
+ * function will raise a warning and return false.
+ *
+ * Parameters corresponding to ANYARRAY columns are instead passed in as text
+ * values, which is a valid input string for an array of the type or element
+ * type of the attribute.
+ */
+Datum
+pg_restore_attribute_stats(PG_FUNCTION_ARGS)
+{
+ #define NUM_ATTARGS 13
+
+ const variadic_args attribute_args[NUM_ATTARGS] = {
+ {4, FLOAT4OID, null_frac_name, "real"},
+ {5, INT4OID, avg_width_name, "integer"},
+ {6, FLOAT4OID, n_distinct_name, "real"},
+ {7, TEXTOID, mc_vals_name, "text"},
+ {8, FLOAT4ARRAYOID, mc_freqs_name, "real[]"},
+ {9, TEXTOID, histogram_bounds_name, "text"},
+ {10, FLOAT4OID, correlation_name, "real"},
+ {11, TEXTOID, mc_elems_name, "text"},
+ {12, FLOAT4ARRAYOID, mc_elem_freqs_name, "real[]"},
+ {13, FLOAT4ARRAYOID, elem_count_hist_name, "real[]"},
+ {14, TEXTOID, range_length_hist_name, "text"},
+ {15, FLOAT4OID, range_empty_frac_name, "real"},
+ {16, TEXTOID, range_bounds_hist_name, "text"},
+ };
+ bool args_set[NUM_ATTARGS] = {false,false,false,false,false,false,false,
+ false,false,false,false,false,false};
+
+ /* build argument values to build the object */
+ Datum *args;
+ bool *argnulls;
+ Oid *types;
+ int nargs;
+
+ LOCAL_FCINFO(set_fcinfo, 17);
+ Datum result;
+
+ InitFunctionCallInfoData(*set_fcinfo, NULL, 17, InvalidOid, NULL, NULL);
+
+ /* line up positional arguments for pg_set_attribute_stats */
+ for (int i = 0; i <= 3; i++)
+ {
+ set_fcinfo->args[i].value = PG_GETARG_DATUM(i);
+ set_fcinfo->args[i].isnull = PG_ARGISNULL(i);
+ }
+
+ /* initialize remaining arguments which may not get set */
+ for (int i = 4; i < 17; i++)
+ {
+ set_fcinfo->args[i].value = (Datum) 0;
+ set_fcinfo->args[i].isnull = true;
+ }
+
+ nargs = extract_variadic_args(fcinfo, 4, true, &args, &types, &argnulls);
+
+ /* if the pairs aren't pairs, something is malformed */
+ if (nargs % 2 == 1)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stats parameters must be in name-value pairs")));
+ PG_RETURN_BOOL(false);
+ }
+
+ /* loop through args, matching params to their arg indexes */
+ for (int i = 0; i < nargs; i += 2)
+ {
+ char *statname;
+ int argidx = i + 1;
+ bool found = false;
+
+ if (types[i] != TEXTOID)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stat names must be of type text")));
+ continue;
+ }
+ statname = TextDatumGetCString(args[i]);
+
+ for (int j = 0; j < NUM_ATTARGS; j++)
+ {
+ const variadic_args *varg = &attribute_args[j];
+
+ if (strcmp(statname, varg->argname) == 0)
+ {
+ found = true;
+
+ /* we go with the first set of each arg, duplicates ignored */
+ if (args_set[j])
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stat name %s already used, subsequent values ignored",
+ statname)));
+ break;
+ }
+ else if (types[argidx] != varg->typoid)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s must be of type %s",
+ statname, varg->typname)));
+ PG_RETURN_BOOL(false);
+ }
+ else
+ {
+ args_set[j] = true;
+ set_fcinfo->args[varg->setargidx].value = args[argidx];
+ set_fcinfo->args[varg->setargidx].isnull = argnulls[argidx];
+ }
+ break; /* already matched one */
+ }
+ }
+ if (!found)
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stat name %s is not recognized, values ignored",
+ statname)));
+ pfree(statname);
+ }
+
+ result = (*pg_set_attribute_stats) (set_fcinfo);
+
+ PG_RETURN_DATUM(result);
+}
diff --git a/src/test/regress/expected/stats_export_import.out b/src/test/regress/expected/stats_export_import.out
index bc99bf3745..ee188ae783 100644
--- a/src/test/regress/expected/stats_export_import.out
+++ b/src/test/regress/expected/stats_export_import.out
@@ -469,40 +469,6 @@ WARNING: Relation test attname id is a scalar type, cannot have stats of type m
t
(1 row)
--- warn: mcelem / mcelem mismatch
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_export_import.test'::regclass,
- attname => 'tags'::name,
- inherited => false::boolean,
- version => 150000::integer,
- null_frac => 0.5::real,
- avg_width => 2::integer,
- n_distinct => -0.1::real,
- most_common_elems => '{one,two}'::text
- );
-WARNING: most_common_elem_freqs cannot be NULL when most_common_elems is NOT NULL
- pg_set_attribute_stats
-------------------------
- t
-(1 row)
-
--- warn: mcelem / mcelem null mismatch part 2
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_export_import.test'::regclass,
- attname => 'tags'::name,
- inherited => false::boolean,
- version => 150000::integer,
- null_frac => 0.5::real,
- avg_width => 2::integer,
- n_distinct => -0.1::real,
- most_common_elem_freqs => '{0.3,0.2,0.2,0.3}'::real[]
- );
-WARNING: most_common_elems cannot be NULL when most_common_elem_freqs is NOT NULL
- pg_set_attribute_stats
-------------------------
- t
-(1 row)
-
-- ok: mcelem
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -520,6 +486,76 @@ SELECT pg_catalog.pg_set_attribute_stats(
t
(1 row)
+-- warn: scalars cannot have mcelem
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elems', '{1,3}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+WARNING: Relation test attname id is a scalar type, cannot have stats of type most_common_elems, ignored
+WARNING: Relation test attname id is a scalar type, cannot have stats of type most_common_elem_freqs, ignored
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: mcelem / mcelem mismatch
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elems', '{one,two}'::text
+ );
+WARNING: most_common_elem_freqs cannot be NULL when most_common_elems is NOT NULL
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: mcelem / mcelem null mismatch part 2
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3}'::real[]
+ );
+WARNING: most_common_elems cannot be NULL when most_common_elem_freqs is NOT NULL
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- ok: mcelem
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elems', '{one,three}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
SELECT *
FROM pg_stats
WHERE schemaname = 'stats_export_import'
@@ -672,23 +708,6 @@ AND attname = 'arange';
stats_export_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | {399,499,Infinity} | 0.5 |
(1 row)
--- warn: scalars can't have range stats
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_export_import.test'::regclass,
- attname => 'id'::name,
- inherited => false::boolean,
- version => 150000::integer,
- null_frac => 0.5::real,
- avg_width => 2::integer,
- n_distinct => -0.1::real,
- range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
- );
-WARNING: Relation test attname id is not a range type, cannot have stats of type range_bounds_histogram
- pg_set_attribute_stats
-------------------------
- t
-(1 row)
-
-- ok: range_bounds_histogram
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -934,6 +953,699 @@ WHERE s.starelid = 'stats_export_import.is_odd'::regclass;
---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+-----------
(0 rows)
+--
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible
+----------+-----------+---------------
+ 1 | 4 | 0
+(1 row)
+
+-- false: object doesn't exist
+SELECT pg_restore_relation_stats('0'::oid,
+ 150000::integer,
+ 'relpages', '17'::integer,
+ 'reltuples', 400::real,
+ 'relallvisible', 4::integer);
+WARNING: pg_class entry for relid 0 not found
+ pg_restore_relation_stats
+---------------------------
+ f
+(1 row)
+
+-- false: reltuples, relallvisible missing
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', '17'::integer);
+WARNING: reltuples cannot be NULL
+ pg_restore_relation_stats
+---------------------------
+ f
+(1 row)
+
+-- false: null value
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', '17'::integer,
+ 'reltuples', NULL::real,
+ 'relallvisible', 4::integer);
+WARNING: reltuples cannot be NULL
+ pg_restore_relation_stats
+---------------------------
+ f
+(1 row)
+
+-- false: bad relpages type
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', 'nope'::text,
+ 'reltuples', 400.0::real,
+ 'relallvisible', 4::integer);
+WARNING: relpages must be of type integer
+ pg_restore_relation_stats
+---------------------------
+ f
+(1 row)
+
+-- ok
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', 17::integer,
+ 'reltuples', 400.0::real,
+ 'relallvisible', 4::integer);
+ pg_restore_relation_stats
+---------------------------
+ t
+(1 row)
+
+-- false: object does not exist
+SELECT pg_catalog.pg_restore_attribute_stats(
+ '0'::oid,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+WARNING: Parameter relation OID 0 is invalid
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- false: relation null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ NULL::oid,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+WARNING: relation cannot be NULL
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- false: attname null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ NULL::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+WARNING: attname cannot be NULL
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- false: inherited null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ NULL::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+WARNING: inherited cannot be NULL
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- false: null_frac null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', NULL::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+WARNING: null_frac cannot be NULL
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- false: avg_width null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', NULL::integer,
+ 'n_distinct', 0.3::real);
+WARNING: avg_width cannot be NULL
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- false: avg_width null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', NULL::real);
+WARNING: n_distinct cannot be NULL
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- ok: no stakinds
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: mcv / mcf null mismatch part 1
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_freqs', '{0.1,0.2,0.3}'::real[]
+ );
+WARNING: most_common_vals cannot be NULL when most_common_freqs is NOT NULL
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: mcv / mcf null mismatch part 2
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{1,2,3}'::text
+ );
+WARNING: most_common_freqs cannot be NULL when most_common_vals is NOT NULL
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: mcv / mcf type mismatch
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,1,3}'::text,
+ 'most_common_freqs', '{0.2,0.1}'::double precision[]
+ );
+WARNING: most_common_freqs must be of type real[]
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+-- warning: mcv cast failure
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,four,3}'::text,
+ 'most_common_freqs', '{0.3,0.25,0.05}'::real[]
+ );
+WARNING: invalid input syntax for type integer: "four"
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- ok: mcv+mcf
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,1,3}'::text,
+ 'most_common_freqs', '{0.3,0.25,0.05}'::real[]
+ );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: NULL in histogram array
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'histogram_bounds', '{1,NULL,3,4}'::text
+ );
+WARNING: histogram_bounds array cannot contain NULL values
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- ok: histogram_bounds
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'histogram_bounds', '{1,2,3,4}'::text );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: elem_count_histogram null element
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[]
+ );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- ok: elem_count_histogram
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[]
+ );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- range stats on a scalar type
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_empty_frac', 0.5::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text
+ );
+WARNING: Relation test attname id is not a range type, cannot have stats of type range_length_histogram
+WARNING: Relation test attname id is not a range type, cannot have stats of type range_empty_frac
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: range_empty_frac range_length_hist null mismatch
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text
+ );
+WARNING: range_empty_frac cannot be NULL when range_length_histogram is NOT NULL
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: range_empty_frac range_length_hist null mismatch part 2
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_empty_frac', 0.5::real
+ );
+WARNING: range_length_histogram cannot be NULL when range_empty_frac is NOT NULL
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- ok: range_empty_frac + range_length_hist
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_empty_frac', 0.5::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text
+ );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
+ );
+WARNING: Relation test attname id is not a range type, cannot have stats of type range_bounds_histogram
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- ok: range_bounds_histogram
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
+ );
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- warn: too many stat kinds
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,1,3}'::text,
+ 'most_common_freqs', '{0.3,0.25,0.05}'::real[],
+ 'histogram_bounds', '{1,2,3,4}'::text,
+ 'correlation', 1.1::real,
+ 'most_common_elems', '{3,1}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
+ 'range_empty_frac', -0.5::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text,
+ 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text);
+WARNING: imported statistics must have a maximum of 5 slots but 6 given
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+SELECT
+ format('SELECT pg_catalog.pg_restore_attribute_stats( '
+ || '%L::regclass::oid, '
+ || '%L::name, '
+ || '%L::boolean, '
+ || '%L::integer, '
+ || '%L, %L::real, '
+ || '%L, %L::integer, '
+ || '%L, %L::real %s)',
+ 'stats_export_import.' || s.tablename || '_clone',
+ s.attname,
+ s.inherited,
+ 150000,
+ 'null_frac', s.null_frac,
+ 'avg_width', s.avg_width,
+ 'n_distinct', s.n_distinct,
+ CASE
+ WHEN s.most_common_vals IS NULL THEN ''
+ ELSE format(', %L, %L::text, %L, %L::real[]',
+ 'most_common_vals', s.most_common_vals,
+ 'most_common_freqs', s.most_common_freqs)
+ END ||
+ CASE
+ WHEN s.histogram_bounds IS NULL THEN ''
+ ELSE format(', %L, %L::text',
+ 'histogram_bounds', s.histogram_bounds)
+ END ||
+ CASE
+ WHEN s.correlation IS NULL THEN ''
+ ELSE format(', %L, %L::real',
+ 'correlation', s.correlation)
+ END ||
+ CASE
+ WHEN s.most_common_elems IS NULL THEN ''
+ ELSE format(', %L, %L::text, %L, %L::real[]',
+ 'most_common_elems', s.most_common_elems,
+ 'most_common_elem_freqs', s.most_common_elem_freqs)
+ END ||
+ CASE
+ WHEN s.elem_count_histogram IS NULL THEN ''
+ ELSE format(', %L, %L::real[]',
+ 'elem_count_histogram', s.elem_count_histogram)
+ END ||
+ CASE
+ WHEN s.range_bounds_histogram IS NULL THEN ''
+ ELSE format(', %L, %L::text',
+ 'range_bounds_histogram', s.range_bounds_histogram)
+ END ||
+ CASE
+ WHEN s.range_empty_frac IS NULL THEN ''
+ ELSE format(', %L, %L::real, %L, %L::text',
+ 'range_empty_frac', s.range_empty_frac,
+ 'range_length_histogram', s.range_length_histogram)
+ END)
+FROM pg_catalog.pg_stats AS s
+WHERE s.schemaname = 'stats_export_import'
+AND s.tablename IN ('test', 'is_odd')
+\gexec
+SELECT pg_catalog.pg_restore_attribute_stats( 'stats_export_import.is_odd_clone'::regclass::oid, 'expr'::name, 'f'::boolean, '150000'::integer, 'null_frac', '0.25'::real, 'avg_width', '1'::integer, 'n_distinct', '-0.5'::real , 'most_common_vals', '{t}'::text, 'most_common_freqs', '{0.5}'::real[], 'correlation', '0.5'::real)
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT pg_catalog.pg_restore_attribute_stats( 'stats_export_import.test_clone'::regclass::oid, 'name'::name, 'f'::boolean, '150000'::integer, 'null_frac', '0'::real, 'avg_width', '4'::integer, 'n_distinct', '-1'::real , 'histogram_bounds', '{four,one,tre,two}'::text, 'correlation', '-0.4'::real)
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT pg_catalog.pg_restore_attribute_stats( 'stats_export_import.test_clone'::regclass::oid, 'comp'::name, 'f'::boolean, '150000'::integer, 'null_frac', '0.25'::real, 'avg_width', '53'::integer, 'n_distinct', '-0.75'::real , 'histogram_bounds', E'{"(1,1.1,ONE,01-01-2001,\\"{\\"\\"xkey\\"\\": \\"\\"xval\\"\\"}\\")","(2,2.2,TWO,02-02-2002,\\"[true, 4, \\"\\"six\\"\\"]\\")","(3,3.3,TRE,03-03-2003,)"}'::text, 'correlation', '1'::real)
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT pg_catalog.pg_restore_attribute_stats( 'stats_export_import.test_clone'::regclass::oid, 'tags'::name, 'f'::boolean, '150000'::integer, 'null_frac', '0.5'::real, 'avg_width', '2'::integer, 'n_distinct', '-0.1'::real , 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[])
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT pg_catalog.pg_restore_attribute_stats( 'stats_export_import.test_clone'::regclass::oid, 'id'::name, 'f'::boolean, '150000'::integer, 'null_frac', '0.5'::real, 'avg_width', '2'::integer, 'n_distinct', '-0.1'::real )
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT pg_catalog.pg_restore_attribute_stats( 'stats_export_import.test_clone'::regclass::oid, 'arange'::name, 'f'::boolean, '150000'::integer, 'null_frac', '0.5'::real, 'avg_width', '2'::integer, 'n_distinct', '-0.1'::real , 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text)
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+-- restore ECHO to original value
+\set ECHO :orig_ECHO
+SELECT c.relname, COUNT(*) AS num_stats
+FROM pg_class AS c
+JOIN pg_statistic s ON s.starelid = c.oid
+WHERE c.relnamespace = 'stats_export_import'::regnamespace
+AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone')
+GROUP BY c.relname
+ORDER BY c.relname;
+ relname | num_stats
+--------------+-----------
+ is_odd | 1
+ is_odd_clone | 1
+ test | 5
+ test_clone | 5
+(4 rows)
+
+-- check test minus test_clone
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test_clone'::regclass;
+ attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction
+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+-----------
+(0 rows)
+
+-- check test_clone minus test
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test_clone'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test'::regclass;
+ attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction
+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+-----------
+(0 rows)
+
+-- check is_odd minus is_odd_clone
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd_clone'::regclass;
+ attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction
+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+-----------
+(0 rows)
+
+-- check is_odd_clone minus is_odd
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd_clone'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd'::regclass;
+ attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction
+---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+-----------
+(0 rows)
+
DROP SCHEMA stats_export_import CASCADE;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to type stats_export_import.complex_type
diff --git a/src/test/regress/sql/stats_export_import.sql b/src/test/regress/sql/stats_export_import.sql
index 76a48e0f94..08f63b11da 100644
--- a/src/test/regress/sql/stats_export_import.sql
+++ b/src/test/regress/sql/stats_export_import.sql
@@ -321,30 +321,6 @@ SELECT pg_catalog.pg_set_attribute_stats(
most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[]
);
--- warn: mcelem / mcelem mismatch
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_export_import.test'::regclass,
- attname => 'tags'::name,
- inherited => false::boolean,
- version => 150000::integer,
- null_frac => 0.5::real,
- avg_width => 2::integer,
- n_distinct => -0.1::real,
- most_common_elems => '{one,two}'::text
- );
-
--- warn: mcelem / mcelem null mismatch part 2
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_export_import.test'::regclass,
- attname => 'tags'::name,
- inherited => false::boolean,
- version => 150000::integer,
- null_frac => 0.5::real,
- avg_width => 2::integer,
- n_distinct => -0.1::real,
- most_common_elem_freqs => '{0.3,0.2,0.2,0.3}'::real[]
- );
-
-- ok: mcelem
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -358,6 +334,56 @@ SELECT pg_catalog.pg_set_attribute_stats(
most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[]
);
+-- warn: scalars cannot have mcelem
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elems', '{1,3}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+
+-- warn: mcelem / mcelem mismatch
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elems', '{one,two}'::text
+ );
+
+-- warn: mcelem / mcelem null mismatch part 2
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3}'::real[]
+ );
+
+-- ok: mcelem
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_elems', '{one,three}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+
SELECT *
FROM pg_stats
WHERE schemaname = 'stats_export_import'
@@ -418,6 +444,7 @@ SELECT pg_catalog.pg_set_attribute_stats(
range_empty_frac => 0.5::real,
range_length_histogram => '{399,499,Infinity}'::text
);
+
-- warn: range_empty_frac range_length_hist null mismatch
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -429,6 +456,7 @@ SELECT pg_catalog.pg_set_attribute_stats(
n_distinct => -0.1::real,
range_length_histogram => '{399,499,Infinity}'::text
);
+
-- warn: range_empty_frac range_length_hist null mismatch part 2
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -440,6 +468,7 @@ SELECT pg_catalog.pg_set_attribute_stats(
n_distinct => -0.1::real,
range_empty_frac => 0.5::real
);
+
-- ok: range_empty_frac + range_length_hist
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -460,17 +489,6 @@ AND tablename = 'test'
AND inherited = false
AND attname = 'arange';
--- warn: scalars can't have range stats
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_export_import.test'::regclass,
- attname => 'id'::name,
- inherited => false::boolean,
- version => 150000::integer,
- null_frac => 0.5::real,
- avg_width => 2::integer,
- n_distinct => -0.1::real,
- range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
- );
-- ok: range_bounds_histogram
SELECT pg_catalog.pg_set_attribute_stats(
relation => 'stats_export_import.test'::regclass,
@@ -715,4 +733,502 @@ FROM pg_statistic s
JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
WHERE s.starelid = 'stats_export_import.is_odd'::regclass;
+--
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+
+-- false: object doesn't exist
+SELECT pg_restore_relation_stats('0'::oid,
+ 150000::integer,
+ 'relpages', '17'::integer,
+ 'reltuples', 400::real,
+ 'relallvisible', 4::integer);
+
+-- false: reltuples, relallvisible missing
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', '17'::integer);
+
+-- false: null value
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', '17'::integer,
+ 'reltuples', NULL::real,
+ 'relallvisible', 4::integer);
+
+-- false: bad relpages type
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', 'nope'::text,
+ 'reltuples', 400.0::real,
+ 'relallvisible', 4::integer);
+
+-- ok
+SELECT pg_restore_relation_stats('stats_export_import.test'::regclass,
+ 150000::integer,
+ 'relpages', 17::integer,
+ 'reltuples', 400.0::real,
+ 'relallvisible', 4::integer);
+
+-- false: object does not exist
+SELECT pg_catalog.pg_restore_attribute_stats(
+ '0'::oid,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+
+-- false: relation null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ NULL::oid,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+
+-- false: attname null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ NULL::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+
+-- false: inherited null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ NULL::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+
+-- false: null_frac null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', NULL::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+
+-- false: avg_width null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', NULL::integer,
+ 'n_distinct', 0.3::real);
+
+-- false: avg_width null
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', NULL::real);
+
+-- ok: no stakinds
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.1::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', 0.3::real);
+
+-- warn: mcv / mcf null mismatch part 1
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_freqs', '{0.1,0.2,0.3}'::real[]
+ );
+
+-- warn: mcv / mcf null mismatch part 2
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{1,2,3}'::text
+ );
+
+-- warn: mcv / mcf type mismatch
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,1,3}'::text,
+ 'most_common_freqs', '{0.2,0.1}'::double precision[]
+ );
+
+-- warning: mcv cast failure
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,four,3}'::text,
+ 'most_common_freqs', '{0.3,0.25,0.05}'::real[]
+ );
+
+-- ok: mcv+mcf
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,1,3}'::text,
+ 'most_common_freqs', '{0.3,0.25,0.05}'::real[]
+ );
+
+-- warn: NULL in histogram array
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'histogram_bounds', '{1,NULL,3,4}'::text
+ );
+
+-- ok: histogram_bounds
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'histogram_bounds', '{1,2,3,4}'::text );
+
+-- warn: elem_count_histogram null element
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[]
+ );
+
+-- ok: elem_count_histogram
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'tags'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[]
+ );
+
+-- range stats on a scalar type
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_empty_frac', 0.5::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text
+ );
+
+-- warn: range_empty_frac range_length_hist null mismatch
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text
+ );
+
+-- warn: range_empty_frac range_length_hist null mismatch part 2
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_empty_frac', 0.5::real
+ );
+
+-- ok: range_empty_frac + range_length_hist
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_empty_frac', 0.5::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text
+ );
+
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'id'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
+ );
+
+-- ok: range_bounds_histogram
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
+ );
+
+-- warn: too many stat kinds
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'stats_export_import.test'::regclass,
+ 'arange'::name,
+ false::boolean,
+ 150000::integer,
+ 'null_frac', 0.5::real,
+ 'avg_width', 2::integer,
+ 'n_distinct', -0.1::real,
+ 'most_common_vals', '{2,1,3}'::text,
+ 'most_common_freqs', '{0.3,0.25,0.05}'::real[],
+ 'histogram_bounds', '{1,2,3,4}'::text,
+ 'correlation', 1.1::real,
+ 'most_common_elems', '{3,1}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
+ 'range_empty_frac', -0.5::real,
+ 'range_length_histogram', '{399,499,Infinity}'::text,
+ 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text);
+
+SELECT
+ format('SELECT pg_catalog.pg_restore_attribute_stats( '
+ || '%L::regclass::oid, '
+ || '%L::name, '
+ || '%L::boolean, '
+ || '%L::integer, '
+ || '%L, %L::real, '
+ || '%L, %L::integer, '
+ || '%L, %L::real %s)',
+ 'stats_export_import.' || s.tablename || '_clone',
+ s.attname,
+ s.inherited,
+ 150000,
+ 'null_frac', s.null_frac,
+ 'avg_width', s.avg_width,
+ 'n_distinct', s.n_distinct,
+ CASE
+ WHEN s.most_common_vals IS NULL THEN ''
+ ELSE format(', %L, %L::text, %L, %L::real[]',
+ 'most_common_vals', s.most_common_vals,
+ 'most_common_freqs', s.most_common_freqs)
+ END ||
+ CASE
+ WHEN s.histogram_bounds IS NULL THEN ''
+ ELSE format(', %L, %L::text',
+ 'histogram_bounds', s.histogram_bounds)
+ END ||
+ CASE
+ WHEN s.correlation IS NULL THEN ''
+ ELSE format(', %L, %L::real',
+ 'correlation', s.correlation)
+ END ||
+ CASE
+ WHEN s.most_common_elems IS NULL THEN ''
+ ELSE format(', %L, %L::text, %L, %L::real[]',
+ 'most_common_elems', s.most_common_elems,
+ 'most_common_elem_freqs', s.most_common_elem_freqs)
+ END ||
+ CASE
+ WHEN s.elem_count_histogram IS NULL THEN ''
+ ELSE format(', %L, %L::real[]',
+ 'elem_count_histogram', s.elem_count_histogram)
+ END ||
+ CASE
+ WHEN s.range_bounds_histogram IS NULL THEN ''
+ ELSE format(', %L, %L::text',
+ 'range_bounds_histogram', s.range_bounds_histogram)
+ END ||
+ CASE
+ WHEN s.range_empty_frac IS NULL THEN ''
+ ELSE format(', %L, %L::real, %L, %L::text',
+ 'range_empty_frac', s.range_empty_frac,
+ 'range_length_histogram', s.range_length_histogram)
+ END)
+FROM pg_catalog.pg_stats AS s
+WHERE s.schemaname = 'stats_export_import'
+AND s.tablename IN ('test', 'is_odd')
+\gexec
+
+-- restore ECHO to original value
+\set ECHO :orig_ECHO
+
+SELECT c.relname, COUNT(*) AS num_stats
+FROM pg_class AS c
+JOIN pg_statistic s ON s.starelid = c.oid
+WHERE c.relnamespace = 'stats_export_import'::regnamespace
+AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone')
+GROUP BY c.relname
+ORDER BY c.relname;
+
+-- check test minus test_clone
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test_clone'::regclass;
+
+-- check test_clone minus test
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test_clone'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'test_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.test'::regclass;
+
+-- check is_odd minus is_odd_clone
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd_clone'::regclass;
+
+-- check is_odd_clone minus is_odd
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd_clone'::regclass
+EXCEPT
+SELECT
+ a.attname, 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, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5, 'is_odd_clone' AS direction
+FROM pg_statistic s
+JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+WHERE s.starelid = 'stats_export_import.is_odd'::regclass;
+
DROP SCHEMA stats_export_import CASCADE;
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5115a97ac2..eda608bad0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -29922,6 +29922,215 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para>
</entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_restore_relation_stats</primary>
+ </indexterm>
+ <function>pg_restore_relation_stats</function> (
+ <parameter>relation</parameter> <type>regclass</type>,
+ <parameter>version</parameter> <type>integer</type>,
+ <literal>VARIADIC</literal> <parameter>stats</parameter>
+ <type>"any"</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ This is a variadic wrapper for
+ <function>pg_set_relation_stats</function>, meant for use by
+ <application>pg_dump</application> so as to be future-proof if the
+ signature of <function>pg_set_relation_stats</function> were to change
+ in a future version. The parameter <parameter>stats</parameter> must
+ contain a list of elements organized in key-value pairs. The first
+ element of each pair must be of type <type>text</type> and must match
+ the name of a parameter of <function>pg_set_relation_stats</function>.
+ The second element of each pair must be a value of datatype
+ coresponding to the parameter named in the first element. If the first
+ element of the pair does not name a parameter from
+ <function>pg_set_relation_stats</function>, or of the datatype of the
+ second element does not correspond to that parameter, then the elements
+ will be rejected with a warning, and the function will continue to process
+ the request, if possible.
+ </para>
+ </entry>
+ </row>
+ <!--
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_restore_attribute_stats</primary>
+ </indexterm>
+ <function>pg_restore_attribute_stats</function> (
+ <parameter>relation</parameter> <type>regclass</type>
+ , <parameter>attname</parameter> <type>name</type>
+ , <parameter>inherited</parameter> <type>boolean</type>
+ , <parameter>version</parameter> <type>integer</type>
+ , <literal>VARIADIC</literal> <parameter>stats</parameter> <type>"any"</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ This is a variadic wrapper for
+ <function>pg_set_attribute_stats</function>, meant for use by
+ <application>pg_dump</application> so as to be future-proof if the
+ signature of <function>pg_set_attribute_stats</function> were to change
+ in a future version. The parameter <parameter>stats</parameter> must
+ contain a list of elements organized in key-value pairs. The first
+ element of each pair must be of type <type>text</type> and must match
+ the name of a parameter of <function>pg_set_attribute_stats</function>.
+ The second element of each pair must be a value of datatype
+ coresponding to the parameter named in the first element. If the first
+ element of the pair does not name a parameter from
+ <function>pg_set_attribute_stats</function>, or of the datatype of the
+ second element does not correspond to that parameter, then the elements
+ will be rejected with a warning, and the function will continue to process
+ the request, if possible.
+ </para>
+ <variablelist>
+ <varlistentry>
+ <term><literal>null_frac</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>real</type>.
+ This parameter is currently required.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>avg_width</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>integer</type>.
+ This parameter is currently required.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>n_distinct</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>real</type>.
+ This parameter is currently required.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>most_common_vals</literal></term>
+ <listitem>
+ <para>
+ <literal>most_common_vals</literal>
+ Must be followed by a parameter of type <type>text</type>. If this
+ parameter is specified then <literal>most_common_freqs</literal>
+ must also be specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>most_common_freqs</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of <type>real[]</type>, and can only
+ be specified if <literal>most_common_vals</literal> is also specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>histogram_bounds</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>text</type>.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>correlation</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>real</type>.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>most_common_elems</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>text</type>. If this
+ parameter is specified then <literal>most_common_elem_freqs</literal>
+ must also be specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>most_common_elem_freqs</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>real[]</type>, and can
+ only be specified if <literal>most_common_elems</literal> is also
+ specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>elem_count_histogram</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>real[]</type>.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>range_length_histogram</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>text</type>. If this
+ parameter is specified then <literal>range_empty_frac</literal>
+ must also be specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>range_empty_frac</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>real</type>, and can
+ only be specified if <literal>range_length_histogram</literal>
+ is also specified.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>range_bounds_histogram</literal></term>
+ <listitem>
+ <para>
+ Must be followed by a parameter of type <type>text</type>
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ <para>
+ Any other parameter names given must also have a value pair, but will emit
+ a warning and thereafter be ignored.
+ </para>
+ <para>
+ The parameters will return true if stats were updated and false if not.
+ </para>
+ <para>
+ This function mimics the behavior of <command>ANALYZE</command> in its
+ effects on the values in <structname>pg_statistic</structname>, except
+ that the values are supplied as parameters rather than derived from
+ table sampling.
+ </para>
+ <para>
+ The purpose of this function is to apply statistics values in an
+ upgrade situation that are "good enough" for system operation until
+ they are replaced by the next <command>ANALYZE</command>, usually via
+ <command>autovacuum</command>.
+ </para>
+ <para>
+ The caller must either be the owner of the relation, or have superuser
+ privileges.
+ </para>
+ </entry>
+ </row>
+ -->
</tbody>
</tgroup>
</table>
--
2.45.2