From 572babd4793e21311d6a4295c3f3b4fca65657ce Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Sat, 27 Jul 2024 02:49:39 -0400 Subject: [PATCH v25 3/5] Create functions pg_restore_*_stats() These functions are variadic requivalents of the pg_set_*_stats() functions, which have a function signature each stat getting its own defined parameter (or parameter pair, as the case may be). Such a rigid function signature would make future compability difficult, and future compatibility is just what pg_dump needs. Instead, these functions have all input arguments put into a variable argument list organized in name-value pairs. The leading or "name" parameters must all be of type text and the string must exactly correspond to the name of a statistics parameter in the corresponding pg_set_X_stats function. The trailing or "value" parameter must be of the type expected by the same-named parameter in the pg_set_X_stats function. Names that do not match a parameter name and types that do not match the expected type will emit a warning and be ignored. The functions return a tuple indicating whether the row was written or not, the names of statistics columns that were applied, the names of statistics columns that were supplied but deficient in some way and therefore discarded, and parameters that didn't match any know parameter and thus were also discarded. The intention of these functions is to be used in pg_dump/pg_restore and pg_upgrade to allow the user to avoid having to run vacuumdb --analyze-in-stages after an upgrade or restore. --- src/include/catalog/pg_proc.dat | 20 + src/include/statistics/statistics.h | 2 + src/backend/statistics/statistics.c | 664 ++++++++++++++- src/test/regress/expected/stats_import.out | 936 ++++++++++++++++++++- src/test/regress/sql/stats_import.sql | 695 ++++++++++++++- doc/src/sgml/func.sgml | 477 +++++++++++ 6 files changed, 2710 insertions(+), 84 deletions(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 11cbd9eded..9fefa0410c 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12250,5 +12250,25 @@ proargtypes => 'oid name bool float4 int4 float4 text _float4 text float4 text _float4 _float4 text float4 text', proargnames => '{relation,attname,inherited,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation,most_common_elems,most_common_elem_freqs,elem_count_histogram,range_length_histogram,range_empty_frac,range_bounds_histogram}', prosrc => 'pg_set_attribute_stats' }, +{ oid => '8050', + descr => 'set statistics on relation', + proname => 'pg_restore_relation_stats', provariadic => 'any', + proisstrict => 'f', provolatile => 'v', + proparallel => 'u', prorettype => 'record', + proargtypes => 'any', + proallargtypes => '{any,bool,_text,_text,_text}', + proargnames => '{kwargs,row_written,stats_applied,stats_rejected,params_rejected}', + proargmodes => '{v, o, o, o, o}', + prosrc => 'pg_restore_relation_stats' }, +{ oid => '8051', + descr => 'set statistics on attribute', + proname => 'pg_restore_attribute_stats', provariadic => 'any', + proisstrict => 'f', provolatile => 'v', + proparallel => 'u', prorettype => 'record', + proargtypes => 'any', + proallargtypes => '{any,bool,_text,_text,_text}', + proargnames => '{kwargs,row_written,stats_applied,stats_rejected,params_rejected}', + proargmodes => '{v, o, o, o, o}', + prosrc => 'pg_restore_attribute_stats' }, ] diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h index 73d3b541dd..06425173e6 100644 --- a/src/include/statistics/statistics.h +++ b/src/include/statistics/statistics.h @@ -129,5 +129,7 @@ extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx); extern Datum pg_set_relation_stats(PG_FUNCTION_ARGS); extern Datum pg_set_attribute_stats(PG_FUNCTION_ARGS); +extern Datum pg_restore_relation_stats(PG_FUNCTION_ARGS); +extern Datum pg_restore_attribute_stats(PG_FUNCTION_ARGS); #endif /* STATISTICS_H */ diff --git a/src/backend/statistics/statistics.c b/src/backend/statistics/statistics.c index bfabe0b563..9767c8f4f3 100644 --- a/src/backend/statistics/statistics.c +++ b/src/backend/statistics/statistics.c @@ -26,6 +26,7 @@ #include "catalog/pg_database.h" #include "catalog/pg_operator.h" #include "catalog/pg_type.h" +#include "catalog/pg_type_d.h" #include "fmgr.h" #include "funcapi.h" #include "miscadmin.h" @@ -39,6 +40,7 @@ #include "utils/float.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" +#include "utils/palloc.h" #include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -46,7 +48,8 @@ /* * Names of parameters found in the functions pg_set_relation_stats and - * pg_set_attribute_stats + * pg_set_attribute_stats, as well as keyword names used in + * pg_restore_relation_stats and pg_restore_attribute_stats. */ const char *relation_name = "relation"; const char *relpages_name = "relpages"; @@ -67,6 +70,17 @@ const char *elem_count_hist_name = "elem_count_histogram"; const char *range_length_hist_name = "range_length_histogram"; const char *range_empty_frac_name = "range_empty_frac"; const char *range_bounds_hist_name = "range_bounds_histogram"; +const char *version_name = "version"; + +typedef struct kwarg_data { + const char *argname; + const char *typname; + Oid typoid; + bool required; + Datum datum; + bool isnull; + bool set; +} kwarg_data; /* * A role has privileges to set statistics on the relation if any of the @@ -95,7 +109,10 @@ relation_statistics_update(Datum relation_datum, bool relation_isnull, Datum relpages_datum, bool relpages_isnull, Datum reltuples_datum, bool reltuples_isnull, Datum relallvisible_datum, bool relallvisible_isnull, - bool raise_errors, bool in_place) + bool raise_errors, bool in_place, + bool *relpages_applied, + bool *reltuples_applied, + bool *relallvisible_applied) { int elevel = (raise_errors) ? ERROR : WARNING; Oid relation; @@ -108,6 +125,10 @@ relation_statistics_update(Datum relation_datum, bool relation_isnull, HeapTuple ctup; Form_pg_class pgcform; + *relpages_applied = false; + *reltuples_applied = false; + *relallvisible_applied = false; + /* If we don't know what relation we're modifying, give up */ if (relation_isnull) { @@ -264,7 +285,11 @@ relation_statistics_update(Datum relation_datum, bool relation_isnull, table_close(rel, NoLock); - PG_RETURN_BOOL(true); + *relpages_applied = true; + *reltuples_applied = true; + *relallvisible_applied = true; + + return true; } /* @@ -283,12 +308,19 @@ pg_set_relation_stats(PG_FUNCTION_ARGS) bool raise_errors = true; bool in_place = false; + bool relpages_applied = false; + bool reltuples_applied = false; + bool relallvisible_applied = false; + relation_statistics_update(PG_GETARG_DATUM(0), PG_ARGISNULL(0), version_datum, version_isnull, PG_GETARG_DATUM(1), PG_ARGISNULL(1), PG_GETARG_DATUM(2), PG_ARGISNULL(2), PG_GETARG_DATUM(3), PG_ARGISNULL(3), - raise_errors, in_place); + raise_errors, in_place, + &relpages_applied, + &reltuples_applied, + &relallvisible_applied); PG_RETURN_VOID(); } @@ -471,7 +503,7 @@ cast_stavalues(FmgrInfo *flinfo, Datum d, Oid typid, int32 typmod, * Report any failures at the level of elevel. */ static bool -array_check(Datum datum, int one_dim, const char *statname, int elevel) +array_check(Datum datum, bool one_dim, const char *statname, int elevel) { ArrayType *arr = DatumGetArrayTypeP(datum); int16 elmlen; @@ -568,7 +600,20 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, Datum range_length_hist_datum, bool range_length_hist_isnull, Datum range_empty_frac_datum, bool range_empty_frac_isnull, Datum range_bounds_hist_datum, bool range_bounds_hist_isnull, - bool raise_errors) + bool raise_errors, + bool *null_frac_applied, + bool *avg_width_applied, + bool *n_distinct_applied, + bool *mc_vals_applied, + bool *mc_freqs_applied, + bool *histogram_bounds_applied, + bool *correlation_applied, + bool *mc_elems_applied, + bool *mc_elem_freqs_applied, + bool *elem_count_hist_applied, + bool *range_length_hist_applied, + bool *range_empty_frac_applied, + bool *range_bounds_hist_applied) { int elevel = (raise_errors) ? ERROR : WARNING; @@ -610,6 +655,20 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, nulls[i] = false; } + *null_frac_applied = false; + *avg_width_applied = false; + *n_distinct_applied = false; + *mc_vals_applied = false; + *mc_freqs_applied = false; + *histogram_bounds_applied = false; + *correlation_applied = false; + *mc_elems_applied = false; + *mc_elem_freqs_applied = false; + *elem_count_hist_applied = false; + *range_length_hist_applied = false; + *range_empty_frac_applied = false; + *range_bounds_hist_applied = false; + /* * Some parameters are "required" in that nothing can happen if any of * them are NULL. @@ -951,6 +1010,9 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, * STATISTIC_KIND_MCV * * most_common_vals: ANYARRAY::text most_common_freqs: real[] + * + * As detailed in statistics.h, the stavalues and stanumbers arrays cannot + * contain NULL values. */ if (!mc_vals_isnull) { @@ -987,6 +1049,9 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, * STATISTIC_KIND_HISTOGRAM * * histogram_bounds: ANYARRAY::text + * + * As detailed in statistics.h, the stavalues array cannot + * contain NULL values. */ if (!histogram_bounds_isnull) { @@ -1044,6 +1109,9 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, * most_common_elem_freqs: real[] * * most_common_elems : ANYARRAY::text + * + * As detailed in statistics.h, the stavalues and stanumbers arrays cannot + * contain NULL values. */ if (!mc_elems_isnull) { @@ -1082,6 +1150,9 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, * STATISTIC_KIND_DECHIST * * elem_count_histogram: real[] + * + * As detailed in statistics.h, the stanumbers array cannot contain NULL + * values. */ if (!elem_count_hist_isnull) { @@ -1090,13 +1161,18 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, Datum stacoll = ObjectIdGetDatum(typcoll); Datum stanumbers = elem_count_hist_datum; - values[Anum_pg_statistic_stakind1 - 1 + stakindidx] = stakind; - values[Anum_pg_statistic_staop1 - 1 + stakindidx] = staop; - values[Anum_pg_statistic_stacoll1 - 1 + stakindidx] = stacoll; - values[Anum_pg_statistic_stanumbers1 - 1 + stakindidx] = stanumbers; - nulls[Anum_pg_statistic_stavalues1 - 1 + stakindidx] = true; + if (array_check(stanumbers, true, elem_count_hist_name, elevel)) + { + values[Anum_pg_statistic_stakind1 - 1 + stakindidx] = stakind; + values[Anum_pg_statistic_staop1 - 1 + stakindidx] = staop; + values[Anum_pg_statistic_stacoll1 - 1 + stakindidx] = stacoll; + values[Anum_pg_statistic_stanumbers1 - 1 + stakindidx] = stanumbers; + nulls[Anum_pg_statistic_stavalues1 - 1 + stakindidx] = true; - stakindidx++; + stakindidx++; + } + else + elem_count_hist_isnull = true; } /* @@ -1108,6 +1184,9 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, * it is numerically greater, and all other stakinds appear in numerical * order. We duplicate this quirk to make before/after tests of * pg_statistic records easier. + * + * As detailed in statistics.h, the stavalues array cannot contain NULL + * values. */ if (!range_bounds_hist_isnull) { @@ -1143,6 +1222,9 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, * range_empty_frac: real * * range_length_histogram: double precision[]::text + * + * As detailed in statistics.h, the stavalues array cannot contain NULL + * values. */ if (!range_length_hist_isnull) { @@ -1169,6 +1251,7 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, values[Anum_pg_statistic_stacoll1 - 1 + stakindidx] = stacoll; values[Anum_pg_statistic_stanumbers1 - 1 + stakindidx] = stanumbers; values[Anum_pg_statistic_stavalues1 - 1 + stakindidx] = stavalues; + stakindidx++; } else @@ -1190,6 +1273,21 @@ attribute_statistics_update(Datum relation_datum, bool relation_isnull, stakindidx++; } + /* Any stats whose null flags remained false got written */ + *null_frac_applied = !null_frac_isnull; + *avg_width_applied = !avg_width_isnull; + *n_distinct_applied = !n_distinct_isnull; + *mc_vals_applied = !mc_vals_isnull; + *mc_freqs_applied = !mc_freqs_isnull; + *histogram_bounds_applied = !histogram_bounds_isnull; + *correlation_applied = !correlation_isnull; + *mc_elems_applied = !mc_elems_isnull; + *mc_elem_freqs_applied = !mc_elem_freqs_isnull; + *elem_count_hist_applied = !elem_count_hist_isnull; + *range_length_hist_applied = !range_length_hist_isnull; + *range_empty_frac_applied = !range_empty_frac_isnull; + *range_bounds_hist_applied = !range_bounds_hist_isnull; + update_pg_statistic(values, nulls); relation_close(rel, NoLock); @@ -1237,6 +1335,20 @@ pg_set_attribute_stats(PG_FUNCTION_ARGS) bool version_isnull = true; bool raise_errors = true; + bool null_frac_applied = false; + bool avg_width_applied = false; + bool n_distinct_applied = false; + bool mc_vals_applied = false; + bool mc_freqs_applied = false; + bool histogram_bounds_applied = false; + bool correlation_applied = false; + bool mc_elems_applied = false; + bool mc_elem_freqs_applied = false; + bool elem_count_hist_applied = false; + bool range_length_hist_applied = false; + bool range_empty_frac_applied = false; + bool range_bounds_hist_applied = false; + attribute_statistics_update( PG_GETARG_DATUM(0), PG_ARGISNULL(0), PG_GETARG_DATUM(1), PG_ARGISNULL(1), @@ -1255,7 +1367,533 @@ pg_set_attribute_stats(PG_FUNCTION_ARGS) PG_GETARG_DATUM(13), PG_ARGISNULL(13), PG_GETARG_DATUM(14), PG_ARGISNULL(14), PG_GETARG_DATUM(15), PG_ARGISNULL(15), - raise_errors); + raise_errors, + &null_frac_applied, + &avg_width_applied, + &n_distinct_applied, + &mc_vals_applied, + &mc_freqs_applied, + &histogram_bounds_applied, + &correlation_applied, + &mc_elems_applied, + &mc_elem_freqs_applied, + &elem_count_hist_applied, + &range_length_hist_applied, + &range_empty_frac_applied, + &range_bounds_hist_applied); PG_RETURN_VOID(); } + +/* + * Append to a potentially NULL Text Array + */ +static ArrayBuildState* +text_array_append(ArrayBuildState *arr, const char *s) +{ + Datum d = CStringGetTextDatum(s); + + if (arr == NULL) + arr = initArrayResult(TEXTOID, CurrentMemoryContext, false); + + return accumArrayResult(arr, d, false, TEXTOID, CurrentMemoryContext); +} + + +/* + * Convert a potentially NULL ArrayBuildState to Datum + */ +static Datum +text_array_to_datum(ArrayBuildState *arr) +{ + if (arr == NULL) + return (Datum) 0; + return makeArrayResult(arr, CurrentMemoryContext); +} + +/* + * Given a table of kwarg_data, and the output of extract_variadic_args(), + * walk the list of variadics, matching those to kwarg_data and filling out + * the datums and nulls accordingly. Parameters that are rejected may be + * appended to the params_rejected_arr. + * + * Returns true if the the whole table was processed and the kwargs can + * be used for the stats-setting function call. + */ +static bool +process_kwargs(FunctionCallInfo fcinfo, int kwarg_start, + kwarg_data kwargs[], int nkwargs, + ArrayBuildState **rejected) +{ + Datum *args; + bool *argnulls; + Oid *types; + int nargs; + + nargs = extract_variadic_args(fcinfo, kwarg_start, true, + &args, &types, &argnulls); + + /* Arguments must be in key-value pairs */ + if (nargs % 2 == 1) + return false; + + /* loop through args, matching params to their arg indexes */ + for (int i = 0; i < nargs; i += 2) + { + char *statname; + int argidx = i + 1; + bool found = false; + + if (types[i] != TEXTOID) + { + ereport(WARNING, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("stat names must be of type text"))); + return false; + } + statname = TextDatumGetCString(args[i]); + + for (int j = 0; !found && j < nkwargs; j++) + { + if (strcmp(statname, kwargs[j].argname) != 0) + continue; + + found = true; + + /* + * Go with the first matching set of each arg, + * ignoring duplicates, do not add to rejected list because + * seeing the same parameter in two places is confusing. + */ + if (kwargs[j].set) + { + ereport(WARNING, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("stat name %s already used, subsequent values ignored", + statname))); + *rejected = text_array_append(*rejected, statname); + } + else if (types[argidx] != kwargs[j].typoid) + { + ereport(WARNING, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s must be of type oid %d (%s) but is type oid %d", + statname, kwargs[j].typoid, kwargs[j].typname, + types[argidx]))); + + *rejected = text_array_append(*rejected, statname); + } + else + { + kwargs[j].set = true; + kwargs[j].datum = args[argidx]; + kwargs[j].isnull = argnulls[argidx]; + } + } + + if (!found) + { + ereport(WARNING, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("stat name %s is not recognized, values ignored", + statname))); + *rejected = text_array_append(*rejected, statname); + } + pfree(statname); + } + + /* + * Iterate over the list to see if any required parameters were omitted. + */ + for (int j = 0; j < nkwargs; j++) + { + if (kwargs[j].required) + { + if (!kwargs[j].set) + { + ereport(WARNING, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("parameter %s is required but not set", + kwargs[j].argname))); + return false; + } + } + } + + return true; +} + + + /* + * Restore statistics for a given pg_class entry. + * + * This does an in-place (i.e. non-transactional) update of pg_class, just as + * is done in ANALYZE. It is also done with elevel set to WARNING, such + * that data errors will generate warnings and may prevent the other stats + * from being written, but it will not actually fail the function. This is a + * requirement for use in binary upgrade. + */ +Datum +pg_restore_relation_stats(PG_FUNCTION_ARGS) +{ + #define NUM_RELARGS 5 + + kwarg_data relargs[NUM_RELARGS] = { + {relation_name, "oid", OIDOID, true, (Datum) 0, true, false }, + {version_name, "integer", INT4OID, true, (Datum) 0, true, false}, + {relpages_name, "integer", INT4OID, true, (Datum) 0, true, false}, + {reltuples_name, "real", FLOAT4OID, true, (Datum) 0, true, false}, + {relallvisible_name, "integer", INT4OID, true, (Datum) 0, true, false} + }; + + bool kwarg_ok; + + TupleDesc tupdesc; + HeapTuple htup; + Datum retvalues[4]; + bool retnulls[4]; + + bool ok; + bool relpages_applied = false; + bool reltuples_applied = false; + bool relallvisible_applied = false; + + ArrayBuildState *params_rejected_arr = NULL; + + /* Initial set of retvals in case we error out */ + retvalues[0] = BoolGetDatum(false); + retnulls[0] = false; + retvalues[1] = (Datum) 0; /* _text */ + retnulls[1] = true; + retvalues[2] = (Datum) 0; /* _text */ + retnulls[2] = true; + retvalues[3] = (Datum) 0; /* _text */ + retnulls[3] = true; + + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + kwarg_ok = process_kwargs(fcinfo, 0, relargs, NUM_RELARGS, + ¶ms_rejected_arr); + + if (!kwarg_ok) + { + retvalues[3] = text_array_to_datum(params_rejected_arr); + retnulls[3] = (params_rejected_arr == NULL); + htup = heap_form_tuple(tupdesc, retvalues, retnulls); + PG_RETURN_DATUM(HeapTupleGetDatum(htup)); + } + + ok = relation_statistics_update(relargs[0].datum, relargs[0].isnull, + relargs[1].datum, relargs[1].isnull, + relargs[2].datum, relargs[2].isnull, + relargs[3].datum, relargs[3].isnull, + relargs[4].datum, relargs[4].isnull, + false, /* raise_errors */ + true, /* in_place */ + &relpages_applied, + &reltuples_applied, + &relallvisible_applied); + + if (ok) + { + /* + * The record was written, report what made it in and what didn't. + */ + ArrayBuildState *applied_arr = NULL; + ArrayBuildState *rejected_arr = NULL; + + if (!relargs[2].isnull) + { + if (relpages_applied) + applied_arr = text_array_append(applied_arr, relpages_name); + else + rejected_arr = text_array_append(rejected_arr, relpages_name); + } + + if (!relargs[3].isnull) + { + if (reltuples_applied) + applied_arr = text_array_append(applied_arr, reltuples_name); + else + rejected_arr = text_array_append(rejected_arr, reltuples_name); + } + + if (!relargs[4].isnull) + { + if (relallvisible_applied) + applied_arr = text_array_append(applied_arr, relallvisible_name); + else + rejected_arr = text_array_append(rejected_arr, relallvisible_name); + } + + retvalues[0] = true; + retvalues[1] = text_array_to_datum(applied_arr); + retnulls[1] = (applied_arr == NULL); + retvalues[2] = text_array_to_datum(rejected_arr); + retnulls[2] = (rejected_arr == NULL); + retvalues[3] = text_array_to_datum(params_rejected_arr); + retnulls[3] = (params_rejected_arr == NULL); + } + + htup = heap_form_tuple(tupdesc, retvalues, retnulls); + PG_RETURN_DATUM(HeapTupleGetDatum(htup)); +} + +/* + * Import statistics for a given relation attribute. + * + * This will insert/replace a row in pg_statistic for the given combinarion + * of relation, attribute, and inherited flag. + * + * The version parameter is for future use in the events as future versions + * may change them meaning of certain parameters. + * + * The variadic parameters all represent name-value pairs, with the names + * corresponding to attributes in pg_stats. Unkown names will generate a + * warning. + * + * Parameters null_frac, avg_width, and n_distinct are required because + * those attributes have no default value in pg_statistic. + * + * The remaining parameters all belong to a specific stakind, and all are + * optional. Some stakinds have multiple parameters, and in those cases + * both parameters must be specified if one of them is, otherwise a + * warning is generated but the rest of the stats may still be imported. + * + * If there is no attribute with a matching attname in the relation, the + * function will raise a warning and return false. + * + * Parameters corresponding to ANYARRAY columns are instead passed in as text + * values, which is a valid input string for an array of the type or element + * type of the attribute. + */ +Datum +pg_restore_attribute_stats(PG_FUNCTION_ARGS) +{ + #define NUM_ATTARGS 17 + + kwarg_data attargs[NUM_ATTARGS] = { + {relation_name, "oid", OIDOID, true, (Datum) 0, true, false }, + {attname_name, "name", NAMEOID, true, (Datum) 0, true, false }, + {inherited_name, "inherited", BOOLOID, true, (Datum) 0, true, false}, + {version_name, "integer", INT4OID, false, (Datum) 0, true, false}, + {null_frac_name, "real", FLOAT4OID, true, (Datum) 0, true, false}, + {avg_width_name, "integer", INT4OID, true, (Datum) 0, true, false}, + {n_distinct_name, "real", FLOAT4OID, true, (Datum) 0, true, false}, + {mc_vals_name, "text", TEXTOID, false, (Datum) 0, true, false}, + {mc_freqs_name, "real[]", FLOAT4ARRAYOID, false, (Datum) 0, true, false}, + {histogram_bounds_name, "text", TEXTOID, false, (Datum) 0, true, false}, + {correlation_name, "real", FLOAT4OID, false, (Datum) 0, true, false}, + {mc_elems_name, "text", TEXTOID, false, (Datum) 0, true, false}, + {mc_elem_freqs_name, "real[]", FLOAT4ARRAYOID, false, (Datum) 0, true, false}, + {elem_count_hist_name, "real[]", FLOAT4ARRAYOID, false, (Datum) 0, true, false}, + {range_length_hist_name, "text", TEXTOID, false, (Datum) 0, true, false}, + {range_empty_frac_name, "real", FLOAT4OID, false, (Datum) 0, true, false}, + {range_bounds_hist_name, "text", TEXTOID, false, (Datum) 0, true, false}, + }; + + bool null_frac_applied = false; + bool avg_width_applied = false; + bool n_distinct_applied = false; + bool mc_vals_applied = false; + bool mc_freqs_applied = false; + bool histogram_bounds_applied = false; + bool correlation_applied = false; + bool mc_elems_applied = false; + bool mc_elem_freqs_applied = false; + bool elem_count_hist_applied = false; + bool range_length_hist_applied = false; + bool range_empty_frac_applied = false; + bool range_bounds_hist_applied = false; + + bool raise_errors = false; + HeapTuple htup; + TupleDesc tupdesc; + Datum retvalues[4]; + bool retnulls[4]; + bool kwarg_ok = false; + bool ok = false; + + ArrayBuildState *params_rejected_arr = NULL; + + /* Initial set of retvals in case we error out */ + retvalues[0] = BoolGetDatum(false); + retnulls[0] = false; + retvalues[1] = (Datum) 0; /* _text */ + retnulls[1] = true; + retvalues[2] = (Datum) 0; /* _text */ + retnulls[2] = true; + retvalues[3] = (Datum) 0; /* _text */ + retnulls[3] = true; + + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + kwarg_ok = process_kwargs(fcinfo, 0, attargs, NUM_ATTARGS, + ¶ms_rejected_arr); + + if (!kwarg_ok) + { + retvalues[3] = text_array_to_datum(params_rejected_arr); + retnulls[3] = (params_rejected_arr == NULL); + htup = heap_form_tuple(tupdesc, retvalues, retnulls); + PG_RETURN_DATUM(HeapTupleGetDatum(htup)); + } + + ok = attribute_statistics_update(attargs[0].datum, attargs[0].isnull, + attargs[1].datum, attargs[1].isnull, + attargs[2].datum, attargs[2].isnull, + attargs[3].datum, attargs[3].isnull, + attargs[4].datum, attargs[4].isnull, + attargs[5].datum, attargs[5].isnull, + attargs[6].datum, attargs[6].isnull, + attargs[7].datum, attargs[7].isnull, + attargs[8].datum, attargs[8].isnull, + attargs[9].datum, attargs[9].isnull, + attargs[10].datum, attargs[10].isnull, + attargs[11].datum, attargs[11].isnull, + attargs[12].datum, attargs[12].isnull, + attargs[13].datum, attargs[13].isnull, + attargs[14].datum, attargs[14].isnull, + attargs[15].datum, attargs[15].isnull, + attargs[16].datum, attargs[16].isnull, + raise_errors, + &null_frac_applied, + &avg_width_applied, + &n_distinct_applied, + &mc_vals_applied, + &mc_freqs_applied, + &histogram_bounds_applied, + &correlation_applied, + &mc_elems_applied, + &mc_elem_freqs_applied, + &elem_count_hist_applied, + &range_length_hist_applied, + &range_empty_frac_applied, + &range_bounds_hist_applied); + + if (ok) + { + /* + * The record was written, report what made it in and what didn't. + */ + ArrayBuildState *applied_arr = NULL; + ArrayBuildState *rejected_arr = NULL; + + if (!attargs[4].isnull) + { + if (null_frac_applied) + applied_arr = text_array_append(applied_arr, null_frac_name); + else + rejected_arr = text_array_append(rejected_arr, null_frac_name); + } + + if (!attargs[5].isnull) + { + if (avg_width_applied) + applied_arr = text_array_append(applied_arr, avg_width_name); + else + rejected_arr = text_array_append(rejected_arr, avg_width_name); + } + + if (!attargs[6].isnull) + { + if (n_distinct_applied) + applied_arr = text_array_append(applied_arr, n_distinct_name); + else + rejected_arr = text_array_append(rejected_arr, n_distinct_name); + } + + if (!attargs[7].isnull) + { + if (mc_vals_applied) + applied_arr = text_array_append(applied_arr, mc_vals_name); + else + rejected_arr = text_array_append(rejected_arr, mc_vals_name); + } + + if (!attargs[8].isnull) + { + if (mc_freqs_applied) + applied_arr = text_array_append(applied_arr, mc_freqs_name); + else + rejected_arr = text_array_append(rejected_arr, mc_freqs_name); + } + + if (!attargs[9].isnull) + { + if (histogram_bounds_applied) + applied_arr = text_array_append(applied_arr, histogram_bounds_name); + else + rejected_arr = text_array_append(rejected_arr, histogram_bounds_name); + } + + if (!attargs[10].isnull) + { + if (correlation_applied) + applied_arr = text_array_append(applied_arr, correlation_name); + else + rejected_arr = text_array_append(rejected_arr, correlation_name); + } + + if (!attargs[11].isnull) + { + if (mc_elems_applied) + applied_arr = text_array_append(applied_arr, mc_elems_name); + else + rejected_arr = text_array_append(rejected_arr, mc_elems_name); + } + + if (!attargs[12].isnull) + { + if (mc_elem_freqs_applied) + applied_arr = text_array_append(applied_arr, mc_elem_freqs_name); + else + rejected_arr = text_array_append(rejected_arr, mc_elem_freqs_name); + } + + if (!attargs[13].isnull) + { + if (elem_count_hist_applied) + applied_arr = text_array_append(applied_arr, elem_count_hist_name); + else + rejected_arr = text_array_append(rejected_arr, elem_count_hist_name); + } + + if (!attargs[14].isnull) + { + if (range_length_hist_applied) + applied_arr = text_array_append(applied_arr, range_length_hist_name); + else + rejected_arr = text_array_append(rejected_arr, range_length_hist_name); + } + + if (!attargs[15].isnull) + { + if (range_empty_frac_applied) + applied_arr = text_array_append(applied_arr, range_empty_frac_name); + else + rejected_arr = text_array_append(rejected_arr, range_empty_frac_name); + } + + if (!attargs[16].isnull) + { + if (range_bounds_hist_applied) + applied_arr = text_array_append(applied_arr, range_bounds_hist_name); + else + rejected_arr = text_array_append(rejected_arr, range_bounds_hist_name); + } + + retvalues[0] = true; + retvalues[1] = text_array_to_datum(applied_arr); + retnulls[1] = (applied_arr == NULL); + retvalues[2] = text_array_to_datum(rejected_arr); + retnulls[2] = (rejected_arr == NULL); + retvalues[3] = text_array_to_datum(params_rejected_arr); + retnulls[3] = (params_rejected_arr == NULL); + } + + htup = heap_form_tuple(tupdesc, retvalues, retnulls); + PG_RETURN_DATUM(HeapTupleGetDatum(htup)); +} diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 834950da72..7026aebb87 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -32,7 +32,7 @@ ERROR: pg_class entry for relid 0 not found -- ERROR: relpages NULL SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => NULL::integer, reltuples => 400.0::real, relallvisible => 4::integer); @@ -40,7 +40,7 @@ ERROR: relpages cannot be NULL -- ERROR: reltuples NULL SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => 17::integer, reltuples => NULL::real, relallvisible => 4::integer); @@ -48,7 +48,7 @@ ERROR: reltuples cannot be NULL -- ERROR: relallvisible NULL SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => 17::integer, reltuples => 400.0::real, relallvisible => NULL::integer); @@ -56,7 +56,7 @@ ERROR: relallvisible cannot be NULL -- true: all named SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => 17::integer, reltuples => 400.0::real, relallvisible => 4::integer); @@ -76,7 +76,7 @@ WHERE oid = 'stats_import.test'::regclass; -- true: all positional SELECT pg_catalog.pg_set_relation_stats( - 'stats_import.test'::regclass, + 'stats_import.test'::regclass::oid, 18::integer, 401.0::real, 5::integer); @@ -113,7 +113,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: relation cannot be NULL -- error: attname null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => NULL::name, inherited => false::boolean, null_frac => 0.1::real, @@ -122,7 +122,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: attname cannot be NULL -- error: inherited null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => NULL::boolean, null_frac => 0.1::real, @@ -131,7 +131,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: inherited cannot be NULL -- error: null_frac null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => NULL::real, @@ -140,16 +140,16 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: null_frac cannot be NULL -- error: avg_width null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.1::real, avg_width => NULL::integer, n_distinct => 0.3::real); ERROR: avg_width cannot be NULL --- error: avg_width null +-- error: n_distinct null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.1::real, @@ -158,7 +158,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: n_distinct cannot be NULL -- ok: no stakinds SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.1::real, @@ -179,7 +179,7 @@ WHERE starelid = 'stats_import.test'::regclass; -- warn: mcv / mcf null mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -190,7 +190,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: most_common_vals cannot be NULL when most_common_freqs is NOT NULL -- warn: mcv / mcf null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -201,7 +201,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: most_common_freqs cannot be NULL when most_common_vals is NOT NULL -- warn: mcv / mcf type mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -213,7 +213,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: invalid input syntax for type integer: "2023-09-30" -- warning: mcv cast failure SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -225,7 +225,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: invalid input syntax for type integer: "four" -- ok: mcv+mcf SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -253,7 +253,7 @@ AND attname = 'id'; -- warn: histogram elements null value -- this generates no warnings, but perhaps it should SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -264,7 +264,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: histogram_bounds array cannot contain NULL values -- ok: histogram_bounds SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -290,7 +290,7 @@ AND attname = 'id'; -- ok: correlation SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -315,7 +315,7 @@ AND attname = 'id'; -- warn: scalars can't have mcelem SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -327,7 +327,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: Relation test attname id is a scalar type, cannot have stats of type most_common_elems, ignored -- warn: mcelem / mcelem mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -338,7 +338,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: most_common_elem_freqs cannot be NULL when most_common_elems is NOT NULL -- warn: mcelem / mcelem null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -349,7 +349,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: most_common_elems cannot be NULL when most_common_elem_freqs is NOT NULL -- ok: mcelem SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -376,7 +376,7 @@ AND attname = 'tags'; -- warn: scalars can't have elem_count_histogram SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -387,7 +387,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: Relation test attname id is a scalar type, cannot have stats of type elem_count_histogram, ignored -- warn: elem_count_histogram null element SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -395,14 +395,21 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, elem_count_histogram => '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] ); - pg_set_attribute_stats ------------------------- - +ERROR: elem_count_histogram array cannot contain NULL values +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | | | | (1 row) -- ok: elem_count_histogram SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -428,7 +435,7 @@ AND attname = 'tags'; -- warn: scalars can't have range stats SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -440,7 +447,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: Relation test attname id is not a range type, cannot have stats of type range_length_histogram -- warn: range_empty_frac range_length_hist null mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -451,7 +458,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: range_empty_frac cannot be NULL when range_length_histogram is NOT NULL -- warn: range_empty_frac range_length_hist null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -462,7 +469,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: range_length_histogram cannot be NULL when range_empty_frac is NOT NULL -- ok: range_empty_frac + range_length_hist SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -489,7 +496,7 @@ AND attname = 'arange'; -- warn: scalars can't have range stats SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -500,7 +507,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ERROR: Relation test attname id is not a range type, cannot have stats of type range_bounds_histogram -- ok: range_bounds_histogram SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -526,7 +533,7 @@ AND attname = 'arange'; -- warn: exceed STATISTIC_NUM_SLOTS SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -735,6 +742,861 @@ WHERE s.starelid = 'stats_import.is_odd'::regclass; ---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- (0 rows) +-- +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 1 | 4 | 0 +(1 row) + +-- reject: object doesn't exist +SELECT * +FROM pg_restore_relation_stats( + 'relation', '0'::oid, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); +WARNING: pg_class entry for relid 0 not found + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- reject: reltuples, relallvisible missing +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', '17'::integer); +WARNING: parameter reltuples is required but not set + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- reject: null value +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', NULL::real, + 'relallvisible', 4::integer); +WARNING: reltuples cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- reject: bad relpages type +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', 'nope'::text, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); +WARNING: relpages must be of type oid 23 (integer) but is type oid 25 +WARNING: parameter relpages is required but not set + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | {relpages} +(1 row) + +-- ok +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', 17::integer, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); + row_written | stats_applied | stats_rejected | params_rejected +-------------+------------------------------------+----------------+----------------- + t | {relpages,reltuples,relallvisible} | | +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass::oid; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 17 | 400 | 4 +(1 row) + +-- reject: object does not exist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', '0'::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +WARNING: Parameter relation OID 0 is invalid + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- reject: relation null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', NULL::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +WARNING: relation cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- reject: attname null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', NULL::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +WARNING: attname cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- false: inherited null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', NULL::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +WARNING: inherited cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- false: null_frac null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', NULL::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); +WARNING: null_frac cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- false: avg_width null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', NULL::integer, + 'n_distinct', 0.3::real); +WARNING: avg_width cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- false: avg_width null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', NULL::real); +WARNING: n_distinct cannot be NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- ok: no stakinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+----------------+----------------- + t | {null_frac,avg_width,n_distinct} | | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.1 | 2 | 0.3 | | | | | | | | | | +(1 row) + +-- warn: mcv / mcf null mismatch part 1 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_freqs', '{0.1,0.2,0.3}'::real[] + ); +WARNING: most_common_vals cannot be NULL when most_common_freqs is NOT NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+---------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {most_common_freqs} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | | | | | | | | | | +(1 row) + +-- warn: mcv / mcf null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.2::real, + 'most_common_vals', '{1,2,3}'::text + ); +WARNING: most_common_freqs cannot be NULL when most_common_vals is NOT NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {most_common_vals} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.2 | | | | | | | | | | +(1 row) + +-- warn: mcv / mcf type mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.3::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.2,0.1}'::double precision[] + ); +WARNING: most_common_freqs must be of type oid 1021 (real[]) but is type oid 1022 +WARNING: most_common_freqs cannot be NULL when most_common_vals is NOT NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------+--------------------- + t | {null_frac,avg_width,n_distinct} | {most_common_vals} | {most_common_freqs} +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.3 | | | | | | | | | | +(1 row) + +-- warning: mcv cast failure +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.4::real, + 'most_common_vals', '{2,four,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); +WARNING: invalid input syntax for type integer: "four" + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {most_common_vals,most_common_freqs} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.4 | | | | | | | | | | +(1 row) + +-- ok: mcv+mcf +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------------------------------------------------------------+----------------+----------------- + t | {null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs} | | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | +(1 row) + +-- warn: NULL in histogram array +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'histogram_bounds', '{1,NULL,3,4}'::text + ); +WARNING: histogram_bounds array cannot contain NULL values + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {histogram_bounds} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | | | | | | | | | | +(1 row) + +-- ok: histogram_bounds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'histogram_bounds', '{1,2,3,4}'::text ); + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------------------------------------------+----------------+----------------- + t | {null_frac,avg_width,n_distinct,histogram_bounds} | | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | | | {1,2,3,4} | | | | | | | +(1 row) + +-- warn: elem_count_histogram null element +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); +WARNING: elem_count_histogram array cannot contain NULL values + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+------------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {elem_count_histogram} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | | | | | | +(1 row) + +-- ok: elem_count_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + row_written | stats_applied | stats_rejected | params_rejected +-------------+-------------------------------------------------------+----------------+----------------- + t | {null_frac,avg_width,n_distinct,elem_count_histogram} | | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | | | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | +(1 row) + +-- range stats on a scalar type +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.15::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); +WARNING: Relation test attname id is not a range type, cannot have stats of type range_length_histogram + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+-------------------------------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {range_length_histogram,range_empty_frac} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.15 | | | | | | | | | | +(1 row) + +-- warn: range_empty_frac range_length_hist null mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); +WARNING: range_empty_frac cannot be NULL when range_length_histogram is NOT NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {range_length_histogram} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | | | +(1 row) + +-- warn: range_empty_frac range_length_hist null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_empty_frac', 0.5::real + ); +WARNING: range_length_histogram cannot be NULL when range_empty_frac is NOT NULL + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {range_empty_frac} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | | | +(1 row) + +-- ok: range_empty_frac + range_length_hist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + row_written | stats_applied | stats_rejected | params_rejected +-------------+--------------------------------------------------------------------------+----------------+----------------- + t | {null_frac,avg_width,n_distinct,range_length_histogram,range_empty_frac} | | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | {399,499,Infinity} | 0.5 | +(1 row) + +-- warn: range bounds histogram on scalar +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); +WARNING: Relation test attname id is not a range type, cannot have stats of type range_bounds_histogram + row_written | stats_applied | stats_rejected | params_rejected +-------------+----------------------------------+--------------------------+----------------- + t | {null_frac,avg_width,n_distinct} | {range_bounds_histogram} | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | | | | | | | | | | +(1 row) + +-- ok: range_bounds_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------------------------------------------------+----------------+----------------- + t | {null_frac,avg_width,n_distinct,range_bounds_histogram} | | +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram | range_length_histogram | range_empty_frac | range_bounds_histogram +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+-------------------------------------- + stats_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | | | {"[-1,1)","[0,4)","[1,4)","[1,100)"} +(1 row) + +-- warn: too many stat kinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[], + 'histogram_bounds', '{1,2,3,4}'::text, + 'correlation', 1.1::real, + 'most_common_elems', '{3,1}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[], + 'range_empty_frac', -0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text); +WARNING: imported statistics must have a maximum of 5 slots but 6 given + row_written | stats_applied | stats_rejected | params_rejected +-------------+---------------+----------------+----------------- + f | | | +(1 row) + +-- +-- Copy stats from test to test_clone, and is_odd to is_odd_clone +-- +SELECT s.schemaname, s.tablename, s.attname, s.inherited, r.* +FROM pg_catalog.pg_stats AS s +CROSS JOIN LATERAL + pg_catalog.pg_restore_attribute_stats( + 'relation', ('stats_import.' || s.tablename || '_clone')::regclass::oid, + 'attname', s.attname, + 'inherited', s.inherited, + 'version', 150000, + 'null_frac', s.null_frac, + 'avg_width', s.avg_width, + 'n_distinct', s.n_distinct, + 'most_common_vals', s.most_common_vals::text, + 'most_common_freqs', s.most_common_freqs, + 'histogram_bounds', s.histogram_bounds::text, + 'correlation', s.correlation, + 'most_common_elems', s.most_common_elems::text, + 'most_common_elem_freqs', s.most_common_elem_freqs, + 'elem_count_histogram', s.elem_count_histogram, + 'range_bounds_histogram', s.range_bounds_histogram::text, + 'range_empty_frac', s.range_empty_frac, + 'range_length_histogram', s.range_length_histogram::text) AS r +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test', 'is_odd') +ORDER BY s.tablename, s.attname, s.inherited; + schemaname | tablename | attname | inherited | row_written | stats_applied | stats_rejected | params_rejected +--------------+-----------+---------+-----------+-------------+---------------------------------------------------------------------------------+----------------+----------------- + stats_import | is_odd | expr | f | t | {null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,correlation} | | + stats_import | test | arange | f | t | {null_frac,avg_width,n_distinct,range_bounds_histogram} | | + stats_import | test | comp | f | t | {null_frac,avg_width,n_distinct,histogram_bounds,correlation} | | + stats_import | test | id | f | t | {null_frac,avg_width,n_distinct} | | + stats_import | test | name | f | t | {null_frac,avg_width,n_distinct,histogram_bounds,correlation} | | + stats_import | test | tags | f | t | {null_frac,avg_width,n_distinct,elem_count_histogram} | | +(6 rows) + +SELECT c.relname, COUNT(*) AS num_stats +FROM pg_class AS c +JOIN pg_statistic s ON s.starelid = c.oid +WHERE c.relnamespace = 'stats_import'::regnamespace +AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone') +GROUP BY c.relname +ORDER BY c.relname; + relname | num_stats +--------------+----------- + is_odd | 1 + is_odd_clone | 1 + test | 5 + test_clone | 5 +(4 rows) + +-- check test minus test_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass; + attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + +-- check test_clone minus test +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass; + attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + +-- check is_odd minus is_odd_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass; + attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + +-- check is_odd_clone minus is_odd +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass; + attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to type stats_import.complex_type diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 96795edf42..79a5a4704a 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -31,7 +31,7 @@ SELECT -- ERROR: relpages NULL SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => NULL::integer, reltuples => 400.0::real, relallvisible => 4::integer); @@ -39,7 +39,7 @@ SELECT -- ERROR: reltuples NULL SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => 17::integer, reltuples => NULL::real, relallvisible => 4::integer); @@ -47,7 +47,7 @@ SELECT -- ERROR: relallvisible NULL SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => 17::integer, reltuples => 400.0::real, relallvisible => NULL::integer); @@ -55,7 +55,7 @@ SELECT -- true: all named SELECT pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, relpages => 17::integer, reltuples => 400.0::real, relallvisible => 4::integer); @@ -67,7 +67,7 @@ WHERE oid = 'stats_import.test'::regclass; -- true: all positional SELECT pg_catalog.pg_set_relation_stats( - 'stats_import.test'::regclass, + 'stats_import.test'::regclass::oid, 18::integer, 401.0::real, 5::integer); @@ -96,7 +96,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- error: attname null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => NULL::name, inherited => false::boolean, null_frac => 0.1::real, @@ -105,7 +105,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- error: inherited null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => NULL::boolean, null_frac => 0.1::real, @@ -114,7 +114,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- error: null_frac null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => NULL::real, @@ -123,16 +123,16 @@ SELECT pg_catalog.pg_set_attribute_stats( -- error: avg_width null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.1::real, avg_width => NULL::integer, n_distinct => 0.3::real); --- error: avg_width null +-- error: n_distinct null SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.1::real, @@ -141,7 +141,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- ok: no stakinds SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.1::real, @@ -154,7 +154,7 @@ WHERE starelid = 'stats_import.test'::regclass; -- warn: mcv / mcf null mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -165,7 +165,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- warn: mcv / mcf null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -176,7 +176,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- warn: mcv / mcf type mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -188,7 +188,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- warning: mcv cast failure SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -200,7 +200,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- ok: mcv+mcf SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -220,7 +220,7 @@ AND attname = 'id'; -- warn: histogram elements null value -- this generates no warnings, but perhaps it should SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -231,7 +231,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- ok: histogram_bounds SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -249,7 +249,7 @@ AND attname = 'id'; -- ok: correlation SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -266,7 +266,7 @@ AND attname = 'id'; -- warn: scalars can't have mcelem SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -278,7 +278,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- warn: mcelem / mcelem mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -289,7 +289,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- warn: mcelem / mcelem null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -300,7 +300,7 @@ SELECT pg_catalog.pg_set_attribute_stats( -- ok: mcelem SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -319,7 +319,7 @@ AND attname = 'tags'; -- warn: scalars can't have elem_count_histogram SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -329,7 +329,7 @@ SELECT pg_catalog.pg_set_attribute_stats( ); -- warn: elem_count_histogram null element SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -337,9 +337,16 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, elem_count_histogram => '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] ); +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + -- ok: elem_count_histogram SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'tags'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -357,7 +364,7 @@ AND attname = 'tags'; -- warn: scalars can't have range stats SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -366,9 +373,10 @@ SELECT pg_catalog.pg_set_attribute_stats( range_empty_frac => 0.5::real, range_length_histogram => '{399,499,Infinity}'::text ); + -- warn: range_empty_frac range_length_hist null mismatch SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -376,9 +384,10 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, range_length_histogram => '{399,499,Infinity}'::text ); + -- warn: range_empty_frac range_length_hist null mismatch part 2 SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -386,9 +395,10 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, range_empty_frac => 0.5::real ); + -- ok: range_empty_frac + range_length_hist SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -407,7 +417,7 @@ AND attname = 'arange'; -- warn: scalars can't have range stats SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'id'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -415,9 +425,10 @@ SELECT pg_catalog.pg_set_attribute_stats( n_distinct => -0.1::real, range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text ); + -- ok: range_bounds_histogram SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -435,7 +446,7 @@ AND attname = 'arange'; -- warn: exceed STATISTIC_NUM_SLOTS SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, + relation => 'stats_import.test'::regclass::oid, attname => 'arange'::name, inherited => false::boolean, null_frac => 0.5::real, @@ -620,4 +631,620 @@ FROM pg_statistic s JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum WHERE s.starelid = 'stats_import.is_odd'::regclass; +-- +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- reject: object doesn't exist +SELECT * +FROM pg_restore_relation_stats( + 'relation', '0'::oid, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + +-- reject: reltuples, relallvisible missing +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', '17'::integer); + +-- reject: null value +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', NULL::real, + 'relallvisible', 4::integer); + +-- reject: bad relpages type +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', 'nope'::text, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); + +-- ok +SELECT * +FROM pg_restore_relation_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'version', 150000::integer, + 'relpages', 17::integer, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass::oid; + +-- reject: object does not exist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', '0'::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- reject: relation null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', NULL::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- reject: attname null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', NULL::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- false: inherited null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', NULL::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- false: null_frac null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', NULL::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- false: avg_width null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', NULL::integer, + 'n_distinct', 0.3::real); + +-- false: avg_width null +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', NULL::real); + +-- ok: no stakinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv / mcf null mismatch part 1 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_freqs', '{0.1,0.2,0.3}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv / mcf null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.2::real, + 'most_common_vals', '{1,2,3}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcv / mcf type mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.3::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.2,0.1}'::double precision[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warning: mcv cast failure +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.4::real, + 'most_common_vals', '{2,four,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: mcv+mcf +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: NULL in histogram array +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'histogram_bounds', '{1,NULL,3,4}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: histogram_bounds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'histogram_bounds', '{1,2,3,4}'::text ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: elem_count_histogram null element +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'elem_count_histogram', '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- ok: elem_count_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- range stats on a scalar type +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.15::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: range_empty_frac range_length_hist null mismatch +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: range_empty_frac range_length_hist null mismatch part 2 +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_empty_frac', 0.5::real + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- ok: range_empty_frac + range_length_hist +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_empty_frac', 0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: range bounds histogram on scalar +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- ok: range_bounds_histogram +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: too many stat kinds +SELECT * +FROM pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass::oid, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{2,1,3}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[], + 'histogram_bounds', '{1,2,3,4}'::text, + 'correlation', 1.1::real, + 'most_common_elems', '{3,1}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[], + 'range_empty_frac', -0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text); + +-- +-- Copy stats from test to test_clone, and is_odd to is_odd_clone +-- +SELECT s.schemaname, s.tablename, s.attname, s.inherited, r.* +FROM pg_catalog.pg_stats AS s +CROSS JOIN LATERAL + pg_catalog.pg_restore_attribute_stats( + 'relation', ('stats_import.' || s.tablename || '_clone')::regclass::oid, + 'attname', s.attname, + 'inherited', s.inherited, + 'version', 150000, + 'null_frac', s.null_frac, + 'avg_width', s.avg_width, + 'n_distinct', s.n_distinct, + 'most_common_vals', s.most_common_vals::text, + 'most_common_freqs', s.most_common_freqs, + 'histogram_bounds', s.histogram_bounds::text, + 'correlation', s.correlation, + 'most_common_elems', s.most_common_elems::text, + 'most_common_elem_freqs', s.most_common_elem_freqs, + 'elem_count_histogram', s.elem_count_histogram, + 'range_bounds_histogram', s.range_bounds_histogram::text, + 'range_empty_frac', s.range_empty_frac, + 'range_length_histogram', s.range_length_histogram::text) AS r +WHERE s.schemaname = 'stats_import' +AND s.tablename IN ('test', 'is_odd') +ORDER BY s.tablename, s.attname, s.inherited; + +SELECT c.relname, COUNT(*) AS num_stats +FROM pg_class AS c +JOIN pg_statistic s ON s.starelid = c.oid +WHERE c.relnamespace = 'stats_import'::regnamespace +AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone') +GROUP BY c.relname +ORDER BY c.relname; + +-- check test minus test_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass; + +-- check test_clone minus test +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'test_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.test'::regclass; + +-- check is_odd minus is_odd_clone +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass; + +-- check is_odd_clone minus is_odd +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd_clone'::regclass +EXCEPT +SELECT + a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct, + s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5, + s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, + s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5, + s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5, + s.stavalues1::text AS sv1, s.stavalues2::text AS sv2, + s.stavalues3::text AS sv3, s.stavalues4::text AS sv4, + s.stavalues5::text AS sv5, 'is_odd_clone' AS direction +FROM pg_statistic s +JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum +WHERE s.starelid = 'stats_import.is_odd'::regclass; + + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index e05073167b..33cf5a3e12 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -29835,6 +29835,14 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset The value of relallvisible must not be less than 0. + + This function is very close in purpose to + pg_restore_relation_stats, but is intended for + interactive use and for that reason has important differences. First, + any error in the data provided causes the function to fail without + modifying the pg_class row. Second, the + function behaves transactionally, like any normal data update. + @@ -29900,6 +29908,475 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset + + + + pg_restore_relation_stats + + pg_restore_relation_stats ( + VARIADIC kwargs "any" ) + setof record + ( row_written boolean, + stats_applied text[], + stats_rejected text[], + params_reject text[] ) + + + Updates the pg_class row for the specified + relation, setting the values for the columns + reltuples, + relpages, and + relallvisible. It is highly similar to + pg_set_relation_stats, which also mimics the + behavior of ANALYZE in its effect on the values + in pg_class. + + + This function is very close in purpose to + pg_set_relation_stats, but is intended for + use in pg_upgrade and pg_restore + and for that reason has important differences. + + First, any error in the data provided is re-cast as a + WARNING to allow the function to reach completion, + even if it cannot update any statistics. + + + Second, the function updates pg_class + in-place, which is to say non-transactionally. This is done to avoid + the table bloat in pg_class that would inevitably + result from modifying nearly every row in quick succession. + + + Lastly, and most noticiably, in order to facilitate compatibility with + future statistical structures, instead of a fixed argument list, it + takes a series of parameters organized in key-value pairs. Each keyword + parameter must be of type text, and must be followed by + another parameter whose type is determined by the value of the keyword + parameter. The list of accepted keywords and their corresponding parameter + types are as follows: + + + + relation + + + This identifies the value of oid of the + pg_class to be modified. It must be followed + by a parameter of type oid, and the value must reference + an existing relation. This parameter is required. + + + + + version + + + This corresponds to the SERVER_VERSION_NUM + of the database that was the origin of these statistics. It must + be followed by a parameter of type integer. The function + will not process statistics that claim to be generated from a server + prior to version 90200, which means 9.2.0. This + serves to inform the function as to how to adapt the statistics given + to the current database version. This parameter is not required. If + omitted, it will default to the current server version. + + + + + relpages + + + This sets the value of relpages in + pg_class. It must be followed by a + parameter of type integer, and the value must not + be less than 0. This parameter is required. + + + + + reltuples + + + This sets the value of reltuples in + pg_class. It must be followed by a + parameter of type real, and the value must not be less + than -1.0. This parameter is required. + + + + + relallvisible + + + This sets the value of relallvisible in + pg_class. It must be followed by a + parameter of type integer, and the value must not be less + than 0. This parameter is required. + + + + + + Any parameter keywords given that are not in this list will generate a + warning but will otherwise be ignored along with the paired value. + + + The function returns one with the following parameters: + + + + row_written + + + a boolean with the value true if the + statistics were written to the database and false + otherwise. + + + + + stats_applied + + + A text[] where each element is the keyword name of a + statistic that was successfully written. It will be NULL + if row_written is false. + + + + + stats_rejected + + + A text[] where each element is the keyword name of a + statistic that was given to the function but could not be written, + but the row was nevertheless successfully written. It will always + be NULL if row_written + is false. + + + + + params_rejected + + + A text[] where each element is the keyword name of a + keyword that was given to the function but could not be processed + for a variety of reasons (unknown keyword, duplicate of a keyword + already given, etc). + + + + + + The caller must either be the owner of the relation, or have superuser + privileges. + + + This function is very close in purpose to + pg_set_relation_stats, but is intended for + use in pg_upgrade and other situations where the + invocation can be machine generated, and priority is given to forward + compatibility over readability. + + + + + + + pg_restore_attribute_stats + + pg_restore_attribute_stats ( + VARIADIC kwargs "any" ) + setof record + ( row_written boolean, + stats_applied text[], + stats_rejected text[], + params_reject text[] ) + + + Replaces the pg_statistic row for the + pg_attribute row specified by + relation, attname + and inherited values given in the + variant argument list kwargs. It is highly + similar to pg_set_attribute_stats, which also + mimics the behavior of ANALYZE in its effect on + the values in pg_statistic. + + + The purpose of this function is to apply statistics values in an + upgrade situation that are "good enough" for system operation until + they are replaced by the next ANALYZE, usually via + autovacuum. This function is equivalent to + pg_set_attribute_stats with a few important + differences: + + First, any error in the data provided is re-cast as a + WARNING to allow the function to reach completion, + even if it cannot update any statistics. + + + Second, in order to facilitate compatibility with future statistical + structures, instead of a fixed argument list, it takes a series of + parameters organized in key-value pairs. Each keyword parameter must + be of type text, and must be followed by another + parameter whose type is determined by the value of the keyword + parameter. The list of accepted keywords and their corresponding parameter + types are as follows: + + + + relation + + + This identifies the value of oid of the + pg_statistic to be modified. It must be followed + by a parameter of type oid, and the value must reference an + existing relation. This parameter is required. + + + + + attname + + + This identifies the value of attname of the + pg_statistic with the attnum + of the pg_statistic to be modified. It must + be followed by a parameter of type name, and the value must not + reference and existing relation. This parameter is required. + + + + + inherited + + + This identifies the value of inherited of the + pg_statistic to be modified. It must be followed + by a parameter of type boolean. This parameter is required. + + + + + version + + + This corresponds to theSERVER_VERSION_NUM + of the database that was the origin of these statistics. It must + be followed by a parameter of type integer. The function + will not process statistics that claim to be generated from a server + prior to version 90200, which means 9.2.0. This + serves to inform the function as to how to adapt the statistics given + to the current database version. This parameter is not required. If + omitted, it will default to the current server version. + + + + + null_frac + + + This sets the value of null_frac in + pg_statistic. It must be followed by a + parameter of type real. This parameter is required. + + + + + avg_width + + + This sets the value of avg_width in + pg_statistic. It must be followed by a + parameter of type integer. This parameter is required. + + + + + n_distinct + + + This sets the value of n_distinct in + pg_statistic. It must be followed by a + parameter of type real. This parameter is required. + + + + + most_common_vals + + + This sets the value of most_common_vals in + pg_statistic. It must be followed by a + parameter of type text, which represents the text I/O + representation of the array of most common values for this this column's + data. This parameter is optional, but if provided then + most_common_freqs must also be provided. + + + + + most_common_freqs + + + This sets the value of most_common_freqs in + pg_statistic. It must be followed by a + parameter of type real[]. The parameter is optional, but if + provided then most_common_elems must + also be provided. + + + + + histogram_bounds + + + This sets the value of most_common_vals in + pg_statistic. It must be followed by a + parameter of type text, which represents the text I/O + representation of the histogram bounds values for this this column's + data. This parameter is optional. + + + + + correlation + + + This sets the value of correlation in + pg_statistic. It must be followed by a + parameter of type real. The parameter is optional. + + + + + most_common_elems + + + This sets the value of most_common_elemss in + pg_statistic. It must be followed by a + parameter of type text, which represents the text I/O + representation of the array of most common elemens for this this column's + data. This parameter is optional, but if provided then the parameter + most_common_elem_freqs must also be provided. + + + + + most_common_elem_freqs + + + This sets the value of most_common_elem_freqs in + pg_statistic. It must be followed by a + parameter of type real[]. The parameter is optional, but if provided + then most_common_elems must also be provided. + + + + + elem_count_histogram + + + This sets the value of elem_count_histogram in + pg_statistic. It must be followed by a + parameter of type real[]. The parameter is optional. + + + + + range_length_histogram + + + This sets the value of range_length_histogram in + pg_statistic. It must be followed by a + parameter of type text, which represents the text I/O + representation of the array of range lengh histogram for this this column's + data. This parameter is optional, but if provided then the parameter + range_empty_frac must also be provided. + + + + + range_empty_frac + + + This sets the value of range_empty_frac in + pg_statistic. It must be followed by a + parameter of type real. The parameter is optional, but if provided + then range_length_histogram must also be provided. + + + + + range_bounds_histogram + + + This sets the value of range_bounds_histogram in + pg_statistic. It must be followed by a + parameter of type text, which represents the text I/O + representation of the array of range bounds histogram for this this column's + data. This parameter is optional. + + + + + + Any other parameter names given must also have a value pair, but will emit + a warning and thereafter be ignored. + + + The function returns one with the following parameters: + + + + row_written + + + a boolean with the value true if the + statistics were written to the database and false + otherwise. + + + + + stats_applied + + + A text[] where each element is the keyword name of a + statistic that was successfully written. It will be NULL + if row_written is false. + + + + + stats_rejected + + + A text[] where each element is the keyword name of a + statistic that was given to the function but could not be written, + but the row was nevertheless successfully written. It will always + be NULL if row_written + is false. + + + + + params_rejected + + + A text[] where each element is the keyword name of a + keyword that was given to the function but could not be processed + for a variety of reasons (unknown keyword, duplicate of a keyword + already given, etc). + + + + + + -- 2.45.2