On Mon, Jan 19, 2026 at 03:11:03PM +0900, Michael Paquier wrote: > (Still looking at the patch in more details now, putting my hands on > it..)
Attached is a v28, with a set of comments that I am letting up to
the author to address.
The patch set has a clear deficiency in test coverage: a lot of code
where we expect failures are totally uncovered, particularly things
related to MCV lists and parameters, MCV array checks, dimensions,
incorrect number of elements, expression imports, etc. There is a
large number of them. I'd expect all of these cases to have proper
coverage, with WARNINGs generated as we don't want hard failures on
these calls, do we? Some more validation for ndistinct and
dependencies would be welcome, as well.
Not surprising, but the patch is weak under trash inputs. I've
quickly come up with a test case that crashes the import, extracted
from a different case (see the POOHHA, that's the problematic value):
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
'relname', 'test_clone',
'statistics_schemaname', 'stats_import',
'statistics_name', 'test_stat_clone',
'inherited', false,
'most_common_vals',
'{{four,NULL,0,NULL},{one,"(POOHHA,1.1,ONE,01-01-2001,\"{\"\"xkey\"\":
\"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true,
4, \"\"six\"\"]\")",1,2}}'::text[],
'most_common_val_nulls',
'{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[],
'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[],
'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double
precision[]);
This has to be stable, meaning that we *must* be able to handle and
reject trash input data, and also relates to the lack of coverage
overall. I am pretty sure that if I begin to inject more buggy values
in other areas of these parameters, things could get funky.
I am wondering if we should not cut the pie into two parts here: the
dependencies and ndistinct data is actually so much more
straight-forward as they have predictive input patterns that we can
discard easiy if they are incorrect. The MCV list part with its
import logic and cross-validation of the inputs is something else
based on the attribute types. I don't think that we are far from a
good solution, still that would be one strategy to get something done
if we cannot get the MCV part completely right.
A lot of the code is still largely under-documented, with zero
explanation about pg_restore_extended_stats(), no explanation about
the individual fields that can be set in pg_restore_extended_stats().
It's impossible to understand for a reader what statext_mcv_import()
does based on its inputs and what a caller can give in input.
There is also zero documentation about the handling of the MCV lists,
especially the expectations around nulls, [base] frequences, common
values, aka all the most_common_* parameters. I do feel that we
should have some documentation about the other stxkinds as well, at
least some description of the parameter names, what they can do, or at
least what they refer to in the catalogs. Having this stuff implied
in the code with zero reference in the documentation is like driving a
car blind, and I doubt it's good for one's health.
My modifications have been added as of 0003, for inclusion with the
rest.
--
Michael
From 5232d995f8e55892b3999951785456ab2b1af186 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Fri, 16 Jan 2026 15:41:46 +0900 Subject: [PATCH v28 1/3] Add pg_restore_extended_stats() This function closely mirror its relation and attribute counterparts, but for extended statistics (i.e. CREATE STATISTICS) objects. --- src/include/catalog/pg_proc.dat | 5 + .../statistics/extended_stats_internal.h | 5 + src/backend/statistics/extended_stats_funcs.c | 1044 +++++++++++++++++ src/backend/statistics/mcv.c | 144 +++ src/test/regress/expected/stats_import.out | 673 +++++++++++ src/test/regress/sql/stats_import.sql | 433 +++++++ doc/src/sgml/func/func-admin.sgml | 84 ++ src/tools/pgindent/typedefs.list | 1 + 8 files changed, 2389 insertions(+) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 894b6a1b6d6b..5e5e33f64fcb 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12619,6 +12619,11 @@ proargtypes => 'int4', prosrc => 'gist_translate_cmptype_common' }, # Extended Statistics functions +{ oid => '9947', descr => 'restore statistics on extended statistics object', + proname => 'pg_restore_extended_stats', provariadic => 'any', + proisstrict => 'f', provolatile => 'v', proparallel => 'u', + prorettype => 'bool', proargtypes => 'any', proargmodes => '{v}', + proargnames => '{kwargs}', prosrc => 'pg_restore_extended_stats' }, { oid => '9948', descr => 'clear statistics on extended statistics object', proname => 'pg_clear_extended_stats', proisstrict => 'f', provolatile => 'v', proparallel => 'u', prorettype => 'void', proargtypes => 'text text text text bool', diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index b5003ec242c1..f7cc1f65e0df 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -135,4 +135,9 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root, Selectivity *overlap_basesel, Selectivity *totalsel); +extern Datum import_mcvlist(HeapTuple tup, int elevel, int numattrs, + Oid *atttypids, int32 *atttypmods, Oid *atttypcolls, + int nitems, Datum *mcv_elems, bool *mcv_nulls, + bool *mcv_elem_nulls, float8 *freqs, float8 *base_freqs); + #endif /* EXTENDED_STATS_INTERNAL_H */ diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c index b4b1bf26463a..3622e5729252 100644 --- a/src/backend/statistics/extended_stats_funcs.c +++ b/src/backend/statistics/extended_stats_funcs.c @@ -17,6 +17,7 @@ #include "postgres.h" #include "access/heapam.h" +#include "catalog/pg_collation_d.h" #include "catalog/indexing.h" #include "catalog/namespace.h" #include "catalog/pg_database.h" @@ -24,12 +25,17 @@ #include "catalog/pg_statistic_ext_data.h" #include "miscadmin.h" #include "nodes/makefuncs.h" +#include "nodes/nodeFuncs.h" +#include "optimizer/optimizer.h" +#include "statistics/extended_stats_internal.h" #include "statistics/stat_utils.h" #include "utils/acl.h" +#include "utils/array.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/syscache.h" +#include "utils/typcache.h" /* @@ -42,6 +48,13 @@ enum extended_stats_argnum STATSCHEMA_ARG, STATNAME_ARG, INHERITED_ARG, + NDISTINCT_ARG, + DEPENDENCIES_ARG, + MOST_COMMON_VALS_ARG, + MOST_COMMON_VAL_NULLS_ARG, + MOST_COMMON_FREQS_ARG, + MOST_COMMON_BASE_FREQS_ARG, + EXPRESSIONS_ARG, NUM_EXTENDED_STATS_ARGS, }; @@ -56,13 +69,122 @@ static struct StatsArgInfo extarginfo[] = [STATSCHEMA_ARG] = {"statistics_schemaname", TEXTOID}, [STATNAME_ARG] = {"statistics_name", TEXTOID}, [INHERITED_ARG] = {"inherited", BOOLOID}, + [NDISTINCT_ARG] = {"n_distinct", PG_NDISTINCTOID}, + [DEPENDENCIES_ARG] = {"dependencies", PG_DEPENDENCIESOID}, + [MOST_COMMON_VALS_ARG] = {"most_common_vals", TEXTARRAYOID}, + [MOST_COMMON_VAL_NULLS_ARG] = {"most_common_val_nulls", BOOLARRAYOID}, + [MOST_COMMON_FREQS_ARG] = {"most_common_freqs", FLOAT8ARRAYOID}, + [MOST_COMMON_BASE_FREQS_ARG] = {"most_common_base_freqs", FLOAT8ARRAYOID}, + [EXPRESSIONS_ARG] = {"exprs", TEXTARRAYOID}, [NUM_EXTENDED_STATS_ARGS] = {0}, }; +/* + * An index of the elements of a stxdexprs 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 and typoids of the repeating arguments for stxdexprs. + */ +static struct StatsArgInfo extexprarginfo[] = +{ + [NULL_FRAC_ELEM] = {"null_frac", FLOAT4OID}, + [AVG_WIDTH_ELEM] = {"avg_width", INT4OID}, + [N_DISTINCT_ELEM] = {"n_distinct", FLOAT4OID}, + [MOST_COMMON_VALS_ELEM] = {"most_common_vals", TEXTOID}, + [MOST_COMMON_FREQS_ELEM] = {"most_common_freqs", FLOAT4ARRAYOID}, + [HISTOGRAM_BOUNDS_ELEM] = {"histogram_bounds", TEXTOID}, + [CORRELATION_ELEM] = {"correlation", FLOAT4OID}, + [MOST_COMMON_ELEMS_ELEM] = {"most_common_elems", TEXTOID}, + [MOST_COMMON_ELEM_FREQS_ELEM] = {"most_common_elem_freqs", FLOAT4ARRAYOID}, + [ELEM_COUNT_HISTOGRAM_ELEM] = {"elem_count_histogram", FLOAT4ARRAYOID}, + [NUM_ATTRIBUTE_STATS_ELEMS] = {0} +}; + +static bool extended_statistics_update(FunctionCallInfo fcinfo); + static HeapTuple get_pg_statistic_ext(Relation pg_stext, Oid nspoid, const char *stxname); static bool delete_pg_statistic_ext_data(Oid stxoid, bool inherited); + +typedef struct +{ + bool ndistinct; + bool dependencies; + bool mcv; + bool expressions; +} StakindFlags; + +static void expand_stxkind(HeapTuple tup, StakindFlags *enabled); +static void upsert_pg_statistic_ext_data(const Datum *values, + const bool *nulls, + const bool *replaces); +static bool check_mcvlist_array(ArrayType *arr, int argindex, + int required_ndims, int mcv_length); +static Datum import_expressions(Relation pgsd, int numexprs, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, ArrayType *exprs_arr); +static bool text_to_float4(Datum input, Datum *output); +static bool text_to_int4(Datum input, Datum *output); + + +/* + * Safe conversion of text to float4. + */ +static bool +text_to_float4(Datum input, Datum *output) +{ + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + char *s; + bool ok; + + s = TextDatumGetCString(input); + ok = DirectInputFunctionCallSafe(float4in, s, InvalidOid, -1, + (Node *) &escontext, output); + + pfree(s); + return ok; +} + +/* + * Safe conversion of text to int4. + */ +static bool +text_to_int4(Datum input, Datum *output) +{ + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + char *s; + bool ok; + + s = TextDatumGetCString(input); + ok = DirectInputFunctionCallSafe(int4in, s, InvalidOid, -1, + (Node *) &escontext, output); + + pfree(s); + return ok; +} + /* * Fetch a pg_statistic_ext row by name and namespace OID. */ @@ -109,6 +231,907 @@ get_pg_statistic_ext(Relation pg_stext, Oid nspoid, const char *stxname) return SearchSysCacheCopy1(STATEXTOID, ObjectIdGetDatum(stxoid)); } +/* + * Decode the stxkind column so that we know which stats types to expect. + */ +static void +expand_stxkind(HeapTuple tup, StakindFlags *enabled) +{ + Datum datum; + ArrayType *arr; + char *kinds; + + datum = SysCacheGetAttrNotNull(STATEXTOID, + tup, + Anum_pg_statistic_ext_stxkind); + arr = DatumGetArrayTypeP(datum); + if (ARR_NDIM(arr) != 1 || ARR_HASNULL(arr) || ARR_ELEMTYPE(arr) != CHAROID) + elog(ERROR, "stxkind is not a one-dimension char array"); + + kinds = (char *) ARR_DATA_PTR(arr); + + for (int i = 0; i < ARR_DIMS(arr)[0]; i++) + { + if (kinds[i] == STATS_EXT_NDISTINCT) + enabled->ndistinct = true; + else if (kinds[i] == STATS_EXT_DEPENDENCIES) + enabled->dependencies = true; + else if (kinds[i] == STATS_EXT_MCV) + enabled->mcv = true; + else if (kinds[i] == STATS_EXT_EXPRESSIONS) + enabled->expressions = true; + } +} + +/* + * Perform the actual storage of a pg_statistic_ext_data tuple. + */ +static void +upsert_pg_statistic_ext_data(const Datum *values, const bool *nulls, + const bool *replaces) +{ + Relation pg_stextdata; + HeapTuple stxdtup; + HeapTuple newtup; + + pg_stextdata = table_open(StatisticExtDataRelationId, RowExclusiveLock); + + stxdtup = SearchSysCache2(STATEXTDATASTXOID, + values[Anum_pg_statistic_ext_data_stxoid - 1], + values[Anum_pg_statistic_ext_data_stxdinherit - 1]); + + if (HeapTupleIsValid(stxdtup)) + { + newtup = heap_modify_tuple(stxdtup, + RelationGetDescr(pg_stextdata), + values, + nulls, + replaces); + CatalogTupleUpdate(pg_stextdata, &newtup->t_self, newtup); + ReleaseSysCache(stxdtup); + } + else + { + newtup = heap_form_tuple(RelationGetDescr(pg_stextdata), values, nulls); + CatalogTupleInsert(pg_stextdata, newtup); + } + + heap_freetuple(newtup); + + CommandCounterIncrement(); + + table_close(pg_stextdata, RowExclusiveLock); +} + +/* + * Insert or Update Extended Statistics + * + * Major errors, such as the table not existing, the statistics object not + * existing, or a permissions failure are always reported at ERROR. Other + * errors, such as a conversion failure on one statistic kind, are reported + * as WARNINGs, and other statistic kinds may still be updated. + */ +static bool +extended_statistics_update(FunctionCallInfo fcinfo) +{ + char *relnspname; + char *relname; + Oid nspoid; + char *nspname; + char *stxname; + bool inherited; + Relation pg_stext; + HeapTuple tup = NULL; + + StakindFlags enabled; + StakindFlags has; + + Form_pg_statistic_ext stxform; + + Datum values[Natts_pg_statistic_ext_data]; + bool nulls[Natts_pg_statistic_ext_data]; + bool replaces[Natts_pg_statistic_ext_data]; + bool success = true; + Datum exprdatum; + bool isnull; + List *exprs = NIL; + int numattnums = 0; + int numexprs = 0; + int numattrs = 0; + + /* arrays of type info, if we need them */ + Oid *atttypids = NULL; + int32 *atttypmods = NULL; + Oid *atttypcolls = NULL; + Oid relid; + Oid locked_table = InvalidOid; + + memset(nulls, false, sizeof(nulls)); + memset(values, 0, sizeof(values)); + memset(replaces, 0, sizeof(replaces)); + memset(&enabled, 0, sizeof(enabled)); + + has.mcv = (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) && + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) && + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) && + !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()) + { + ereport(WARNING, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("Statistics cannot be modified during recovery.")); + PG_RETURN_BOOL(false); + } + + /* relation arguments */ + stats_check_required_arg(fcinfo, extarginfo, RELSCHEMA_ARG); + relnspname = TextDatumGetCString(PG_GETARG_DATUM(RELSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, RELNAME_ARG); + relname = TextDatumGetCString(PG_GETARG_DATUM(RELNAME_ARG)); + + /* extended statistics arguments */ + stats_check_required_arg(fcinfo, extarginfo, STATSCHEMA_ARG); + nspname = TextDatumGetCString(PG_GETARG_DATUM(STATSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, STATNAME_ARG); + stxname = TextDatumGetCString(PG_GETARG_DATUM(STATNAME_ARG)); + stats_check_required_arg(fcinfo, extarginfo, INHERITED_ARG); + inherited = PG_GETARG_BOOL(INHERITED_ARG); + + /* + * First open the relation where we expect to find the statistics. This + * is similar to relation and attribute statistics, so as ACL checks are + * done before any locks are taken, even before any attempts related to + * the extended stats object. + */ + relid = RangeVarGetRelidExtended(makeRangeVar(relnspname, relname, -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); + + nspoid = get_namespace_oid(nspname, true); + if (nspoid == InvalidOid) + { + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find schema \"%s\"", stxname)); + PG_RETURN_BOOL(false); + } + + pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock); + tup = get_pg_statistic_ext(pg_stext, nspoid, stxname); + + if (!HeapTupleIsValid(tup)) + { + table_close(pg_stext, RowExclusiveLock); + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find extended statistics object \"%s\".\"%s\"", + get_namespace_name(nspoid), stxname)); + PG_RETURN_BOOL(false); + } + + stxform = (Form_pg_statistic_ext) GETSTRUCT(tup); + + /* + * The relation tracked by the stats object has to match with the relation + * we have already locked. + */ + if (stxform->stxrelid != relid) + { + table_close(pg_stext, RowExclusiveLock); + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not restore extended statistics object \"%s\".\"%s\": incorrect relation \"%s\".\"%s\" specified", + get_namespace_name(nspoid), stxname, + relnspname, relname)); + PG_RETURN_BOOL(false); + } + + expand_stxkind(tup, &enabled); + numattnums = stxform->stxkeys.dim1; + + /* decode expression (if any) */ + exprdatum = SysCacheGetAttr(STATEXTOID, + tup, + Anum_pg_statistic_ext_stxexprs, + &isnull); + + if (!isnull) + { + char *s; + + s = TextDatumGetCString(exprdatum); + exprs = (List *) stringToNode(s); + pfree(s); + + /* + * Run the expressions through eval_const_expressions. This is not + * just an optimization, but is necessary, because the planner will be + * comparing them to similarly-processed qual clauses, and may fail to + * detect valid matches without this. We must not use + * canonicalize_qual, however, since these aren't qual expressions. + */ + exprs = (List *) eval_const_expressions(NULL, (Node *) exprs); + + /* May as well fix opfuncids too */ + fix_opfuncids((Node *) exprs); + } + numexprs = list_length(exprs); + numattrs = numattnums + numexprs; + + if (has.mcv) + { + if (!enabled.mcv) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot specify parameters \"%s\", \"%s\", \"%s\", and \"%s\" for extended statistics object", + extarginfo[MOST_COMMON_VALS_ARG].argname, + extarginfo[MOST_COMMON_VAL_NULLS_ARG].argname, + extarginfo[MOST_COMMON_FREQS_ARG].argname, + extarginfo[MOST_COMMON_BASE_FREQS_ARG].argname)); + has.mcv = false; + success = false; + } + } + else + { + /* The MCV args must all be NULL. */ + if (!PG_ARGISNULL(MOST_COMMON_VALS_ARG) || + !PG_ARGISNULL(MOST_COMMON_VAL_NULLS_ARG) || + !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) || + !PG_ARGISNULL(MOST_COMMON_BASE_FREQS_ARG)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("must specify parameters \"%s\", \"%s\", \"%s\", and \"%s\" for extended statistics object", + extarginfo[MOST_COMMON_VALS_ARG].argname, + extarginfo[MOST_COMMON_VAL_NULLS_ARG].argname, + extarginfo[MOST_COMMON_FREQS_ARG].argname, + extarginfo[MOST_COMMON_BASE_FREQS_ARG].argname)); + success = false; + } + } + + if (has.ndistinct && !enabled.ndistinct) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot specify parameter \"%s\" for extended statistics object", + extarginfo[NDISTINCT_ARG].argname)); + has.ndistinct = false; + success = false; + } + + if (has.dependencies && !enabled.dependencies) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot specify parameter \"%s\" for extended statistics object", + extarginfo[DEPENDENCIES_ARG].argname)); + has.dependencies = false; + success = false; + } + + if (has.expressions && !enabled.expressions) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot specify parameter \"%s\" for extended statistics object", + extarginfo[EXPRESSIONS_ARG].argname)); + has.expressions = false; + success = false; + } + + /* + * Either of these statistic types requires that we supply a semi-filled + * VacAttrStatP array. + * + * It is not possible to use the existing lookup_var_attr_stats() and + * examine_attribute() because these functions will skip attributes where + * attstattarget is 0, and we may have statistics data to import for those + * attributes. + */ + if (has.mcv || has.expressions) + { + atttypids = palloc0_array(Oid, numattrs); + atttypmods = palloc0_array(int32, numattrs); + atttypcolls = palloc0_array(Oid, numattrs); + + for (int i = 0; i < numattnums; i++) + { + AttrNumber attnum = stxform->stxkeys.values[i]; + + Oid lt_opr; + Oid eq_opr; + char typetype; + + /* + * Fetch attribute entries the same way as what is done for + * attribute statistics. + */ + statatt_get_type(stxform->stxrelid, + attnum, + &atttypids[i], + &atttypmods[i], + &typetype, + &atttypcolls[i], + <_opr, + &eq_opr); + } + + for (int i = numattnums; i < numattrs; i++) + { + Node *expr = list_nth(exprs, i - numattnums); + + atttypids[i] = exprType(expr); + atttypmods[i] = exprTypmod(expr); + atttypcolls[i] = exprCollation(expr); + + /* + * If it's a multirange, step down to the range type, as is done + * by multirange_typanalyze(). + */ + if (type_is_multirange(atttypids[i])) + atttypids[i] = get_multirange_range(atttypids[i]); + + /* + * Special case: collation for tsvector is DEFAULT_COLLATION_OID. + * See compute_tsvector_stats(). + */ + if (atttypids[i] == TSVECTOROID) + atttypcolls[i] = DEFAULT_COLLATION_OID; + + } + } + + /* Primary Key: cannot be NULL or replaced. */ + values[Anum_pg_statistic_ext_data_stxoid - 1] = ObjectIdGetDatum(stxform->oid); + values[Anum_pg_statistic_ext_data_stxdinherit - 1] = BoolGetDatum(inherited); + + /* + * For each stats kind, deserialize the data at hand and perform a round + * of validation. The resulting tuple is filled with a set of updated + * values. + */ + + if (has.ndistinct) + { + Datum ndistinct_datum = PG_GETARG_DATUM(NDISTINCT_ARG); + bytea *data = DatumGetByteaPP(ndistinct_datum); + MVNDistinct *ndistinct = statext_ndistinct_deserialize(data); + + if (statext_ndistinct_validate(ndistinct, &stxform->stxkeys, numexprs, WARNING)) + { + values[Anum_pg_statistic_ext_data_stxdndistinct - 1] = ndistinct_datum; + replaces[Anum_pg_statistic_ext_data_stxdndistinct - 1] = true; + } + else + { + nulls[Anum_pg_statistic_ext_data_stxdndistinct - 1] = true; + success = false; + } + + statext_ndistinct_free(ndistinct); + } + else + nulls[Anum_pg_statistic_ext_data_stxdndistinct - 1] = true; + + if (has.dependencies) + { + Datum dependencies_datum = PG_GETARG_DATUM(DEPENDENCIES_ARG); + bytea *data = DatumGetByteaPP(dependencies_datum); + MVDependencies *dependencies = statext_dependencies_deserialize(data); + + if (statext_dependencies_validate(dependencies, &stxform->stxkeys, numexprs, WARNING)) + { + values[Anum_pg_statistic_ext_data_stxddependencies - 1] = dependencies_datum; + replaces[Anum_pg_statistic_ext_data_stxddependencies - 1] = true; + } + else + { + nulls[Anum_pg_statistic_ext_data_stxddependencies - 1] = true; + success = false; + } + + statext_dependencies_free(dependencies); + } + else + nulls[Anum_pg_statistic_ext_data_stxddependencies - 1] = true; + + if (has.mcv) + { + Datum datum; + ArrayType *mcv_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_VALS_ARG); + ArrayType *nulls_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_VAL_NULLS_ARG); + ArrayType *freqs_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_FREQS_ARG); + ArrayType *base_freqs_arr = PG_GETARG_ARRAYTYPE_P(MOST_COMMON_BASE_FREQS_ARG); + int nitems; + Datum *mcv_elems; + bool *mcv_nulls; + int check_nummcv; + + /* + * The mcv_arr is an array of arrays of text. We use it as the + * reference array for checking the lengths of the other 3 arrays. + */ + if (ARR_NDIM(mcv_arr) != 2) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use parameter \"%s\": expected text array of 2 dimensions", + extarginfo[MOST_COMMON_VALS_ARG].argname)); + return (Datum) 0; + } + + nitems = ARR_DIMS(mcv_arr)[0]; + + /* Fixed length arrays that cannot contain NULLs. */ + if (!check_mcvlist_array(nulls_arr, MOST_COMMON_VAL_NULLS_ARG, + 2, nitems) || + !check_mcvlist_array(freqs_arr, MOST_COMMON_FREQS_ARG, + 1, nitems) || + !check_mcvlist_array(base_freqs_arr, MOST_COMMON_BASE_FREQS_ARG, + 1, nitems)) + return (Datum) 0; + + + deconstruct_array_builtin(mcv_arr, TEXTOID, &mcv_elems, + &mcv_nulls, &check_nummcv); + + Assert(check_nummcv == (nitems * numattrs)); + + datum = import_mcvlist(tup, WARNING, numattrs, + atttypids, atttypmods, atttypcolls, + nitems, mcv_elems, mcv_nulls, + (bool *) ARR_DATA_PTR(nulls_arr), + (float8 *) ARR_DATA_PTR(freqs_arr), + (float8 *) ARR_DATA_PTR(base_freqs_arr)); + + values[Anum_pg_statistic_ext_data_stxdmcv - 1] = datum; + replaces[Anum_pg_statistic_ext_data_stxdmcv - 1] = true; + } + else + nulls[Anum_pg_statistic_ext_data_stxdmcv - 1] = true; + + if (has.expressions) + { + Datum datum; + Relation pgsd; + + pgsd = table_open(StatisticRelationId, RowExclusiveLock); + + /* + * Generate the expressions array. + * + * The attytypids, attytypmods, and atttypcols arrays have all the + * regular attributes listed first, so we can pass those arrays with a + * start point after the last regular attribute, and there should be + * numexprs elements remaining. + */ + datum = import_expressions(pgsd, numexprs, + &atttypids[numattnums], &atttypmods[numattnums], + &atttypcolls[numattnums], + PG_GETARG_ARRAYTYPE_P(EXPRESSIONS_ARG)); + + table_close(pgsd, RowExclusiveLock); + + values[Anum_pg_statistic_ext_data_stxdexpr - 1] = datum; + replaces[Anum_pg_statistic_ext_data_stxdexpr - 1] = true; + } + else + nulls[Anum_pg_statistic_ext_data_stxdexpr - 1] = true; + + upsert_pg_statistic_ext_data(values, nulls, replaces); + + heap_freetuple(tup); + table_close(pg_stext, RowExclusiveLock); + + if (atttypids != NULL) + pfree(atttypids); + if (atttypmods != NULL) + pfree(atttypmods); + if (atttypcolls != NULL) + pfree(atttypcolls); + return success; +} + +/* + * Consistency checks to ensure that other mcvlist arrays are in alignment + * with the mcv array. + */ +static bool +check_mcvlist_array(ArrayType *arr, int argindex, int required_ndims, + int mcv_length) +{ + if (ARR_NDIM(arr) != required_ndims) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use parameter \"%s\": expected array of %d dimensions", + extarginfo[argindex].argname, required_ndims)); + return false; + } + + if (array_contains_nulls(arr)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use array \"%s\": NULL value found", + extarginfo[argindex].argname)); + return false; + } + + if (ARR_DIMS(arr)[0] != mcv_length) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use parameter \"%s\": incorrect number of elements (same as \"%s\" required)", + extarginfo[argindex].argname, + extarginfo[MOST_COMMON_VALS_ARG].argname)); + return false; + } + + return true; +} + +/* + * Warn of type mismatch. Common pattern. + */ +static Datum +warn_type_mismatch(Datum d, const char *argname) +{ + char *s = TextDatumGetCString(d); + + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not match expression %s element \"%s\" with input type", + argname, s)); + return (Datum) 0; +} + +/* + * Create the stxdexprs datum using the user input in an array of array of + * text, referenced against the datatypes for the expressions. + * + * This datum is needed to fill out a complete pg_statistic_ext_data tuple. + * + * The input arrays should each have "numexprs" elements in them and they + * should be in the order that the expressions appear in the statistics + * object. + */ +static Datum +import_expressions(Relation pgsd, int numexprs, + Oid *atttypids, int32 *atttypmods, + Oid *atttypcolls, ArrayType *exprs_arr) +{ + Datum *exprs_elems; + bool *exprs_nulls; + int check_numexprs; + int offset = 0; + + FmgrInfo array_in_fn; + + Oid pgstypoid = get_rel_type_id(StatisticRelationId); + + ArrayBuildState *astate = NULL; + + /* + * Verify that the exprs_array is something that matches the expectations + * set by stxdexprs generally and the specific statistics object + * definition. + */ + if (ARR_NDIM(exprs_arr) != 2) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use parameter \"%s\" due to incorrect dimension (2 required)", + extarginfo[EXPRESSIONS_ARG].argname)); + return (Datum) 0; + } + + if (ARR_DIMS(exprs_arr)[0] != numexprs) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use parameter \"%s\" due to incorrect outer dimension with %d elements", + extarginfo[EXPRESSIONS_ARG].argname, numexprs)); + return (Datum) 0; + } + if (ARR_DIMS(exprs_arr)[1] != NUM_ATTRIBUTE_STATS_ELEMS) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use parameter \"%s\" due to incorrect inner dimension with %d elements", + extarginfo[EXPRESSIONS_ARG].argname, + NUM_ATTRIBUTE_STATS_ELEMS)); + return (Datum) 0; + } + + fmgr_info(F_ARRAY_IN, &array_in_fn); + + deconstruct_array_builtin(exprs_arr, TEXTOID, &exprs_elems, + &exprs_nulls, &check_numexprs); + + /* + * Iterate over each expected expression. + * + * The values/nulls/replaces arrays are deconstructed into a 1-D arrays, + * so we have to advance an offset by NUM_ATTRIBUTE_STATS_ELEMS to get to + * the next row of the 2-D array. + */ + for (int i = 0; i < numexprs; i++) + { + Oid typid = atttypids[i]; + int32 typmod = atttypmods[i]; + Oid stacoll = atttypcolls[i]; + TypeCacheEntry *typcache; + + Oid elemtypid = InvalidOid; + Oid elem_eq_opr = InvalidOid; + + bool ok; + + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + bool replaces[Natts_pg_statistic]; + + HeapTuple pgstup; + Datum pgstdat; + + /* Advance the indexes to the next offset. */ + const int null_frac_idx = offset + NULL_FRAC_ELEM; + const int avg_width_idx = offset + AVG_WIDTH_ELEM; + const int n_distinct_idx = offset + N_DISTINCT_ELEM; + const int most_common_vals_idx = offset + MOST_COMMON_VALS_ELEM; + const int most_common_freqs_idx = offset + MOST_COMMON_FREQS_ELEM; + const int histogram_bounds_idx = offset + HISTOGRAM_BOUNDS_ELEM; + const int correlation_idx = offset + CORRELATION_ELEM; + const int most_common_elems_idx = offset + MOST_COMMON_ELEMS_ELEM; + const int most_common_elems_freqs_idx = offset + MOST_COMMON_ELEM_FREQS_ELEM; + const int elem_count_histogram_idx = offset + ELEM_COUNT_HISTOGRAM_ELEM; + + /* 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); + + /* + * Check each of the fixed attributes to see if they have values set. + * If not set, then just let them stay with the default values set in + * statatt_init_empty_tuple(). + */ + if (!exprs_nulls[null_frac_idx]) + { + ok = text_to_float4(exprs_elems[null_frac_idx], + &values[Anum_pg_statistic_stanullfrac - 1]); + + if (!ok) + return warn_type_mismatch(exprs_elems[null_frac_idx], + extexprarginfo[NULL_FRAC_ELEM].argname); + } + + if (!exprs_nulls[avg_width_idx]) + { + ok = text_to_int4(exprs_elems[avg_width_idx], + &values[Anum_pg_statistic_stawidth - 1]); + + if (!ok) + return warn_type_mismatch(exprs_elems[avg_width_idx], + extexprarginfo[AVG_WIDTH_ELEM].argname); + } + + if (!exprs_nulls[n_distinct_idx]) + { + ok = text_to_float4(exprs_elems[n_distinct_idx], + &values[Anum_pg_statistic_stadistinct - 1]); + + if (!ok) + return warn_type_mismatch(exprs_elems[n_distinct_idx], + extexprarginfo[N_DISTINCT_ELEM].argname); + } + + /* + * The STAKIND statistics are the same as the ones found in attribute + * stats. However, these are all derived from text columns, 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. + */ + + /* STATISTIC_KIND_MCV */ + if (exprs_nulls[most_common_vals_idx] != + exprs_nulls[most_common_freqs_idx]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use expressions %s and %s: conflicting NULL/NOT NULL", + extexprarginfo[MOST_COMMON_VALS_ELEM].argname, + extexprarginfo[MOST_COMMON_FREQS_ELEM].argname)); + return (Datum) 0; + } + + if (!exprs_nulls[most_common_vals_idx]) + { + Datum stavalues; + Datum stanumbers; + + stavalues = statatt_build_stavalues(extexprarginfo[MOST_COMMON_VALS_ELEM].argname, + &array_in_fn, exprs_elems[most_common_vals_idx], + typid, typmod, &ok); + + if (!ok) + return (Datum) 0; + + stanumbers = statatt_build_stavalues(extexprarginfo[MOST_COMMON_FREQS_ELEM].argname, + &array_in_fn, exprs_elems[most_common_freqs_idx], + FLOAT4OID, -1, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_MCV, + typcache->eq_opr, stacoll, + stanumbers, false, stavalues, false); + } + + /* STATISTIC_KIND_HISTOGRAM */ + if (!exprs_nulls[histogram_bounds_idx]) + { + Datum stavalues; + + stavalues = statatt_build_stavalues(extexprarginfo[HISTOGRAM_BOUNDS_ELEM].argname, + &array_in_fn, exprs_elems[histogram_bounds_idx], + typid, typmod, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_HISTOGRAM, + typcache->lt_opr, stacoll, + 0, true, stavalues, false); + } + + /* STATISTIC_KIND_CORRELATION */ + if (!exprs_nulls[correlation_idx]) + { + Datum corr[] = {(Datum) 0}; + ArrayType *arry; + Datum stanumbers; + + ok = text_to_float4(exprs_elems[correlation_idx], &corr[0]); + + if (!ok) + { + char *s = TextDatumGetCString(exprs_elems[correlation_idx]); + + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not match expression %s of element \"%s\" with input type", + extexprarginfo[CORRELATION_ELEM].argname, s)); + return (Datum) 0; + } + + arry = construct_array_builtin(corr, 1, FLOAT4OID); + + stanumbers = PointerGetDatum(arry); + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_CORRELATION, + typcache->lt_opr, stacoll, + stanumbers, false, 0, true); + } + + /* STATISTIC_KIND_MCELEM */ + if (exprs_nulls[most_common_elems_idx] != + exprs_nulls[most_common_elems_freqs_idx]) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not use expressions %s and %s: conflicting NULL and NOT NULL", + extexprarginfo[MOST_COMMON_ELEMS_ELEM].argname, + extexprarginfo[MOST_COMMON_ELEM_FREQS_ELEM].argname)); + return (Datum) 0; + } + + /* + * 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 (!exprs_nulls[most_common_elems_idx] || + !exprs_nulls[elem_count_histogram_idx]) + { + if (!statatt_get_elem_type(typid, typcache->typtype, + &elemtypid, &elem_eq_opr)) + { + ereport(WARNING, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not determine element type of expression")); + return (Datum) 0; + } + } + + if (!exprs_nulls[most_common_elems_idx]) + { + Datum stavalues; + Datum stanumbers; + + stavalues = statatt_build_stavalues(extexprarginfo[MOST_COMMON_ELEMS_ELEM].argname, + &array_in_fn, + exprs_elems[most_common_elems_idx], + elemtypid, typmod, &ok); + + if (!ok) + return (Datum) 0; + + stanumbers = statatt_build_stavalues(extexprarginfo[MOST_COMMON_ELEM_FREQS_ELEM].argname, + &array_in_fn, + exprs_elems[most_common_elems_freqs_idx], + FLOAT4OID, -1, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, + STATISTIC_KIND_MCELEM, + elem_eq_opr, stacoll, + stanumbers, false, stavalues, false); + } + + if (!exprs_nulls[elem_count_histogram_idx]) + { + Datum stanumbers; + + stanumbers = statatt_build_stavalues(extexprarginfo[ELEM_COUNT_HISTOGRAM_ELEM].argname, + &array_in_fn, + exprs_elems[elem_count_histogram_idx], + FLOAT4OID, -1, &ok); + + if (!ok) + return (Datum) 0; + + statatt_set_slot(values, nulls, replaces, STATISTIC_KIND_DECHIST, + elem_eq_opr, stacoll, + stanumbers, false, 0, true); + } + + /* + * 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)); + astate = accumArrayResult(astate, pgstdat, false, pgstypoid, + CurrentMemoryContext); + + offset += NUM_ATTRIBUTE_STATS_ELEMS; + } + + pfree(exprs_elems); + pfree(exprs_nulls); + + return makeArrayResult(astate, CurrentMemoryContext); +} + /* * Remove an existing pg_statistic_ext_data row for a given pg_statistic_ext * row and "inherited" pair. @@ -139,6 +1162,27 @@ delete_pg_statistic_ext_data(Oid stxoid, bool inherited) return result; } +/* + * Restore (insert or replace) statistics for the given statistics object. + */ +Datum +pg_restore_extended_stats(PG_FUNCTION_ARGS) +{ + LOCAL_FCINFO(positional_fcinfo, NUM_EXTENDED_STATS_ARGS); + bool result = true; + + InitFunctionCallInfoData(*positional_fcinfo, NULL, NUM_EXTENDED_STATS_ARGS, + InvalidOid, NULL, NULL); + + if (!stats_fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo, extarginfo)) + result = false; + + if (!extended_statistics_update(positional_fcinfo)) + result = false; + + PG_RETURN_BOOL(result); +} + /* * Delete statistics for the given statistics object. */ diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index e5ac422c1b4e..f4f6836149a5 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -2171,3 +2171,147 @@ mcv_clause_selectivity_or(PlannerInfo *root, StatisticExtInfo *stat, return s; } + +/* + * The MCV is an array of records, but this is expected as 4 separate arrays. + * It is not possible to have a generic input function for pg_mcv_list + * because the most_common_values is a composite type with element types + * defined by the specific statistics object. + */ +Datum +import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, + int32 *atttypmods, Oid *atttypcolls, int nitems, + Datum *mcv_elems, bool *mcv_nulls, + bool *mcv_elem_nulls, float8 *freqs, float8 *base_freqs) +{ + MCVList *mcvlist; + bytea *bytes; + + HeapTuple *vatuples; + VacAttrStats **vastats; + + /* + * Allocate the MCV list structure, set the global parameters. + */ + mcvlist = (MCVList *) palloc0(offsetof(MCVList, items) + + (sizeof(MCVItem) * nitems)); + + mcvlist->magic = STATS_MCV_MAGIC; + mcvlist->type = STATS_MCV_TYPE_BASIC; + mcvlist->ndimensions = numattrs; + mcvlist->nitems = nitems; + + /* Set the values for the 1-D arrays and allocate space for the 2-D arrays */ + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + item->frequency = freqs[i]; + item->base_frequency = base_freqs[i]; + item->values = (Datum *) palloc0_array(Datum, numattrs); + item->isnull = (bool *) palloc0_array(bool, numattrs); + } + + /* Walk through each dimension */ + for (int j = 0; j < numattrs; j++) + { + FmgrInfo finfo; + Oid ioparam; + Oid infunc; + int index = j; + + getTypeInputInfo(atttypids[j], &infunc, &ioparam); + fmgr_info(infunc, &finfo); + + /* store info about data type OIDs */ + mcvlist->types[j] = atttypids[j]; + + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + /* These should be in agreement, but just to be safe check both */ + if (mcv_elem_nulls[index] || mcv_nulls[index]) + { + item->values[j] = (Datum) 0; + item->isnull[j] = true; + } + else + { + char *s = TextDatumGetCString(mcv_elems[index]); + ErrorSaveContext escontext = {T_ErrorSaveContext}; + + if (!InputFunctionCallSafe(&finfo, s, ioparam, atttypmods[j], + (Node *) &escontext, &item->values[j])) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("MCV elemement \"%s\" does not match expected input type.", s))); + return (Datum) 0; + } + + pfree(s); + } + + index += numattrs; + } + } + + /* + * The function statext_mcv_serialize() requires an array of pointers to + * VacAttrStats records, but only a few fields within those records have + * to be filled out. + */ + vastats = (VacAttrStats **) palloc0_array(VacAttrStats *, numattrs); + vatuples = (HeapTuple *) palloc0_array(HeapTuple, numattrs); + + for (int i = 0; i < numattrs; i++) + { + Oid typid = atttypids[i]; + HeapTuple typtuple; + + typtuple = SearchSysCacheCopy1(TYPEOID, ObjectIdGetDatum(typid)); + + if (!HeapTupleIsValid(typtuple)) + elog(ERROR, "cache lookup failed for type %u", typid); + + vatuples[i] = typtuple; + + vastats[i] = palloc0_object(VacAttrStats); + + vastats[i]->attrtype = (Form_pg_type) GETSTRUCT(typtuple); + vastats[i]->attrtypid = typid; + vastats[i]->attrcollid = atttypcolls[i]; + } + + bytes = statext_mcv_serialize(mcvlist, vastats); + + for (int i = 0; i < numattrs; i++) + { + pfree(vatuples[i]); + pfree(vastats[i]); + } + pfree((void *) vatuples); + pfree((void *) vastats); + + if (bytes == NULL) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Unable to import mcv list"))); + return (Datum) 0; + } + + for (int i = 0; i < nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + pfree(item->values); + pfree(item->isnull); + } + pfree(mcvlist); + pfree(mcv_elems); + pfree(mcv_nulls); + + return PointerGetDatum(bytes); +} diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index d61ab92d17b1..e3a993f9fcf7 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1564,6 +1564,679 @@ RESET ROLE; REVOKE MAINTAIN ON stats_import.test FROM regress_test_extstat_clear; REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_clear; DROP ROLE regress_test_extstat_clear; +-- Tests for pg_restore_extended_stats(). +-- Invalid argument values. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', NULL, + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +ERROR: argument "schemaname" must not be null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', NULL, + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +ERROR: argument "relname" must not be null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', NULL, + 'statistics_name', 'test_stat_clone', + 'inherited', false); +ERROR: argument "statistics_schemaname" must not be null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', NULL, + 'inherited', false); +ERROR: argument "statistics_name" must not be null +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', NULL); +ERROR: argument "inherited" must not be null +-- Missing objects +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'schema_not_exist', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +ERROR: schema "schema_not_exist" does not exist +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'table_not_exist', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +ERROR: relation "stats_import.table_not_exist" does not exist +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'schema_not_exist', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +WARNING: could not find schema "test_stat_clone" + pg_restore_extended_stats +--------------------------- + f +(1 row) + +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'ext_stats_not_exist', + 'inherited', false); +WARNING: could not find extended statistics object "stats_import"."ext_stats_not_exist" + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- Incorrect relation/extended stats combination +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +WARNING: could not restore extended statistics object "stats_import"."test_stat_clone": incorrect relation "stats_import"."test" specified + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- Set n_distinct using at attnum (1) that is not in the statistics +-- object +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [3,-1,-2], "ndistinct" : 4}, + {"attributes" : [1,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct); +WARNING: could not validate "pg_ndistinct" object: invalid attribute number 1 found + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- Set n_distinct using at attnum that is 0 +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [0,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct); +ERROR: malformed pg_ndistinct: "[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [0,2,3,-1,-2], "ndistinct" : 4}]" +LINE 7: 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + ^ +DETAIL: Invalid "attributes" element has been found: 0. +-- Set n_distinct using at attnum that is outside the expression bounds +-- (below -2) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [3,-1,-4], "ndistinct" : 4}, + {"attributes" : [3,-2,-4], "ndistinct" : 4}, + {"attributes" : [-1,-2,-4], "ndistinct" : 4}, + {"attributes" : [3,-1,-2,-4], "ndistinct" : 4}]'::pg_ndistinct); +WARNING: could not validate "pg_ndistinct" object: invalid attribute number -4 found + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- Check that MAINTAIN is required when restoring statistics. +CREATE ROLE regress_test_extstat_restore; +GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_restore; +SET ROLE regress_test_extstat_restore; +-- No data restore, but it does not matter as this fails on a permission +-- failure. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +ERROR: permission denied for table test_clone +RESET ROLE; +GRANT MAINTAIN ON stats_import.test_clone TO regress_test_extstat_clear; +ERROR: role "regress_test_extstat_clear" does not exist +SET ROLE regress_test_extstat_clear; +ERROR: role "regress_test_extstat_clear" does not exist +-- This works, check the lock on the relation while on it. +BEGIN; +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}, + {"attributes" : [2,-1], "ndistinct" : 4}, + {"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT mode FROM pg_locks WHERE locktype = 'relation' AND + relation = 'stats_import.test_clone'::regclass AND + pid = pg_backend_pid(); + mode +-------------------------- + ShareUpdateExclusiveLock +(1 row) + +COMMIT; +RESET ROLE; +REVOKE MAINTAIN ON stats_import.test_clone FROM regress_test_extstat_restore; +REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_restore; +DROP ROLE regress_test_extstat_restore; +SELECT replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + e.dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx +-[ RECORD 1 ]----------+------------------------------------------------ +n_distinct | [{"attributes": [2, 3], "ndistinct": 4}, + + | {"attributes": [2, -1], "ndistinct": 4}, + + | {"attributes": [3, -1], "ndistinct": 4}, + + | {"attributes": [3, -2], "ndistinct": 4}, + + | {"attributes": [-1, -2], "ndistinct": 3}, + + | {"attributes": [2, 3, -1], "ndistinct": 4}, + + | {"attributes": [2, 3, -2], "ndistinct": 4}, + + | {"attributes": [2, -1, -2], "ndistinct": 4}, + + | {"attributes": [2, 3, -1, -2], "ndistinct": 4}] +dependencies | +most_common_vals | +most_common_val_nulls | +most_common_freqs | +most_common_base_freqs | + +-- set dependencies using at attnum (1) that is not in the statistics object +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 1, "degree": 1.000000}]'::pg_dependencies); +WARNING: could not validate "pg_dependencies" object: invalid attribute number 1 found + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- set dependencies using at attnum that is 0 +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [0], "dependency": -1, "degree": 1.000000}]'::pg_dependencies); +ERROR: malformed pg_dependencies: "[{"attributes": [0], "dependency": -1, "degree": 1.000000}]" +LINE 7: 'dependencies', '[{"attributes": [0], "dependency": -1, "d... + ^ +DETAIL: Invalid "attributes" element has been found: 0. +-- set dependencies using at attnum that is outside the expression bounds(below -2) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": -3, "degree": 1.000000}]'::pg_dependencies); +WARNING: could not validate "pg_dependencies" object: invalid attribute number -3 found + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2], "dependency": -2, "degree": 1.000000}, + {"attributes": [3], "dependency": 2, "degree": 1.000000}, + {"attributes": [3], "dependency": -1, "degree": 1.000000}, + {"attributes": [3], "dependency": -2, "degree": 1.000000}, + {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [-1,-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1,-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [2,3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,-1,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [3,-1,-2], "dependency": 2, "degree": 1.000000}]'::pg_dependencies); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx +-[ RECORD 1 ]----------+------------------------------------------------------------------ +n_distinct | [{"attributes": [2, 3], "ndistinct": 4}, + + | {"attributes": [2, -1], "ndistinct": 4}, + + | {"attributes": [3, -1], "ndistinct": 4}, + + | {"attributes": [3, -2], "ndistinct": 4}, + + | {"attributes": [-1, -2], "ndistinct": 3}, + + | {"attributes": [2, 3, -1], "ndistinct": 4}, + + | {"attributes": [2, 3, -2], "ndistinct": 4}, + + | {"attributes": [2, -1, -2], "ndistinct": 4}, + + | {"attributes": [2, 3, -1, -2], "ndistinct": 4}] +dependencies | [{"attributes": [2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [-1, -2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1, -2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [2, 3, -2], "dependency": -1, "degree": 1.000000},+ + | {"attributes": [2, -1, -2], "dependency": 3, "degree": 1.000000},+ + | {"attributes": [3, -1, -2], "dependency": 2, "degree": 1.000000}] +most_common_vals | +most_common_val_nulls | +most_common_freqs | +most_common_base_freqs | + +-- if any one mcv param specified, all four must be specified (part 1) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[]); +WARNING: must specify parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" for extended statistics object + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- if any one mcv param specified, all four must be specified (part 2) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[]); +WARNING: must specify parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" for extended statistics object + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- if any one mcv param specified, all four must be specified (part 3) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[]); +WARNING: must specify parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" for extended statistics object + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- if any one mcv param specified, all four must be specified (part 4) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[]); +WARNING: must specify parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" for extended statistics object + pg_restore_extended_stats +--------------------------- + f +(1 row) + +-- ok +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[]); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx +-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- +n_distinct | [{"attributes": [2, 3], "ndistinct": 4}, + + | {"attributes": [2, -1], "ndistinct": 4}, + + | {"attributes": [3, -1], "ndistinct": 4}, + + | {"attributes": [3, -2], "ndistinct": 4}, + + | {"attributes": [-1, -2], "ndistinct": 3}, + + | {"attributes": [2, 3, -1], "ndistinct": 4}, + + | {"attributes": [2, 3, -2], "ndistinct": 4}, + + | {"attributes": [2, -1, -2], "ndistinct": 4}, + + | {"attributes": [2, 3, -1, -2], "ndistinct": 4}] +dependencies | [{"attributes": [2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, 3], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [2, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -1], "dependency": -2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": 2, "degree": 1.000000}, + + | {"attributes": [3, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [-1, -2], "dependency": 2, "degree": 0.500000}, + + | {"attributes": [-1, -2], "dependency": 3, "degree": 0.500000}, + + | {"attributes": [2, 3, -2], "dependency": -1, "degree": 1.000000}, + + | {"attributes": [2, -1, -2], "dependency": 3, "degree": 1.000000}, + + | {"attributes": [3, -1, -2], "dependency": 2, "degree": 1.000000}] +most_common_vals | {{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}} +most_common_val_nulls | {{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}} +most_common_freqs | {0.25,0.25,0.25,0.25} +most_common_base_freqs | {0.00390625,0.015625,0.00390625,0.015625} + +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', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[]); + pg_restore_extended_stats +--------------------------- + t +(1 row) + +SELECT e.inherited, 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 ]----------+------- +inherited | f +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 ]----------+------- +inherited | f +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 | + +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test_clone', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat_clone', + inherited => false); + pg_clear_extended_stats +------------------------- + +(1 row) + +-- +-- Copy stats from test_stat to test_stat_clone +-- +SELECT e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'schemaname', e.statistics_schemaname::text, + 'relname', 'test_clone', + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_val_nulls', e.most_common_val_nulls, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs) +FROM pg_stats_ext AS e +CROSS JOIN LATERAL ( + SELECT array_agg( + ARRAY[ee.null_frac::text, ee.avg_width::text, + ee.n_distinct::text, ee.most_common_vals::text, + ee.most_common_freqs::text, ee.histogram_bounds::text, + ee.correlation::text, ee.most_common_elems::text, + ee.most_common_elem_freqs::text, + ee.elem_count_histogram::text]) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname AND + ee.statistics_name = e.statistics_name AND + ee.inherited = e.inherited + ) AS x(exprs) +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat'; + statistics_name | pg_restore_extended_stats +-----------------+--------------------------- +(0 rows) + +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone'; + inherited | n_distinct | dependencies | most_common_vals | most_common_val_nulls | most_common_freqs | most_common_base_freqs +-----------+------------+--------------+------------------+-----------------------+-------------------+------------------------ +(0 rows) + +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat'; + inherited | n_distinct | dependencies | most_common_vals | most_common_val_nulls | most_common_freqs | most_common_base_freqs +-----------+------------+--------------+------------------+-----------------------+-------------------+------------------------ +(0 rows) + +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone'; + 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 +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- +(0 rows) + +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat'; + 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 +-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- +(0 rows) + DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to type stats_import.complex_type diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index d1934a8a42bf..c199bc2076ce 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1113,4 +1113,437 @@ REVOKE MAINTAIN ON stats_import.test FROM regress_test_extstat_clear; REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_clear; DROP ROLE regress_test_extstat_clear; +-- Tests for pg_restore_extended_stats(). +-- Invalid argument values. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', NULL, + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', NULL, + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', NULL, + 'statistics_name', 'test_stat_clone', + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', NULL, + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', NULL); +-- Missing objects +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'schema_not_exist', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'table_not_exist', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'schema_not_exist', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'ext_stats_not_exist', + 'inherited', false); +-- Incorrect relation/extended stats combination +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); + +-- Set n_distinct using at attnum (1) that is not in the statistics +-- object +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [3,-1,-2], "ndistinct" : 4}, + {"attributes" : [1,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct); + +-- Set n_distinct using at attnum that is 0 +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [0,2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct); + +-- Set n_distinct using at attnum that is outside the expression bounds +-- (below -2) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [3,-1,-4], "ndistinct" : 4}, + {"attributes" : [3,-2,-4], "ndistinct" : 4}, + {"attributes" : [-1,-2,-4], "ndistinct" : 4}, + {"attributes" : [3,-1,-2,-4], "ndistinct" : 4}]'::pg_ndistinct); + +-- Check that MAINTAIN is required when restoring statistics. +CREATE ROLE regress_test_extstat_restore; +GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_restore; +SET ROLE regress_test_extstat_restore; +-- No data restore, but it does not matter as this fails on a permission +-- failure. +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false); +RESET ROLE; +GRANT MAINTAIN ON stats_import.test_clone TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +-- This works, check the lock on the relation while on it. +BEGIN; +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '[{"attributes" : [2,3], "ndistinct" : 4}, + {"attributes" : [2,-1], "ndistinct" : 4}, + {"attributes" : [3,-1], "ndistinct" : 4}, + {"attributes" : [3,-2], "ndistinct" : 4}, + {"attributes" : [-1,-2], "ndistinct" : 3}, + {"attributes" : [2,3,-1], "ndistinct" : 4}, + {"attributes" : [2,3,-2], "ndistinct" : 4}, + {"attributes" : [2,-1,-2], "ndistinct" : 4}, + {"attributes" : [2,3,-1,-2], "ndistinct" : 4}]'::pg_ndistinct); +SELECT mode FROM pg_locks WHERE locktype = 'relation' AND + relation = 'stats_import.test_clone'::regclass AND + pid = pg_backend_pid(); +COMMIT; +RESET ROLE; +REVOKE MAINTAIN ON stats_import.test_clone FROM regress_test_extstat_restore; +REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_restore; +DROP ROLE regress_test_extstat_restore; + +SELECT replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + e.dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx + +-- set dependencies using at attnum (1) that is not in the statistics object +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 1, "degree": 1.000000}]'::pg_dependencies); + +-- set dependencies using at attnum that is 0 +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [0], "dependency": -1, "degree": 1.000000}]'::pg_dependencies); + +-- set dependencies using at attnum that is outside the expression bounds(below -2) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": -3, "degree": 1.000000}]'::pg_dependencies); + +-- ok +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '[{"attributes": [2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2], "dependency": -2, "degree": 1.000000}, + {"attributes": [3], "dependency": 2, "degree": 1.000000}, + {"attributes": [3], "dependency": -1, "degree": 1.000000}, + {"attributes": [3], "dependency": -2, "degree": 1.000000}, + {"attributes": [-1], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1], "dependency": 3, "degree": 0.500000}, + {"attributes": [-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,3], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [2,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-1], "dependency": -2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": 2, "degree": 1.000000}, + {"attributes": [3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [-1,-2], "dependency": 2, "degree": 0.500000}, + {"attributes": [-1,-2], "dependency": 3, "degree": 0.500000}, + {"attributes": [2,3,-2], "dependency": -1, "degree": 1.000000}, + {"attributes": [2,-1,-2], "dependency": 3, "degree": 1.000000}, + {"attributes": [3,-1,-2], "dependency": 2, "degree": 1.000000}]'::pg_dependencies); + +SELECT + replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat_clone' +AND e.inherited = false +\gx + +-- if any one mcv param specified, all four must be specified (part 1) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[]); + +-- if any one mcv param specified, all four must be specified (part 2) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[]); + +-- if any one mcv param specified, all four must be specified (part 3) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[]); + +-- if any one mcv param specified, all four must be specified (part 4) +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[]); + +-- ok +SELECT pg_catalog.pg_restore_extended_stats( + 'schemaname', 'stats_import', + 'relname', 'test_clone', + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[]); + +SELECT replace(e.n_distinct, '}, ', E'},\n') AS n_distinct, + replace(e.dependencies, '}, ', E'},\n') AS dependencies, + e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat_clone' AND + e.inherited = false +\gx + +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', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[]); + +SELECT e.inherited, 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 + +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test_clone', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat_clone', + inherited => false); + +-- +-- Copy stats from test_stat to test_stat_clone +-- +SELECT e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'schemaname', e.statistics_schemaname::text, + 'relname', 'test_clone', + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_val_nulls', e.most_common_val_nulls, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs) +FROM pg_stats_ext AS e +CROSS JOIN LATERAL ( + SELECT array_agg( + ARRAY[ee.null_frac::text, ee.avg_width::text, + ee.n_distinct::text, ee.most_common_vals::text, + ee.most_common_freqs::text, ee.histogram_bounds::text, + ee.correlation::text, ee.most_common_elems::text, + ee.most_common_elem_freqs::text, + ee.elem_count_histogram::text]) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname AND + ee.statistics_name = e.statistics_name AND + ee.inherited = e.inherited + ) AS x(exprs) +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat'; + +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone'; + +SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, n.most_common_freqs, + n.most_common_base_freqs + FROM pg_stats_ext AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, o.most_common_freqs, + o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat'; + +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat' +EXCEPT +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone'; + +SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' AND + n.statistics_name = 'test_stat_clone' +EXCEPT +SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' AND + o.statistics_name = 'test_stat'; + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index e7ea16f73b31..da3719e72b45 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2165,6 +2165,90 @@ SELECT pg_restore_attribute_stats( </para> </entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_restore_extended_stats</primary> + </indexterm> + <function>pg_restore_extended_stats</function> ( + <literal>VARIADIC</literal> <parameter>kwargs</parameter> <type>"any"</type> ) + <returnvalue>boolean</returnvalue> + </para> + <para> + Creates or updates statistics for statistics objects. Ordinarily, + these statistics are collected automatically or updated as a part of + <xref linkend="sql-vacuum"/> or <xref linkend="sql-analyze"/>, so + it's not necessary to call this function. However, it is useful + after a restore to enable the optimizer to choose better plans if + <command>ANALYZE</command> has not been run yet. + </para> + <para> + The tracked statistics may change from version to version, so + arguments are passed as pairs of <replaceable>argname</replaceable> + and <replaceable>argvalue</replaceable> in the form: +<programlisting> + SELECT pg_restore_extended_stats( + '<replaceable>arg1name</replaceable>', '<replaceable>arg1value</replaceable>'::<replaceable>arg1type</replaceable>, + '<replaceable>arg2name</replaceable>', '<replaceable>arg2value</replaceable>'::<replaceable>arg2type</replaceable>, + '<replaceable>arg3name</replaceable>', '<replaceable>arg3value</replaceable>'::<replaceable>arg3type</replaceable>); +</programlisting> + </para> + <para> + For example, to set the <structfield>n_distinct</structfield>, + <structfield>dependencies</structfield>, and <structfield>exprs</structfield> + values for the statistics object <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, + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4}'::pg_ndistinct, + 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000}'::pg_dependencies + 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[]); +</programlisting> + </para> + <para> + The required arguments are <literal>schemaname</literal>, with a value + of type <type>name</type>, for the schema of the table to which the + statistics are related to, <literal>relname</literal>, with a value + of type <type>name</type>, for the the table to which the statistics + are related to, <literal>statistics_schemaname</literal> + with a value of type <type>name</type>, which specifies the statistics + object's schema, <literal>statistics_name</literal> with a value of + type <type>name</type>, which specifies the name of the statistics + object; and <literal>inherited</literal>, which specifies whether + the statistics include values from child tables. + Other arguments are the names and values of statistics corresponding + to columns in <link linkend="view-pg-stats-ext"><structname>pg_stats_ext</structname> + </link>. To accept statistics for any expressions in the extended + statistics object, the parameter <literal>exprs</literal> with a type + <type>text[]</type> is available, the array must be two dimensional with + an outer array in length equal to the number of expressions in the object, + and the inner array elements for each of the statistical columns in + <link linkend="view-pg-stats-ext-exprs"><structname>pg_stats_ext_exprs</structname></link>, + some of which are themselves arrays. + </para> + <para> + Additionally, this function accepts argument name + <literal>version</literal> of type <type>integer</type>, which + specifies the server version from which the statistics originated. + This is anticipated to be helpful in porting statistics from older + versions of <productname>PostgreSQL</productname>. + </para> + <para> + Minor errors are reported as a <literal>WARNING</literal> and + ignored, and remaining statistics will still be restored. If all + specified statistics are successfully restored, returns + <literal>true</literal>, otherwise <literal>false</literal>. + </para> + <para> + The caller must have the <literal>MAINTAIN</literal> privilege on the + table or be the owner of the database. + </para> + </entry> + </row> <row> <entry role="func_table_entry"> <para role="func_signature"> diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 3f3a888fd0ec..e4ec767f8100 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2897,6 +2897,7 @@ SplitPoint SplitTextOutputData SplitVar StackElem +StakindFlags StartDataPtrType StartLOPtrType StartLOsPtrType -- 2.51.0
From ba5d03495f2e6d721bc730776384ecdd0ae75c9c Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Sat, 17 Jan 2026 09:12:00 +0900 Subject: [PATCH v28 2/3] Include Extended Statistics in pg_dump. Incorporate the new pg_restore_extended_stats() function into pg_dump. This detects the existence of extended statistics statistics (i.e. pg_statistic_ext_data rows). This handles many of the changes that have happened to extended statistic statistics over the various versions, including: * Format change for pg_ndistinct and pg_dependencies in current development version. Earlier versions have the format translated via the pg_dump SQL statement. * Inherited extended statistics were introduced in v15. * Expressions were introduced to extended statistics in v14. * MCV extended statistics were introduced in v13. * pg_statistic_ext_data and pg_stats_ext introduced in v12, prior to that ndstinct and depdendencies data (the only kind of stats that existed were directly on pg_statistic_ext. * Extended Statistics were introduced in v10, so there is no support for prior versions necessary. --- src/bin/pg_dump/pg_backup.h | 1 + src/bin/pg_dump/pg_backup_archiver.c | 3 +- src/bin/pg_dump/pg_dump.c | 264 +++++++++++++++++++++++++++ src/bin/pg_dump/t/002_pg_dump.pl | 28 +++ 4 files changed, 295 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index d9041dad7206..2f8d9799c30c 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -68,6 +68,7 @@ enum _dumpPreparedQueries PREPQUERY_DUMPCOMPOSITETYPE, PREPQUERY_DUMPDOMAIN, PREPQUERY_DUMPENUMTYPE, + PREPQUERY_DUMPEXTSTATSOBJSTATS, PREPQUERY_DUMPFUNC, PREPQUERY_DUMPOPR, PREPQUERY_DUMPRANGETYPE, diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 4a63f7392ae8..1d42b0343eb3 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -3007,7 +3007,8 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) strcmp(te->desc, "SEARCHPATH") == 0) return REQ_SPECIAL; - if (strcmp(te->desc, "STATISTICS DATA") == 0) + if ((strcmp(te->desc, "STATISTICS DATA") == 0) || + (strcmp(te->desc, "EXTENDED STATISTICS DATA") == 0)) { if (!ropt->dumpStatistics) return 0; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 687dc98e46db..f2e48fc691f9 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -325,6 +325,7 @@ static void dumpSequenceData(Archive *fout, const TableDataInfo *tdinfo); static void dumpIndex(Archive *fout, const IndxInfo *indxinfo); static void dumpIndexAttach(Archive *fout, const IndexAttachInfo *attachinfo); static void dumpStatisticsExt(Archive *fout, const StatsExtInfo *statsextinfo); +static void dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo); static void dumpConstraint(Archive *fout, const ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, const ConstraintInfo *coninfo); static void dumpTSParser(Archive *fout, const TSParserInfo *prsinfo); @@ -8273,6 +8274,9 @@ getExtendedStatistics(Archive *fout) /* Decide whether we want to dump it */ selectDumpableStatisticsObject(&(statsextinfo[i]), fout); + + if (fout->dopt->dumpStatistics) + statsextinfo[i].dobj.components |= DUMP_COMPONENT_STATISTICS; } PQclear(res); @@ -11727,6 +11731,7 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) break; case DO_STATSEXT: dumpStatisticsExt(fout, (const StatsExtInfo *) dobj); + dumpStatisticsExtStats(fout, (const StatsExtInfo *) dobj); break; case DO_REFRESH_MATVIEW: refreshMatViewData(fout, (const TableDataInfo *) dobj); @@ -18529,6 +18534,265 @@ dumpStatisticsExt(Archive *fout, const StatsExtInfo *statsextinfo) free(qstatsextname); } +/* + * dumpStatisticsExtStats + * write out to fout the stats for an extended statistics object + */ +static void +dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) +{ + DumpOptions *dopt = fout->dopt; + PQExpBuffer query; + PGresult *res; + int nstats; + + /* Do nothing if not dumping statistics */ + if (!dopt->dumpStatistics) + return; + + if (!fout->is_prepared[PREPQUERY_DUMPEXTSTATSOBJSTATS]) + { + PQExpBuffer pq = createPQExpBuffer(); + + /* + * Set up query for constraint-specific details. + * + * 19+: query pg_stats_ext and pg_stats_ext_exprs as-is 15-18: query + * pg_stats_ext translating the ndistinct and dependencies, 14: + * inherited is always NULL 12-13: no pg_stats_ext_exprs 10-11: no + * pg_stats_ext, join pg_statistic_ext and pg_namespace + */ + + appendPQExpBufferStr(pq, + "PREPARE getExtStatsStats(pg_catalog.name, pg_catalog.name) AS\n" + "SELECT "); + + /* + * Versions 15+ have inherited stats. + * + * Create this column in all version because we need to order by it + * later. + */ + if (fout->remoteVersion >= 150000) + appendPQExpBufferStr(pq, "e.inherited, "); + else + appendPQExpBufferStr(pq, "false AS inherited, "); + + /* + * The ndistinnct and depdendencies formats changed in v19, so + * everything before that needs to be translated. + * + * The ndistinct translation converts this: + * + * {"3, 4": 11, "3, 6": 11, "4, 6": 11, "3, 4, 6": 11} + * + * to this: + * + * [ {"attributes": [3,4], "ndistinct": 11}, {"attributes": [3,6], + * "ndistinct": 11}, {"attributes": [4,6], "ndistinct": 11}, + * {"attributes": [3,4,6], "ndistinct": 11} ] + * + * and the dependencies translation converts this: + * + * {"3 => 4": 1.000000, "3 => 6": 1.000000, "4 => 6": 1.000000, "3, 4 + * => 6": 1.000000, "3, 6 => 4": 1.000000} + * + * to this: + * + * [ {"attributes": [3], "dependency": 4, "degree": 1.000000}, + * {"attributes": [3], "dependency": 6, "degree": 1.000000}, + * {"attributes": [4], "dependency": 6, "degree": 1.000000}, + * {"attributes": [3,4], "dependency": 6, "degree": 1.000000}, + * {"attributes": [3,6], "dependency": 4, "degree": 1.000000} ] + */ + if (fout->remoteVersion >= 190000) + appendPQExpBufferStr(pq, "e.n_distinct, e.dependencies, "); + else + appendPQExpBufferStr(pq, + "( " + "SELECT json_agg( " + " json_build_object( " + " 'attributes', " + " string_to_array(kv.key, ', ')::integer[], " + " 'ndistinct', " + " kv.value::bigint )) " + "FROM json_each_text(e.n_distinct::text::json) AS kv" + ") AS n_distinct, " + "( " + "SELECT json_agg( " + " json_build_object( " + " 'attributes', " + " string_to_array( " + " split_part(kv.key, ' => ', 1), " + " ', ')::integer[], " + " 'dependency', " + " split_part(kv.key, ' => ', 2)::integer, " + " 'degree', " + " kv.value::double precision )) " + "FROM json_each_text(e.dependencies::text::json) AS kv " + ") AS dependencies, "); + + /* MCV was introduced v13 */ + if (fout->remoteVersion >= 130000) + appendPQExpBufferStr(pq, + "e.most_common_vals, e.most_common_val_nulls, " + "e.most_common_freqs, e.most_common_base_freqs, "); + else + appendPQExpBufferStr(pq, + "NULL AS most_common_vals, NULL AS most_common_val_nulls, " + "NULL AS most_common_freqs, NULL AS most_common_base_freqs, "); + + /* Expressions were introduced in v14 */ + if (fout->remoteVersion >= 140000) + { + appendPQExpBufferStr(pq, + "( " + "SELECT array_agg( " + " ARRAY[ee.null_frac::text, ee.avg_width::text, " + " ee.n_distinct::text, ee.most_common_vals::text, " + " ee.most_common_freqs::text, ee.histogram_bounds::text, " + " ee.correlation::text, ee.most_common_elems::text, " + " ee.most_common_elem_freqs::text, " + " ee.elem_count_histogram::text]) " + "FROM pg_stats_ext_exprs AS ee " + "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) + appendPQExpBufferStr(pq, + "FROM pg_catalog.pg_stats_ext AS e " + "WHERE e.statistics_schemaname = $1 " + "AND e.statistics_name = $2 "); + else + appendPQExpBufferStr(pq, + "FROM ( " + "SELECT s.stxndistinct AS n_distinct, " + " s.stxdependencies AS dependencies " + "FROM pg_catalog.pg_statistics_ext AS s " + "JOIN pg_catalog.pg_namespace AS n " + "ON n.oid = s.stxnamespace " + "WHERE n.nspname = $1 " + "AND e.stxname = $2 " + ") AS e "); + + /* we always have an inherited column, but it may be a constant */ + appendPQExpBufferStr(pq, "ORDER BY inherited"); + + ExecuteSqlStatement(fout, pq->data); + + fout->is_prepared[PREPQUERY_DUMPEXTSTATSOBJSTATS] = true; + + destroyPQExpBuffer(pq); + } + + query = createPQExpBuffer(); + + appendPQExpBufferStr(query, "EXECUTE getExtStatsStats("); + appendStringLiteralAH(query, statsextinfo->dobj.namespace->dobj.name, fout); + appendPQExpBufferStr(query, "::pg_catalog.name, "); + appendStringLiteralAH(query, statsextinfo->dobj.name, fout); + appendPQExpBufferStr(query, "::pg_catalog.name)"); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + destroyPQExpBuffer(query); + + nstats = PQntuples(res); + + if (nstats > 0) + { + PQExpBuffer out = createPQExpBuffer(); + + int i_inherited = PQfnumber(res, "inherited"); + int i_ndistinct = PQfnumber(res, "n_distinct"); + int i_dependencies = PQfnumber(res, "dependencies"); + int i_mcv = PQfnumber(res, "most_common_vals"); + int i_mcv_nulls = PQfnumber(res, "most_common_val_nulls"); + 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++) + { + TableInfo *tbinfo = statsextinfo->stattable; + + if (PQgetisnull(res, i, i_inherited)) + pg_fatal("inherited cannot be NULL"); + + appendPQExpBufferStr(out, + "SELECT * FROM pg_catalog.pg_restore_extended_stats(\n"); + appendPQExpBuffer(out, "\t'version', '%d'::integer,\n", + fout->remoteVersion); + + /* Relation information */ + appendPQExpBufferStr(out, "\t'schemaname', "); + appendStringLiteralAH(out, tbinfo->dobj.namespace->dobj.name, fout); + appendPQExpBufferStr(out, ",\n\t'relname', "); + appendStringLiteralAH(out, tbinfo->dobj.name, fout); + + /* Extended statistics information */ + appendPQExpBufferStr(out, ",\n\t'statistics_schemaname', "); + appendStringLiteralAH(out, statsextinfo->dobj.namespace->dobj.name, fout); + appendPQExpBufferStr(out, ",\n\t'statistics_name', "); + appendStringLiteralAH(out, statsextinfo->dobj.name, fout); + appendNamedArgument(out, fout, "inherited", "boolean", + PQgetvalue(res, i, i_inherited)); + + if (!PQgetisnull(res, i, i_ndistinct)) + appendNamedArgument(out, fout, "n_distinct", "pg_ndistinct", + PQgetvalue(res, i, i_ndistinct)); + + if (!PQgetisnull(res, i, i_dependencies)) + appendNamedArgument(out, fout, "dependencies", "pg_dependencies", + PQgetvalue(res, i, i_dependencies)); + + if (!PQgetisnull(res, i, i_mcv)) + appendNamedArgument(out, fout, "most_common_vals", "text[]", + PQgetvalue(res, i, i_mcv)); + + if (!PQgetisnull(res, i, i_mcv_nulls)) + appendNamedArgument(out, fout, "most_common_val_nulls", "boolean[]", + PQgetvalue(res, i, i_mcv_nulls)); + + if (!PQgetisnull(res, i, i_mcf)) + appendNamedArgument(out, fout, "most_common_freqs", "double precision[]", + PQgetvalue(res, i, i_mcf)); + + if (!PQgetisnull(res, i, i_mcbf)) + appendNamedArgument(out, fout, "most_common_base_freqs", "double precision[]", + PQgetvalue(res, i, i_mcbf)); + + if (!PQgetisnull(res, i, i_exprs)) + appendNamedArgument(out, fout, "exprs", "text[]", + PQgetvalue(res, i, i_exprs)); + + appendPQExpBufferStr(out, "\n);\n"); + } + + ArchiveEntry(fout, nilCatalogId, createDumpId(), + ARCHIVE_OPTS(.tag = statsextinfo->dobj.name, + .namespace = statsextinfo->dobj.namespace->dobj.name, + .owner = statsextinfo->rolname, + .description = "EXTENDED STATISTICS DATA", + .section = SECTION_POST_DATA, + .createStmt = out->data, + .deps = &statsextinfo->dobj.dumpId, + .nDeps = 1)); + destroyPQExpBuffer(out); + } + PQclear(res); +} + /* * dumpConstraint * write out to fout a user-defined constraint diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 28812d28aa9a..a8dcc2b5c757 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -4772,6 +4772,34 @@ my %tests = ( }, }, + # + # EXTENDED stats will end up in SECTION_POST_DATA. + # + 'extended_statistics_import' => { + create_sql => ' + CREATE TABLE dump_test.has_ext_stats + AS SELECT g.g AS x, g.g / 2 AS y FROM generate_series(1,100) AS g(g); + CREATE STATISTICS dump_test.es1 ON x, (y % 2) FROM dump_test.has_ext_stats; + ANALYZE dump_test.has_ext_stats;', + regexp => qr/^ + \QSELECT * FROM pg_catalog.pg_restore_extended_stats(\E\s+/xm, + like => { + %full_runs, + %dump_test_schema_runs, + no_data_no_schema => 1, + no_schema => 1, + section_post_data => 1, + statistics_only => 1, + schema_only_with_statistics => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + no_statistics => 1, + only_dump_measurement => 1, + schema_only => 1, + }, + }, + # # While attribute stats (aka pg_statistic stats) only appear for tables # that have been analyzed, all tables will have relation stats because -- 2.51.0
From 7021bf02a373779c2792d14fcaf3af258ad05342 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Mon, 19 Jan 2026 15:48:01 +0900 Subject: [PATCH v28 3/3] Several fixes and adjustments --- .../statistics/extended_stats_internal.h | 12 ++- src/backend/statistics/extended_stats_funcs.c | 93 ++++++++++++------- src/backend/statistics/mcv.c | 74 ++++++++------- src/bin/pg_dump/pg_dump.c | 8 +- src/test/regress/expected/stats_import.out | 2 +- doc/src/sgml/func/func-admin.sgml | 2 +- 6 files changed, 112 insertions(+), 79 deletions(-) diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h index f7cc1f65e0df..7bf0bf3b008a 100644 --- a/src/include/statistics/extended_stats_internal.h +++ b/src/include/statistics/extended_stats_internal.h @@ -89,6 +89,13 @@ extern MCVList *statext_mcv_build(StatsBuildData *data, double totalrows, int stattarget); extern bytea *statext_mcv_serialize(MCVList *mcvlist, VacAttrStats **stats); extern MCVList *statext_mcv_deserialize(bytea *data); +extern void statext_mcv_free(MCVList *mcvlist); +extern Datum statext_mcv_import(HeapTuple tup, int elevel, + int numattrs, Oid *atttypids, + int32 *atttypmods, Oid *atttypcolls, + int nitems, Datum *mcv_elems, + bool *mcv_nulls, bool *mcv_elem_nulls, + float8 *freqs, float8 *base_freqs); extern MultiSortSupport multi_sort_init(int ndims); extern void multi_sort_add_dimension(MultiSortSupport mss, int sortdim, @@ -135,9 +142,4 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root, Selectivity *overlap_basesel, Selectivity *totalsel); -extern Datum import_mcvlist(HeapTuple tup, int elevel, int numattrs, - Oid *atttypids, int32 *atttypmods, Oid *atttypcolls, - int nitems, Datum *mcv_elems, bool *mcv_nulls, - bool *mcv_elem_nulls, float8 *freqs, float8 *base_freqs); - #endif /* EXTENDED_STATS_INTERNAL_H */ diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c index 3622e5729252..d867445d85c7 100644 --- a/src/backend/statistics/extended_stats_funcs.c +++ b/src/backend/statistics/extended_stats_funcs.c @@ -252,14 +252,24 @@ expand_stxkind(HeapTuple tup, StakindFlags *enabled) for (int i = 0; i < ARR_DIMS(arr)[0]; i++) { - if (kinds[i] == STATS_EXT_NDISTINCT) - enabled->ndistinct = true; - else if (kinds[i] == STATS_EXT_DEPENDENCIES) - enabled->dependencies = true; - else if (kinds[i] == STATS_EXT_MCV) - enabled->mcv = true; - else if (kinds[i] == STATS_EXT_EXPRESSIONS) - enabled->expressions = true; + switch (kinds[i]) + { + case STATS_EXT_NDISTINCT: + enabled->ndistinct = true; + break; + case STATS_EXT_DEPENDENCIES: + enabled->dependencies = true; + break; + case STATS_EXT_MCV: + enabled->mcv = true; + break; + case STATS_EXT_EXPRESSIONS: + enabled->expressions = true; + break; + default: + elog(ERROR, "incorrect stxkind %c found", kinds[i]); + break; + } } } @@ -304,12 +314,13 @@ upsert_pg_statistic_ext_data(const Datum *values, const bool *nulls, } /* - * Insert or Update Extended Statistics + * Insert or update an extended statistics object. * - * Major errors, such as the table not existing, the statistics object not - * existing, or a permissions failure are always reported at ERROR. Other - * errors, such as a conversion failure on one statistic kind, are reported - * as WARNINGs, and other statistic kinds may still be updated. + * Major errors, such as the table not existing or permission errors, are + * reported as ERRORs. There are a couple of paths that generate a WARNING, + * like when the statistics object or its schema do not exist, a conversion + * failure on one statistic kind, or when other statistic kinds may still + * be updated. */ static bool extended_statistics_update(FunctionCallInfo fcinfo) @@ -365,7 +376,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("recovery is in progress"), errhint("Statistics cannot be modified during recovery.")); - PG_RETURN_BOOL(false); + return false; } /* relation arguments */ @@ -397,8 +408,8 @@ extended_statistics_update(FunctionCallInfo fcinfo) { ereport(WARNING, errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("could not find schema \"%s\"", stxname)); - PG_RETURN_BOOL(false); + errmsg("could not find schema \"%s\"", nspname)); + return false; } pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock); @@ -411,7 +422,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) errcode(ERRCODE_UNDEFINED_OBJECT), errmsg("could not find extended statistics object \"%s\".\"%s\"", get_namespace_name(nspoid), stxname)); - PG_RETURN_BOOL(false); + return false; } stxform = (Form_pg_statistic_ext) GETSTRUCT(tup); @@ -428,7 +439,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) errmsg("could not restore extended statistics object \"%s\".\"%s\": incorrect relation \"%s\".\"%s\" specified", get_namespace_name(nspoid), stxname, relnspname, relname)); - PG_RETURN_BOOL(false); + return false; } expand_stxkind(tup, &enabled); @@ -665,7 +676,7 @@ extended_statistics_update(FunctionCallInfo fcinfo) errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("could not use parameter \"%s\": expected text array of 2 dimensions", extarginfo[MOST_COMMON_VALS_ARG].argname)); - return (Datum) 0; + return false; } nitems = ARR_DIMS(mcv_arr)[0]; @@ -677,20 +688,19 @@ extended_statistics_update(FunctionCallInfo fcinfo) 1, nitems) || !check_mcvlist_array(base_freqs_arr, MOST_COMMON_BASE_FREQS_ARG, 1, nitems)) - return (Datum) 0; - + return false; deconstruct_array_builtin(mcv_arr, TEXTOID, &mcv_elems, &mcv_nulls, &check_nummcv); Assert(check_nummcv == (nitems * numattrs)); - datum = import_mcvlist(tup, WARNING, numattrs, - atttypids, atttypmods, atttypcolls, - nitems, mcv_elems, mcv_nulls, - (bool *) ARR_DATA_PTR(nulls_arr), - (float8 *) ARR_DATA_PTR(freqs_arr), - (float8 *) ARR_DATA_PTR(base_freqs_arr)); + datum = statext_mcv_import(tup, WARNING, numattrs, + atttypids, atttypmods, atttypcolls, + nitems, mcv_elems, mcv_nulls, + (bool *) ARR_DATA_PTR(nulls_arr), + (float8 *) ARR_DATA_PTR(freqs_arr), + (float8 *) ARR_DATA_PTR(base_freqs_arr)); values[Anum_pg_statistic_ext_data_stxdmcv - 1] = datum; replaces[Anum_pg_statistic_ext_data_stxdmcv - 1] = true; @@ -780,9 +790,9 @@ check_mcvlist_array(ArrayType *arr, int argindex, int required_ndims, } /* - * Warn of type mismatch. Common pattern. + * Warn of type mismatch. */ -static Datum +static void warn_type_mismatch(Datum d, const char *argname) { char *s = TextDatumGetCString(d); @@ -791,7 +801,6 @@ warn_type_mismatch(Datum d, const char *argname) errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("could not match expression %s element \"%s\" with input type", argname, s)); - return (Datum) 0; } /* @@ -912,8 +921,11 @@ import_expressions(Relation pgsd, int numexprs, &values[Anum_pg_statistic_stanullfrac - 1]); if (!ok) - return warn_type_mismatch(exprs_elems[null_frac_idx], - extexprarginfo[NULL_FRAC_ELEM].argname); + { + warn_type_mismatch(exprs_elems[null_frac_idx], + extexprarginfo[NULL_FRAC_ELEM].argname); + return (Datum) 0; + } } if (!exprs_nulls[avg_width_idx]) @@ -922,8 +934,11 @@ import_expressions(Relation pgsd, int numexprs, &values[Anum_pg_statistic_stawidth - 1]); if (!ok) - return warn_type_mismatch(exprs_elems[avg_width_idx], - extexprarginfo[AVG_WIDTH_ELEM].argname); + { + warn_type_mismatch(exprs_elems[avg_width_idx], + extexprarginfo[AVG_WIDTH_ELEM].argname); + return (Datum) 0; + } } if (!exprs_nulls[n_distinct_idx]) @@ -932,8 +947,11 @@ import_expressions(Relation pgsd, int numexprs, &values[Anum_pg_statistic_stadistinct - 1]); if (!ok) - return warn_type_mismatch(exprs_elems[n_distinct_idx], - extexprarginfo[N_DISTINCT_ELEM].argname); + { + warn_type_mismatch(exprs_elems[n_distinct_idx], + extexprarginfo[N_DISTINCT_ELEM].argname); + return (Datum) 0; + } } /* @@ -1025,6 +1043,7 @@ import_expressions(Relation pgsd, int numexprs, errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("could not match expression %s of element \"%s\" with input type", extexprarginfo[CORRELATION_ELEM].argname, s)); + pfree(s); return (Datum) 0; } @@ -1164,6 +1183,8 @@ delete_pg_statistic_ext_data(Oid stxoid, bool inherited) /* * Restore (insert or replace) statistics for the given statistics object. + * + * TODO add a bunch of comments here.. */ Datum pg_restore_extended_stats(PG_FUNCTION_ARGS) diff --git a/src/backend/statistics/mcv.c b/src/backend/statistics/mcv.c index f4f6836149a5..3f17c4903ce3 100644 --- a/src/backend/statistics/mcv.c +++ b/src/backend/statistics/mcv.c @@ -2173,21 +2173,39 @@ mcv_clause_selectivity_or(PlannerInfo *root, StatisticExtInfo *stat, } /* - * The MCV is an array of records, but this is expected as 4 separate arrays. - * It is not possible to have a generic input function for pg_mcv_list - * because the most_common_values is a composite type with element types - * defined by the specific statistics object. - */ + * Free allocations of a MCVList. + */ +void +statext_mcv_free(MCVList *mcvlist) +{ + for (int i = 0; i < mcvlist->nitems; i++) + { + MCVItem *item = &mcvlist->items[i]; + + pfree(item->values); + pfree(item->isnull); + } + pfree(mcvlist); +} + +/* + * The MCV is an array of records, but this is expected as 4 separate arrays. + * It is not possible to have a generic input function for pg_mcv_list + * because the most_common_values is a composite type with element types + * defined by the specific statistics object. + * + * TODO: We ought to document this function, its purpose, and from where + * the inputs come from as well as what they mean. + */ Datum -import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, - int32 *atttypmods, Oid *atttypcolls, int nitems, - Datum *mcv_elems, bool *mcv_nulls, - bool *mcv_elem_nulls, float8 *freqs, float8 *base_freqs) +statext_mcv_import(HeapTuple tup, int elevel, int numattrs, + Oid *atttypids, int32 *atttypmods, Oid *atttypcolls, + int nitems, Datum *mcv_elems, bool *mcv_nulls, + bool *mcv_elem_nulls, float8 *freqs, + float8 *base_freqs) { MCVList *mcvlist; bytea *bytes; - - HeapTuple *vatuples; VacAttrStats **vastats; /* @@ -2246,8 +2264,9 @@ import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, { ereport(elevel, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("MCV elemement \"%s\" does not match expected input type.", s))); - return (Datum) 0; + errmsg("could not parse MCV element \"%s\": incorrect value", s))); + pfree(s); + goto error; } pfree(s); @@ -2263,7 +2282,6 @@ import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, * to be filled out. */ vastats = (VacAttrStats **) palloc0_array(VacAttrStats *, numattrs); - vatuples = (HeapTuple *) palloc0_array(HeapTuple, numattrs); for (int i = 0; i < numattrs; i++) { @@ -2275,8 +2293,6 @@ import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, if (!HeapTupleIsValid(typtuple)) elog(ERROR, "cache lookup failed for type %u", typid); - vatuples[i] = typtuple; - vastats[i] = palloc0_object(VacAttrStats); vastats[i]->attrtype = (Form_pg_type) GETSTRUCT(typtuple); @@ -2288,30 +2304,24 @@ import_mcvlist(HeapTuple tup, int elevel, int numattrs, Oid *atttypids, for (int i = 0; i < numattrs; i++) { - pfree(vatuples[i]); pfree(vastats[i]); } - pfree((void *) vatuples); - pfree((void *) vastats); + pfree(vastats); + + pfree(mcv_elems); + pfree(mcv_nulls); if (bytes == NULL) { ereport(elevel, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("Unable to import mcv list"))); - return (Datum) 0; + errmsg("could not import MCV list"))); + goto error; } - for (int i = 0; i < nitems; i++) - { - MCVItem *item = &mcvlist->items[i]; - - pfree(item->values); - pfree(item->isnull); - } - pfree(mcvlist); - pfree(mcv_elems); - pfree(mcv_nulls); - return PointerGetDatum(bytes); + +error: + statext_mcv_free(mcvlist); + return (Datum) 0; } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index f2e48fc691f9..88b917b29f4b 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -18555,7 +18555,7 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) PQExpBuffer pq = createPQExpBuffer(); /* - * Set up query for constraint-specific details. + * Set up query for details about extended statistics objects. * * 19+: query pg_stats_ext and pg_stats_ext_exprs as-is 15-18: query * pg_stats_ext translating the ndistinct and dependencies, 14: @@ -18579,7 +18579,7 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) appendPQExpBufferStr(pq, "false AS inherited, "); /* - * The ndistinnct and depdendencies formats changed in v19, so + * The ndistinct and dependencies formats changed in v19, so * everything before that needs to be translated. * * The ndistinct translation converts this: @@ -18678,11 +18678,11 @@ dumpStatisticsExtStats(Archive *fout, const StatsExtInfo *statsextinfo) "FROM ( " "SELECT s.stxndistinct AS n_distinct, " " s.stxdependencies AS dependencies " - "FROM pg_catalog.pg_statistics_ext AS s " + "FROM pg_catalog.pg_statistic_ext AS s " "JOIN pg_catalog.pg_namespace AS n " "ON n.oid = s.stxnamespace " "WHERE n.nspname = $1 " - "AND e.stxname = $2 " + "AND s.stxname = $2 " ") AS e "); /* we always have an inherited column, but it may be a constant */ diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index e3a993f9fcf7..63e84a099487 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1622,7 +1622,7 @@ SELECT pg_catalog.pg_restore_extended_stats( 'statistics_schemaname', 'schema_not_exist', 'statistics_name', 'test_stat_clone', 'inherited', false); -WARNING: could not find schema "test_stat_clone" +WARNING: could not find schema "schema_not_exist" pg_restore_extended_stats --------------------------- f diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index da3719e72b45..b9c1b7362f69 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2205,7 +2205,7 @@ SELECT pg_restore_attribute_stats( 'statistics_name', 'stats_name'::name, 'inherited', false, 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4}'::pg_ndistinct, - 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000}'::pg_dependencies + 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000}'::pg_dependencies, 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[]); </programlisting> </para> -- 2.51.0
signature.asc
Description: PGP signature
