v11 attached.
- TAP tests passing (the big glitch was that indexes that are used in
constraints should have their stats dependent on the constraint, not the
index, thanks Jeff)
- The new range-specific statistics types are now supported. I'm not happy
with the typid machinations I do to get them to work, but it is working so
far. These are stored out-of-stakind-order (7 before 6), which is odd
because all other types seem store stakinds in ascending order. It
shouldn't matter, it was just odd.
- regression tests now make simpler calls with arbitrary stats to
demonstrate the function usage more cleanly
- pg_set_*_stats function now have all of their parameters in the same
order as the table/view they pull from
From 5c63ed5748eb3817d193b64329b57dc590e0196e Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huin...@gmail.com>
Date: Mon, 11 Mar 2024 14:18:39 -0400
Subject: [PATCH v11 1/2] Create pg_set_relation_stats, pg_set_attribute_stats.
These functions will be used by pg_dump/restore and pg_upgrade to convey
relation and attribute statistics from the source database to the
target. This would be done instead of vacuumdb --analyze-in-stages.
Both functions take an oid to identify the target relation that will
receive the statistics. There is nothing requiring that relation to be
the same one as the one exported, though the statistics would have to
make sense in the context of the new relation.
The parameters of pg_set_attribute_stats intentionaly mirror the columns
in the view pg_stats, with the ANYARRAY types casted to TEXT. Those
values will be cast to arrays of the basetype of the attribute, and that
operation may fail if the attribute type has changed.
The statistics imported by pg_set_attribute_stats are imported
transactionally like any other operation. However, pg_set_relation_stats
does it's update in-place, which is to say non-transactionally. This is
in line with what ANALYZE does to avoid table bloat in pg_class.
These functions also allows for tweaking of table statistics in-place,
allowing the user to inflate rowcounts, skew histograms, etc, to see
what those changes will evoke from the query planner.
---
src/include/catalog/pg_proc.dat | 15 +
src/include/statistics/statistics.h | 2 +
src/backend/statistics/Makefile | 3 +-
src/backend/statistics/meson.build | 1 +
src/backend/statistics/statistics.c | 603 ++++++++++++++++++
.../regress/expected/stats_export_import.out | 283 ++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/stats_export_import.sql | 246 +++++++
doc/src/sgml/func.sgml | 91 +++
9 files changed, 1244 insertions(+), 2 deletions(-)
create mode 100644 src/backend/statistics/statistics.c
create mode 100644 src/test/regress/expected/stats_export_import.out
create mode 100644 src/test/regress/sql/stats_export_import.sql
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 177d81a891..f31412d4a6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12177,4 +12177,19 @@
proargtypes => 'int2',
prosrc => 'gist_stratnum_identity' },
+# Statistics Import
+{ oid => '8048',
+ descr => 'set statistics on relation',
+ proname => 'pg_set_relation_stats', provolatile => 'v', proisstrict => 't',
+ proparallel => 'u', prorettype => 'bool',
+ proargtypes => 'oid int4 float4 int4',
+ proargnames => '{relation,relpages,reltuples,relallvisible}',
+ prosrc => 'pg_set_relation_stats' },
+{ oid => '8049',
+ descr => 'set statistics on attribute',
+ proname => 'pg_set_attribute_stats', provolatile => 'v', proisstrict => 'f',
+ proparallel => 'u', prorettype => 'bool',
+ proargtypes => 'oid name bool float4 int4 float4 text _float4 text float4 text _float4 _float4 text float4 text',
+ proargnames => '{relation,attname,inherited,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' },
]
diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h
index 7f2bf18716..1dddf96576 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -127,4 +127,6 @@ extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind,
int nclauses);
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 */
diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makefile
index 89cf8c2797..e4f8ab7c4f 100644
--- a/src/backend/statistics/Makefile
+++ b/src/backend/statistics/Makefile
@@ -16,6 +16,7 @@ OBJS = \
dependencies.o \
extended_stats.o \
mcv.o \
- mvdistinct.o
+ mvdistinct.o \
+ statistics.o
include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/statistics/meson.build b/src/backend/statistics/meson.build
index 73b29a3d50..331e82c776 100644
--- a/src/backend/statistics/meson.build
+++ b/src/backend/statistics/meson.build
@@ -5,4 +5,5 @@ backend_sources += files(
'extended_stats.c',
'mcv.c',
'mvdistinct.c',
+ 'statistics.c',
)
diff --git a/src/backend/statistics/statistics.c b/src/backend/statistics/statistics.c
new file mode 100644
index 0000000000..f27bfd60a3
--- /dev/null
+++ b/src/backend/statistics/statistics.c
@@ -0,0 +1,603 @@
+/*-------------------------------------------------------------------------
+ * statistics.c
+ *
+ * POSTGRES statistics import
+ *
+ * Code supporting the direct importation of relation statistics, similar to
+ * what is done by the ANALYZE command.
+ *
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/statistics/statistics.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/heapam.h"
+#include "catalog/pg_database.h"
+#include "catalog/pg_operator.h"
+#include "catalog/indexing.h"
+#include "catalog/pg_type.h"
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
+#include "parser/parse_oper.h"
+#include "statistics/statistics.h"
+#include "utils/acl.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/lsyscache.h"
+#include "utils/syscache.h"
+#include "utils/typcache.h"
+
+/*
+ * A role has privileges to vacuum or analyze the relation if any of the
+ * following are true:
+ * - the role owns the current database and the relation is not shared
+ * - the role has the MAINTAIN privilege on the relation
+ *
+ */
+static bool
+canModifyRelation(Oid relid, Form_pg_class reltuple)
+{
+ return ((object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId())
+ && !reltuple->relisshared) ||
+ pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK);
+}
+
+/*
+ * Set 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.
+ *
+ */
+Datum
+pg_set_relation_stats(PG_FUNCTION_ARGS)
+{
+ enum
+ {
+ P_RELATION = 0, /* oid */
+ P_RELPAGES, /* int */
+ P_RELTUPLES, /* float4 */
+ P_RELALLVISIBLE, /* int */
+ P_NUM_PARAMS
+ };
+
+ Oid relid;
+ Relation rel;
+ HeapTuple ctup;
+ Form_pg_class pgcform;
+ float4 reltuples;
+ int relpages;
+ int relallvisible;
+
+ /* The function is strict, but just to be safe */
+ Assert(!PG_ARGISNULL(P_RELATION) && !PG_ARGISNULL(P_RELTUPLES) &&
+ !PG_ARGISNULL(P_RELPAGES) && !PG_ARGISNULL(P_RELALLVISIBLE));
+
+ relid = PG_GETARG_OID(P_RELATION);
+
+ /*
+ * Open the relation, getting ShareUpdateExclusiveLock to ensure that no
+ * other stat-setting operation can run on it concurrently.
+ */
+ rel = table_open(RelationRelationId, ShareUpdateExclusiveLock);
+
+ /*
+ * TODO: choose an error code appropriate for this situation.
+ * Canidates are:
+ * ERRCODE_INVALID_CATALOG_NAME
+ * ERRCODE_UNDEFINED_TABLE
+ * ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE
+ * ERRCODE_OBJECT_IN_USE
+ * ERRCODE_SYSTEM_ERROR
+ * ERRCODE_INTERNAL_ERROR
+ */
+ ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(ctup))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("pg_class entry for relid %u not found", relid)));
+
+ pgcform = (Form_pg_class) GETSTRUCT(ctup);
+
+ if (!canModifyRelation(relid, pgcform))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for relation %s",
+ RelationGetRelationName(rel))));
+
+
+ relpages = PG_GETARG_INT32(P_RELPAGES);
+ reltuples = PG_GETARG_FLOAT4(P_RELTUPLES);
+ relallvisible = PG_GETARG_INT32(P_RELALLVISIBLE);
+
+ /* Only update pg_class if there is a meaningful change */
+ if ((pgcform->reltuples != reltuples)
+ || (pgcform->relpages != relpages)
+ || (pgcform->relallvisible != relallvisible))
+ {
+ pgcform->relpages = PG_GETARG_INT32(P_RELPAGES);
+ pgcform->reltuples = PG_GETARG_FLOAT4(P_RELTUPLES);
+ pgcform->relallvisible = PG_GETARG_INT32(P_RELALLVISIBLE);
+
+ heap_inplace_update(rel, ctup);
+ }
+
+ table_close(rel, NoLock);
+
+ PG_RETURN_BOOL(true);
+}
+
+/*
+ * Convenience wrapper to confirm that the user can modify the relation.
+ * Use this when there is no need to modify the pg_class entry itself.
+ */
+static void
+checkCanModifyRelation(Relation rel)
+{
+ Oid relid;
+ HeapTuple tup;
+ Form_pg_class pgcform;
+
+ relid = RelationGetRelid(rel);
+ tup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+
+ pgcform = (Form_pg_class) GETSTRUCT(tup);
+
+ if (!canModifyRelation(relid, pgcform))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for relation %s",
+ RelationGetRelationName(rel))));
+
+ ReleaseSysCache(tup);
+}
+
+
+/*
+ * Perform the cast of text to some array type
+ */
+static Datum
+cast_stavalue(FmgrInfo *finfo, Datum d, Oid typid, int32 typmod)
+{
+ char *s = TextDatumGetCString(d);
+ Datum out = FunctionCall3(finfo, CStringGetDatum(s),
+ ObjectIdGetDatum(typid),
+ Int32GetDatum(typmod));
+
+ pfree(s);
+
+ return out;
+}
+
+
+/*
+ * Import statistics for a given relation attribute.
+ *
+ * This will insert/replace a row in pg_statistic for the given relation and
+ * attribute name.
+ *
+ * This function will return NULL if either the relation or the attname are
+ * NULL.
+ *
+ * If there is no attribute with a matching attname in the relation, the
+ * function will return false. If there is a matching attribute but the
+ * attribute is dropped, then function will return false.
+ *
+ * The function does not specify values for staopN or stacollN parameters
+ * because those values are determined by the corresponding stakindN value and
+ * the attribute's underlying datatype.
+ *
+ * The stavaluesN parameters are text values, and must be a valid input string
+ * for an array of the basetype of the attribute. Any error generated by the
+ * array_in() function will in turn fail the function.
+ */
+Datum
+pg_set_attribute_stats(PG_FUNCTION_ARGS)
+{
+ enum
+ {
+ P_RELATION = 0, /* oid */
+ P_ATTNAME, /* name */
+ P_INHERITED, /* bool */
+ P_NULL_FRAC, /* float4 */
+ P_AVG_WIDTH, /* int32 */
+ P_N_DISTINCT, /* float4 */
+ P_MC_VALS, /* text, null */
+ P_MC_FREQS, /* float4[], null */
+ P_HIST_BOUNDS, /* text, null */
+ P_CORRELATION, /* float4, null */
+ P_MC_ELEMS, /* text, null */
+ P_MC_ELEM_FREQS, /* float4[], null */
+ P_ELEM_COUNT_HIST, /* float4[], null */
+ P_RANGE_LENGTH_HIST, /* text, null */
+ P_RANGE_EMPTY_FRAC, /* float4, null */
+ P_RANGE_BOUNDS_HIST, /* text, null */
+ P_NUM_PARAMS
+ };
+
+ /* names of columns that cannot be null */
+ const char *required_param_names[] = {
+ "relation",
+ "attname",
+ "stainherit",
+ "stanullfrac",
+ "stawidth",
+ "stadistinct"
+ };
+
+ Oid relid;
+ Name attname;
+ Relation rel;
+ HeapTuple atup;
+
+ Oid typid;
+ int32 typmod;
+ Oid typcoll;
+ Oid eqopr;
+ Oid ltopr;
+ Oid basetypid;
+ Oid baseeqopr;
+ Oid baseltopr;
+
+ float4 stanullfrac;
+ int stawidth;
+
+ Datum values[Natts_pg_statistic] = {0};
+ bool nulls[Natts_pg_statistic] = {false};
+
+ Relation sd;
+ HeapTuple oldtup;
+ CatalogIndexState indstate;
+ HeapTuple stup;
+ Form_pg_attribute attr;
+
+ FmgrInfo finfo;
+
+ int k = 0;
+
+ /*
+ * This function is pseudo-strct in that a NULL for the relation oid or
+ * the attribute name results is a NULL return value.
+ */
+ if (PG_ARGISNULL(P_RELATION) || PG_ARGISNULL(P_ATTNAME))
+ PG_RETURN_NULL();
+
+ for (int i = P_INHERITED; i <= P_N_DISTINCT; i++)
+ if (PG_ARGISNULL(i))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot be NULL", required_param_names[i])));
+
+ relid = PG_GETARG_OID(P_RELATION);
+
+ attname = PG_GETARG_NAME(P_ATTNAME);
+
+ rel = relation_open(relid, ShareUpdateExclusiveLock);
+ checkCanModifyRelation(rel);
+
+ sd = table_open(StatisticRelationId, RowExclusiveLock);
+ checkCanModifyRelation(sd);
+
+ atup = SearchSysCache2(ATTNAME,
+ ObjectIdGetDatum(relid),
+ NameGetDatum(attname));
+
+ if (!HeapTupleIsValid(atup))
+ {
+ /* Attribute not found nowhere to import the stats to */
+ relation_close(rel, NoLock);
+ PG_RETURN_BOOL(false);
+ }
+
+ attr = (Form_pg_attribute) GETSTRUCT(atup);
+ if (attr->attisdropped)
+ {
+ ReleaseSysCache(atup);
+ relation_close(rel, NoLock);
+ PG_RETURN_BOOL(false);
+ }
+
+ /*
+ * If this relation is an index and that index has expressions in it, and
+ * the attnum specified is known to be an expression, then we must walk
+ * the list attributes up to the specified attnum to get the right
+ * expression.
+ */
+ if ((rel->rd_rel->relkind == RELKIND_INDEX
+ || (rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX))
+ && (rel->rd_indexprs != NIL)
+ && (rel->rd_index->indkey.values[attr->attnum - 1] == 0))
+ {
+ ListCell *indexpr_item = list_head(rel->rd_indexprs);
+ Node *expr;
+
+ for (int i = 0; i < attr->attnum - 1; i++)
+ if (rel->rd_index->indkey.values[i] == 0)
+ indexpr_item = lnext(rel->rd_indexprs, indexpr_item);
+
+ if (indexpr_item == NULL) /* shouldn't happen */
+ elog(ERROR, "too few entries in indexprs list");
+
+ expr = (Node *) lfirst(indexpr_item);
+
+ typid = exprType(expr);
+ typmod = exprTypmod(expr);
+
+ /*
+ * If a collation has been specified for the index column, use that in
+ * preference to anything else; but if not, fall back to whatever we
+ * can get from the expression.
+ */
+ if (OidIsValid(attr->attcollation))
+ typcoll = attr->attcollation;
+ else
+ typcoll = exprCollation(expr);
+ }
+ else
+ {
+ typid = attr->atttypid;
+ typmod = attr->atttypmod;
+ typcoll = attr->attcollation;
+ }
+
+ /* if it's a multirange, step down to the range type */
+ if (type_is_multirange(typid))
+ typid = get_multirange_range(typid);
+
+
+ get_sort_group_operators(typid, false, false, false,
+ <opr, &eqopr, NULL, NULL);
+
+ /*
+ * if it's a range type, swap the subtype for the base type
+ */
+ if (type_is_range(typid))
+ basetypid = get_range_subtype(typid);
+ else
+ basetypid = get_base_element_type(typid);
+
+ if (basetypid == InvalidOid)
+ {
+ /* type is its own base type */
+ basetypid = typid;
+ baseltopr = ltopr;
+ baseeqopr = eqopr;
+ }
+ else
+ get_sort_group_operators(basetypid, false, false, false,
+ &baseltopr, &baseeqopr, NULL, NULL);
+
+ stanullfrac = PG_GETARG_FLOAT4(P_NULL_FRAC);
+ if ((stanullfrac < 0.0) || (stanullfrac > 1.0))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stanullfrac %f is out of range 0.0 to 1.0", stanullfrac)));
+
+ stawidth = PG_GETARG_INT32(P_AVG_WIDTH);
+ if (stawidth < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("stawidth %d must be >= 0", stawidth)));
+
+ values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(relid);
+ values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(attr->attnum);
+ values[Anum_pg_statistic_stainherit - 1] = PG_GETARG_DATUM(P_INHERITED);
+ values[Anum_pg_statistic_stanullfrac - 1] = PG_GETARG_DATUM(P_NULL_FRAC);
+ values[Anum_pg_statistic_stawidth - 1] = PG_GETARG_DATUM(P_AVG_WIDTH);
+ values[Anum_pg_statistic_stadistinct - 1] = PG_GETARG_DATUM(P_N_DISTINCT);
+
+ fmgr_info(F_ARRAY_IN, &finfo);
+
+ /* MC_VALS || MC_FREQS => STATISTIC_KIND_MCV */
+ if ((!PG_ARGISNULL(P_MC_VALS)) || (!PG_ARGISNULL(P_MC_FREQS)))
+ {
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_MCV);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(eqopr);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(typcoll);
+
+ if (PG_ARGISNULL(P_MC_FREQS))
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true;
+ else
+ values[Anum_pg_statistic_stanumbers1 - 1 + k] =
+ PG_GETARG_DATUM(P_MC_FREQS);
+
+ if (PG_ARGISNULL(P_MC_VALS))
+ nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true;
+ else
+ values[Anum_pg_statistic_stavalues1 - 1 + k] =
+ cast_stavalue(&finfo, PG_GETARG_DATUM(P_MC_VALS), basetypid, typmod);
+
+ k++;
+ }
+
+ /* HIST_BOUNDS => STATISTIC_KIND_HISTOGRAM */
+ if (!PG_ARGISNULL(P_HIST_BOUNDS))
+ {
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_HISTOGRAM);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(ltopr);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(typcoll);
+
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true;
+ values[Anum_pg_statistic_stavalues1 - 1 + k] =
+ cast_stavalue(&finfo, PG_GETARG_DATUM(P_HIST_BOUNDS), basetypid, typmod);
+
+ k++;
+ }
+
+ /* CORRELATION => STATISTIC_KIND_CORRELATION */
+ if (!PG_ARGISNULL(P_CORRELATION))
+ {
+ Datum elems[] = { PG_GETARG_DATUM(P_CORRELATION) };
+ ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID);
+
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_CORRELATION);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(ltopr);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(typcoll);
+ values[Anum_pg_statistic_stanumbers1 - 1 + k] = PointerGetDatum(arry);
+ nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true;
+
+ k++;
+ }
+
+ /* MC_ELEMS || MC_ELEM_FREQS => STATISTIC_KIND_MCELEM */
+ if ((!PG_ARGISNULL(P_MC_ELEMS)) || (!PG_ARGISNULL(P_MC_ELEM_FREQS)))
+ {
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_MCELEM);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(baseeqopr);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(typcoll);
+
+ if (PG_ARGISNULL(P_MC_ELEM_FREQS))
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true;
+ else
+ values[Anum_pg_statistic_stanumbers1 - 1 + k] =
+ PG_GETARG_DATUM(P_MC_ELEM_FREQS);
+
+ if (PG_ARGISNULL(P_MC_ELEMS))
+ nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true;
+ else
+ values[Anum_pg_statistic_stavalues1 - 1 + k] =
+ cast_stavalue(&finfo, PG_GETARG_DATUM(P_MC_ELEMS),
+ basetypid, typmod);
+
+ k++;
+ }
+
+ /* ELEM_COUNT_HIST => STATISTIC_KIND_DECHIST */
+ if (!PG_ARGISNULL(P_ELEM_COUNT_HIST))
+ {
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_DECHIST);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(baseeqopr);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(typcoll);
+
+ values[Anum_pg_statistic_stanumbers1 - 1 + k] =
+ PG_GETARG_DATUM(P_ELEM_COUNT_HIST);
+ nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true;
+
+ k++;
+ }
+
+ /*
+ * After this point, it is theoretically possible to overflow
+ * STATISTIC_NUM_SLOTS so we should check that before adding any
+ * more stat entries.
+ */
+
+ /*
+ * ELEM_COUNT_HIST => STATISTIC_KIND_BOUNDS_HISTOGRAM
+ *
+ * This stakind appears before STATISTIC_KIND_BOUNDS_HISTOGRAM
+ * even though it is numerically greater, and all other stakinds
+ * appear in numerical order. We duplicate this quirk to make
+ * before/after tests of pg_statistic records easier.
+ */
+ if (!PG_ARGISNULL(P_RANGE_BOUNDS_HIST))
+ {
+ if (k >= (STATISTIC_NUM_SLOTS - 1))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("imported statistics must have a maximum of %d slots",
+ STATISTIC_NUM_SLOTS)));
+
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_BOUNDS_HISTOGRAM);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(InvalidOid);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(InvalidOid);
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true;
+ values[Anum_pg_statistic_stavalues1 - 1 + k] =
+ cast_stavalue(&finfo, PG_GETARG_DATUM(P_RANGE_BOUNDS_HIST), typid, typmod);
+
+ k++;
+ }
+
+ /* P_RANGE_LENGTH_HIST || P_RANGE_EMPTY_FRAC => STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM */
+ if ((!PG_ARGISNULL(P_RANGE_LENGTH_HIST)) ||
+ (!PG_ARGISNULL(P_RANGE_EMPTY_FRAC)))
+ {
+ if (k >= (STATISTIC_NUM_SLOTS - 1))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("imported statistics must have a maximum of %d slots",
+ STATISTIC_NUM_SLOTS)));
+
+ values[Anum_pg_statistic_stakind1 - 1 + k] =
+ Int16GetDatum(STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(Float8LessOperator);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(InvalidOid);
+
+ if (PG_ARGISNULL(P_RANGE_EMPTY_FRAC))
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true;
+ else
+ {
+ Datum elems[] = { PG_GETARG_DATUM(P_RANGE_EMPTY_FRAC) };
+ ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID);
+ values[Anum_pg_statistic_stanumbers1 - 1 + k] = PointerGetDatum(arry);
+ }
+
+ if (PG_ARGISNULL(P_RANGE_LENGTH_HIST))
+ nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true;
+ else
+ values[Anum_pg_statistic_stavalues1 - 1 + k] =
+ cast_stavalue(&finfo, PG_GETARG_DATUM(P_RANGE_LENGTH_HIST), FLOAT8OID, typmod);
+
+ k++;
+ }
+
+ /* fill in all remaining slots */
+ for(; k < STATISTIC_NUM_SLOTS; k++)
+ {
+ values[Anum_pg_statistic_stakind1 - 1 + k] = Int16GetDatum(0);
+ values[Anum_pg_statistic_staop1 - 1 + k] = ObjectIdGetDatum(InvalidOid);
+ values[Anum_pg_statistic_stacoll1 - 1 + k] = ObjectIdGetDatum(InvalidOid);
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + k] = true;
+ nulls[Anum_pg_statistic_stavalues1 - 1 + k] = true;
+ }
+
+ /* Is there already a pg_statistic tuple for this attribute? */
+ oldtup = SearchSysCache3(STATRELATTINH,
+ ObjectIdGetDatum(relid),
+ Int16GetDatum(attr->attnum),
+ PG_GETARG_DATUM(P_INHERITED));
+
+ indstate = CatalogOpenIndexes(sd);
+
+ if (HeapTupleIsValid(oldtup))
+ {
+ /* Yes, replace it */
+ bool replaces[Natts_pg_statistic] = {true};
+
+ stup = heap_modify_tuple(oldtup, RelationGetDescr(sd),
+ values, nulls, replaces);
+ ReleaseSysCache(oldtup);
+ CatalogTupleUpdateWithInfo(sd, &stup->t_self, stup, indstate);
+ }
+ else
+ {
+ /* No, insert new tuple */
+ stup = heap_form_tuple(RelationGetDescr(sd), values, nulls);
+ CatalogTupleInsertWithInfo(sd, stup, indstate);
+ }
+
+ heap_freetuple(stup);
+ CatalogCloseIndexes(indstate);
+ table_close(sd, NoLock);
+ relation_close(rel, NoLock);
+ ReleaseSysCache(atup);
+ PG_RETURN_BOOL(true);
+}
diff --git a/src/test/regress/expected/stats_export_import.out b/src/test/regress/expected/stats_export_import.out
new file mode 100644
index 0000000000..2e4c7fada6
--- /dev/null
+++ b/src/test/regress/expected/stats_export_import.out
@@ -0,0 +1,283 @@
+CREATE SCHEMA stats_export_import;
+CREATE TYPE stats_export_import.complex_type AS (
+ a integer,
+ b real,
+ c text,
+ d date,
+ e jsonb);
+CREATE TABLE stats_export_import.test(
+ id INTEGER PRIMARY KEY,
+ name text,
+ comp stats_export_import.complex_type,
+ arange int4range,
+ tags text[]
+);
+SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible
+----------+-----------+---------------
+ 0 | -1 | 0
+(1 row)
+
+SELECT pg_set_relation_stats('stats_export_import.test'::regclass, 999, 3.6::real, 15000);
+ pg_set_relation_stats
+-----------------------
+ t
+(1 row)
+
+SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible
+----------+-----------+---------------
+ 999 | 3.6 | 15000
+(1 row)
+
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_export_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real,
+ most_common_vals => '{1,2,3}'::text,
+ most_common_freqs => '{0.1,0.2,0.3}'::real[],
+ histogram_bounds => '{1,2,3,4}'::text,
+ correlation => 0.9::real,
+ most_common_elems => '{1,3}'::text,
+ most_common_elem_freqs => '{0.3,0.2}'::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_length_histogram => NULL::text,
+ range_empty_frac => NULL::real,
+ range_bounds_histogram => NULL::text
+ );
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+SELECT *
+FROM pg_stats
+WHERE schemaname = 'stats_export_import'
+AND tablename = 'test'
+AND attname = 'id';
+ schemaname | tablename | attname | inherited | 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
+---------------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------
+ stats_export_import | test | id | f | 0.1 | 2 | 0.3 | {1,2,3} | {0.1,0.2,0.3} | {1,2,3,4} | 0.9 | {1,3} | {0.3,0.2} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | |
+(1 row)
+
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_export_import.test'::regclass,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real,
+ most_common_vals => NULL::text,
+ most_common_freqs => NULL::real[],
+ histogram_bounds => NULL::text,
+ correlation => NULL::real,
+ most_common_elems => NULL::text,
+ most_common_elem_freqs => NULL::real[],
+ elem_count_histogram => NULL::real[],
+ range_length_histogram => '{399,499,Infinity}'::text,
+ range_empty_frac => 0.5::real,
+ range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
+ );
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+SELECT range_empty_frac, range_bounds_histogram
+FROM pg_stats
+WHERE schemaname = 'stats_export_import'
+AND tablename = 'test'
+AND attname = 'arange';
+ range_empty_frac | range_bounds_histogram
+------------------+--------------------------------------
+ 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"}
+(1 row)
+
+INSERT INTO stats_export_import.test
+SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_export_import.complex_type, int4range(1,4), array['red','green']
+UNION ALL
+SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_export_import.complex_type, int4range(1,4), array['blue','yellow']
+UNION ALL
+SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_export_import.complex_type, int4range(-1,1), array['"orange"', 'purple', 'cyan']
+UNION ALL
+SELECT 4, 'four', NULL, int4range(0,100), NULL;
+CREATE INDEX is_odd ON stats_export_import.test(((comp).a % 2 = 1));
+-- Generate statistics on table with data
+ANALYZE stats_export_import.test;
+CREATE TABLE stats_export_import.test_clone ( LIKE stats_export_import.test );
+CREATE INDEX is_odd_clone ON stats_export_import.test_clone(((comp).a % 2 = 1));
+--
+-- Turn off ECHO for the transfer, because the actual stats generated by
+-- ANALYZE could change, and we don't care about the actual stats, we care
+-- about the ability to transfer them to another relation.
+--
+\set orig_ECHO :ECHO
+\set ECHO none
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+ pg_set_attribute_stats
+------------------------
+ t
+(1 row)
+
+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)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 1d8a414eea..0c89ffc02d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -103,7 +103,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# ----------
# Another group of parallel tests (JSON related)
# ----------
-test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
+test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson stats_export_import
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/stats_export_import.sql b/src/test/regress/sql/stats_export_import.sql
new file mode 100644
index 0000000000..4788929bdd
--- /dev/null
+++ b/src/test/regress/sql/stats_export_import.sql
@@ -0,0 +1,246 @@
+CREATE SCHEMA stats_export_import;
+
+CREATE TYPE stats_export_import.complex_type AS (
+ a integer,
+ b real,
+ c text,
+ d date,
+ e jsonb);
+
+CREATE TABLE stats_export_import.test(
+ id INTEGER PRIMARY KEY,
+ name text,
+ comp stats_export_import.complex_type,
+ arange int4range,
+ tags text[]
+);
+
+SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_export_import.test'::regclass;
+
+SELECT pg_set_relation_stats('stats_export_import.test'::regclass, 999, 3.6::real, 15000);
+
+SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_export_import.test'::regclass;
+
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_export_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real,
+ most_common_vals => '{1,2,3}'::text,
+ most_common_freqs => '{0.1,0.2,0.3}'::real[],
+ histogram_bounds => '{1,2,3,4}'::text,
+ correlation => 0.9::real,
+ most_common_elems => '{1,3}'::text,
+ most_common_elem_freqs => '{0.3,0.2}'::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_length_histogram => NULL::text,
+ range_empty_frac => NULL::real,
+ range_bounds_histogram => NULL::text
+ );
+
+
+
+SELECT *
+FROM pg_stats
+WHERE schemaname = 'stats_export_import'
+AND tablename = 'test'
+AND attname = 'id';
+
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_export_import.test'::regclass,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real,
+ most_common_vals => NULL::text,
+ most_common_freqs => NULL::real[],
+ histogram_bounds => NULL::text,
+ correlation => NULL::real,
+ most_common_elems => NULL::text,
+ most_common_elem_freqs => NULL::real[],
+ elem_count_histogram => NULL::real[],
+ range_length_histogram => '{399,499,Infinity}'::text,
+ range_empty_frac => 0.5::real,
+ range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text
+ );
+
+SELECT range_empty_frac, range_bounds_histogram
+FROM pg_stats
+WHERE schemaname = 'stats_export_import'
+AND tablename = 'test'
+AND attname = 'arange';
+
+INSERT INTO stats_export_import.test
+SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_export_import.complex_type, int4range(1,4), array['red','green']
+UNION ALL
+SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_export_import.complex_type, int4range(1,4), array['blue','yellow']
+UNION ALL
+SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_export_import.complex_type, int4range(-1,1), array['"orange"', 'purple', 'cyan']
+UNION ALL
+SELECT 4, 'four', NULL, int4range(0,100), NULL;
+
+CREATE INDEX is_odd ON stats_export_import.test(((comp).a % 2 = 1));
+
+-- Generate statistics on table with data
+ANALYZE stats_export_import.test;
+
+CREATE TABLE stats_export_import.test_clone ( LIKE stats_export_import.test );
+
+CREATE INDEX is_odd_clone ON stats_export_import.test_clone(((comp).a % 2 = 1));
+
+--
+-- Turn off ECHO for the transfer, because the actual stats generated by
+-- ANALYZE could change, and we don't care about the actual stats, we care
+-- about the ability to transfer them to another relation.
+--
+\set orig_ECHO :ECHO
+\set ECHO none
+
+SELECT
+ format('SELECT pg_catalog.pg_set_attribute_stats( '
+ || 'relation => %L::regclass::oid, attname => %L::name, '
+ || 'inherited => %L::boolean, null_frac => %L::real, '
+ || 'avg_width => %L::integer, n_distinct => %L::real, '
+ || 'most_common_vals => %L::text, '
+ || 'most_common_freqs => %L::real[], '
+ || 'histogram_bounds => %L::text, '
+ || 'correlation => %L::real, '
+ || 'most_common_elems => %L::text, '
+ || 'most_common_elem_freqs => %L::real[], '
+ || 'elem_count_histogram => %L::real[], '
+ || 'range_length_histogram => %L::text, '
+ || 'range_empty_frac => %L::real, '
+ || 'range_bounds_histogram => %L::text) ',
+ 'stats_export_import.' || s.tablename || '_clone', s.attname,
+ s.inherited, s.null_frac,
+ s.avg_width, s.n_distinct,
+ s.most_common_vals, s.most_common_freqs, s.histogram_bounds,
+ s.correlation, s.most_common_elems, s.most_common_elem_freqs,
+ s.elem_count_histogram, s.range_length_histogram,
+ s.range_empty_frac, s.range_bounds_histogram)
+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;
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5b225ccf4f..75aa3e3d1c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28865,6 +28865,97 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
in identifying the specific disk files associated with database objects.
</para>
+ <table id="functions-admin-statsimport">
+ <title>Database Object Statistics Import Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_set_relation_stats</primary>
+ </indexterm>
+ <function>pg_set_relation_stats</function> (
+ <parameter>relation</parameter> <type>regclass</type>,
+ <parameter>relpages</parameter> <type>integer</type> )
+ <parameter>reltuples</parameter> <type>real</type>,
+ <parameter>relallvisible</parameter> <type>integer</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Updates the <structname>pg_class</structname> row for the specified
+ <parameter>relation</parameter>, setting the values for the columns
+ <structfield>reltuples</structfield>,
+ <structfield>relpages</structfield>, and
+ <structfield>relallvisible</structfield>.
+ To avoid table bloat in <structname>pg_class</structname>, this change
+ is made with an in-place update, and therefore cannot be rolled back through
+ normal transaction processing.
+ </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 auto-analyze. This function is used by
+ <command>pg_upgrade</command> and <command>pg_restore</command> to
+ convey the statistics from the old system version into the new one.
+ </para>
+ </entry>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_set_attribute_stats</primary>
+ </indexterm>
+ <function>pg_set_attribute_stats</function> (
+ <parameter>relation</parameter> <type>regclass</type>,
+ <parameter>attname</parameter> <type>name</type>,
+ <parameter>inherited</parameter> <type>boolean</type>,
+ <parameter>null_frac</parameter> <type>real</type>,
+ <parameter>avg_width</parameter> <type>integer</type>,
+ <parameter>n_distinct</parameter> <type>real</type>,
+ <parameter>most_common_vals</parameter> <type>text</type>,
+ <parameter>most_common_freqs</parameter> <type>real[]</type>,
+ <parameter>histogram_bounds</parameter> <type>text</type>,
+ <parameter>correlation</parameter> <type>real</type>,
+ <parameter>most_common_elems</parameter> <type>text</type>,
+ <parameter>most_common_elem_freqs</parameter> <type>real[]</type>,
+ <parameter>elem_count_histogram</parameter> <type>real[]</type>,
+ <parameter>range_length_histogram</parameter> <type>text</type>,
+ <parameter>range_empty_frac</parameter> <type>real</type>,
+ <parameter>range_bounds_histogram</parameter> <type>text</type>)
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Replaces the <structname>pg_statistic</structname> row for the
+ <structname>pg_attribute</structname> row specified by
+ <parameter>relation</parameter>, <parameter>attname</parameter>
+ and <parameter>inherited</parameter>. Aside from
+ <parameter>relation</parameter>, the parameters in this are all
+ derived from <structname>pg_stats</structname>, and the values
+ given are most often extracted from there.
+ </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 auto-analyze. This function is used by
+ <command>pg_dump</command>, <command>pg_restore</command>, and
+ <command>pg_upgrade</command> to convey the statistics from the old
+ system version into the new one.
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<table id="functions-admin-dblocation">
<title>Database Object Location Functions</title>
<tgroup cols="1">
--
2.44.0
From db1e0b5bbb2a8f640f45d8a7271831535c3d1cea Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huin...@gmail.com>
Date: Sat, 16 Mar 2024 17:21:10 -0400
Subject: [PATCH v11 2/2] Enable dumping of table/index stats in pg_dump.
For each table/matview/index dumped, it will also generate a statement
that calls all of the pg_set_relation_stats() and
pg_set_attribute_stats() calls necessary to restore the statistics of
the current system onto the destination system.
As is the pattern with pg_dump options, this can be disabled with
--no-statistics.
---
src/include/fe_utils/stats_export.h | 36 +++++
src/fe_utils/Makefile | 1 +
src/fe_utils/meson.build | 1 +
src/fe_utils/stats_export.c | 201 +++++++++++++++++++++++++++
src/bin/pg_dump/pg_backup.h | 2 +
src/bin/pg_dump/pg_backup_archiver.c | 5 +
src/bin/pg_dump/pg_dump.c | 100 ++++++++++++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/pg_dumpall.c | 5 +
src/bin/pg_dump/pg_restore.c | 3 +
10 files changed, 353 insertions(+), 2 deletions(-)
create mode 100644 src/include/fe_utils/stats_export.h
create mode 100644 src/fe_utils/stats_export.c
diff --git a/src/include/fe_utils/stats_export.h b/src/include/fe_utils/stats_export.h
new file mode 100644
index 0000000000..f0dc7041f7
--- /dev/null
+++ b/src/include/fe_utils/stats_export.h
@@ -0,0 +1,36 @@
+/*-------------------------------------------------------------------------
+ *
+ * stats_export.h
+ * Queries to export statistics from current and past versions.
+ *
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1995, Regents of the University of California
+ *
+ * src/include/varatt.h
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#ifndef STATS_EXPORT_H
+#define STATS_EXPORT_H
+
+#include "postgres_fe.h"
+#include "libpq-fe.h"
+
+/*
+ * The minimum supported version number. No attempt is made to get statistics
+ * import to work on versions older than this. This version was initially chosen
+ * because that was the minimum version supported by pg_dump at the time.
+ */
+#define MIN_SERVER_NUM 90200
+
+extern bool exportStatsSupported(int server_version_num);
+extern bool exportExtStatsSupported(int server_version_num);
+
+extern const char *exportClassStatsSQL(int server_verson_num);
+extern const char *exportAttributeStatsSQL(int server_verson_num);
+
+extern char *exportRelationStatsSQL(int server_version_num);
+
+#endif
diff --git a/src/fe_utils/Makefile b/src/fe_utils/Makefile
index 946c05258f..c734f9f6d3 100644
--- a/src/fe_utils/Makefile
+++ b/src/fe_utils/Makefile
@@ -32,6 +32,7 @@ OBJS = \
query_utils.o \
recovery_gen.o \
simple_list.o \
+ stats_export.o \
string_utils.o
ifeq ($(PORTNAME), win32)
diff --git a/src/fe_utils/meson.build b/src/fe_utils/meson.build
index 14d0482a2c..fce503f641 100644
--- a/src/fe_utils/meson.build
+++ b/src/fe_utils/meson.build
@@ -12,6 +12,7 @@ fe_utils_sources = files(
'query_utils.c',
'recovery_gen.c',
'simple_list.c',
+ 'stats_export.c',
'string_utils.c',
)
diff --git a/src/fe_utils/stats_export.c b/src/fe_utils/stats_export.c
new file mode 100644
index 0000000000..fd09e6ea8a
--- /dev/null
+++ b/src/fe_utils/stats_export.c
@@ -0,0 +1,201 @@
+/*-------------------------------------------------------------------------
+ *
+ * Utility functions for extracting object statistics for frontend code
+ *
+ * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/fe_utils/stats_export.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres_fe.h"
+
+#include "fe_utils/stats_export.h"
+/*
+#include "libpq/libpq-fs.h"
+*/
+#include "fe_utils/string_utils.h"
+
+/*
+ * No-frills catalog queries that are named according to the statistics they
+ * fetch (relation, attribute, extended) and the earliest server version for
+ * which they work. These are presented so that if other use cases arise they
+ * can share the same base queries but utilize them in their own way.
+ *
+ * The queries themselves do not filter results, so it is up to the caller
+ * to append a WHERE clause filtering either on either c.oid or a combination
+ * of c.relname and n.nspname.
+ */
+
+const char *export_class_stats_query_v9_2 =
+ "SELECT c.oid, n.nspname, c.relname, c.relpages, c.reltuples, c.relallvisible "
+ "FROM pg_class AS c "
+ "JOIN pg_namespace AS n ON n.oid = c.relnamespace";
+
+const char *export_attribute_stats_query_v17 =
+ "SELECT c.oid, n.nspname, c.relname, a.attnum, a.attname, s.inherited, "
+ "s.null_frac, s.avg_width, s.n_distinct, "
+ "s.most_common_vals::text AS most_common_vals, s.most_common_freqs, "
+ "s.histogram_bounds::text AS histogram_bounds, s.correlation, "
+ "s.most_common_elems::text AS most_common_elems, "
+ "s.most_common_elem_freqs, s.elem_count_histogram, "
+ "s.range_length_histogram::text AS range_length_histogram, "
+ "s.range_empty_frac, "
+ "s.range_bounds_histogram::text AS range_bounds_histogram "
+ "FROM pg_class AS c "
+ "JOIN pg_namespace AS n ON n.oid = c.relnamespace "
+ "JOIN pg_attribute AS a ON a.attrelid = c.oid AND not a.attisdropped "
+ "JOIN pg_stats AS s ON s.schemaname = n.nspname AND s.tablename = c.relname";
+
+const char *export_attribute_stats_query_v9_2 =
+ "SELECT c.oid, n.nspname, c.relname, a.attnum, a.attname, s.inherited, "
+ "s.null_frac, s.avg_width, s.n_distinct, "
+ "s.most_common_vals::text AS most_common_vals, s.most_common_freqs, "
+ "s.histogram_bounds::text AS histogram_bounds, s.correlation, "
+ "s.most_common_elems::text AS most_common_elems, "
+ "s.most_common_elem_freqs, s.elem_count_histogram, "
+ "NULL::text AS range_length_histogram, NULL::real AS range_empty_frac, "
+ "NULL::text AS range_bounds_histogram "
+ "FROM pg_class AS c "
+ "JOIN pg_namespace AS n ON n.oid = c.relnamespace "
+ "JOIN pg_attribute AS a ON a.attrelid = c.oid AND not a.attisdropped "
+ "JOIN pg_stats AS s ON s.schemaname = n.nspname AND s.tablename = c.relname";
+
+/*
+ * Returns true if the server version number supports exporting regular
+ * (e.g. pg_statistic) statistics.
+ */
+bool
+exportStatsSupported(int server_version_num)
+{
+ return (server_version_num >= MIN_SERVER_NUM);
+}
+
+/*
+ * Returns true if the server version number supports exporting extended
+ * (e.g. pg_statistic_ext, pg_statitic_ext_data) statistics.
+ *
+ * Currently, none do.
+ */
+bool
+exportExtStatsSupported(int server_version_num)
+{
+ return false;
+}
+
+/*
+ * Return the query appropriate for extracting relation statistics for the
+ * given server version, if one exists.
+ */
+const char *
+exportClassStatsSQL(int server_version_num)
+{
+ if (server_version_num >= MIN_SERVER_NUM)
+ return export_class_stats_query_v9_2;
+ return NULL;
+}
+
+/*
+ * Return the query appropriate for extracting attribute statistics for the
+ * given server version, if one exists.
+ */
+const char *
+exportAttributeStatsSQL(int server_version_num)
+{
+ if (server_version_num >= 170000)
+ return export_attribute_stats_query_v17;
+ if (server_version_num >= MIN_SERVER_NUM)
+ return export_attribute_stats_query_v9_2;
+ return NULL;
+}
+
+/*
+ * Generate a SQL statement that will itself generate a SQL statement to
+ * import all regular stats from a given relation into another relation.
+ *
+ * The query generated takes two parameters.
+ *
+ * $1 is of type Oid, and represents the oid of the source relation.
+ *
+ * $2 is is a cstring, and represents the qualified name of the destination
+ * relation. If NULL, then the qualified name of the source relation will
+ * be used. In either case, the value is casted via ::regclass.
+ *
+ * The function will return NULL for invalid server version numbers.
+ * Otherwise,
+ *
+ * This function needs to work on databases back to 9.2.
+ * The format() function was introduced in 9.1.
+ * The string_agg() aggregate was introduced in 9.0.
+ *
+ */
+char *exportRelationStatsSQL(int server_version_num)
+{
+ const char *relsql = exportClassStatsSQL(server_version_num);
+ const char *attrsql = exportAttributeStatsSQL(server_version_num);
+ const char *filter = "WHERE c.oid = $1::regclass";
+ char *s;
+ PQExpBuffer sql;
+
+ if ((relsql == NULL) || (attrsql == NULL))
+ return NULL;
+
+ /*
+ * Set up the initial CTEs each with the same oid filter
+ */
+ sql = createPQExpBuffer();
+ appendPQExpBuffer(sql,
+ "WITH r AS (%s %s), a AS (%s %s), ",
+ relsql, filter, attrsql, filter);
+
+ /*
+ * Generate the pg_set_relation_stats function call for the relation
+ * and one pg_set_attribute_stats function call for each attribute with
+ * a pg_statistic entry. Give each row an order value such that the
+ * set relation stats call will be first, followed by the set attribute
+ * stats calls in attnum order (even though the attributes are identified
+ * by attname).
+ *
+ * Then aggregate the function calls into a single SELECT statement that
+ * puts the calls in the order described above.
+ */
+ appendPQExpBufferStr(sql,
+ "s(ord,sql) AS ( "
+ "SELECT 0, format('pg_catalog.pg_set_relation_stats("
+ "%L::regclass, %L::integer, %L::real, %L::integer)', "
+ "coalesce($2, format('%I.%I', r.nspname, r.relname)), "
+ "r.relpages, r.reltuples, r.relallvisible) "
+ "FROM r "
+ "UNION ALL "
+ "SELECT 1, format('pg_catalog.pg_set_attribute_stats( "
+ "relation => %L::regclass, attname => %L::name, "
+ "inherited => %L::boolean, null_frac => %L::real, "
+ "avg_width => %L::integer, n_distinct => %L::real, "
+ "most_common_vals => %L::text, "
+ "most_common_freqs => %L::real[], "
+ "histogram_bounds => %L::text, "
+ "correlation => %L::real, "
+ "most_common_elems => %L::text, "
+ "most_common_elem_freqs => %L::real[], "
+ "elem_count_histogram => %L::real[], "
+ "range_length_histogram => %L::text, "
+ "range_empty_frac => %L::real, "
+ "range_bounds_histogram => %L::text)', "
+ "coalesce($2, format('%I.%I', a.nspname, a.relname)), "
+ "a.attname, a.inherited, a.null_frac, a.avg_width, "
+ "a.n_distinct, a.most_common_vals, a.most_common_freqs, "
+ "a.histogram_bounds, a.correlation, "
+ "a.most_common_elems, a.most_common_elem_freqs, "
+ "a.elem_count_histogram, a.range_length_histogram, "
+ "a.range_empty_frac, a.range_bounds_histogram ) "
+ "FROM a "
+ ") "
+ "SELECT 'SELECT ' || string_agg(s.sql, ', ' ORDER BY s.ord) "
+ "FROM s ");
+
+ s = strdup(sql->data);
+ destroyPQExpBuffer(sql);
+ return s;
+}
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 9ef2f2017e..1db5cf52eb 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -112,6 +112,7 @@ typedef struct _restoreOptions
int no_publications; /* Skip publication entries */
int no_security_labels; /* Skip security label entries */
int no_subscriptions; /* Skip subscription entries */
+ int no_statistics; /* Skip statistics import */
int strict_names;
const char *filename;
@@ -179,6 +180,7 @@ typedef struct _dumpOptions
int no_security_labels;
int no_publications;
int no_subscriptions;
+ int no_statistics;
int no_toast_compression;
int no_unlogged_table_data;
int serializable_deferrable;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index d97ebaff5b..d5f61399d9 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2833,6 +2833,10 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
if (ropt->no_subscriptions && strcmp(te->desc, "SUBSCRIPTION") == 0)
return 0;
+ /* If it's a stats dump, maybe ignore it */
+ if (ropt->no_statistics && strcmp(te->desc, "STATISTICS") == 0)
+ return 0;
+
/* Ignore it if section is not to be dumped/restored */
switch (curSection)
{
@@ -2862,6 +2866,7 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
*/
if (strcmp(te->desc, "ACL") == 0 ||
strcmp(te->desc, "COMMENT") == 0 ||
+ strcmp(te->desc, "STATISTICS") == 0 ||
strcmp(te->desc, "SECURITY LABEL") == 0)
{
/* Database properties react to createDB, not selectivity options. */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a5149ca823..5db230e020 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -59,6 +59,7 @@
#include "compress_io.h"
#include "dumputils.h"
#include "fe_utils/option_utils.h"
+#include "fe_utils/stats_export.h"
#include "fe_utils/string_utils.h"
#include "filter.h"
#include "getopt_long.h"
@@ -425,6 +426,7 @@ main(int argc, char **argv)
{"no-comments", no_argument, &dopt.no_comments, 1},
{"no-publications", no_argument, &dopt.no_publications, 1},
{"no-security-labels", no_argument, &dopt.no_security_labels, 1},
+ {"no-statistics", no_argument, &dopt.no_statistics, 1},
{"no-subscriptions", no_argument, &dopt.no_subscriptions, 1},
{"no-toast-compression", no_argument, &dopt.no_toast_compression, 1},
{"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1},
@@ -1130,6 +1132,7 @@ help(const char *progname)
printf(_(" --no-comments do not dump comments\n"));
printf(_(" --no-publications do not dump publications\n"));
printf(_(" --no-security-labels do not dump security label assignments\n"));
+ printf(_(" --no-statistics do not dump statistics\n"));
printf(_(" --no-subscriptions do not dump subscriptions\n"));
printf(_(" --no-table-access-method do not dump table access methods\n"));
printf(_(" --no-tablespaces do not dump tablespace assignments\n"));
@@ -6981,6 +6984,7 @@ getTables(Archive *fout, int *numTables)
/* Tables have data */
tblinfo[i].dobj.components |= DUMP_COMPONENT_DATA;
+ tblinfo[i].dobj.components |= DUMP_COMPONENT_STATISTICS;
/* Mark whether table has an ACL */
if (!PQgetisnull(res, i, i_relacl))
@@ -7478,6 +7482,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
AssignDumpId(&indxinfo[j].dobj);
+ indxinfo[j].dobj.components |= DUMP_COMPONENT_STATISTICS;
indxinfo[j].dobj.dump = tbinfo->dobj.dump;
indxinfo[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_indexname));
indxinfo[j].dobj.namespace = tbinfo->dobj.namespace;
@@ -10227,6 +10232,82 @@ dumpComment(Archive *fout, const char *type,
catalogId, subid, dumpId, NULL);
}
+/*
+ * dumpRelationStats --
+ *
+ * Dump command to import stats into the relation on the new database.
+ */
+static void
+dumpRelationStats(Archive *fout, const DumpableObject *dobj,
+ const char *reltypename, DumpId dumpid)
+{
+ const char *stmtname = "relstats";
+ static bool prepared = false;
+ const char *values[2];
+ PGconn *conn;
+ PGresult *res;
+ PQExpBuffer query;
+ PQExpBuffer tag;
+
+ /* do nothing, if --no-statistics is supplied */
+ if (fout->dopt->no_statistics)
+ return;
+
+ conn = GetConnection(fout);
+
+ if (!prepared)
+ {
+ int ver = PQserverVersion(conn);
+ char *sql = exportRelationStatsSQL(ver);
+
+ if (sql == NULL)
+ pg_fatal("could not prepare stats export query for server version %d",
+ ver);
+
+ res = PQprepare(conn, stmtname, sql, 2, NULL);
+ if (res == NULL || PQresultStatus(res) != PGRES_COMMAND_OK)
+ pg_fatal("prepared statement failed: %s",
+ PQerrorMessage(conn));
+
+ free(sql);
+ prepared = true;
+ }
+
+ values[0] = fmtQualifiedId(dobj->namespace->dobj.name, dobj->name);
+ values[1] = NULL;
+ res = PQexecPrepared(conn, stmtname, 2, values, NULL, NULL, 0);
+
+
+ /* Result set must be 1x1 */
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pg_fatal("error in statistics extraction: %s", PQerrorMessage(conn));
+
+ if (PQntuples(res) != 1)
+ pg_fatal("statistics extraction expected one row, but got %d rows",
+ PQntuples(res));
+
+ query = createPQExpBuffer();
+ appendPQExpBufferStr(query, strdup(PQgetvalue(res, 0, 0)));
+ appendPQExpBufferStr(query, ";\n");
+
+ tag = createPQExpBuffer();
+ appendPQExpBuffer(tag, "%s %s", reltypename,
+ fmtId(dobj->name));
+
+ ArchiveEntry(fout, nilCatalogId, createDumpId(),
+ ARCHIVE_OPTS(.tag = tag->data,
+ .namespace = dobj->namespace->dobj.name,
+ .description = "STATS IMPORT",
+ .section = SECTION_POST_DATA,
+ .createStmt = query->data,
+ .deps = &dumpid,
+ .nDeps = 1));
+
+ PQclear(res);
+ destroyPQExpBuffer(query);
+ destroyPQExpBuffer(tag);
+}
+
/*
* dumpTableComment --
*
@@ -16660,6 +16741,13 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (tbinfo->dobj.dump & DUMP_COMPONENT_SECLABEL)
dumpTableSecLabel(fout, tbinfo, reltypename);
+ /* Statistics are dependent on the definition, not the data */
+ /* Views don't have stats */
+ if ((tbinfo->dobj.dump & DUMP_COMPONENT_STATISTICS) &&
+ (tbinfo->relkind == RELKIND_VIEW))
+ dumpRelationStats(fout, &tbinfo->dobj, reltypename,
+ tbinfo->dobj.dumpId);
+
/* Dump comments on inlined table constraints */
for (j = 0; j < tbinfo->ncheck; j++)
{
@@ -16861,6 +16949,7 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo)
PQExpBuffer delq;
char *qindxname;
char *qqindxname;
+ DumpId dumpid;
/* Do nothing in data-only dump */
if (dopt->dataOnly)
@@ -16973,14 +17062,21 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo)
free(indstatvalsarray);
}
+ /* Comments and stats share same .dep */
+ dumpid = is_constraint ? indxinfo->indexconstraint :
+ indxinfo->dobj.dumpId;
+
/* Dump Index Comments */
if (indxinfo->dobj.dump & DUMP_COMPONENT_COMMENT)
dumpComment(fout, "INDEX", qindxname,
tbinfo->dobj.namespace->dobj.name,
tbinfo->rolname,
indxinfo->dobj.catId, 0,
- is_constraint ? indxinfo->indexconstraint :
- indxinfo->dobj.dumpId);
+ dumpid);
+
+ /* Dump Index Stats */
+ if (indxinfo->dobj.dump & DUMP_COMPONENT_STATISTICS)
+ dumpRelationStats(fout, &indxinfo->dobj, "INDEX", dumpid);
destroyPQExpBuffer(q);
destroyPQExpBuffer(delq);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 9bc93520b4..d6a071ec28 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -101,6 +101,7 @@ typedef uint32 DumpComponents;
#define DUMP_COMPONENT_ACL (1 << 4)
#define DUMP_COMPONENT_POLICY (1 << 5)
#define DUMP_COMPONENT_USERMAP (1 << 6)
+#define DUMP_COMPONENT_STATISTICS (1 << 7)
#define DUMP_COMPONENT_ALL (0xFFFF)
/*
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 491311fe79..37b6ba8a49 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -105,6 +105,7 @@ static int use_setsessauth = 0;
static int no_comments = 0;
static int no_publications = 0;
static int no_security_labels = 0;
+static int no_statistics = 0;
static int no_subscriptions = 0;
static int no_toast_compression = 0;
static int no_unlogged_table_data = 0;
@@ -174,6 +175,7 @@ main(int argc, char *argv[])
{"no-role-passwords", no_argument, &no_role_passwords, 1},
{"no-security-labels", no_argument, &no_security_labels, 1},
{"no-subscriptions", no_argument, &no_subscriptions, 1},
+ {"no-statistics", no_argument, &no_statistics, 1},
{"no-sync", no_argument, NULL, 4},
{"no-toast-compression", no_argument, &no_toast_compression, 1},
{"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1},
@@ -453,6 +455,8 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --no-publications");
if (no_security_labels)
appendPQExpBufferStr(pgdumpopts, " --no-security-labels");
+ if (no_statistics)
+ appendPQExpBufferStr(pgdumpopts, " --no-statistics");
if (no_subscriptions)
appendPQExpBufferStr(pgdumpopts, " --no-subscriptions");
if (no_toast_compression)
@@ -668,6 +672,7 @@ help(void)
printf(_(" --no-publications do not dump publications\n"));
printf(_(" --no-role-passwords do not dump passwords for roles\n"));
printf(_(" --no-security-labels do not dump security label assignments\n"));
+ printf(_(" --no-statistics do not dump statistics\n"));
printf(_(" --no-subscriptions do not dump subscriptions\n"));
printf(_(" --no-sync do not wait for changes to be written safely to disk\n"));
printf(_(" --no-table-access-method do not dump table access methods\n"));
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index c3beacdec1..2d326dec72 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -75,6 +75,7 @@ main(int argc, char **argv)
static int no_publications = 0;
static int no_security_labels = 0;
static int no_subscriptions = 0;
+ static int no_statistics = 0;
static int strict_names = 0;
struct option cmdopts[] = {
@@ -126,6 +127,7 @@ main(int argc, char **argv)
{"no-security-labels", no_argument, &no_security_labels, 1},
{"no-subscriptions", no_argument, &no_subscriptions, 1},
{"filter", required_argument, NULL, 4},
+ {"no-statistics", no_argument, &no_statistics, 1},
{NULL, 0, NULL, 0}
};
@@ -358,6 +360,7 @@ main(int argc, char **argv)
opts->no_publications = no_publications;
opts->no_security_labels = no_security_labels;
opts->no_subscriptions = no_subscriptions;
+ opts->no_statistics = no_statistics;
if (if_exists && !opts->dropSchema)
pg_fatal("option --if-exists requires option -c/--clean");
--
2.44.0
