On Fri, Feb 06, 2026 at 02:55:47AM -0500, Corey Huinker wrote: >> If >> my test of the patch is right, the restore function accepts this case, >> and decides to set the following fields: >> "avg_width": "0", >> "null_frac": "0", >> "n_distinct": "0" > > statatt_get_empty_stat_tuple is just assigning the defaults.
Okay. I have looked at the default assignment path, and I don't have
an issue with that after thinking more about it.
> Any explicit null parameters would be handled by the loop that fills out
> the found[] and val[] arrays, and explicit jbvNull is treated the same as
> if the parameter wasn't specified in the first place. So I think the null
> checks you seek are already there, but the existence of defaults in the
> pg_statistic tuple (which has no nullable columns, btw) is throwing you off.
Right, I can see that. As far as my review has brought me, there was
a case that was not checked: passing down a non-null, non-string value
was not tested. Not a big issue, but still.
import_pg_statistic() could be tricked to trigger the assertion based
on JsonContainerIsObject(), for example with test_stat_clone in the
regression tests:
[null, [{"avg_width" : [1]}]]
I have added a test for that, changing the code inside
import_pg_statistic() to emit a WARNING. This seemed simpler than
touching more elem->val.binary.data in import_expressions().
Here is another one: [1, null]. This triggers the default clause for
the loop in import_expressions(). This did not fail, but it was
uncovered.
In import_pg_statistic(), there should be some coverage for the case
where a value is not a string or a NULL, like { "null_frac": 1 }.
Added one with an integer.
By the way, the test you have proposed in [1] to copy the expression
stats from an origin to a target and check the differences was super
nice. Could you add that back, with 'exprs' set as a jsonb blob?
This was posted in 0003, but got cut due to the initial integration of
the feature without expressions.
I have put my hands on the patch, and fixed all these issues, adding
tests to cover the holes I have spotted, and addressed a lot of
stylistic things. I did not take the time to add the diff test,
though when the expressions are cloned. Could you?
The cases of STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and
STATISTIC_KIND_BOUNDS_HISTOGRAM are a bit sad. How about exposing
them into pg_stats_ext_exprs as a first patch, then extend the
expression restore function to be able to work on them? Modifying the
view would be an entirely independent thing. Please note that I do
not mind skipping these two cases for now, the proposed patch is
already doing a lot. Still if we are able to close entirely the loop
for this release that would be nice. Anyway, let's keep this stuff as
separate patches on top of this v8 and future reviewed versions.
[1]:
https://www.postgresql.org/message-id/CADkLM%3DdgvTvuOy%2BZOWQX51gGtLgAmQqqZw7Oi74tWrQ542_bvQ%40mail.gmail.com
--
Michael
From 9112f20a1a88a629591240e1b4ed31aa46cb45f3 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Mon, 9 Feb 2026 15:28:39 +0900 Subject: [PATCH v8] Add support for "exprs" in pg_restore_extended_stats() This commit adds support for the restore of extended statistics of the kind "exprs". The input format consists of a jsonb object which must be an array of objects which are keyed by statistics parameter names, like this: [{"stat_type1": "...", "stat_type2": "...", ...}, {"stat_type1": "...", "stat_type2": "...", ...}, ...] The outer array must have as many elements as there are expressions defined in the statistics object. The elements of the array must be either objects or null values. The keys of the inner objects are names of the statistical columns in pg_stats_ext_exprs (i.e. everything after "inherited"). Not all parameter keys need to be provided, those omitted are silently ignored. Key values that do not match a statistical column name will cause a warning to be issued, but will not otherwise fail the expression or the import as a whole. The expected value type for all parameters is jbvString, which allows us to validate the values using the input function specific to that parameter. Any parameters with a null value are silently ignored, same as if they werent provided in the first place. --- src/backend/statistics/extended_stats_funcs.c | 794 +++++++++++++++++- src/bin/pg_dump/pg_dump.c | 46 +- src/test/regress/expected/stats_import.out | 787 ++++++++++++++++- src/test/regress/sql/stats_import.sql | 480 ++++++++++- doc/src/sgml/func/func-admin.sgml | 42 +- 5 files changed, 2138 insertions(+), 11 deletions(-) diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c index b640941a9cc6..a34e8e31352a 100644 --- a/src/backend/statistics/extended_stats_funcs.c +++ b/src/backend/statistics/extended_stats_funcs.c @@ -19,6 +19,7 @@ #include "access/heapam.h" #include "catalog/indexing.h" #include "catalog/namespace.h" +#include "catalog/pg_collation_d.h" #include "catalog/pg_database.h" #include "catalog/pg_statistic_ext.h" #include "catalog/pg_statistic_ext_data.h" @@ -32,8 +33,10 @@ #include "utils/array.h" #include "utils/builtins.h" #include "utils/fmgroids.h" +#include "utils/jsonb.h" #include "utils/lsyscache.h" #include "utils/syscache.h" +#include "utils/typcache.h" /* @@ -51,6 +54,7 @@ enum extended_stats_argnum MOST_COMMON_VALS_ARG, MOST_COMMON_FREQS_ARG, MOST_COMMON_BASE_FREQS_ARG, + EXPRESSIONS_ARG, NUM_EXTENDED_STATS_ARGS, }; @@ -70,9 +74,49 @@ static struct StatsArgInfo extarginfo[] = [MOST_COMMON_VALS_ARG] = {"most_common_vals", TEXTARRAYOID}, [MOST_COMMON_FREQS_ARG] = {"most_common_freqs", FLOAT8ARRAYOID}, [MOST_COMMON_BASE_FREQS_ARG] = {"most_common_base_freqs", FLOAT8ARRAYOID}, + [EXPRESSIONS_ARG] = {"exprs", JSONBOID}, [NUM_EXTENDED_STATS_ARGS] = {0}, }; +/* + * An index of the elements of a stxdexpr Datum, which repeat for each + * expression in the extended statistics object. + * + * NOTE: the RANGE_LENGTH & RANGE_BOUNDS stats are not yet reflected in any + * version of pg_stat_ext_exprs. + */ +enum extended_stats_exprs_element +{ + NULL_FRAC_ELEM = 0, + AVG_WIDTH_ELEM, + N_DISTINCT_ELEM, + MOST_COMMON_VALS_ELEM, + MOST_COMMON_FREQS_ELEM, + HISTOGRAM_BOUNDS_ELEM, + CORRELATION_ELEM, + MOST_COMMON_ELEMS_ELEM, + MOST_COMMON_ELEM_FREQS_ELEM, + ELEM_COUNT_HISTOGRAM_ELEM, + NUM_ATTRIBUTE_STATS_ELEMS +}; + +/* + * The argument names of the repeating arguments for stxdexpr. + */ +static const char *extexprargname[NUM_ATTRIBUTE_STATS_ELEMS] = +{ + "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", +}; + static bool extended_statistics_update(FunctionCallInfo fcinfo); static HeapTuple get_pg_statistic_ext(Relation pg_stext, Oid nspoid, @@ -98,6 +142,10 @@ static void upsert_pg_statistic_ext_data(const Datum *values, static bool check_mcvlist_array(const ArrayType *arr, int argindex, int required_ndims, int mcv_length); +static Datum import_expressions(Relation pgsd, int numexprs, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, Jsonb *exprs_jsonb, + bool *exprs_is_perfect); static Datum import_mcv(const ArrayType *mcv_arr, const ArrayType *freqs_arr, const ArrayType *base_freqs_arr, @@ -105,6 +153,19 @@ static Datum import_mcv(const ArrayType *mcv_arr, Oid *atttypcolls, int numattrs, bool *ok); +static char *jbv_string_get_cstr(JsonbValue *jval); +static bool jbv_to_infunc_datum(JsonbValue *jval, PGFunction func, + AttrNumber exprnum, const char *argname, + Datum *datum); +static bool key_in_expr_argnames(JsonbValue *key); +static bool check_all_expr_argnames_valid(JsonbContainer *cont, AttrNumber exprnum); +static Datum array_in_safe(FmgrInfo *array_in, const char *s, Oid typid, + int32 typmod, AttrNumber exprnum, + const char *element_name, bool *ok); +static Datum import_pg_statistic(Relation pgsd, JsonbContainer *cont, + AttrNumber exprnum, FmgrInfo *array_in_fn, + Oid typid, int32 typmod, Oid typcoll, + bool *pg_statistic_ok); /* * Fetch a pg_statistic_ext row by name and namespace OID. @@ -296,6 +357,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)); has.ndistinct = !PG_ARGISNULL(NDISTINCT_ARG); has.dependencies = !PG_ARGISNULL(DEPENDENCIES_ARG); + has.expressions = !PG_ARGISNULL(EXPRESSIONS_ARG); if (RecoveryInProgress()) { @@ -492,6 +554,24 @@ extended_statistics_update(FunctionCallInfo fcinfo) } } + /* + * If the object cannot support expressions, we should not have data for + * it. + */ + if (has.expressions && !enabled.expressions) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot specify parameter \"%s\"", + extarginfo[EXPRESSIONS_ARG].argname), + errhint("Extended statistics object \"%s\".\"%s\" does not support statistics of this type.", + quote_identifier(nspname), + quote_identifier(stxname))); + + has.expressions = false; + success = false; + } + /* * Either of these statistic types requires that we supply a semi-filled * VacAttrStatP array. @@ -501,7 +581,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) * attstattarget is 0, and we may have statistics data to import for those * attributes. */ - if (has.mcv) + if (has.mcv || has.expressions) { atttypids = palloc0_array(Oid, numattrs); atttypmods = palloc0_array(int32, numattrs); @@ -636,6 +716,42 @@ extended_statistics_update(FunctionCallInfo fcinfo) success = false; } + if (has.expressions) + { + Datum datum; + Relation pgsd; + bool ok = false; + + pgsd = table_open(StatisticRelationId, RowExclusiveLock); + + /* + * Generate the expressions array. + * + * The attytypids, attytypmods, and atttypcolls arrays have all the + * regular attributes listed first, so we can pass those arrays with a + * start point after the last regular attribute. There are numexprs + * elements remaining. + */ + datum = import_expressions(pgsd, numexprs, + &atttypids[numattnums], + &atttypmods[numattnums], + &atttypcolls[numattnums], + PG_GETARG_JSONB_P(EXPRESSIONS_ARG), + &ok); + + table_close(pgsd, RowExclusiveLock); + + if (ok) + { + Assert(datum != (Datum) 0); + values[Anum_pg_statistic_ext_data_stxdexpr - 1] = datum; + replaces[Anum_pg_statistic_ext_data_stxdexpr - 1] = true; + nulls[Anum_pg_statistic_ext_data_stxdexpr - 1] = false; + } + else + success = false; + } + upsert_pg_statistic_ext_data(values, nulls, replaces); cleanup: @@ -764,6 +880,682 @@ mcv_error: return mcv; } +/* + * Check validity is found in the list of expression argnames. + */ +static bool +key_in_expr_argnames(JsonbValue *key) +{ + Assert(key->type == jbvString); + for (int i = 0; i < NUM_ATTRIBUTE_STATS_ELEMS; i++) + { + if (strncmp(extexprargname[i], key->val.string.val, key->val.string.len) == 0) + return true; + } + return false; +} + +/* + * Verify that all of the keys in the object are valid argnames. + */ +static bool +check_all_expr_argnames_valid(JsonbContainer *cont, AttrNumber exprnum) +{ + bool all_keys_valid = true; + + JsonbIterator *jbit; + JsonbIteratorToken jitok; + JsonbValue jkey; + + Assert(JsonContainerIsObject(cont)); + + jbit = JsonbIteratorInit(cont); + + /* We always start off with a BEGIN OBJECT */ + jitok = JsonbIteratorNext(&jbit, &jkey, false); + Assert(jitok == WJB_BEGIN_OBJECT); + + while (true) + { + JsonbValue jval; + + jitok = JsonbIteratorNext(&jbit, &jkey, false); + + /* + * We have run of keys. This is the only condition where it is + * memory-safe to break out of the loop. + */ + if (jitok == WJB_END_OBJECT) + break; + + /* We can only find keys inside an object */ + Assert(jitok == WJB_KEY); + Assert(jkey.type == jbvString); + + /* A value must follow the key */ + jitok = JsonbIteratorNext(&jbit, &jval, false); + Assert(jitok == WJB_VALUE); + + /* + * If we have already found an invalid key, there is no point in + * looking for more, because additional WARNINGs are just clutter. We + * must continue iterating over the json to ensure that we clean\ up + * all allocated memory. + */ + if (!all_keys_valid) + continue; + + if (!key_in_expr_argnames(&jkey)) + { + char *bad_element_name = jbv_string_get_cstr(&jkey); + + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not import element in expression %d: invalid key name", + exprnum)); + + pfree(bad_element_name); + all_keys_valid = false; + } + } + return all_keys_valid; +} + +/* + * Simple conversion of jbvString to cstring + */ +static char * +jbv_string_get_cstr(JsonbValue *jval) +{ + char *s; + + Assert(jval->type == jbvString); + + s = palloc0(jval->val.string.len + 1); + memcpy(s, jval->val.string.val, jval->val.string.len); + + return s; +} + +/* + * Apply a jbvString value to a safe scalar input function. + */ +static bool +jbv_to_infunc_datum(JsonbValue *jval, PGFunction func, AttrNumber exprnum, + const char *argname, Datum *datum) +{ + ErrorSaveContext escontext = { + .type = T_ErrorSaveContext, + .details_wanted = true + }; + + char *s = jbv_string_get_cstr(jval); + bool ok; + + ok = DirectInputFunctionCallSafe(func, s, InvalidOid, -1, + (Node *) &escontext, datum); + + /* + * If we got a type import error, use that, but add in expr/element + * context and throw as a warning. + */ + if (!ok) + { + StringInfoData hint_str; + + initStringInfo(&hint_str); + appendStringInfo(&hint_str, + "Element \"%s\" in expression %d could not be parsed.", + argname, exprnum); + + escontext.error_data->elevel = WARNING; + escontext.error_data->hint = hint_str.data; + + ThrowErrorData(escontext.error_data); + pfree(hint_str.data); + } + + pfree(s); + return ok; +} + +/* + * Build an array datum with element type elemtypid from a text datum, used as + * value of an attribute in a pg_statistic tuple. + * + * If an error is encountered, capture it, and reduce the elevel to WARNING. + * + * This is an adaptation of statatt_build_stavalues(). + */ +static Datum +array_in_safe(FmgrInfo *array_in, const char *s, Oid typid, int32 typmod, + AttrNumber exprnum, const char *element_name, bool *ok) +{ + LOCAL_FCINFO(fcinfo, 3); + Datum result; + + ErrorSaveContext escontext = { + .type = T_ErrorSaveContext, + .details_wanted = true + }; + + *ok = false; + 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); + + /* + * If the array_in function returned an error, we will want to report that + * ERROR as a WARNING, and add some location context to the error message. + * Overwriting the existing hint (if any) is not ideal, and an error + * context would only work for level >= ERROR. + */ + if (escontext.error_occurred) + { + StringInfoData hint_str; + + initStringInfo(&hint_str); + appendStringInfo(&hint_str, + "Element \"%s\" in expression %d could not be parsed.", + element_name, exprnum); + escontext.error_data->elevel = WARNING; + escontext.error_data->hint = hint_str.data; + ThrowErrorData(escontext.error_data); + pfree(hint_str.data); + return (Datum) 0; + } + + if (array_contains_nulls(DatumGetArrayTypeP(result))) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not import element \"%s\" in expression %d: null value found", + element_name, exprnum)); + return (Datum) 0; + } + + *ok = true; + return result; +} + +/* + * Create a pg_statistic tuple from an expression JSONB container. + * + * The pg_statistic tuple is pre-populated with acceptable defaults, therefore + * even if there is an issue with all of the keys in the container, we can + * still return a legit tuple datum. + * + * Set pg_statistic_ok to true if all of the values found in the container + * were imported without issue. Any invalid input will set pg_statistic_ok + * to false. + */ +static Datum +import_pg_statistic(Relation pgsd, JsonbContainer *cont, + AttrNumber exprnum, FmgrInfo *array_in_fn, + Oid typid, int32 typmod, Oid typcoll, + bool *pg_statistic_ok) +{ + const char *argname = extarginfo[EXPRESSIONS_ARG].argname; + TypeCacheEntry *typcache; + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + bool replaces[Natts_pg_statistic]; + HeapTuple pgstup = NULL; + Datum pgstdat = (Datum) 0; + Oid elemtypid = InvalidOid; + Oid elemeqopr = InvalidOid; + bool found[NUM_ATTRIBUTE_STATS_ELEMS] = {0}; + JsonbValue val[NUM_ATTRIBUTE_STATS_ELEMS] = {0}; + + /* Assume the worst by default. */ + *pg_statistic_ok = false; + + if (!JsonContainerIsObject(cont)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element in expression %d", + argname, exprnum)); + goto pg_statistic_error; + } + + /* + * Loop through all keys that we need to look up. If any value found is + * neither a string nor a NULL, there is not much we can do, so just give + * on the entire tuple for this expression. + */ + for (int i = 0; i < NUM_ATTRIBUTE_STATS_ELEMS; i++) + { + const char *s = extexprargname[i]; + int len = strlen(s); + + if (getKeyJsonValueFromContainer(cont, s, len, &val[i]) == NULL) + continue; + + switch (val[i].type) + { + case jbvString: + found[i] = true; + break; + + case jbvNull: + break; + + default: + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element in expression %d", argname, exprnum), + errhint("Value of element \"%s\" must be type a null or a string.", + s, exprnum)); + goto pg_statistic_error; + } + } + + /* Look for invalid keys */ + if (!check_all_expr_argnames_valid(cont, exprnum)) + goto pg_statistic_error; + + /* + * There are two arg pairs, MCV+MCF and MCEV+MCEF. Both values must either + * be found or not be found. Any disagreement is a warning. Once we have + * ruled out disagreeing pairs, we can use either found flag as a proxy + * for the other. + */ + if (found[MOST_COMMON_VALS_ELEM] != found[MOST_COMMON_FREQS_ELEM]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element in expression %d", + argname, exprnum), + errhint("\"%s\" and \"%s\" must be both either strings or nulls.", + extexprargname[MOST_COMMON_VALS_ELEM], + extexprargname[MOST_COMMON_FREQS_ELEM], exprnum)); + goto pg_statistic_error; + } + if (found[MOST_COMMON_ELEMS_ELEM] != found[MOST_COMMON_ELEM_FREQS_ELEM]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element in expression %d", + argname, exprnum), + errhint("\"%s\" and \"%s\" must be both either strings or nulls.", + extexprargname[MOST_COMMON_ELEMS_ELEM], + extexprargname[MOST_COMMON_ELEM_FREQS_ELEM], exprnum)); + goto pg_statistic_error; + } + + /* This finds the right operators even if atttypid is a domain */ + typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR); + + statatt_init_empty_tuple(InvalidOid, InvalidAttrNumber, false, + values, nulls, replaces); + + /* + * Special case: collation for tsvector is DEFAULT_COLLATION_OID. See + * compute_tsvector_stats(). + */ + if (typid == TSVECTOROID) + typcoll = DEFAULT_COLLATION_OID; + + /* + * We only need to fetch element type and eq operator if we have a stat of + * type MCELEM or DECHIST, otherwise the values are unnecessary and not + * meaningful. + */ + if (found[MOST_COMMON_ELEMS_ELEM] || found[ELEM_COUNT_HISTOGRAM_ELEM]) + { + if (!statatt_get_elem_type(typid, typcache->typtype, + &elemtypid, &elemeqopr)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element type in expression %d", + argname, exprnum)); + goto pg_statistic_error; + } + } + + /* null_frac */ + if (found[NULL_FRAC_ELEM]) + { + Datum datum; + + if (jbv_to_infunc_datum(&val[NULL_FRAC_ELEM], float4in, exprnum, + extexprargname[NULL_FRAC_ELEM], &datum)) + values[Anum_pg_statistic_stanullfrac - 1] = datum; + else + goto pg_statistic_error; + } + + /* avg_width */ + if (found[AVG_WIDTH_ELEM]) + { + Datum datum; + + if (jbv_to_infunc_datum(&val[AVG_WIDTH_ELEM], int4in, exprnum, + extexprargname[AVG_WIDTH_ELEM], &datum)) + values[Anum_pg_statistic_stawidth - 1] = datum; + else + goto pg_statistic_error; + } + + /* n_distinct */ + if (found[N_DISTINCT_ELEM]) + { + Datum datum; + + if (jbv_to_infunc_datum(&val[N_DISTINCT_ELEM], float4in, exprnum, + extexprargname[N_DISTINCT_ELEM], &datum)) + values[Anum_pg_statistic_stadistinct - 1] = datum; + else + goto pg_statistic_error; + } + + /* + * The STAKIND statistics are the same as the ones found in attribute + * stats. However, these are all derived from json strings, whereas the + * ones derived for attribute stats are a mix of datatypes. This limits + * the opportunities for code sharing between the two. + * + * Some statistic kinds have both a stanumbers and a stavalues components. + * In those cases, both values must either be NOT NULL or both NULL, and + * if they aren't then we need to reject that stakind completely. + * Currently we go a step further and reject the expression array + * completely. + * + * Once it is established that the pairs are in NULL/NOT-NULL alignment, + * we can test either expr_nulls[] value to see if the stakind has + * value(s) that we can set or not. + */ + + if (found[MOST_COMMON_VALS_ELEM]) + { + Datum stavalues; + Datum stanumbers; + bool val_ok = false; + bool num_ok = false; + char *s; + + s = jbv_string_get_cstr(&val[MOST_COMMON_VALS_ELEM]); + stavalues = array_in_safe(array_in_fn, s, typid, typmod, exprnum, + extexprargname[MOST_COMMON_VALS_ELEM], + &val_ok); + + pfree(s); + + s = jbv_string_get_cstr(&val[MOST_COMMON_FREQS_ELEM]); + stanumbers = array_in_safe(array_in_fn, s, FLOAT4OID, -1, exprnum, + extexprargname[MOST_COMMON_FREQS_ELEM], + &num_ok); + pfree(s); + + /* Only set the slot if both datums have been built */ + if (val_ok && num_ok) + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_MCV, + typcache->eq_opr, typcoll, + stanumbers, false, stavalues, false); + else + goto pg_statistic_error; + } + + /* STATISTIC_KIND_HISTOGRAM */ + if (found[HISTOGRAM_BOUNDS_ELEM]) + { + Datum stavalues; + bool val_ok = false; + char *s = jbv_string_get_cstr(&val[HISTOGRAM_BOUNDS_ELEM]); + + stavalues = array_in_safe(array_in_fn, s, typid, typmod, exprnum, + extexprargname[HISTOGRAM_BOUNDS_ELEM], + &val_ok); + pfree(s); + + if (val_ok) + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_HISTOGRAM, + typcache->lt_opr, typcoll, + 0, true, stavalues, false); + else + goto pg_statistic_error; + } + + /* STATISTIC_KIND_CORRELATION */ + if (found[CORRELATION_ELEM]) + { + Datum corr[] = {(Datum) 0}; + + if (jbv_to_infunc_datum(&val[CORRELATION_ELEM], float4in, exprnum, + extexprargname[CORRELATION_ELEM], &corr[0])) + { + ArrayType *arry = construct_array_builtin(corr, 1, FLOAT4OID); + Datum stanumbers = PointerGetDatum(arry); + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_CORRELATION, + typcache->lt_opr, typcoll, + stanumbers, false, 0, true); + } + else + goto pg_statistic_error; + } + + /* STATISTIC_KIND_MCELEM */ + if (found[MOST_COMMON_ELEMS_ELEM]) + { + Datum stavalues; + Datum stanumbers; + bool val_ok = false; + bool num_ok = false; + char *s; + + s = jbv_string_get_cstr(&val[MOST_COMMON_ELEMS_ELEM]); + stavalues = array_in_safe(array_in_fn, s, elemtypid, typmod, exprnum, + extexprargname[MOST_COMMON_ELEMS_ELEM], + &val_ok); + pfree(s); + + + s = jbv_string_get_cstr(&val[MOST_COMMON_ELEM_FREQS_ELEM]); + stanumbers = array_in_safe(array_in_fn, s, FLOAT4OID, -1, exprnum, + extexprargname[MOST_COMMON_ELEM_FREQS_ELEM], + &num_ok); + pfree(s); + + /* Only set the slot if both datums have been built */ + if (val_ok && num_ok) + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_MCELEM, + elemeqopr, typcoll, + stanumbers, false, stavalues, false); + else + goto pg_statistic_error; + } + + /* STATISTIC_KIND_DECHIST */ + if (found[ELEM_COUNT_HISTOGRAM_ELEM]) + { + Datum stanumbers; + bool num_ok = false; + char *s; + + s = jbv_string_get_cstr(&val[ELEM_COUNT_HISTOGRAM_ELEM]); + stanumbers = array_in_safe(array_in_fn, s, FLOAT4OID, -1, exprnum, + extexprargname[ELEM_COUNT_HISTOGRAM_ELEM], + &num_ok); + pfree(s); + + if (num_ok) + statatt_set_slot(values, nulls, replaces, STATISTIC_KIND_DECHIST, + elemeqopr, typcoll, stanumbers, false, 0, true); + else + goto pg_statistic_error; + } + + /* + * Currently there is no extended stats export of the statistic kinds + * BOUNDS_HISTOGRAM or RANGE_LENGTH_HISTOGRAM so these cannot be imported. + * These may be added in the future. + */ + pgstup = heap_form_tuple(RelationGetDescr(pgsd), values, nulls); + pgstdat = heap_copy_tuple_as_datum(pgstup, RelationGetDescr(pgsd)); + + pfree(pgstup); + + *pg_statistic_ok = true; + + return pgstdat; + +pg_statistic_error: + return (Datum) 0; +} + +/* + * Create the stxdexpr datum, which is an array of pg_statistic rows with all + * of the object identification fields left at defaults, using the json array + * of objects/nulls,referenced against the datatypes for the expressions. + * + * The exprs_is_perfect will be set to true if all pg_statistic rows were + * able to import cleanly. If any of them experienced a problem (and thus were + * set as if they were null), then the expression is kept but exprs_is_perfect + * will be marked false. + * + * This datum is needed to fill out a complete pg_statistic_ext_data tuple. + */ +static Datum +import_expressions(Relation pgsd, int numexprs, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, Jsonb *exprs_jsonb, + bool *exprs_is_perfect) +{ + const char *argname = extarginfo[EXPRESSIONS_ARG].argname; + Oid pgstypoid = get_rel_type_id(StatisticRelationId); + ArrayBuildState *astate = NULL; + Datum result = (Datum) 0; + int num_import_ok = 0; + JsonbContainer *root; + int num_root_elements; + + FmgrInfo array_in_fn; + + *exprs_is_perfect = false; + + /* Json schema must be [{expr},...] */ + if (!JB_ROOT_IS_ARRAY(exprs_jsonb)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": root-level array required", argname)); + goto exprs_error; + } + + root = &exprs_jsonb->root; + + /* + * The number of elements in the array must match the number of + * expressions in the stats object definition. + */ + num_root_elements = JsonContainerSize(root); + if (numexprs != num_root_elements) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": incorrect number of elements (%d required)", + argname, num_root_elements)); + goto exprs_error; + } + + fmgr_info(F_ARRAY_IN, &array_in_fn); + + /* + * Iterate over each expected expression object in the array. Some of + * them could be null. If the element is a completely wrong data type, + * give a WARNING and then treat the element like a NULL element in the + * result array. + * + * Each expression *MUST* have a value appended in the result pg_statistic + * array. + */ + for (int i = 0; i < numexprs; i++) + { + Datum pgstdat = (Datum) 0; + bool isnull = false; + AttrNumber exprattnum = -1 - i; + + JsonbValue *elem = getIthJsonbValueFromContainer(root, i); + + switch (elem->type) + { + case jbvBinary: + { + bool sta_ok = false; + + /* a real stats object */ + pgstdat = import_pg_statistic(pgsd, elem->val.binary.data, + exprattnum, &array_in_fn, + atttypids[i], atttypmods[i], + atttypcolls[i], &sta_ok); + + /* + * If some incorrect data has been found, assign NULL for + * this expression as a mean to give up. + */ + if (sta_ok) + num_import_ok++; + else + { + isnull = true; + pgstdat = (Datum) 0; + } + } + break; + + case jbvNull: + /* a placeholder, still fine */ + isnull = true; + num_import_ok++; + break; + + default: + /* cannot possibly be valid */ + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse \"%s\": invalid element in expression %d", + argname, exprattnum)); + goto exprs_error; + } + + astate = accumArrayResult(astate, pgstdat, isnull, pgstypoid, + CurrentMemoryContext); + } + + /* + * The expressions datum is perfect *if and only* if all of the + * pg_statistic elements were also ok, for a number of elements equal to + * the number of expressions. Anything else means a failure in restoring + * the data of this statistics object. + */ + *exprs_is_perfect = (num_import_ok == numexprs); + + if (astate != NULL) + result = makeArrayResult(astate, CurrentMemoryContext); + + return result; + +exprs_error: + if (astate != NULL) + pfree(astate); + return (Datum) 0; +}; + /* * Remove an existing pg_statistic_ext_data row for a given pg_statistic_ext * row and "inherited" pair. diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 2bebefd0ba21..293aee02866f 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -18652,11 +18652,48 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) if (fout->remoteVersion >= 130000) appendPQExpBufferStr(pq, "e.most_common_vals, e.most_common_freqs, " - "e.most_common_base_freqs "); + "e.most_common_base_freqs, "); else appendPQExpBufferStr(pq, "NULL AS most_common_vals, NULL AS most_common_freqs, " - "NULL AS most_common_base_freqs "); + "NULL AS most_common_base_freqs, "); + + /* Expressions were introduced in v14 */ + if (fout->remoteVersion >= 140000) + { + /* + * There is no ordering column in pg_stats_ext_exprs. However, we + * can rely on the unnesting of pg_statistic.ext_data.stxdexpr to + * maintain the desired order of expression elements. + */ + appendPQExpBufferStr(pq, + "( " + "SELECT jsonb_pretty(jsonb_agg(" + "nullif(j.obj, '{}'::jsonb))) " + "FROM pg_stats_ext_exprs AS ee " + "CROSS JOIN LATERAL jsonb_strip_nulls(" + " jsonb_build_object( " + " 'null_frac', ee.null_frac::text, " + " 'avg_width', ee.avg_width::text, " + " 'n_distinct', ee.n_distinct::text, " + " 'most_common_vals', ee.most_common_vals::text, " + " 'most_common_freqs', ee.most_common_freqs::text, " + " 'histogram_bounds', ee.histogram_bounds::text, " + " 'correlation', ee.correlation::text, " + " 'most_common_elems', ee.most_common_elems::text, " + " 'most_common_elem_freqs', ee.most_common_elem_freqs::text, " + " 'elem_count_histogram', ee.elem_count_histogram::text " + " )) AS j(obj)" + "WHERE ee.statistics_schemaname = $1 " + "AND ee.statistics_name = $2 "); + /* Inherited expressions introduced in v15 */ + if (fout->remoteVersion >= 150000) + appendPQExpBufferStr(pq, "AND ee.inherited = e.inherited"); + + appendPQExpBufferStr(pq, ") AS exprs "); + } + else + appendPQExpBufferStr(pq, "NULL AS exprs "); /* pg_stats_ext introduced in v12 */ if (fout->remoteVersion >= 120000) @@ -18710,6 +18747,7 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) int i_mcv = PQfnumber(res, "most_common_vals"); int i_mcf = PQfnumber(res, "most_common_freqs"); int i_mcbf = PQfnumber(res, "most_common_base_freqs"); + int i_exprs = PQfnumber(res, "exprs"); for (int i = 0; i < nstats; i++) { @@ -18757,6 +18795,10 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) appendNamedArgument(out, fout, "most_common_base_freqs", "double precision[]", PQgetvalue(res, i, i_mcbf)); + if (!PQgetisnull(res, i, i_exprs)) + appendNamedArgument(out, fout, "exprs", "jsonb", + PQgetvalue(res, i, i_exprs)); + appendPQExpBufferStr(out, "\n);\n"); } diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 37131f9ceaba..bb2b9abe9a0b 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -2155,8 +2155,8 @@ SELECT pg_catalog.pg_restore_extended_stats( {red,"{[11,13),[15,19),[20,30)}","{[11,13),[15,19),[20,30),[10000,10200)}"}, {red,"{[21,23),[25,29),[120,130)}","{[21,23),[25,29),[120,130),[10000,10200)}"}}'::text[], 'most_common_freqs', '{0.3333333333333333,0.3333333333333333,0.3333333333333333}'::double precision[], - 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[] -); + 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[], + 'exprs', '[{ "avg_width": "60", "null_frac": "0", "n_distinct": "-1" }]'::jsonb); pg_restore_extended_stats --------------------------- t @@ -2191,6 +2191,789 @@ most_common_val_nulls | {{f,f,f},{f,f,f},{f,f,f}} most_common_freqs | {0.3333333333333333,0.3333333333333333,0.3333333333333333} most_common_base_freqs | {0.1111111111111111,0.1111111111111111,0.1111111111111111} +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_mr_stat' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+--------------------------------------------- +expr | (mrange + '{[10000,10200)}'::int4multirange) +null_frac | 0 +avg_width | 60 +n_distinct | -1 +most_common_vals | +most_common_freqs | +histogram_bounds | +correlation | +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | + +-- Incorrect extended stats kind, exprs not supported +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_ndistinct', + 'inherited', false, + 'exprs', '[ { "avg_width": "4" } ]'::jsonb); +WARNING: cannot specify parameter "exprs" +HINT: Extended statistics object "stats_import"."test_stat_ndistinct" does not support statistics of this type. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- Invalid exprs, not an array +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '{ "avg_width": "4", "null_frac": "0" }'::jsonb); +WARNING: could not parse "exprs": root-level array required + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- wrong number of exprs +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "avg_width": "4" } ]'::jsonb); +WARNING: could not parse "exprs": incorrect number of elements (1 required) + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- incorrect type of value: should be a string or a NULL. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "null_frac": 1 }, + { "null_frac": "0.25" } ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: Value of element "null_frac" must be type a null or a string. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs null_frac not a float +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "null_frac": "BADNULLFRAC" }, + { "null_frac": "0.25" } ]'::jsonb); +WARNING: invalid input syntax for type real: "BADNULLFRAC" +HINT: Element "null_frac" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs avg_width not an integer +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "avg_width": "BADAVGWIDTH" }, + { "avg_width": "4" } ]'::jsonb); +WARNING: invalid input syntax for type integer: "BADAVGWIDTH" +HINT: Element "avg_width" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs n_dinstinct not a float +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "n_distinct": "BADNDISTINCT" }, + { "n_distinct": "-0.5" } ]'::jsonb); +WARNING: invalid input syntax for type real: "BADNDISTINCT" +HINT: Element "n_distinct" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCV not null, MCF null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": "{1}", "most_common_elems": null }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_vals" and "most_common_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCV not null, MCF missing +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": "{1}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_vals" and "most_common_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCV null, MCF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": null, "most_common_freqs": "{0.5}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_vals" and "most_common_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCV missing, MCF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_freqs": "{0.5}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_vals" and "most_common_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs most_common_vals element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{BADMCV}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); +WARNING: invalid input syntax for type integer: "BADMCV" +HINT: Element "most_common_vals" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs most_common_freqs element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": "{1}", "most_common_freqs": "{BADMCF}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +WARNING: invalid input syntax for type real: "BADMCF" +HINT: Element "most_common_freqs" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs histogram wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "histogram_bounds": "{BADHIST,0}" }, + { "histogram_bounds": null } + ]'::jsonb); +WARNING: invalid input syntax for type integer: "BADHIST" +HINT: Element "histogram_bounds" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs correlation wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "correlation": "BADCORR" }, + { "correlation": "1" } + ]'::jsonb); +WARNING: invalid input syntax for type real: "BADCORR" +HINT: Element "correlation" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- invalid element type in array +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[1, null]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- invalid element in array, as valid jbvBinary. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[null, [{"avg_width" : [1]}]]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -2 + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok: exprs first null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + null, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+---------------------- +expr | lower(arange) +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 | +-[ RECORD 2 ]----------+---------------------- +expr | array_length(tags, 1) +null_frac | 0.25 +avg_width | 4 +n_distinct | -0.5 +most_common_vals | {2} +most_common_freqs | {0.5} +histogram_bounds | +correlation | 1 +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | + +-- ok: exprs last null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{1}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + null + ]'::jsonb); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+---------------------- +expr | lower(arange) +null_frac | 0 +avg_width | 4 +n_distinct | -0.75 +most_common_vals | {1} +most_common_freqs | {0.5} +histogram_bounds | {-1,0} +correlation | -0.6 +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | +-[ RECORD 2 ]----------+---------------------- +expr | array_length(tags, 1) +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 | + +-- ok: both exprs +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{1}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+---------------------- +expr | lower(arange) +null_frac | 0 +avg_width | 4 +n_distinct | -0.75 +most_common_vals | {1} +most_common_freqs | {0.5} +histogram_bounds | {-1,0} +correlation | -0.6 +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | +-[ RECORD 2 ]----------+---------------------- +expr | array_length(tags, 1) +null_frac | 0.25 +avg_width | 4 +n_distinct | -0.5 +most_common_vals | {2} +most_common_freqs | {0.5} +histogram_bounds | +correlation | 1 +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | + +-- A statistics object for testing MCELEM values in expressions +CREATE STATISTICS stats_import.test_stat_mcelem + ON name, (ARRAY[(comp).a, lower(arange)]) + FROM stats_import.test; +-- MCEV not null, MCEF null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,0,1,2,3}", + "most_common_elem_freqs": null + } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_elems" and "most_common_elem_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCEV not null, MCEF missing +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,0,1,2,3}" + } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_elems" and "most_common_elem_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCEV null, MCEF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": null, + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_elems" and "most_common_elem_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- MCEV missing, MCEF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +WARNING: could not parse "exprs": invalid element in expression -1 +HINT: "most_common_elems" and "most_common_elem_freqs" must be both either strings or nulls. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs most_common_elems element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,BADELEM,1,2,3}", + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +WARNING: invalid input syntax for type integer: "BADELEM" +HINT: Element "most_common_elems" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs most_common_elem_freqs element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,0,1,2,3}", + "most_common_elem_freqs": "{BADELEMFREQ,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +WARNING: invalid input syntax for type real: "BADELEMFREQ" +HINT: Element "most_common_elem_freqs" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- exprs histogram bounds element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "elem_count_histogram": "{BADELEMHIST,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5}" + } + ]'::jsonb); +WARNING: invalid input syntax for type real: "BADELEMHIST" +HINT: Element "elem_count_histogram" in expression -1 could not be parsed. + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok: exprs mcelem +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "33", + "null_frac": "0", + "n_distinct": "-1", + "correlation": "1", + "histogram_bounds": "{\"{1,1}\",\"{2,1}\",\"{3,-1}\",\"{NULL,0}\"}", + "most_common_vals": null, + "most_common_elems": "{-1,0,1,2,3}", + "most_common_freqs": null, + "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,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5}", + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_mcelem' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +expr | ARRAY[(comp).a, lower(arange)] +null_frac | 0 +avg_width | 33 +n_distinct | -1 +most_common_vals | +most_common_freqs | +histogram_bounds | {"{1,1}","{2,1}","{3,-1}","{NULL,0}"} +correlation | 1 +most_common_elems | {-1,0,1,2,3} +most_common_elem_freqs | {0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25} +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,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5} + +-- ok, with warning: extra exprs param +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "33", + "null_frac": "0", + "n_distinct": "-1", + "correlation": "1", + "histogram_bounds": "{\"{1,1}\",\"{2,1}\",\"{3,-1}\",\"{NULL,0}\"}", + "most_common_vals": null, + "most_common_elems": "{-1,0,1,2,3}", + "most_common_freqs": null, + "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,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5}", + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}", + "bad_param": "text no one will ever parse" + } + ]'::jsonb); +WARNING: could not import element in expression -1: invalid key name + pg_restore_extended_stats +--------------------------- + f +(1 row) + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_mcelem' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +expr | ARRAY[(comp).a, lower(arange)] +null_frac | 0 +avg_width | 33 +n_distinct | -1 +most_common_vals | +most_common_freqs | +histogram_bounds | {"{1,1}","{2,1}","{3,-1}","{NULL,0}"} +correlation | 1 +most_common_elems | {-1,0,1,2,3} +most_common_elem_freqs | {0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25} +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,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5} + +-- ok: tsvector exceptions, test just the collation exceptions +CREATE STATISTICS stats_import.test_stat_tsvec ON (length(name)), (to_tsvector(name)) FROM stats_import.test; +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_tsvec', + 'inherited', false, + 'exprs', '[null, + { + "most_common_elems": "{one,tre,two,four}", + "most_common_elem_freqs": "{0.25,0.25,0.25,0.25,0.25,0.25}" + } + ]'::jsonb); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_tsvec' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+-------------------------------- +expr | length(name) +most_common_elems | +most_common_elem_freqs | +-[ RECORD 2 ]----------+-------------------------------- +expr | to_tsvector(name) +most_common_elems | {one,tre,two,four} +most_common_elem_freqs | {0.25,0.25,0.25,0.25,0.25,0.25} + -- Test the ability of pg_restore_extended_stats() to import all of the -- statistic values from an extended statistic object that has been -- populated via a regular ANALYZE. This checks after the statistics diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 8db7cd93b88c..93ca7a526684 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1543,8 +1543,8 @@ SELECT pg_catalog.pg_restore_extended_stats( {red,"{[11,13),[15,19),[20,30)}","{[11,13),[15,19),[20,30),[10000,10200)}"}, {red,"{[21,23),[25,29),[120,130)}","{[21,23),[25,29),[120,130),[10000,10200)}"}}'::text[], 'most_common_freqs', '{0.3333333333333333,0.3333333333333333,0.3333333333333333}'::double precision[], - 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[] -); + 'most_common_base_freqs', '{0.1111111111111111,0.1111111111111111,0.1111111111111111}'::double precision[], + 'exprs', '[{ "avg_width": "60", "null_frac": "0", "n_distinct": "-1" }]'::jsonb); SELECT replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, replace(e.dependencies, '}, ', E'},\n') AS dependencies, @@ -1557,6 +1557,482 @@ WHERE e.statistics_schemaname = 'stats_import' AND e.inherited = false \gx +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_mr_stat' AND + e.inherited = false +\gx + +-- Incorrect extended stats kind, exprs not supported +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_ndistinct', + 'inherited', false, + 'exprs', '[ { "avg_width": "4" } ]'::jsonb); + +-- Invalid exprs, not an array +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '{ "avg_width": "4", "null_frac": "0" }'::jsonb); +-- wrong number of exprs +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "avg_width": "4" } ]'::jsonb); +-- incorrect type of value: should be a string or a NULL. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "null_frac": 1 }, + { "null_frac": "0.25" } ]'::jsonb); +-- exprs null_frac not a float +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "null_frac": "BADNULLFRAC" }, + { "null_frac": "0.25" } ]'::jsonb); +-- exprs avg_width not an integer +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "avg_width": "BADAVGWIDTH" }, + { "avg_width": "4" } ]'::jsonb); +-- exprs n_dinstinct not a float +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "n_distinct": "BADNDISTINCT" }, + { "n_distinct": "-0.5" } ]'::jsonb); +-- MCV not null, MCF null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": "{1}", "most_common_elems": null }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +-- MCV not null, MCF missing +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": "{1}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +-- MCV null, MCF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": null, "most_common_freqs": "{0.5}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +-- MCV missing, MCF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_freqs": "{0.5}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +-- exprs most_common_vals element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{BADMCV}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); +-- exprs most_common_freqs element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "most_common_vals": "{1}", "most_common_freqs": "{BADMCF}" }, + { "most_common_vals": "{2}", "most_common_freqs": "{0.5}" } + ]'::jsonb); +-- exprs histogram wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { "histogram_bounds": "{BADHIST,0}" }, + { "histogram_bounds": null } + ]'::jsonb); +-- exprs correlation wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ { "correlation": "BADCORR" }, + { "correlation": "1" } + ]'::jsonb); +-- invalid element type in array +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[1, null]'::jsonb); +-- invalid element in array, as valid jbvBinary. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[null, [{"avg_width" : [1]}]]'::jsonb); +-- ok: exprs first null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + null, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx +-- ok: exprs last null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{1}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + null + ]'::jsonb); + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx +-- ok: both exprs +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{1}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx + +-- A statistics object for testing MCELEM values in expressions +CREATE STATISTICS stats_import.test_stat_mcelem + ON name, (ARRAY[(comp).a, lower(arange)]) + FROM stats_import.test; + +-- MCEV not null, MCEF null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,0,1,2,3}", + "most_common_elem_freqs": null + } + ]'::jsonb); +-- MCEV not null, MCEF missing +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,0,1,2,3}" + } + ]'::jsonb); +-- MCEV null, MCEF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": null, + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +-- MCEV missing, MCEF not null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +-- exprs most_common_elems element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,BADELEM,1,2,3}", + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +-- exprs most_common_elem_freqs element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "most_common_elems": "{-1,0,1,2,3}", + "most_common_elem_freqs": "{BADELEMFREQ,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); +-- exprs histogram bounds element wrong type +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "elem_count_histogram": "{BADELEMHIST,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5}" + } + ]'::jsonb); +-- ok: exprs mcelem +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "33", + "null_frac": "0", + "n_distinct": "-1", + "correlation": "1", + "histogram_bounds": "{\"{1,1}\",\"{2,1}\",\"{3,-1}\",\"{NULL,0}\"}", + "most_common_vals": null, + "most_common_elems": "{-1,0,1,2,3}", + "most_common_freqs": null, + "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,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5}", + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}" + } + ]'::jsonb); + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_mcelem' AND + e.inherited = false +\gx + +-- ok, with warning: extra exprs param +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_mcelem', + 'inherited', false, + 'exprs', '[ + { + "avg_width": "33", + "null_frac": "0", + "n_distinct": "-1", + "correlation": "1", + "histogram_bounds": "{\"{1,1}\",\"{2,1}\",\"{3,-1}\",\"{NULL,0}\"}", + "most_common_vals": null, + "most_common_elems": "{-1,0,1,2,3}", + "most_common_freqs": null, + "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,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.5}", + "most_common_elem_freqs": "{0.25,0.25,0.5,0.25,0.25,0.25,0.5,0.25}", + "bad_param": "text no one will ever parse" + } + ]'::jsonb); + +SELECT e.expr, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_mcelem' AND + e.inherited = false +\gx + +-- ok: tsvector exceptions, test just the collation exceptions +CREATE STATISTICS stats_import.test_stat_tsvec ON (length(name)), (to_tsvector(name)) FROM stats_import.test; +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_tsvec', + 'inherited', false, + 'exprs', '[null, + { + "most_common_elems": "{one,tre,two,four}", + "most_common_elem_freqs": "{0.25,0.25,0.25,0.25,0.25,0.25}" + } + ]'::jsonb); +SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs +FROM pg_stats_ext_exprs AS e +WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_tsvec' AND + e.inherited = false +\gx + -- Test the ability of pg_restore_extended_stats() to import all of the -- statistic values from an extended statistic object that has been -- populated via a regular ANALYZE. This checks after the statistics diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index 3ac81905d1f1..17319f9f969a 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2198,12 +2198,39 @@ SELECT pg_restore_attribute_stats( <structname>myschema.mystatsobj</structname>: <programlisting> SELECT pg_restore_extended_stats( - 'schemaname', 'tab_schema'::name, - 'relname', 'tab_name'::name, - 'statistics_schemaname', 'stats_schema'::name, - 'statistics_name', 'stats_name'::name, + 'schemaname', 'tab_schema', + 'relname', 'tab_name', + 'statistics_schemaname', 'stats_schema', + 'statistics_name', 'stats_name', 'inherited', false, 'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}]'::pg_ndistinct); + 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000}'::pg_dependencies, + 'exprs', '[ + { + "avg_width": "4", + "null_frac": "0.5", + "n_distinct": "-0.75", + "correlation": "-0.6", + "histogram_bounds": "{-1,0}", + "most_common_vals": "{1}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + }, + { + "avg_width": "4", + "null_frac": "0.25", + "n_distinct": "-0.5", + "correlation": "1", + "histogram_bounds": null, + "most_common_vals": "{2}", + "most_common_elems": null, + "most_common_freqs": "{0.5}", + "elem_count_histogram": null, + "most_common_elem_freqs": null + } + ]'::jsonb); </programlisting> </para> <para> @@ -2226,6 +2253,13 @@ SELECT pg_restore_attribute_stats( <literal>dependencies</literal>, <literal>most_common_vals</literal>, <literal>most_common_freqs</literal>, and <literal>most_common_base_freqs</literal>. + To accept statistics for any expressions in the extended + statistics object, the parameter <literal>exprs</literal> with a type + <type>jsonb</type> is available. This should be an one-dimension array + with a number of expressions matching the definition of the extended + statistics object, made of json elements for each of the statistical + columns in + <link linkend="view-pg-stats-ext-exprs"><structname>pg_stats_ext_exprs</structname></link>. </para> <para> Additionally, this function accepts argument name -- 2.52.0
signature.asc
Description: PGP signature
