From 66e6e735d7e7c3aeb2f374812259864d4c4e2f1e Mon Sep 17 00:00:00 2001 From: Jeff Davis Date: Thu, 8 Aug 2024 17:56:22 -0700 Subject: [PATCH v26j 2/2] Create function pg_set_attribute_stats. --- doc/src/sgml/func.sgml | 60 ++ src/backend/catalog/system_functions.sql | 22 + src/backend/statistics/import_stats.c | 957 ++++++++++++++++++++- src/include/catalog/pg_proc.dat | 7 + src/include/statistics/statistics.h | 3 + src/test/regress/expected/stats_import.out | 645 +++++++++++++- src/test/regress/sql/stats_import.sql | 544 ++++++++++++ 7 files changed, 2236 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 02d93f6c925..402a14fdb1f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -29999,6 +29999,66 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a + + + + + 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 ) + void + + + 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, and the values supplied in the parameter must meet the + requirements of the corresponding attribute. + + + Any optional parameters left unspecified will cause the corresponding + statistics to be set to NULL. + + + 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. + + + + diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql index 623b9539b15..49f0d0dab2d 100644 --- a/src/backend/catalog/system_functions.sql +++ b/src/backend/catalog/system_functions.sql @@ -639,6 +639,28 @@ LANGUAGE INTERNAL CALLED ON NULL INPUT VOLATILE PARALLEL SAFE AS 'pg_stat_reset_slru'; +CREATE OR REPLACE FUNCTION + pg_set_attribute_stats(relation oid, + attname name, + inherited bool, + null_frac real, + avg_width integer, + n_distinct real, + 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 void +LANGUAGE INTERNAL +CALLED ON NULL INPUT VOLATILE +AS 'pg_set_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/import_stats.c b/src/backend/statistics/import_stats.c index 8c0a2bab305..3a09b880a38 100644 --- a/src/backend/statistics/import_stats.c +++ b/src/backend/statistics/import_stats.c @@ -20,18 +20,61 @@ #include "access/heapam.h" #include "catalog/indexing.h" +#include "catalog/pg_collation.h" #include "catalog/pg_database.h" +#include "catalog/pg_operator.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" +static void update_pg_statistic(Datum values[], bool nulls[]); +static Datum cast_stavalues(FmgrInfo *flinfo, Datum d, Oid typid, + int32 typmod, int elevel, bool *ok); +static bool array_check(Datum datum, int one_dim, const char *statname, + int elevel); +static bool type_is_scalar(Oid typid); +static TypeCacheEntry *get_attr_stat_type(Relation rel, AttrNumber attnum, + int elevel, int32 *typmod, Oid *typcoll); +static void use_stats_slot(Datum *values, bool *nulls, int slotidx, + int16 stakind, Oid staop, Oid stacoll, + Datum stanumbers, bool stanumbers_isnull, + Datum stavalues, bool stavalues_isnull); + +/* + * Names of parameters found in the functions pg_set_relation_stats and + * pg_set_attribute_stats + */ +const char *relation_name = "relation"; +const char *relpages_name = "relpages"; +const char *reltuples_name = "reltuples"; +const char *relallvisible_name = "relallvisible"; +const char *attname_name = "attname"; +const char *inherited_name = "inherited"; +const char *null_frac_name = "null_frac"; +const char *avg_width_name = "avg_width"; +const char *n_distinct_name = "n_distinct"; +const char *mc_vals_name = "most_common_vals"; +const char *mc_freqs_name = "most_common_freqs"; +const char *histogram_bounds_name = "histogram_bounds"; +const char *correlation_name = "correlation"; +const char *mc_elems_name = "most_common_elems"; +const char *mc_elem_freqs_name = "most_common_elem_freqs"; +const char *elem_count_hist_name = "elem_count_histogram"; +const char *range_length_hist_name = "range_length_histogram"; +const char *range_empty_frac_name = "range_empty_frac"; +const char *range_bounds_hist_name = "range_bounds_histogram"; + /* * 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 bool check_can_update_stats(Relation rel) @@ -158,6 +201,760 @@ relation_statistics_update(Oid reloid, int version, int32 relpages, PG_RETURN_BOOL(true); } +/* + * Insert or Update Attribute Statistics + * + * 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(Oid reloid, AttrNumber attnum, int version, + int elevel, bool inherited, float null_frac, + int avg_width, float n_distinct, + Datum mc_vals_datum, bool mc_vals_isnull, + Datum mc_freqs_datum, bool mc_freqs_isnull, + Datum histogram_bounds_datum, bool histogram_bounds_isnull, + Datum correlation_datum, bool correlation_isnull, + Datum mc_elems_datum, bool mc_elems_isnull, + Datum mc_elem_freqs_datum, bool mc_elem_freqs_isnull, + Datum elem_count_hist_datum, bool elem_count_hist_isnull, + Datum range_length_hist_datum, bool range_length_hist_isnull, + Datum range_empty_frac_datum, bool range_empty_frac_isnull, + Datum range_bounds_hist_datum, bool range_bounds_hist_isnull) +{ + Relation rel; + + TypeCacheEntry *typcache; + TypeCacheEntry *elemtypcache = NULL; + + int32 typmod; + Oid typcoll; + + FmgrInfo finfo; + + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + + /* + * The statkind index, we have only STATISTIC_NUM_SLOTS to hold these stats + */ + int stakindidx = 0; + char *attname = get_attname(reloid, attnum, false); + + /* + * 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; + } + + /* + * Some parameters are linked, should both be NULL or NOT NULL. + * Disagreement means that the statistic pair will fail so the + * NOT NULL one must be abandoned (set NULL) after an + * ERROR/WARNING. By ensuring that the values are aligned it is + * possible to use one as a proxy for the other later. + */ + + /* + * STATISTIC_KIND_MCV = mc_vals + mc_freqs + */ + if (mc_vals_isnull != mc_freqs_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL when %s is NOT NULL", + mc_vals_isnull ? mc_vals_name : mc_freqs_name, + !mc_vals_isnull ? mc_vals_name : mc_freqs_name))); + + mc_vals_isnull = true; + mc_freqs_isnull = true; + } + + /* + * STATISTIC_KIND_MCELEM = mc_elems + mc_elem_freqs + */ + if (mc_elems_isnull != mc_elem_freqs_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL when %s is NOT NULL", + mc_elems_isnull ? mc_elems_name : mc_elem_freqs_name, + !mc_elems_isnull ? mc_elems_name : mc_elem_freqs_name))); + mc_elems_isnull = true; + mc_elem_freqs_isnull = true; + } + + /* + * STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM = + * range_length_histogram + range_empty_frac + */ + else if (range_length_hist_isnull != range_empty_frac_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL when %s is NOT NULL", + range_length_hist_isnull ? + range_length_hist_name : range_empty_frac_name, + !range_length_hist_isnull ? + range_length_hist_name : range_empty_frac_name))); + range_length_hist_isnull = true; + range_empty_frac_isnull = true; + } + + rel = try_relation_open(reloid, ShareUpdateExclusiveLock); + + if (rel == NULL) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameter relation OID %u is invalid", reloid))); + return false; + } + + /* + * Many of the values that are set for a particular stakind are entirely + * derived from the attribute itself, or it's expression. + */ + typcache = get_attr_stat_type(rel, attnum, elevel, &typmod, &typcoll); + if (typcache == NULL) + { + relation_close(rel, NoLock); + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unexpected typecache error"))); + return false; + } + + /* + * Derive element type if we have stat kinds that need it. + * + * This duplicates some type-specific logic found in various typanalyze + * functions which are called from vacuum's examine_attribute(), but using + * that directly has proven awkward. + */ + if (!mc_elems_isnull || !elem_count_hist_isnull) + { + Oid elemtypid; + + if (typcache->type_id == TSVECTOROID) + { + /* + * tsvectors always have a text oid base type and default + * collation + */ + elemtypid = TEXTOID; + typcoll = DEFAULT_COLLATION_OID; + } + else if (typcache->typtype == TYPTYPE_RANGE) + elemtypid = get_range_subtype(typcache->type_id); + else + elemtypid = get_base_element_type(typcache->type_id); + + /* not finding a basetype means we already had it */ + if (elemtypid == InvalidOid) + elemtypid = typcache->type_id; + + /* The stats need the eq_opr, any validation would need the lt_opr */ + elemtypcache = lookup_type_cache(elemtypid, TYPECACHE_EQ_OPR); + if (elemtypcache == NULL) + { + /* warn and ignore any stats that can't be fulfilled */ + if (!mc_elems_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot accept %s stats, ignored", + attname, + mc_elems_name))); + mc_elems_isnull = true; + mc_elem_freqs_isnull = true; + } + + if (!elem_count_hist_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot accept %s stats, ignored", + attname, + elem_count_hist_name))); + elem_count_hist_isnull = true; + } + } + } + + /* + * histogram_bounds and correlation must have a type < operator + */ + if (typcache->lt_opr == InvalidOid) + { + if (!histogram_bounds_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Relation %s attname %s cannot " + "have stats of type %s, ignored.", + RelationGetRelationName(rel), + attname, + histogram_bounds_name))); + histogram_bounds_isnull = true; + } + + if (!correlation_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Relation %s attname %s cannot " + "have stats of type %s, ignored.", + RelationGetRelationName(rel), + attname, + correlation_name))); + correlation_isnull = true; + } + } + + /* + * Scalar types can't have most_common_elems, most_common_elem_freqs, or + * element_count_histogram + */ + if (type_is_scalar(typcache->type_id)) + { + /* warn and ignore any stats that can't be fulfilled */ + if (!mc_elems_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Relation %s attname %s is a scalar type, " + "cannot have stats of type %s, ignored", + RelationGetRelationName(rel), + attname, + mc_elems_name))); + mc_elems_isnull = true; + mc_elem_freqs_isnull = true; + } + + if (!elem_count_hist_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Relation %s attname %s is a scalar type, " + "cannot have stats of type %s, ignored", + RelationGetRelationName(rel), + attname, + elem_count_hist_name))); + elem_count_hist_isnull = true; + } + } + + /* Only range types can have range stats */ + if ((typcache->typtype != TYPTYPE_RANGE) && + (typcache->typtype != TYPTYPE_MULTIRANGE)) + { + /* warn and ignore any stats that can't be fulfilled */ + if (!range_length_hist_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Relation %s attname %s is not a range type, " + "cannot have stats of type %s", + RelationGetRelationName(rel), + attname, + range_length_hist_name))); + range_length_hist_isnull = true; + range_empty_frac_isnull = true; + } + + if (!range_bounds_hist_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Relation %s attname %s is not a range type, " + "cannot have stats of type %s", + RelationGetRelationName(rel), + attname, + range_bounds_hist_name))); + range_bounds_hist_isnull = true; + } + } + + if (!check_can_update_stats(rel)) + { + relation_close(rel, NoLock); + ereport(elevel, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied for relation %s", + RelationGetRelationName(rel)))); + return false; + } + + /* Populate pg_statistic tuple */ + 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] = Float4GetDatum(null_frac); + values[Anum_pg_statistic_stawidth - 1] = Int32GetDatum(avg_width); + values[Anum_pg_statistic_stadistinct - 1] = Float4GetDatum(n_distinct); + + fmgr_info(F_ARRAY_IN, &finfo); + + /* + * STATISTIC_KIND_MCV + * + * most_common_vals: ANYARRAY::text most_common_freqs: real[] + */ + if (!mc_vals_isnull) + { + bool converted = false; + Datum stanumbers = mc_freqs_datum; + Datum stavalues = cast_stavalues(&finfo, mc_vals_datum, + typcache->type_id, typmod, + elevel, &converted); + + if (converted && + array_check(stavalues, false, mc_vals_name, elevel) && + array_check(stanumbers, true, mc_freqs_name, elevel)) + { + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_MCV, + typcache->eq_opr, typcoll, + stanumbers, false, stavalues, false); + } + else + { + /* Mark as skipped */ + mc_vals_isnull = true; + mc_freqs_isnull = true; + } + } + + /* + * STATISTIC_KIND_HISTOGRAM + * + * histogram_bounds: ANYARRAY::text + */ + if (!histogram_bounds_isnull) + { + Datum stavalues; + bool converted = false; + + stavalues = cast_stavalues(&finfo, histogram_bounds_datum, + typcache->type_id, typmod, elevel, + &converted); + + if (converted && + array_check(stavalues, false, histogram_bounds_name, elevel)) + { + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_HISTOGRAM, + typcache->lt_opr, typcoll, + 0, true, stavalues, false); + } + else + histogram_bounds_isnull = true; + } + + /* + * STATISTIC_KIND_CORRELATION + * + * correlation: real + */ + if (!correlation_isnull) + { + Datum elems[] = {correlation_datum}; + ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID); + Datum stanumbers = PointerGetDatum(arry); + + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_CORRELATION, + typcache->lt_opr, typcoll, + stanumbers, false, 0, true); + } + + /* + * STATISTIC_KIND_MCELEM + * + * most_common_elem_freqs: real[] + * + * most_common_elems : ANYARRAY::text + */ + if (!mc_elems_isnull) + { + Datum stanumbers = mc_elem_freqs_datum; + bool converted = false; + Datum stavalues; + + stavalues = cast_stavalues(&finfo, mc_elems_datum, + elemtypcache->type_id, typmod, + elevel, &converted); + + if (converted && + array_check(stavalues, false, mc_elems_name, elevel) && + array_check(stanumbers, true, mc_elem_freqs_name, elevel)) + { + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_MCELEM, + elemtypcache->eq_opr, typcoll, + stanumbers, false, stavalues, false); + } + else + { + /* the mc_elem stat did not write */ + mc_elems_isnull = true; + mc_elem_freqs_isnull = true; + } + } + + /* + * STATISTIC_KIND_DECHIST + * + * elem_count_histogram: real[] + */ + if (!elem_count_hist_isnull) + { + Datum stanumbers = elem_count_hist_datum; + + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_DECHIST, + elemtypcache->eq_opr, typcoll, + 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 (!range_bounds_hist_isnull) + { + bool converted = false; + Datum stavalues; + + stavalues = cast_stavalues(&finfo, range_bounds_hist_datum, + typcache->type_id, typmod, + elevel, &converted); + + if (converted && + array_check(stavalues, false, "range_bounds_histogram", elevel)) + { + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_BOUNDS_HISTOGRAM, + InvalidOid, InvalidOid, + 0, true, stavalues, false); + } + else + range_bounds_hist_isnull = true; + } + + /* + * STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM + * + * range_empty_frac: real + * + * range_length_histogram: double precision[]::text + */ + if (!range_length_hist_isnull) + { + /* The anyarray is always a float8[] for this stakind */ + Datum elems[] = {range_empty_frac_datum}; + ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID); + Datum stanumbers = PointerGetDatum(arry); + + bool converted = false; + Datum stavalues; + + stavalues = cast_stavalues(&finfo, range_length_hist_datum, FLOAT8OID, + 0, elevel, &converted); + + if (converted && + array_check(stavalues, false, range_length_hist_name, elevel)) + { + use_stats_slot(values, nulls, stakindidx++, + STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM, + Float8LessOperator, InvalidOid, + stanumbers, false, stavalues, false); + } + else + { + range_empty_frac_isnull = true; + range_length_hist_isnull = true; + } + } + + update_pg_statistic(values, nulls); + + relation_close(rel, NoLock); + + return true; +} + +/* + * Test if the type is a scalar for MCELEM purposes + */ +static bool +type_is_scalar(Oid typid) +{ + HeapTuple tp; + bool result = false; + + tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp); + + result = (!OidIsValid(typtup->typanalyze)); + ReleaseSysCache(tp); + } + return result; +} + +/* + * 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; +} + +/* + * Fetch datatype information, this is needed to derive the proper staopN + * and stacollN values. + * + */ +static TypeCacheEntry * +get_attr_stat_type(Relation rel, AttrNumber attnum, int elevel, + int32 *typmod, Oid *typcoll) +{ + Oid relid = RelationGetRelid(rel); + Form_pg_attribute attr; + HeapTuple atup; + Oid typid; + Node *expr; + + atup = SearchSysCache2(ATTNUM, ObjectIdGetDatum(relid), + Int16GetDatum(attnum)); + + /* Attribute not found */ + if (!HeapTupleIsValid(atup)) + { + ereport(elevel, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("Relation %s has no attribute %d", + RelationGetRelationName(rel), attnum))); + return NULL; + } + + attr = (Form_pg_attribute) GETSTRUCT(atup); + if (attr->attisdropped) + { + ereport(elevel, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("Relation %s attribute %d is dropped", + RelationGetRelationName(rel), + attnum))); + return NULL; + } + + expr = get_attr_expr(rel, attr->attnum); + + if (expr == NULL) + { + /* regular attribute */ + typid = attr->atttypid; + *typmod = attr->atttypmod; + *typcoll = attr->attcollation; + } + else + { + typid = exprType(expr); + *typmod = exprTypmod(expr); + + /* + * If a collation has been specified for the index column, use that in + * preference to anything else; but if not, fall back to whatever we + * can get from the expression. + */ + if (OidIsValid(attr->attcollation)) + *typcoll = attr->attcollation; + else + *typcoll = exprCollation(expr); + } + ReleaseSysCache(atup); + + /* if it's a multirange, step down to the range type */ + if (type_is_multirange(typid)) + typid = get_multirange_range(typid); + + return lookup_type_cache(typid, + TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR); +} + +/* + * Perform the cast of a known TextDatum into the type specified. + * + * If no errors are found, ok is set to true. + * + * Otherwise, set ok to false, capture the error found, and re-throw at the + * level specified by elevel. + */ +static Datum +cast_stavalues(FmgrInfo *flinfo, 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, flinfo, 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); + + if (SOFT_ERROR_OCCURRED(&escontext)) + { + if (elevel != ERROR) + escontext.error_data->elevel = elevel; + ThrowErrorData(escontext.error_data); + *ok = false; + } + else + *ok = true; + + pfree(s); + + return result; +} + + +/* + * Check array for any NULLs, and optionally for one-dimensionality. + * + * Report any failures at the level of elevel. + */ +static bool +array_check(Datum datum, int one_dim, const char *statname, int elevel) +{ + ArrayType *arr = DatumGetArrayTypeP(datum); + int16 elmlen; + char elmalign; + bool elembyval; + Datum *values; + bool *nulls; + int nelems; + + if (one_dim && (arr->ndim != 1)) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be a multidimensional array", statname))); + return false; + } + + get_typlenbyvalalign(ARR_ELEMTYPE(arr), &elmlen, &elembyval, &elmalign); + + deconstruct_array(arr, ARR_ELEMTYPE(arr), elmlen, elembyval, elmalign, + &values, &nulls, &nelems); + + for (int i = 0; i < nelems; i++) + if (nulls[i]) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s array cannot contain NULL values", statname))); + return false; + } + return true; +} + +/* + * Update the pg_statistic record. + */ +static void +update_pg_statistic(Datum values[], bool nulls[]) +{ + Relation sd = table_open(StatisticRelationId, RowExclusiveLock); + CatalogIndexState indstate = CatalogOpenIndexes(sd); + HeapTuple oldtup; + HeapTuple stup; + + /* Is there already a pg_statistic tuple for this attribute? */ + oldtup = SearchSysCache3(STATRELATTINH, + values[Anum_pg_statistic_starelid - 1], + values[Anum_pg_statistic_staattnum - 1], + values[Anum_pg_statistic_stainherit - 1]); + + if (HeapTupleIsValid(oldtup)) + { + /* Yes, replace it */ + bool replaces[Natts_pg_statistic]; + + for (int i = 0; i < Natts_pg_statistic; i++) + replaces[i] = true; + + stup = heap_modify_tuple(oldtup, RelationGetDescr(sd), + values, nulls, replaces); + ReleaseSysCache(oldtup); + CatalogTupleUpdateWithInfo(sd, &stup->t_self, stup, indstate); + } + else + { + /* No, insert new tuple */ + stup = heap_form_tuple(RelationGetDescr(sd), values, nulls); + CatalogTupleInsertWithInfo(sd, stup, indstate); + } + + heap_freetuple(stup); + CatalogCloseIndexes(indstate); + table_close(sd, NoLock); +} + /* * Set statistics for a given pg_class entry. * @@ -210,3 +1007,161 @@ pg_set_relation_stats(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } + +static +void use_stats_slot(Datum *values, bool *nulls, int slotidx, + int16 stakind, Oid staop, Oid stacoll, + Datum stanumbers, bool stanumbers_isnull, + Datum stavalues, bool stavalues_isnull) +{ + if (slotidx >= STATISTIC_NUM_SLOTS) + ereport(ERROR, + (errmsg("maximum number of statistics slots exceeded: %d", slotidx + 1))); + + /* slot should not be taken */ + Assert(values[Anum_pg_statistic_stakind1 - 1 + slotidx] == (Datum) 0); + Assert(values[Anum_pg_statistic_staop1 - 1 + slotidx] == (Datum) 0); + Assert(values[Anum_pg_statistic_stacoll1 - 1 + slotidx] == (Datum) 0); + Assert(values[Anum_pg_statistic_stanumbers1 - 1 + slotidx] == (Datum) 0); + Assert(values[Anum_pg_statistic_stavalues1 - 1 + slotidx] == (Datum) 0); + + /* nulls should be false for non-NULL attributes, true for nullable */ + Assert(!nulls[Anum_pg_statistic_stakind1 - 1 + slotidx]); + Assert(!nulls[Anum_pg_statistic_staop1 - 1 + slotidx]); + Assert(!nulls[Anum_pg_statistic_stacoll1 - 1 + slotidx]); + Assert(nulls[Anum_pg_statistic_stanumbers1 - 1 + slotidx]); + Assert(nulls[Anum_pg_statistic_stavalues1 - 1 + slotidx]); + + 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; + } +} + +/* + * 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) +{ + Oid reloid; + Name attname; + AttrNumber attnum; + int version = PG_VERSION_NUM; + int elevel = ERROR; + bool inherited; + float null_frac; + int avg_width; + float n_distinct; + + if (PG_ARGISNULL(0)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("relation cannot be NULL"))); + return false; + } + reloid = PG_GETARG_OID(0); + + if (PG_ARGISNULL(1)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("attname cannot be NULL"))); + return false; + } + attname = PG_GETARG_NAME(1); + attnum = get_attnum(reloid, NameStr(*attname)); + + if (PG_ARGISNULL(2)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("inherited cannot be NULL"))); + return false; + } + inherited = PG_GETARG_BOOL(2); + + if (PG_ARGISNULL(3)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("null_frac cannot be NULL"))); + return false; + } + null_frac = PG_GETARG_FLOAT4(3); + + if (PG_ARGISNULL(4)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("avg_width cannot be NULL"))); + return false; + } + avg_width = PG_GETARG_INT32(4); + + if (PG_ARGISNULL(5)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("n_distinct cannot be NULL"))); + return false; + } + n_distinct = PG_GETARG_FLOAT4(5); + + attribute_statistics_update( + reloid, attnum, version, elevel, inherited, + null_frac, avg_width, n_distinct, + PG_GETARG_DATUM(6), PG_ARGISNULL(6), + PG_GETARG_DATUM(7), PG_ARGISNULL(7), + PG_GETARG_DATUM(8), PG_ARGISNULL(8), + PG_GETARG_DATUM(9), PG_ARGISNULL(9), + PG_GETARG_DATUM(10), PG_ARGISNULL(10), + PG_GETARG_DATUM(11), PG_ARGISNULL(11), + PG_GETARG_DATUM(12), PG_ARGISNULL(12), + PG_GETARG_DATUM(13), PG_ARGISNULL(13), + PG_GETARG_DATUM(14), PG_ARGISNULL(14), + PG_GETARG_DATUM(15), PG_ARGISNULL(15)); + + PG_RETURN_VOID(); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index d700dd50f7b..fbd1a8a384d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12263,5 +12263,12 @@ proargtypes => 'oid int4 float4 int4', proargnames => '{relation,relpages,reltuples,relallvisible}', prosrc => 'pg_set_relation_stats' }, +{ oid => '8049', + descr => 'set statistics on attribute', + proname => 'pg_set_attribute_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'void', + proargtypes => 'oid name bool float4 int4 float4 text _float4 text float4 text _float4 _float4 text float4 text', + proargnames => '{relation,attname,inherited,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation,most_common_elems,most_common_elem_freqs,elem_count_histogram,range_length_histogram,range_empty_frac,range_bounds_histogram}', + prosrc => 'pg_set_attribute_stats' }, ] diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h index 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 index f727cce9765..3a26b1f7d67 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -93,7 +93,650 @@ WHERE oid = 'stats_import.test'::regclass; 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: cache lookup failed for attribute 0 of relation 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); +ERROR: null_frac cannot be NULL +-- 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 cannot be NULL +-- 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); +ERROR: n_distinct cannot be NULL +-- 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 +------------------------ + +(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 cannot be NULL when most_common_freqs is NOT NULL +-- 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 cannot be NULL when most_common_vals is NOT NULL +-- 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 +------------------------ + +(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 +------------------------ + +(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 | | | {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 +------------------------ + +(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 | | | | 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: Relation test attname id is a scalar type, cannot have stats of type most_common_elems, ignored +-- 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_elem_freqs cannot be NULL when most_common_elems is NOT NULL +-- 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 cannot be NULL when most_common_elem_freqs is NOT NULL +-- 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 +------------------------ + +(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: Relation test attname id is a scalar type, cannot have stats of type elem_count_histogram, ignored +-- 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[] + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +-- 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 +------------------------ + +(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 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,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: Relation test attname id is not a range type, cannot have stats of type range_length_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 cannot be NULL when range_length_histogram is NOT NULL +-- 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 cannot be NULL when range_empty_frac is NOT NULL +-- 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 +------------------------ + +(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: Relation test attname id is not a range type, cannot have stats of type range_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 +------------------------ + +(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 | | | | | | | | | | {"[-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 2 other objects +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/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index effd5b892bf..4fa6ea41519 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -76,4 +76,548 @@ 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