From 21599a31f5d266421a9b9470d68817e055437bcb Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Wed, 24 Jul 2024 23:45:26 -0400 Subject: [PATCH v28j] Create functions pg_set_relation_stats(), pg_clear_relation_stats(), pg_restore_relation_stats(), pg_set_attribute_stats(), pg_clear_attribute_stats(), and pg_restore_attribute_stats(). --- CATVERSION BUMP --- --- doc/src/sgml/func.sgml | 299 +++++ src/backend/catalog/system_functions.sql | 31 + src/backend/statistics/Makefile | 1 + src/backend/statistics/import_stats.c | 1263 ++++++++++++++++++++ src/backend/statistics/meson.build | 1 + src/include/catalog/pg_proc.dat | 48 + src/include/statistics/statistics.h | 3 + src/test/regress/expected/stats_import.out | 766 ++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/stats_import.sql | 623 ++++++++++ 10 files changed, 3036 insertions(+), 1 deletion(-) create mode 100644 src/backend/statistics/import_stats.c create mode 100644 src/test/regress/expected/stats_import.out create mode 100644 src/test/regress/sql/stats_import.sql diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 461fc3f437c..9abf7927ab7 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -29949,6 +29949,305 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a in identifying the specific disk files associated with database objects. + + Database Object Statistics Import Functions + + + + + Function + + + Description + + + + + + + + + + pg_set_relation_stats + + pg_set_relation_stats ( + relation regclass + , relpages integer + , reltuples real + , relallvisible integer ) + boolean + + + Updates table-level statistics for the given relation to the + specified values. The parameters correspond to columns in pg_class. Unspecified + or NULL values leave the setting + unchanged. Returns true if a change was made; + false otherwise. + + + Ordinarily, these statistics are collected automatically or updated + as a part of or , so it's not necessary to call this + function. However, it may be useful when testing the effects of + statistics on the planner to understand or anticipate plan changes. + + + The caller must have the MAINTAIN privilege on + the table or be the owner of the database. + + + The value of relpages must be greater than + or equal to 0, + reltuples must be greater than or equal to + -1.0, and relallvisible + must be greater than or equal to 0. + + + + Changes made by this function are likely to be overwritten by autovacuum (or manual + VACUUM or ANALYZE) and should + be considered temporary. + + + The signature of this function may change in new major releases if + there are changes to the statistics being tracked. + + + + + + + + + + pg_clear_relation_stats + + pg_clear_relation_stats ( relation regclass ) + boolean + + + Clears table-level statistics for the given relation, as though the + table was newly created. Returns true if a change + was made; false otherwise. + + + The caller must have the MAINTAIN privilege on + the table or be the owner of the database. + + + + Changes made by this function are likely to be overwritten by autovacuum (or manual + VACUUM or ANALYZE) and should + be considered temporary. + + + The signature of this function may change in new major releases if + there are changes to the statistics being tracked. + + + + + + + + + pg_restore_relation_stats + + pg_restore_relation_stats ( + VARIADIC kwargs "any" ) + boolean + + + Similar to pg_set_relation_stats(), but intended + for bulk restore of relation statistics. The tracked statistics may + change from version to version, so the primary purpose of this + function is to maintain a consistent function signature to avoid + errors when restoring statistics from previous versions. + + + Arguments are passed as pairs of argname + and argvalue, where + argname corresponds to a named argument in + pg_set_relation_stats() and + argvalue is of the corresponding type. + + + Additionally, this function supports argument name + version of type integer, which + specifies the version from which the statistics originated, improving + intepretation of older statistics. + + + For example, to set the relpages and + reltuples of the table + mytable: + + SELECT pg_restore_relation_stats( + 'relation', 'mytable'::regclass, + 'relpages', 173::integer, + 'reltuples', 10000::float4); + + + + Minor errors are reported as a WARNING and + ignored, and remaining statistics specified will still be restored. + + + + + + + + + pg_set_attribute_stats + + pg_set_attribute_stats ( + relation regclass, + attname name, + inherited boolean + , null_frac real + , avg_width integer + , n_distinct real + , most_common_vals text, most_common_freqs real[] + , histogram_bounds text + , correlation real + , most_common_elems text, most_common_elem_freqs real[] + , elem_count_histogram real[] + , range_length_histogram text + , range_empty_frac real + , range_bounds_histogram text ) + booolean + + + Creates or updates attribute-level statistics for the given relation + and attribute name to the specified values. The parameters correspond + to to attributes of the same name found in the pg_stats + view. Returns true if a change was made; + false otherwise. + + + All parameters default to NULL, which leave the + corresponding statistic unchanged. + + + Ordinarily, these statistics are collected automatically or updated + as a part of or , so it's not necessary to call this + function. However, it may be useful when testing the effects of + statistics on the planner to understand or anticipate plan changes. + + + The caller must have the MAINTAIN privilege on + the table or be the owner of the database. + + + + Changes made by this function are likely to be overwritten by autovacuum (or manual + VACUUM or ANALYZE) and should + be considered temporary. + + + The signature of this function may change in new major releases if + there are changes to the statistics being tracked. + + + + + + + + + + pg_clear_attribute_stats + + pg_clear_attribute_stats ( + relation regclass, + attname name, + inherited boolean ) + boolean + + + Clears table-level statistics for the given relation attribute, as + though the table was newly created. Returns true + if a change was made; false otherwise. + + + The caller must have the MAINTAIN privilege on + the table or be the owner of the database. + + + + Changes made by this function are likely to be overwritten by autovacuum (or manual + VACUUM or ANALYZE) and should + be considered temporary. + + + The signature of this function may change in new major releases if + there are changes to the statistics being tracked. + + + + + + + + + pg_restore_attribute_stats + + pg_restore_attribute_stats ( + VARIADIC kwargs "any" ) + boolean + + + Similar to pg_set_attribute_stats(), but + intended for bulk restore of attribute statistics. The tracked + statistics may change from version to version, so the primary purpose + of this function is to maintain a consistent function signature to + avoid errors when restoring statistics from previous versions. + + + Arguments are passed as pairs of argname + and argvalue, where + argname corresponds to a named argument in + pg_set_attribute_stats() and + argvalue is of the corresponding type. + + + Additionally, this function supports argument name + version of type integer, which + specifies the version from which the statistics originated, improving + intepretation of older statistics. + + + For example, to set the avg_width and + null_frac for the attribute + col1 of the table + mytable: + + SELECT pg_restore_attribute_stats( + 'relation', 'mytable'::regclass, + 'attname', 'col1'::name, + 'inherited', false, + 'avg_width', 125::integer, + 'null_frac', 0.5::real); + + + + Minor errors are reported as a WARNING and + ignored, and remaining statistics specified will still be restored. + + + + + +
+ Database Object Location Functions diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 623b9539b15..b3b2705f4bb 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -639,6 +639,37 @@ LANGUAGE INTERNAL CALLED ON NULL INPUT VOLATILE PARALLEL SAFE AS 'pg_stat_reset_slru'; +CREATE OR REPLACE FUNCTION + pg_set_attribute_stats(relation regclass, + attname name, + inherited bool, + null_frac real DEFAULT NULL, + avg_width integer DEFAULT NULL, + n_distinct real DEFAULT NULL, + most_common_vals text DEFAULT NULL, + most_common_freqs real[] DEFAULT NULL, + histogram_bounds text DEFAULT NULL, + correlation real DEFAULT NULL, + most_common_elems text DEFAULT NULL, + most_common_elem_freqs real[] DEFAULT NULL, + elem_count_histogram real[] DEFAULT NULL, + range_length_histogram text DEFAULT NULL, + range_empty_frac real DEFAULT NULL, + range_bounds_histogram text DEFAULT NULL) +RETURNS bool +LANGUAGE INTERNAL +CALLED ON NULL INPUT VOLATILE +AS 'pg_set_attribute_stats'; + +CREATE OR REPLACE FUNCTION + pg_clear_attribute_stats(relation regclass, + attname name, + inherited bool) +RETURNS bool +LANGUAGE INTERNAL +CALLED ON NULL INPUT VOLATILE +AS 'pg_clear_attribute_stats'; + -- -- The default permissions for functions mean that anyone can execute them. -- A number of functions shouldn't be executable by just anyone, but rather diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makefile index 89cf8c27973..5e776c02218 100644 --- a/src/backend/statistics/Makefile +++ b/src/backend/statistics/Makefile @@ -15,6 +15,7 @@ include $(top_builddir)/src/Makefile.global OBJS = \ dependencies.o \ extended_stats.o \ + import_stats.o \ mcv.o \ mvdistinct.o diff --git a/src/backend/statistics/import_stats.c b/src/backend/statistics/import_stats.c new file mode 100644 index 00000000000..ea87a8e849a --- /dev/null +++ b/src/backend/statistics/import_stats.c @@ -0,0 +1,1263 @@ +/*------------------------------------------------------------------------- + * import_stats.c + * + * PostgreSQL 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/import_stats.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/heapam.h" +#include "catalog/indexing.h" +#include "catalog/pg_collation.h" +#include "catalog/pg_database.h" +#include "catalog/pg_operator.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "nodes/nodeFuncs.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" + +#define DEFAULT_RELPAGES Int32GetDatum(0) +#define DEFAULT_RELTUPLES Float4GetDatum(-1.0) +#define DEFAULT_RELALLVISIBLE Int32GetDatum(0) + +#define DEFAULT_NULL_FRAC Float4GetDatum(0.0) +#define DEFAULT_AVG_WIDTH Int32GetDatum(0) /* unknown */ +#define DEFAULT_N_DISTINCT Float4GetDatum(0.0) /* unknown */ + +/* + * Positional argument numbers, names, and types for + * relation_statistics_update() and attribute_statistics_update(). + */ + +typedef enum +{ + RELATION_ARG = 0, + RELPAGES_ARG, + RELTUPLES_ARG, + RELALLVISIBLE_ARG, + NUM_RELATION_STATS_ARGS +} relation_stats_argnum; + +typedef enum +{ + ATTRELATION_ARG = 0, + ATTNAME_ARG, + INHERITED_ARG, + NULL_FRAC_ARG, + AVG_WIDTH_ARG, + N_DISTINCT_ARG, + MOST_COMMON_VALS_ARG, + MOST_COMMON_FREQS_ARG, + HISTOGRAM_BOUNDS_ARG, + CORRELATION_ARG, + MOST_COMMON_ELEMS_ARG, + MOST_COMMON_ELEM_FREQS_ARG, + ELEM_COUNT_HISTOGRAM_ARG, + RANGE_LENGTH_HISTOGRAM_ARG, + RANGE_EMPTY_FRAC_ARG, + RANGE_BOUNDS_HISTOGRAM_ARG, + NUM_ATTRIBUTE_STATS_ARGS +} attribute_stats_argnum; + +struct arginfo +{ + const char *argname; + Oid argtype; +}; + +static struct arginfo relarginfo[] = +{ + [RELATION_ARG] = {"relation", REGCLASSOID}, + [RELPAGES_ARG] = {"relpages", INT4OID}, + [RELTUPLES_ARG] = {"reltuples", FLOAT4OID}, + [RELALLVISIBLE_ARG] = {"relallvisible", INT4OID}, + [NUM_RELATION_STATS_ARGS] = {0} +}; + +static struct arginfo attarginfo[] = +{ + [ATTRELATION_ARG] = {"relation", REGCLASSOID}, + [ATTNAME_ARG] = {"attname", NAMEOID}, + [INHERITED_ARG] = {"inherited", BOOLOID}, + [NULL_FRAC_ARG] = {"null_frac", FLOAT4OID}, + [AVG_WIDTH_ARG] = {"avg_width", INT4OID}, + [N_DISTINCT_ARG] = {"n_distinct", FLOAT4OID}, + [MOST_COMMON_VALS_ARG] = {"most_common_vals", TEXTOID}, + [MOST_COMMON_FREQS_ARG] = {"most_common_freqs", FLOAT4ARRAYOID}, + [HISTOGRAM_BOUNDS_ARG] = {"histogram_bounds", TEXTOID}, + [CORRELATION_ARG] = {"correlation", FLOAT4OID}, + [MOST_COMMON_ELEMS_ARG] = {"most_common_elems", TEXTOID}, + [MOST_COMMON_ELEM_FREQS_ARG] = {"most_common_elems_freq", FLOAT4ARRAYOID}, + [ELEM_COUNT_HISTOGRAM_ARG] = {"elem_count_histogram", FLOAT4ARRAYOID}, + [RANGE_LENGTH_HISTOGRAM_ARG] = {"range_length_histogram", TEXTOID}, + [RANGE_EMPTY_FRAC_ARG] = {"range_empty_frac", FLOAT4OID}, + [RANGE_BOUNDS_HISTOGRAM_ARG] = {"range_bounds_histogram", TEXTOID}, + [NUM_ATTRIBUTE_STATS_ARGS] = {0} +}; + +static bool relation_statistics_update(FunctionCallInfo fcinfo, int elevel); +static bool attribute_statistics_update(FunctionCallInfo fcinfo, int elevel); +static Node * get_attr_expr(Relation rel, int attnum); +static void get_attr_stat_type(Oid reloid, AttrNumber attnum, int elevel, + Oid *atttypid, int32 *atttypmod, + char *atttyptype, Oid *atttypcoll, + Oid *eq_opr, Oid *lt_opr); +static bool get_elem_stat_type(Oid atttypid, char atttyptype, int elevel, + Oid *elemtypid, Oid *elem_eq_opr); +static Datum text_to_stavalues(const char *staname, FmgrInfo *array_in, Datum d, + Oid typid, int32 typmod, int elevel, bool *ok); +static void set_stats_slot(Datum *values, bool *nulls, + int16 stakind, Oid staop, Oid stacoll, + Datum stanumbers, bool stanumbers_isnull, + Datum stavalues, bool stavalues_isnull); +static void upsert_pg_statistic(Relation starel, HeapTuple oldtup, + Datum values[], bool nulls[]); +static bool delete_pg_statistic(Oid reloid, AttrNumber attnum, bool stainherit); +static void lock_check_privileges(Oid reloid); +static void check_required_arg(FunctionCallInfo fcinfo, + struct arginfo *arginfo, int argnum); +static void check_arg_array(FunctionCallInfo fcinfo, struct arginfo *arginfo, + int argnum, int elevel); +static void check_arg_pair(FunctionCallInfo fcinfo, struct arginfo *arginfo, + int argnum1, int argnum2, int elevel); + +/* + * Internal function for modifying statistics for a relation. + */ +static bool +relation_statistics_update(FunctionCallInfo fcinfo, int elevel) +{ + Oid reloid; + Relation crel; + HeapTuple ctup; + Form_pg_class pgcform; + int replaces[3] = {0}; + Datum values[3] = {0}; + bool nulls[3] = {false, false, false}; + int ncols = 0; + TupleDesc tupdesc; + HeapTuple newtup; + + + check_required_arg(fcinfo, relarginfo, RELATION_ARG); + reloid = PG_GETARG_OID(RELATION_ARG); + + lock_check_privileges(reloid); + + /* + * Take RowExclusiveLock on pg_class, consistent with + * vac_update_relstats(). + */ + crel = table_open(RelationRelationId, RowExclusiveLock); + + tupdesc = RelationGetDescr(crel); + ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid)); + if (!HeapTupleIsValid(ctup)) + { + ereport(elevel, + (errcode(ERRCODE_OBJECT_IN_USE), + errmsg("pg_class entry for relid %u not found", reloid))); + table_close(crel, RowExclusiveLock); + return false; + } + + pgcform = (Form_pg_class) GETSTRUCT(ctup); + + /* relpages */ + if (!PG_ARGISNULL(RELPAGES_ARG)) + { + int32 relpages = PG_GETARG_INT32(RELPAGES_ARG); + + if (relpages < 0) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relpages cannot be < 0"))); + table_close(crel, RowExclusiveLock); + return false; + } + + if (relpages != pgcform->relpages) + { + replaces[ncols] = Anum_pg_class_relpages; + values[ncols] = Int32GetDatum(relpages); + ncols++; + } + } + + if (!PG_ARGISNULL(RELTUPLES_ARG)) + { + float reltuples = PG_GETARG_FLOAT4(RELTUPLES_ARG); + + if (reltuples < -1.0) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("reltuples cannot be < -1.0"))); + table_close(crel, RowExclusiveLock); + return false; + } + + if (reltuples != pgcform->reltuples) + { + replaces[ncols] = Anum_pg_class_reltuples; + values[ncols] = Float4GetDatum(reltuples); + ncols++; + } + } + + if (!PG_ARGISNULL(RELALLVISIBLE_ARG)) + { + int32 relallvisible = PG_GETARG_INT32(RELALLVISIBLE_ARG); + + if (relallvisible < 0) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relallvisible cannot be < 0"))); + table_close(crel, RowExclusiveLock); + return false; + } + + if (relallvisible != pgcform->relallvisible) + { + replaces[ncols] = Anum_pg_class_relallvisible; + values[ncols] = Int32GetDatum(relallvisible); + ncols++; + } + } + + /* only update pg_class if there is a meaningful change */ + if (ncols == 0) + { + table_close(crel, RowExclusiveLock); + return false; + } + + newtup = heap_modify_tuple_by_cols(ctup, tupdesc, ncols, replaces, values, + nulls); + + CatalogTupleUpdate(crel, &newtup->t_self, newtup); + heap_freetuple(newtup); + + /* release the lock, consistent with vac_update_relstats() */ + table_close(crel, RowExclusiveLock); + + return true; +} + +/* + * Insert or Update Attribute Statistics + * + * Major errors, such as the table not existing, the attribute not existing, + * or a permissions failure are always reported at ERROR. Other errors, such + * as a conversion failure, are reported at 'elevel', and a partial update + * will result. + * + * See pg_statistic.h for an explanation of how each statistic kind is + * stored. Custom statistics kinds are not supported. + * + * Depending on the statistics kind, we need to derive information from the + * attribute for which we're storing the stats. For instance, the MCVs are + * stored as an anyarray, and the representation of the array needs to store + * the correct element type, which must be derived from the attribute. + */ +static bool +attribute_statistics_update(FunctionCallInfo fcinfo, int elevel) +{ + Oid reloid; + Name attname; + bool inherited; + AttrNumber attnum; + + Relation starel; + HeapTuple statup; + + Oid atttypid; + int32 atttypmod; + char atttyptype; + Oid atttypcoll; + Oid eq_opr; + Oid lt_opr; + + Oid elemtypid; + Oid elem_eq_opr; + + FmgrInfo array_in_fn; + + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + + check_required_arg(fcinfo, attarginfo, ATTRELATION_ARG); + reloid = PG_GETARG_OID(ATTRELATION_ARG); + + check_required_arg(fcinfo, attarginfo, ATTNAME_ARG); + attname = PG_GETARG_NAME(ATTNAME_ARG); + attnum = get_attnum(reloid, NameStr(*attname)); + + if (PG_ARGISNULL(INHERITED_ARG)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" cannot be NULL", + attarginfo[INHERITED_ARG].argname))); + PG_RETURN_BOOL(false); + } + inherited = PG_GETARG_BOOL(INHERITED_ARG); + + /* + * Check argument sanity. If some arguments are unusable, emit at elevel + * and set the corresponding argument to NULL in fcinfo. + * + * NB: may modify fcinfo + */ + + check_arg_array(fcinfo, attarginfo, MOST_COMMON_FREQS_ARG, elevel); + check_arg_array(fcinfo, attarginfo, MOST_COMMON_ELEM_FREQS_ARG, elevel); + check_arg_array(fcinfo, attarginfo, ELEM_COUNT_HISTOGRAM_ARG, elevel); + + /* STATISTIC_KIND_MCV */ + check_arg_pair(fcinfo, attarginfo, + MOST_COMMON_VALS_ARG, MOST_COMMON_FREQS_ARG, + elevel); + + /* STATISTIC_KIND_MCELEM */ + check_arg_pair(fcinfo, attarginfo, + MOST_COMMON_ELEMS_ARG, MOST_COMMON_ELEM_FREQS_ARG, + elevel); + + /* STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM */ + check_arg_pair(fcinfo, attarginfo, + RANGE_LENGTH_HISTOGRAM_ARG, RANGE_EMPTY_FRAC_ARG, + elevel); + + lock_check_privileges(reloid); + + /* derive information from attribute */ + get_attr_stat_type(reloid, attnum, elevel, + &atttypid, &atttypmod, + &atttyptype, &atttypcoll, + &eq_opr, <_opr); + + /* if needed, derive element type */ + if (!PG_ARGISNULL(MOST_COMMON_ELEMS_ARG) || + !PG_ARGISNULL(ELEM_COUNT_HISTOGRAM_ARG)) + { + if (!get_elem_stat_type(atttypid, atttyptype, elevel, + &elemtypid, &elem_eq_opr)) + { + ereport(elevel, + (errmsg("unable to determine element type of attribute \"%s\"", NameStr(*attname)), + errdetail("Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST."))); + elemtypid = InvalidOid; + elem_eq_opr = InvalidOid; + fcinfo->args[MOST_COMMON_ELEMS_ARG].isnull = true; + fcinfo->args[ELEM_COUNT_HISTOGRAM_ARG].isnull = true; + } + } + + /* histogram and correlation require less-than operator */ + if ((!PG_ARGISNULL(HISTOGRAM_BOUNDS_ARG) || !PG_ARGISNULL(CORRELATION_ARG)) && + !OidIsValid(lt_opr)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not determine less-than operator for attribute \"%s\"", NameStr(*attname)), + errdetail("Cannot set STATISTIC_KIND_HISTOGRAM or STATISTIC_KIND_CORRELATION."))); + fcinfo->args[HISTOGRAM_BOUNDS_ARG].isnull = true; + fcinfo->args[CORRELATION_ARG].isnull = true; + } + + /* only range types can have range stats */ + if ((!PG_ARGISNULL(RANGE_LENGTH_HISTOGRAM_ARG) || !PG_ARGISNULL(RANGE_BOUNDS_HISTOGRAM_ARG)) && + !(atttyptype == TYPTYPE_RANGE || atttyptype == TYPTYPE_MULTIRANGE)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("attribute \"%s\" is not a range type", NameStr(*attname)), + errdetail("Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM."))); + + fcinfo->args[RANGE_LENGTH_HISTOGRAM_ARG].isnull = true; + fcinfo->args[RANGE_EMPTY_FRAC_ARG].isnull = true; + } + + fmgr_info(F_ARRAY_IN, &array_in_fn); + + starel = table_open(StatisticRelationId, RowExclusiveLock); + + statup = SearchSysCache3(STATRELATTINH, reloid, attnum, inherited); + if (HeapTupleIsValid(statup)) + { + /* initialize from existing tuple */ + heap_deform_tuple(statup, RelationGetDescr(starel), values, nulls); + } + else + { + /* + * Initialize nulls array to be false for all non-NULL attributes, and + * true for all nullable attributes. + */ + for (int i = 0; i < Natts_pg_statistic; i++) + { + values[i] = (Datum) 0; + if (i < Anum_pg_statistic_stanumbers1 - 1) + nulls[i] = false; + else + nulls[i] = true; + } + + values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(reloid); + values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(attnum); + values[Anum_pg_statistic_stainherit - 1] = BoolGetDatum(inherited); + values[Anum_pg_statistic_stanullfrac - 1] = DEFAULT_NULL_FRAC; + values[Anum_pg_statistic_stawidth - 1] = DEFAULT_AVG_WIDTH; + values[Anum_pg_statistic_stadistinct - 1] = DEFAULT_N_DISTINCT; + } + + if (!PG_ARGISNULL(NULL_FRAC_ARG)) + values[Anum_pg_statistic_stanullfrac - 1] = PG_GETARG_DATUM(NULL_FRAC_ARG); + if (!PG_ARGISNULL(AVG_WIDTH_ARG)) + values[Anum_pg_statistic_stawidth - 1] = PG_GETARG_DATUM(AVG_WIDTH_ARG); + if (!PG_ARGISNULL(N_DISTINCT_ARG)) + values[Anum_pg_statistic_stadistinct - 1] = PG_GETARG_DATUM(N_DISTINCT_ARG); + + /* + * STATISTIC_KIND_MCV + * + * Convert most_common_vals from text to anyarray, where the element type + * is the attribute type, and store in stavalues. Store most_common_freqs + * in stanumbers. + */ + if (!PG_ARGISNULL(MOST_COMMON_VALS_ARG)) + { + bool converted; + Datum stanumbers = PG_GETARG_DATUM(MOST_COMMON_FREQS_ARG); + Datum stavalues = text_to_stavalues("most_common_vals", + &array_in_fn, + PG_GETARG_DATUM(MOST_COMMON_VALS_ARG), + atttypid, atttypmod, + elevel, &converted); + + if (converted) + { + set_stats_slot(values, nulls, + STATISTIC_KIND_MCV, + eq_opr, atttypcoll, + stanumbers, false, stavalues, false); + } + else + { + fcinfo->args[MOST_COMMON_VALS_ARG].isnull = true; + fcinfo->args[MOST_COMMON_FREQS_ARG].isnull = true; + } + } + + /* + * STATISTIC_KIND_HISTOGRAM + * + * histogram_bounds: ANYARRAY::text + */ + if (!PG_ARGISNULL(HISTOGRAM_BOUNDS_ARG)) + { + Datum stavalues; + bool converted = false; + + stavalues = text_to_stavalues("histogram_bounds", + &array_in_fn, + PG_GETARG_DATUM(HISTOGRAM_BOUNDS_ARG), + atttypid, atttypmod, elevel, + &converted); + + if (converted) + { + set_stats_slot(values, nulls, + STATISTIC_KIND_HISTOGRAM, + lt_opr, atttypcoll, + 0, true, stavalues, false); + } + else + fcinfo->args[HISTOGRAM_BOUNDS_ARG].isnull = true; + } + + /* + * STATISTIC_KIND_CORRELATION + * + * correlation: real + */ + if (!PG_ARGISNULL(CORRELATION_ARG)) + { + Datum elems[] = {PG_GETARG_DATUM(CORRELATION_ARG)}; + ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID); + Datum stanumbers = PointerGetDatum(arry); + + set_stats_slot(values, nulls, + STATISTIC_KIND_CORRELATION, + lt_opr, atttypcoll, + stanumbers, false, 0, true); + } + + /* + * STATISTIC_KIND_MCELEM + * + * most_common_elem_freqs: real[] + * + * most_common_elems : ANYARRAY::text + */ + if (!PG_ARGISNULL(MOST_COMMON_ELEMS_ARG)) + { + Datum stanumbers = PG_GETARG_DATUM(MOST_COMMON_ELEM_FREQS_ARG); + bool converted = false; + Datum stavalues; + + stavalues = text_to_stavalues("most_common_elems", + &array_in_fn, + PG_GETARG_DATUM(MOST_COMMON_ELEMS_ARG), + elemtypid, atttypmod, + elevel, &converted); + + if (converted) + { + set_stats_slot(values, nulls, + STATISTIC_KIND_MCELEM, + elem_eq_opr, atttypcoll, + stanumbers, false, stavalues, false); + } + else + { + /* the mc_elem stat did not write */ + fcinfo->args[MOST_COMMON_ELEMS_ARG].isnull = true; + fcinfo->args[MOST_COMMON_ELEM_FREQS_ARG].isnull = true; + } + } + + /* + * STATISTIC_KIND_DECHIST + * + * elem_count_histogram: real[] + */ + if (!PG_ARGISNULL(ELEM_COUNT_HISTOGRAM_ARG)) + { + Datum stanumbers = PG_GETARG_DATUM(ELEM_COUNT_HISTOGRAM_ARG); + + set_stats_slot(values, nulls, + STATISTIC_KIND_DECHIST, + elem_eq_opr, atttypcoll, + stanumbers, false, 0, true); + } + + /* + * STATISTIC_KIND_BOUNDS_HISTOGRAM + * + * range_bounds_histogram: ANYARRAY::text + * + * 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(RANGE_BOUNDS_HISTOGRAM_ARG)) + { + bool converted = false; + Datum stavalues; + + stavalues = text_to_stavalues("range_bounds_histogram", + &array_in_fn, + PG_GETARG_DATUM(RANGE_BOUNDS_HISTOGRAM_ARG), + atttypid, atttypmod, + elevel, &converted); + + if (converted) + { + set_stats_slot(values, nulls, + STATISTIC_KIND_BOUNDS_HISTOGRAM, + InvalidOid, InvalidOid, + 0, true, stavalues, false); + } + else + fcinfo->args[RANGE_BOUNDS_HISTOGRAM_ARG].isnull = true; + } + + /* + * STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM + * + * range_empty_frac: real + * + * range_length_histogram: double precision[]::text + */ + if (!PG_ARGISNULL(RANGE_LENGTH_HISTOGRAM_ARG)) + { + /* The anyarray is always a float8[] for this stakind */ + Datum elems[] = {PG_GETARG_DATUM(RANGE_EMPTY_FRAC_ARG)}; + ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID); + Datum stanumbers = PointerGetDatum(arry); + + bool converted = false; + Datum stavalues; + + stavalues = text_to_stavalues("range_length_histogram", + &array_in_fn, + PG_GETARG_DATUM(RANGE_LENGTH_HISTOGRAM_ARG), + FLOAT8OID, 0, elevel, &converted); + + if (converted) + { + set_stats_slot(values, nulls, + STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM, + Float8LessOperator, InvalidOid, + stanumbers, false, stavalues, false); + } + else + { + fcinfo->args[RANGE_EMPTY_FRAC_ARG].isnull = true; + fcinfo->args[RANGE_LENGTH_HISTOGRAM_ARG].isnull = true; + } + } + + upsert_pg_statistic(starel, statup, values, nulls); + + if (HeapTupleIsValid(statup)) + ReleaseSysCache(statup); + table_close(starel, RowExclusiveLock); + + return true; +} + +/* + * 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. + */ +static Node * +get_attr_expr(Relation rel, int attnum) +{ + if ((rel->rd_rel->relkind == RELKIND_INDEX + || (rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)) + && (rel->rd_indexprs != NIL) + && (rel->rd_index->indkey.values[attnum - 1] == 0)) + { + ListCell *indexpr_item = list_head(rel->rd_indexprs); + + for (int i = 0; i < 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"); + + return (Node *) lfirst(indexpr_item); + } + return NULL; +} + +/* + * Derive type information from the attribute. + */ +static void +get_attr_stat_type(Oid reloid, AttrNumber attnum, int elevel, + Oid *atttypid, int32 *atttypmod, + char *atttyptype, Oid *atttypcoll, + Oid *eq_opr, Oid *lt_opr) +{ + Relation rel = relation_open(reloid, AccessShareLock); + Form_pg_attribute attr; + HeapTuple atup; + Node *expr; + TypeCacheEntry *typcache; + + atup = SearchSysCache2(ATTNUM, ObjectIdGetDatum(reloid), + Int16GetDatum(attnum)); + + /* Attribute not found */ + if (!HeapTupleIsValid(atup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("attribute %d of relation with OID %u does not exist", + attnum, reloid))); + + attr = (Form_pg_attribute) GETSTRUCT(atup); + + if (attr->attisdropped) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("attribute %d of relation with OID %u does not exist", + attnum, RelationGetRelid(rel)))); + + expr = get_attr_expr(rel, attr->attnum); + + if (expr == NULL) + { + *atttypid = attr->atttypid; + *atttypmod = attr->atttypmod; + *atttypcoll = attr->attcollation; + } + else + { + *atttypid = exprType(expr); + *atttypmod = exprTypmod(expr); + + /* TODO: better explanation */ + /* + * 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)) + *atttypcoll = attr->attcollation; + else + *atttypcoll = exprCollation(expr); + } + ReleaseSysCache(atup); + + /* TODO: better explanation */ + /* if it's a multirange, step down to the range type */ + if (type_is_multirange(*atttypid)) + *atttypid = get_multirange_range(*atttypid); + + typcache = lookup_type_cache(*atttypid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR); + *atttyptype = typcache->typtype; + *eq_opr = typcache->eq_opr; + *lt_opr = typcache->lt_opr; + + /* TODO: explain special case for tsvector */ + if (*atttypid == TSVECTOROID) + *atttypcoll = DEFAULT_COLLATION_OID; + + relation_close(rel, NoLock); +} + +/* + * Derive element type information from the attribute type. + */ +static bool +get_elem_stat_type(Oid atttypid, char atttyptype, int elevel, + Oid *elemtypid, Oid *elem_eq_opr) +{ + TypeCacheEntry *elemtypcache; + + /* TODO: explain special case for tsvector */ + if (atttypid == TSVECTOROID) + *elemtypid = TEXTOID; + else if (atttyptype == TYPTYPE_RANGE) + *elemtypid = get_range_subtype(atttypid); + else + *elemtypid = get_base_element_type(atttypid); + + if (!OidIsValid(*elemtypid)) + return false; + + elemtypcache = lookup_type_cache(*elemtypid, TYPECACHE_EQ_OPR); + if (!OidIsValid(elemtypcache->eq_opr)) + return false; + + *elem_eq_opr = elemtypcache->eq_opr; + + return true; +} + +/* + * Cast a text datum into an array with element type elemtypid. + * + * If an error is encountered, capture it and re-throw at elevel, and set ok + * to false. If the resulting array contains NULLs, raise an error at elevel + * and set ok to false. Otherwise, set ok to true. + */ +static Datum +text_to_stavalues(const char *staname, FmgrInfo *array_in, Datum d, Oid typid, + int32 typmod, int elevel, bool *ok) +{ + LOCAL_FCINFO(fcinfo, 8); + char *s; + Datum result; + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + escontext.details_wanted = true; + + s = TextDatumGetCString(d); + + InitFunctionCallInfoData(*fcinfo, array_in, 3, InvalidOid, + (Node *) &escontext, NULL); + + fcinfo->args[0].value = CStringGetDatum(s); + fcinfo->args[0].isnull = false; + fcinfo->args[1].value = ObjectIdGetDatum(typid); + fcinfo->args[1].isnull = false; + fcinfo->args[2].value = Int32GetDatum(typmod); + fcinfo->args[2].isnull = false; + + result = FunctionCallInvoke(fcinfo); + + pfree(s); + + if (SOFT_ERROR_OCCURRED(&escontext)) + { + if (elevel != ERROR) + escontext.error_data->elevel = elevel; + ThrowErrorData(escontext.error_data); + *ok = false; + return (Datum)0; + } + + if (array_contains_nulls(DatumGetArrayTypeP(result))) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" array cannot contain NULL values", staname))); + *ok = false; + return (Datum)0; + } + + *ok = true; + + return result; +} + +/* + * Find and update the slot with the given stakind, or use the first empty + * slot. + */ +static void +set_stats_slot(Datum *values, bool *nulls, + int16 stakind, Oid staop, Oid stacoll, + Datum stanumbers, bool stanumbers_isnull, + Datum stavalues, bool stavalues_isnull) +{ + int slotidx; + int first_empty = -1; + + /* find existing slot with given stakind */ + for (slotidx = 0; slotidx < STATISTIC_NUM_SLOTS; slotidx++) + { + AttrNumber stakind_attnum = Anum_pg_statistic_stakind1 - 1 + slotidx; + if (first_empty < 0 && + DatumGetInt16(values[stakind_attnum]) == 0) + first_empty = slotidx; + if (DatumGetInt16(values[stakind_attnum]) == stakind) + break; + } + + if (slotidx >= STATISTIC_NUM_SLOTS && first_empty >= 0) + slotidx = first_empty; + + if (slotidx >= STATISTIC_NUM_SLOTS) + ereport(ERROR, + (errmsg("maximum number of statistics slots exceeded: %d", + slotidx + 1))); + + values[Anum_pg_statistic_stakind1 - 1 + slotidx] = stakind; + values[Anum_pg_statistic_staop1 - 1 + slotidx] = staop; + values[Anum_pg_statistic_stacoll1 - 1 + slotidx] = stacoll; + + if (!stanumbers_isnull) + { + values[Anum_pg_statistic_stanumbers1 - 1 + slotidx] = stanumbers; + nulls[Anum_pg_statistic_stanumbers1 - 1 + slotidx] = false; + } + if (!stavalues_isnull) + { + values[Anum_pg_statistic_stavalues1 - 1 + slotidx] = stavalues; + nulls[Anum_pg_statistic_stavalues1 - 1 + slotidx] = false; + } +} + +/* + * Upsert the pg_statistic record. + */ +static void +upsert_pg_statistic(Relation starel, HeapTuple oldtup, + Datum values[], bool nulls[]) +{ + HeapTuple newtup; + + if (HeapTupleIsValid(oldtup)) + { + bool replaces[Natts_pg_statistic]; + + for (int i = 0; i < Natts_pg_statistic; i++) + replaces[i] = true; + + newtup = heap_modify_tuple(oldtup, RelationGetDescr(starel), + values, nulls, replaces); + CatalogTupleUpdate(starel, &newtup->t_self, newtup); + } + else + { + newtup = heap_form_tuple(RelationGetDescr(starel), values, nulls); + CatalogTupleInsert(starel, newtup); + } + + heap_freetuple(newtup); +} + +/* + * Delete pg_statistic record. + */ +static bool +delete_pg_statistic(Oid reloid, AttrNumber attnum, bool stainherit) +{ + Relation sd = table_open(StatisticRelationId, RowExclusiveLock); + HeapTuple oldtup; + + /* Is there already a pg_statistic tuple for this attribute? */ + oldtup = SearchSysCache3(STATRELATTINH, + ObjectIdGetDatum(reloid), + Int16GetDatum(attnum), + BoolGetDatum(stainherit)); + + if (HeapTupleIsValid(oldtup)) + { + CatalogTupleDelete(sd, &oldtup->t_self); + ReleaseSysCache(oldtup); + table_close(sd, RowExclusiveLock); + return true; + } + + table_close(sd, RowExclusiveLock); + return false; +} + +/* + * Lock relation in ShareUpdateExclusive mode, check privileges, and close the + * relation (but retain the lock). + * + * A role has privileges to set statistics on 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 void +lock_check_privileges(Oid reloid) +{ + Relation rel = relation_open(reloid, ShareUpdateExclusiveLock); + + if (rel->rd_rel->relisshared) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot modify statistics for shared relation"))); + + if (!object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId())) + { + AclResult aclresult = pg_class_aclcheck(RelationGetRelid(rel), + GetUserId(), + ACL_MAINTAIN); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, + get_relkind_objtype(rel->rd_rel->relkind), + NameStr(rel->rd_rel->relname)); + } + + relation_close(rel, NoLock); +} + +static bool +check_arg_type(const char *argname, Oid argtype, Oid expectedtype, int elevel) +{ + if (argtype != expectedtype) + { + ereport(elevel, + (errmsg("argument \"%s\" has type \"%s\", expected type \"%s\"", + argname, format_type_be(argtype), + format_type_be(expectedtype)))); + return false; + } + + return true; +} + +static void +check_required_arg(FunctionCallInfo fcinfo, struct arginfo *arginfo, + int argnum) +{ + if (PG_ARGISNULL(argnum)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" cannot be NULL", + arginfo[argnum].argname))); +} + +/* + * Check that array argument is one dimensional with no NULLs. + * + * If not, emit at elevel, and set argument to NULL in fcinfo. + */ +static void +check_arg_array(FunctionCallInfo fcinfo, struct arginfo *arginfo, + int argnum, int elevel) +{ + ArrayType *arr; + + if (PG_ARGISNULL(argnum)) + return; + + arr = DatumGetArrayTypeP(PG_GETARG_DATUM(argnum)); + + if (ARR_NDIM(arr) != 1) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" cannot be a multidimensional array", + arginfo[argnum].argname))); + fcinfo->args[argnum].isnull = true; + } + + if (array_contains_nulls(arr)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" array cannot contain NULL values", + arginfo[argnum].argname))); + fcinfo->args[argnum].isnull = true; + } +} + +/* + * Enforce parameter pairs that must be specified together for a particular + * stakind, such as most_common_vals and most_common_freqs for + * STATISTIC_KIND_MCV. If one is NULL and the other is not, emit at elevel, + * and ignore the stakind by setting both to NULL in fcinfo. + */ +static void +check_arg_pair(FunctionCallInfo fcinfo, struct arginfo *arginfo, + int argnum1, int argnum2, int elevel) +{ + if (PG_ARGISNULL(argnum1) && !PG_ARGISNULL(argnum2)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" must be specified when \"%s\" is specified", + arginfo[argnum1].argname, + arginfo[argnum2].argname))); + fcinfo->args[argnum2].isnull = true; + } + if (!PG_ARGISNULL(argnum1) && PG_ARGISNULL(argnum2)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("\"%s\" must be specified when \"%s\" is specified", + arginfo[argnum2].argname, + arginfo[argnum1].argname))); + fcinfo->args[argnum1].isnull = true; + } +} + +/* + * Set statistics for a given pg_class entry. + */ +Datum +pg_set_relation_stats(PG_FUNCTION_ARGS) +{ + PG_RETURN_BOOL(relation_statistics_update(fcinfo, ERROR)); +} + +/* + * Find the argument number for the given argument name, returning -1 if not + * found. + */ +static int +get_argnum(const char *argname, struct arginfo *arginfo, int elevel) +{ + int argnum; + + for (argnum = 0; arginfo[argnum].argname != NULL; argnum++) + if (strcasecmp(argname, arginfo[argnum].argname) == 0) + return argnum; + + ereport(elevel, + (errmsg("unrecognized argument name: \"%s\"", argname))); + + return -1; +} + +/* + * Translate variadic argument pairs from 'pairs_fcinfo' into a + * 'positional_fcinfo' appropriate for calling relation_statistics_update() or + * attribute_statistics_update() with positional arguments. + * + * Caller should have already initialized positional_fcinfo with a size + * appropriate for calling the intended positional function, and arginfo + * should also match the intended positional function. + */ +static void +fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo, + FunctionCallInfo positional_fcinfo, + struct arginfo *arginfo, + int elevel) +{ + Datum *args; + bool *argnulls; + Oid *types; + int nargs; + + for (int i = 0; arginfo[i].argname != NULL; i++) + positional_fcinfo->args[i].isnull = true; + + nargs = extract_variadic_args(pairs_fcinfo, 0, true, + &args, &types, &argnulls); + + if (nargs % 2 != 0) + ereport(ERROR, + errmsg("need even number of variable arguments")); + + for (int i = 0; i < nargs; i+=2) + { + int argnum; + char *argname; + + if (argnulls[i] || types[i] != TEXTOID) + ereport(ERROR, (errmsg("need text argument names"))); + + if (argnulls[i+1]) + continue; + + argname = TextDatumGetCString(args[i]); + + /* + * Version is not a valid positional argument. In the future, it can + * be used to interpret older statistics properly, but it is ignored + * for now. + */ + if (strcasecmp(argname, "version") == 0) + continue; + + argnum = get_argnum(argname, arginfo, elevel); + + if (argnum >= 0 && + check_arg_type(argname, types[i+1], arginfo[argnum].argtype, + elevel)) + { + positional_fcinfo->args[argnum].value = args[i+1]; + positional_fcinfo->args[argnum].isnull = false; + } + } +} + +/* + * Clear statistics for a given pg_class entry; that is, set back to initial + * stats for a newly-created table. + */ +Datum +pg_clear_relation_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(newfcinfo, 4); + + InitFunctionCallInfoData(*newfcinfo, NULL, 4, InvalidOid, NULL, NULL); + + newfcinfo->args[0].value = PG_GETARG_OID(0); + newfcinfo->args[0].isnull = PG_ARGISNULL(0); + newfcinfo->args[1].value = DEFAULT_RELPAGES; + newfcinfo->args[1].isnull = false; + newfcinfo->args[2].value = DEFAULT_RELTUPLES; + newfcinfo->args[2].isnull = false; + newfcinfo->args[3].value = DEFAULT_RELALLVISIBLE; + newfcinfo->args[3].isnull = false; + + PG_RETURN_BOOL(relation_statistics_update(newfcinfo, ERROR)); +} + +Datum +pg_restore_relation_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(positional_fcinfo, NUM_RELATION_STATS_ARGS); + + InitFunctionCallInfoData(*positional_fcinfo, NULL, + NUM_RELATION_STATS_ARGS, + InvalidOid, NULL, NULL); + + fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo, relarginfo, + WARNING); + PG_RETURN_BOOL(relation_statistics_update(positional_fcinfo, WARNING)); +} + +/* + * Import statistics for a given relation attribute. + * + * This will insert/replace a row in pg_statistic for the given relation and + * attribute name. + * + * The function takes input parameters that correspond to columns in the view + * pg_stats. + * + * Of those, the columns attname, inherited, null_frac, avg_width, and + * n_distinct all correspond to NOT NULL columns in pg_statistic. These + * parameters have no default value and passing NULL to them will result + * in an error. + * + * If there is no attribute with a matching attname in the relation, the + * function will raise an error. Likewise for setting inherited statistics + * on a table that is not partitioned. + * + * The remaining parameters all belong to a specific stakind. Some stakinds + * have multiple parameters, and in those cases both parameters must be + * NOT NULL or both NULL, otherwise an error will be raised. + * + * Omitting a parameter or explicitly passing NULL means that that particular + * stakind is not associated with the attribute. + * + * Parameters that are NOT NULL will be inspected for consistency checks, + * any of which can raise an error. + * + * 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. Any error generated by the array_in() function will + * in turn fail the function. + */ +Datum +pg_set_attribute_stats(PG_FUNCTION_ARGS) +{ + PG_RETURN_BOOL(attribute_statistics_update(fcinfo, ERROR)); +} + +/* + * Delete statistics for the given attribute. + */ +Datum +pg_clear_attribute_stats(PG_FUNCTION_ARGS) +{ + Oid reloid; + Name attname; + AttrNumber attnum; + bool inherited; + + check_required_arg(fcinfo, attarginfo, ATTRELATION_ARG); + reloid = PG_GETARG_OID(ATTRELATION_ARG); + + check_required_arg(fcinfo, attarginfo, ATTNAME_ARG); + attname = PG_GETARG_NAME(ATTNAME_ARG); + attnum = get_attnum(reloid, NameStr(*attname)); + + check_required_arg(fcinfo, attarginfo, INHERITED_ARG); + inherited = PG_GETARG_BOOL(INHERITED_ARG); + + lock_check_privileges(reloid); + + PG_RETURN_BOOL(delete_pg_statistic(reloid, attnum, inherited)); +} + +Datum +pg_restore_attribute_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(positional_fcinfo, NUM_ATTRIBUTE_STATS_ARGS); + + InitFunctionCallInfoData(*positional_fcinfo, NULL, NUM_ATTRIBUTE_STATS_ARGS, + InvalidOid, NULL, NULL); + + fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo, attarginfo, WARNING); + PG_RETURN_BOOL(attribute_statistics_update(positional_fcinfo, WARNING)); +} diff --git a/src/backend/statistics/meson.build b/src/backend/statistics/meson.build index 73b29a3d50a..849df3bf323 100644 --- a/src/backend/statistics/meson.build +++ b/src/backend/statistics/meson.build @@ -3,6 +3,7 @@ backend_sources += files( 'dependencies.c', 'extended_stats.c', + 'import_stats.c', 'mcv.c', 'mvdistinct.c', ) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 4abc6d95262..80e224beb18 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12255,4 +12255,52 @@ proargnames => '{summarized_tli,summarized_lsn,pending_lsn,summarizer_pid}', prosrc => 'pg_get_wal_summarizer_state' }, +# Statistics Import +{ oid => '8048', + descr => 'set statistics on relation', + proname => 'pg_set_relation_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'regclass int4 float4 int4', + proargnames => '{relation,relpages,reltuples,relallvisible}', + prosrc => 'pg_set_relation_stats' }, +{ oid => '8049', + descr => 'clear statistics on relation', + proname => 'pg_clear_relation_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'regclass', + proargnames => '{relation}', + prosrc => 'pg_clear_relation_stats' }, +{ oid => '8050', + descr => 'restore statistics on relation', + proname => 'pg_restore_relation_stats', provolatile => 'v', proisstrict => 'f', + provariadic => 'any', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'any', + proargnames => '{relation}', + proargmodes => '{v}', + prosrc => 'pg_restore_relation_stats' }, +{ oid => '8051', + descr => 'set statistics on attribute', + proname => 'pg_set_attribute_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'regclass 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' }, +{ oid => '8052', + descr => 'clear statistics on attribute', + proname => 'pg_clear_attribute_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'regclass name bool', + proargnames => '{relation}', + prosrc => 'pg_clear_attribute_stats' }, +{ oid => '8053', + descr => 'restore statistics on attribute', + proname => 'pg_restore_attribute_stats', provolatile => 'v', proisstrict => 'f', + provariadic => 'any', + proparallel => 'u', prorettype => 'bool', + proargtypes => 'any', + proargnames => '{relation}', + proargmodes => '{v}', + prosrc => 'pg_restore_attribute_stats' }, + ] diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h index 7f2bf18716d..73d3b541dda 100644 --- a/src/include/statistics/statistics.h +++ b/src/include/statistics/statistics.h @@ -127,4 +127,7 @@ 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/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out new file mode 100644 index 00000000000..5edfb7451b5 --- /dev/null +++ b/src/test/regress/expected/stats_import.out @@ -0,0 +1,766 @@ +CREATE SCHEMA stats_import; +CREATE TYPE stats_import.complex_type AS ( + a integer, + b real, + c text, + d date, + e jsonb); +CREATE TABLE stats_import.test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import.complex_type, + arange int4range, + tags text[] +); +-- starting stats +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 0 | -1 | 0 +(1 row) + +-- error: regclass not found +SELECT + pg_catalog.pg_set_relation_stats( + relation => 0::Oid, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); +ERROR: could not open relation with OID 0 +-- relpages default +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => NULL::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + pg_set_relation_stats +----------------------- + t +(1 row) + +-- reltuples default +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => NULL::real, + relallvisible => 4::integer); + pg_set_relation_stats +----------------------- + t +(1 row) + +-- relallvisible default +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => NULL::integer); + pg_set_relation_stats +----------------------- + f +(1 row) + +-- named arguments +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + pg_set_relation_stats +----------------------- + f +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 17 | 400 | 4 +(1 row) + +-- positional arguments +SELECT + pg_catalog.pg_set_relation_stats( + 'stats_import.test'::regclass, + 18::integer, + 401.0::real, + 5::integer); + pg_set_relation_stats +----------------------- + t +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 18 | 401 | 5 +(1 row) + +-- error: object doesn't exist +SELECT pg_catalog.pg_set_attribute_stats( + relation => '0'::oid, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); +ERROR: could not open relation with OID 0 +-- error: relation null +SELECT pg_catalog.pg_set_attribute_stats( + relation => NULL::oid, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); +ERROR: "relation" cannot be NULL +-- error: attname null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => NULL::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); +ERROR: "attname" cannot be NULL +-- error: inherited null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => NULL::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); +ERROR: "inherited" cannot be NULL +-- error: null_frac null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => NULL::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + pg_set_attribute_stats +------------------------ + t +(1 row) + +-- error: avg_width null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => NULL::integer, + n_distinct => 0.3::real); + pg_set_attribute_stats +------------------------ + t +(1 row) + +-- error: avg_width null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => NULL::real); + pg_set_attribute_stats +------------------------ + t +(1 row) + +-- ok: no stakinds +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + pg_set_attribute_stats +------------------------ + t +(1 row) + +SELECT stanullfrac, stawidth, stadistinct +FROM pg_statistic +WHERE starelid = 'stats_import.test'::regclass; + stanullfrac | stawidth | stadistinct +-------------+----------+------------- + 0.1 | 2 | 0.3 +(1 row) + +-- warn: mcv / mcf null mismatch +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_freqs => '{0.1,0.2,0.3}'::real[] + ); +ERROR: "most_common_vals" must be specified when "most_common_freqs" is specified +-- warn: mcv / mcf null mismatch part 2 +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_vals => '{1,2,3}'::text + ); +ERROR: "most_common_freqs" must be specified when "most_common_vals" is specified +-- warn: mcv / mcf type mismatch +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_vals => '{2023-09-30,2024-10-31,3}'::text, + most_common_freqs => '{0.2,0.1}'::real[] + ); +ERROR: invalid input syntax for type integer: "2023-09-30" +-- warning: mcv cast failure +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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[] + ); +ERROR: invalid input syntax for type integer: "four" +-- ok: mcv+mcf +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +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_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | +(1 row) + +-- warn: histogram elements null value +-- this generates no warnings, but perhaps it should +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + histogram_bounds => '{1,NULL,3,4}'::text + ); +ERROR: "histogram_bounds" array cannot contain NULL values +-- ok: histogram_bounds +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + histogram_bounds => '{1,2,3,4}'::text + ); + pg_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +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_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | +(1 row) + +-- ok: correlation +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + correlation => 0.5::real); + pg_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +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_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | 0.5 | | | | | | +(1 row) + +-- warn: scalars can't have mcelem +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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[] + ); +ERROR: unable to determine element type of attribute "id" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. +-- warn: mcelem / mcelem mismatch +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_elems => '{one,two}'::text + ); +ERROR: "most_common_elems_freq" must be specified when "most_common_elems" is specified +-- warn: mcelem / mcelem null mismatch part 2 +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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[] + ); +ERROR: "most_common_elems" must be specified when "most_common_elems_freq" is specified +-- ok: mcelem +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + 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_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | | | | +(1 row) + +-- warn: scalars can't have elem_count_histogram +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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[] + ); +ERROR: unable to determine element type of attribute "id" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. +-- warn: elem_count_histogram null element +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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[] + ); +ERROR: "elem_count_histogram" array cannot contain NULL values +-- ok: elem_count_histogram +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + 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_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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) + +-- warn: scalars can't have range stats +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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 + ); +ERROR: attribute "id" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. +-- warn: range_empty_frac range_length_hist null mismatch +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + range_length_histogram => '{399,499,Infinity}'::text + ); +ERROR: "range_empty_frac" must be specified when "range_length_histogram" is specified +-- warn: range_empty_frac range_length_hist null mismatch part 2 +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + range_empty_frac => 0.5::real + ); +ERROR: "range_length_histogram" must be specified when "range_empty_frac" is specified +-- ok: range_empty_frac + range_length_hist +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + 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_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + 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_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_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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 + ); +ERROR: attribute "id" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. +-- ok: range_bounds_histogram +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + 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_set_attribute_stats +------------------------ + t +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + 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_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} +(1 row) + +-- warn: exceed STATISTIC_NUM_SLOTS +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_vals => '{"[2,3)","[1,2)","[3,4)"}'::text, + most_common_freqs => '{0.3,0.25,0.05}'::real[], + histogram_bounds => '{"[1,2)","[2,3)","[3,4)","[4,5)"}'::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 + ); +ERROR: maximum number of statistics slots exceeded: 6 +-- +-- Test the ability to exactly copy data from one table to an identical table, +-- correctly reconstructing the stakind order as well as the staopN and +-- stacollN values. Because oids are not stable across databases, we can only +-- test this when the source and destination are on the same database +-- instance. For that reason, we borrow and adapt a query found in fe_utils +-- and used by pg_dump/pg_upgrade. +-- +INSERT INTO stats_import.test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_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_import.complex_type, int4range(1,4), array['blue','yellow'] +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_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_import.test(((comp).a % 2 = 1)); +-- Generate statistics on table with data +ANALYZE stats_import.test; +CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ); +CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); +-- +-- Copy stats from test to test_clone, and is_odd to is_odd_clone +-- +SELECT s.schemaname, s.tablename, s.attname, s.inherited +FROM pg_catalog.pg_stats AS s +CROSS JOIN LATERAL + pg_catalog.pg_set_attribute_stats( + relation => ('stats_import.' || s.tablename || '_clone')::regclass::oid, + attname => s.attname, + inherited => s.inherited, + null_frac => s.null_frac, + avg_width => s.avg_width, + n_distinct => s.n_distinct, + most_common_vals => s.most_common_vals::text, + most_common_freqs => s.most_common_freqs, + histogram_bounds => s.histogram_bounds::text, + correlation => s.correlation, + most_common_elems => s.most_common_elems::text, + most_common_elem_freqs => s.most_common_elem_freqs, + elem_count_histogram => s.elem_count_histogram, + range_bounds_histogram => s.range_bounds_histogram::text, + range_empty_frac => s.range_empty_frac, + range_length_histogram => s.range_length_histogram::text) AS r +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test', 'is_odd') +ORDER BY s.tablename, s.attname, s.inherited; + schemaname | tablename | attname | inherited +--------------+-----------+---------+----------- + stats_import | is_odd | expr | f + stats_import | test | arange | f + stats_import | test | comp | f + stats_import | test | id | f + stats_import | test | name | f + stats_import | test | tags | f +(6 rows) + +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_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_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_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_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_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_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_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_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_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_import CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to type stats_import.complex_type +drop cascades to table stats_import.test +drop cascades to table stats_import.test_clone diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 2429ec2bbaa..85fc85bfa03 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -28,7 +28,7 @@ test: strings md5 numerology point lseg line box path polygon circle date time t # geometry depends on point, lseg, line, box, path, polygon, circle # horology depends on date, time, timetz, timestamp, timestamptz, interval # ---------- -test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc database +test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc database stats_import # ---------- # Load huge amounts of data diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql new file mode 100644 index 00000000000..0582c27146a --- /dev/null +++ b/src/test/regress/sql/stats_import.sql @@ -0,0 +1,623 @@ +CREATE SCHEMA stats_import; + +CREATE TYPE stats_import.complex_type AS ( + a integer, + b real, + c text, + d date, + e jsonb); + +CREATE TABLE stats_import.test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import.complex_type, + arange int4range, + tags text[] +); + +-- starting stats +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- error: regclass not found +SELECT + pg_catalog.pg_set_relation_stats( + relation => 0::Oid, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + +-- relpages default +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => NULL::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + +-- reltuples default +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => NULL::real, + relallvisible => 4::integer); + +-- relallvisible default +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => NULL::integer); + +-- named arguments +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- positional arguments +SELECT + pg_catalog.pg_set_relation_stats( + 'stats_import.test'::regclass, + 18::integer, + 401.0::real, + 5::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- error: object doesn't exist +SELECT pg_catalog.pg_set_attribute_stats( + relation => '0'::oid, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +-- error: relation null +SELECT pg_catalog.pg_set_attribute_stats( + relation => NULL::oid, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +-- error: attname null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => NULL::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +-- error: inherited null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => NULL::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +-- error: null_frac null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => NULL::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +-- error: avg_width null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => NULL::integer, + n_distinct => 0.3::real); + +-- error: avg_width null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => NULL::real); + +-- ok: no stakinds +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.1::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +SELECT stanullfrac, stawidth, stadistinct +FROM pg_statistic +WHERE starelid = 'stats_import.test'::regclass; + +-- warn: mcv / mcf null mismatch +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_vals => '{2023-09-30,2024-10-31,3}'::text, + most_common_freqs => '{0.2,0.1}'::real[] + ); + +-- warning: mcv cast failure +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: histogram elements null value +-- this generates no warnings, but perhaps it should +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + histogram_bounds => '{1,2,3,4}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: correlation +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + correlation => 0.5::real); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: scalars can't have mcelem +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- warn: scalars can't have elem_count_histogram +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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[] + ); +-- warn: elem_count_histogram null element +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'tags'::name, + inherited => false::boolean, + 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[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- warn: scalars can't have range stats +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + 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_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + 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 * +FROM pg_stats +WHERE schemaname = 'stats_import' +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_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + 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_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + 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 + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: exceed STATISTIC_NUM_SLOTS +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean, + null_frac => 0.5::real, + avg_width => 2::integer, + n_distinct => -0.1::real, + most_common_vals => '{"[2,3)","[1,2)","[3,4)"}'::text, + most_common_freqs => '{0.3,0.25,0.05}'::real[], + histogram_bounds => '{"[1,2)","[2,3)","[3,4)","[4,5)"}'::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 + ); +-- +-- Test the ability to exactly copy data from one table to an identical table, +-- correctly reconstructing the stakind order as well as the staopN and +-- stacollN values. Because oids are not stable across databases, we can only +-- test this when the source and destination are on the same database +-- instance. For that reason, we borrow and adapt a query found in fe_utils +-- and used by pg_dump/pg_upgrade. +-- +INSERT INTO stats_import.test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_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_import.complex_type, int4range(1,4), array['blue','yellow'] +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_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_import.test(((comp).a % 2 = 1)); + +-- Generate statistics on table with data +ANALYZE stats_import.test; + +CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ); + +CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); + +-- +-- Copy stats from test to test_clone, and is_odd to is_odd_clone +-- +SELECT s.schemaname, s.tablename, s.attname, s.inherited +FROM pg_catalog.pg_stats AS s +CROSS JOIN LATERAL + pg_catalog.pg_set_attribute_stats( + relation => ('stats_import.' || s.tablename || '_clone')::regclass::oid, + attname => s.attname, + inherited => s.inherited, + null_frac => s.null_frac, + avg_width => s.avg_width, + n_distinct => s.n_distinct, + most_common_vals => s.most_common_vals::text, + most_common_freqs => s.most_common_freqs, + histogram_bounds => s.histogram_bounds::text, + correlation => s.correlation, + most_common_elems => s.most_common_elems::text, + most_common_elem_freqs => s.most_common_elem_freqs, + elem_count_histogram => s.elem_count_histogram, + range_bounds_histogram => s.range_bounds_histogram::text, + range_empty_frac => s.range_empty_frac, + range_length_histogram => s.range_length_histogram::text) AS r +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test', 'is_odd') +ORDER BY s.tablename, s.attname, s.inherited; + +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_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_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_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_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_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_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_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_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_import.is_odd'::regclass; + +DROP SCHEMA stats_import CASCADE; -- 2.34.1