From 834a415f594b3662716c9728a2ab46e425e80e82 Mon Sep 17 00:00:00 2001 From: coreyhuinker Date: Thu, 31 Aug 2023 01:30:57 -0400 Subject: [PATCH v1] Introduce the system view pg_stats_export and the function pg_import_rel_stats. pg_stats_export is a view that aggregates pg_statistic data by relation oid and stores all of the column statistical data in a system-indepdent (i.e. no oids) jsonb format, along with the relation's relname, reltuples, and relpages from pg_class, as well as the schemaname from pg_namespace. pg_import_rel_stats is a function which takes a relation oid, server_version_num, num_tuples, num_pages, and a column_stats jsonb in a format matching that of pg_stats_export, and applies that data to the specified pg_class and pg_statistics rows for the relation specified. The most common use-case for such a function is in upgrades and dump/restore, wherein the upgrade process would capture the output of pg_stats_export into a regular table, perform the upgrade, and then join that data to the existing pg_class rows, updating statistics to be a close approximation of what they were just prior to the upgrade. The hope is that these statistics are better than the early stages of --analyze-in-stages and can be applied faster, thus reducing system downtime. The values applied to pg_class are done inline, which is to say non-transactionally. The values applied to pg_statitics are applied transactionally, as if an ANALYZE operation was reading from a cheat-sheet. The statistics applied are no more durable than any other, and will likely be overwritten by the next autovacuum analyze. --- src/include/catalog/pg_proc.dat | 5 + src/include/commands/vacuum.h | 3 + src/backend/catalog/system_views.sql | 92 +++++ src/backend/commands/analyze.c | 581 +++++++++++++++++++++++++++ src/test/regress/expected/rules.out | 47 +++ src/test/regress/expected/vacuum.out | 95 +++++ src/test/regress/sql/vacuum.sql | 83 ++++ doc/src/sgml/func.sgml | 44 +- doc/src/sgml/system-views.sgml | 5 + 9 files changed, 954 insertions(+), 1 deletion(-) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 9805bc6118..48c662c93c 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5636,6 +5636,11 @@ proname => 'pg_stat_get_db_checksum_last_failure', provolatile => 's', proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', prosrc => 'pg_stat_get_db_checksum_last_failure' }, +{ oid => '3813', + descr => 'statistics: import to relation', + proname => 'pg_import_rel_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'bool', proargtypes => 'oid int4 float4 int4 jsonb', + prosrc => 'pg_import_rel_stats' }, { oid => '3074', descr => 'statistics: last reset for a database', proname => 'pg_stat_get_db_stat_reset_time', provolatile => 's', proparallel => 'r', prorettype => 'timestamptz', proargtypes => 'oid', diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 4af02940c5..3ef05fa8a1 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -386,4 +386,7 @@ extern double anl_random_fract(void); extern double anl_init_selection_state(int n); extern double anl_get_next_S(double t, int n, double *stateptr); +extern Datum pg_import_rel_stats(PG_FUNCTION_ARGS); + + #endif /* VACUUM_H */ diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 77b06e2a7a..37383be786 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -253,6 +253,98 @@ CREATE VIEW pg_stats WITH (security_barrier) AS REVOKE ALL ON pg_statistic FROM public; +CREATE VIEW pg_statistic_export WITH (security_barrier) AS + SELECT + n.nspname AS schemaname, + r.relname AS relname, + current_setting('server_version_num')::integer AS server_version_num, + r.reltuples::float4 AS n_tuples, + r.relpages::integer AS n_pages, + ( + SELECT + jsonb_object_agg(a.attname, + ( + SELECT + jsonb_object_agg(attsta.inherit_type, + attsta.stat_obj) + FROM ( + SELECT + CASE s.stainherit + WHEN TRUE THEN 'inherited' + ELSE 'regular' + END AS inherit_type, + jsonb_build_object( + 'attstattarget', + CASE + WHEN a.attstattarget >= 0 THEN a.attstattarget + ELSE current_setting('default_statistics_target')::int4 + END, + 'stanullfrac', s.stanullfrac::text, + 'stawidth', s.stawidth::text, + 'stadistinct', s.stadistinct::text, + 'stakind1', s.stakind1::text, + 'stakind2', s.stakind2::text, + 'stakind3', s.stakind3::text, + 'stakind4', s.stakind4::text, + 'stakind5', s.stakind5::text, + 'staop1', + CASE s.stakind1 + WHEN 0 THEN '0' + WHEN 1 THEN '=' + ELSE '<' + END, + 'staop2', + CASE s.stakind2 + WHEN 0 THEN '0' + WHEN 1 THEN '=' + ELSE '<' + END, + 'staop3', + CASE s.stakind3 + WHEN 0 THEN '0' + WHEN 1 THEN '=' + ELSE '<' + END, + 'staop4', + CASE s.stakind4 + WHEN 0 THEN '0' + WHEN 1 THEN '=' + ELSE '<' + END, + 'staop5', + CASE s.stakind5 + WHEN 0 THEN '0' + WHEN 1 THEN '=' + ELSE '<' + END, + 'stanumbers1', s.stanumbers1::text[], + 'stanumbers2', s.stanumbers2::text[], + 'stanumbers3', s.stanumbers3::text[], + 'stanumbers4', s.stanumbers4::text[], + 'stanumbers5', s.stanumbers5::text[], + /* casting these to text makes re-casting easier */ + 'stavalues1', s.stavalues1::text::text[], + 'stavalues2', s.stavalues2::text::text[], + 'stavalues3', s.stavalues3::text::text[], + 'stavalues4', s.stavalues4::text::text[], + 'stavalues5', s.stavalues5::text::text[] + ) AS stat_obj + FROM pg_statistic AS s + WHERE s.starelid = a.attrelid + AND s.staattnum = a.attnum + ) AS attsta + ) + ORDER BY a.attnum) + FROM pg_attribute AS a + WHERE a.attrelid = r.oid + AND NOT a.attisdropped + AND has_column_privilege(r.oid, a.attnum, 'SELECT') + AND a.attnum > 0 + ) AS columns + FROM pg_class AS r + JOIN pg_namespace AS n + ON n.oid = r.relnamespace; + CREATE VIEW pg_stats_ext WITH (security_barrier) AS SELECT cn.nspname AS schemaname, c.relname AS tablename, diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index bfd981aa3f..426eb19990 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -18,6 +18,7 @@ #include "access/detoast.h" #include "access/genam.h" +#include "access/heapam.h" #include "access/multixact.h" #include "access/relation.h" #include "access/sysattr.h" @@ -40,6 +41,7 @@ #include "commands/vacuum.h" #include "common/pg_prng.h" #include "executor/executor.h" +#include "fmgr.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" @@ -59,14 +61,17 @@ #include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/guc.h" +#include "utils/jsonb.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/numeric.h" #include "utils/pg_rusage.h" #include "utils/sampling.h" #include "utils/sortsupport.h" #include "utils/spccache.h" #include "utils/syscache.h" #include "utils/timestamp.h" +#include "utils/typcache.h" /* Per-index data for ANALYZE */ @@ -3056,3 +3061,579 @@ analyze_mcv_list(int *mcv_counts, } return num_mcv; } + +/* + * Get a JsonbValue from a JsonbContainer + */ +static +JsonbValue *key_lookup(JsonbContainer *cont, const char *key) +{ + return getKeyJsonValueFromContainer(cont, key, strlen(key), NULL); +} + +/* + * Get a JsonbValue from a JsonbContainer and ensure that it is a string + */ +static +JsonbValue *key_lookup_string(JsonbContainer *cont, const char *key) +{ + JsonbValue *j = key_lookup(cont,key); + + if (j == NULL) + return NULL; + + if (j->type != jbvString) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be a string",key))); + } + + return j; +} + +/* + * Get a JsonbContainer from a JsonbContainer and ensure that it is a object + */ +static +JsonbContainer *key_lookup_object(JsonbContainer *cont, const char *key) +{ + JsonbValue *j = key_lookup(cont,key); + + if (j == NULL) + return NULL; + + if ((j->type != jbvBinary) || (!JsonContainerIsObject(j->val.binary.data))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be a binary object", key))); + + return j->val.binary.data; +} + +static +bool jbvIsBinaryArray(JsonbValue *j) +{ + return (j->type == jbvBinary) && JsonContainerIsArray(j->val.binary.data); +} + +static +char *jbvstr_to_cstring(JsonbValue *j) +{ + char *s; + + Assert(j->type == jbvString); + /* make a string we are sure is null-terminated */ + s = palloc(j->val.string.len + 1); + memcpy(s, j->val.string.val, j->val.string.len); + s[j->val.string.len] = '\0'; + return s; +} + +static +Datum jbvstr_to_float4datum(JsonbValue *j) +{ + char * s = jbvstr_to_cstring(j); + Datum result = DirectFunctionCall1(float4in, CStringGetDatum(s)); + pfree(s); + return result; +} + +static +Datum jbvstr_to_int32datum(JsonbValue *j) +{ + char * s = jbvstr_to_cstring(j); + Datum result = DirectFunctionCall1(int4in, CStringGetDatum(s)); + pfree(s); + return result; +} + +static +Datum jbvstr_to_int16datum(JsonbValue *j) +{ + char * s = jbvstr_to_cstring(j); + Datum result = DirectFunctionCall1(int2in, CStringGetDatum(s)); + pfree(s); + return result; +} + +static +Datum jbvstr_to_attrtypedatum(JsonbValue *j, FmgrInfo *finfo, Oid input_func, Oid typioparam, int32 typmod) +{ + char * s = jbvstr_to_cstring(j); + Datum result = InputFunctionCall(finfo, s, typioparam, typmod); + pfree(s); + return result; +} + +static +void import_pg_statistic_rows(Oid relid, Relation sd, TupleDesc tupleDesc, + Form_pg_attribute attr, + FmgrInfo *finfo, Oid input_func, + Oid typioparams, Oid eq_opr, Oid lt_opr, + CatalogIndexState indstate, + JsonbContainer *cont, const char *name, bool inh) +{ + + HeapTuple stup, + tup; + int i, + j, + k; + Datum values[Natts_pg_statistic]; + bool nulls[Natts_pg_statistic]; + bool replaces[Natts_pg_statistic]; + bool newrow = true; + JsonbValue *jv; + + /* Is there already a pg_statistic tuple for this attribute? */ + tup = SearchSysCache3(STATRELATTINH, + ObjectIdGetDatum(relid), + Int16GetDatum(attr->attnum), + BoolGetDatum(inh)); + + if (HeapTupleIsValid(tup)) + { + /* use the tuple that already exists */ + newrow = false; + heap_deform_tuple(tup, tupleDesc, values, nulls); + for (i = 0; i < Natts_pg_statistic; ++i) + replaces[i] = false; + } + else + { + /* new row */ + for (i = 0; i < Natts_pg_statistic; ++i) + { + nulls[i] = false; + replaces[i] = true; + } + + values[Anum_pg_statistic_starelid - 1] = relid; + values[Anum_pg_statistic_staattnum - 1] = attr->attnum; + values[Anum_pg_statistic_stainherit - 1] = BoolGetDatum(inh); + } + + i = Anum_pg_statistic_stanullfrac - 1; + jv = key_lookup_string(cont, "stanullfrac"); + if (jv != NULL) + { + values[i] = jbvstr_to_float4datum(jv); + replaces[i] = true; + } + else if (newrow) + values[i] = Float4GetDatum(0.0); + + i = Anum_pg_statistic_stawidth - 1; + jv = key_lookup_string(cont, "stawidth"); + if (jv != NULL) + { + values[i] = jbvstr_to_int32datum(jv); + replaces[i] = true; + } + else if (newrow) + values[i] = Int32GetDatum(0); + + i = Anum_pg_statistic_stadistinct - 1; + jv = key_lookup_string(cont, "stadistinct"); + if (jv != NULL) + { + values[i] = jbvstr_to_float4datum(jv); + replaces[i] = true; + } + else if (newrow) + values[i] = Float4GetDatum(0.0); + + i = Anum_pg_statistic_stakind1 - 1; + for (k = 1; k <= STATISTIC_NUM_SLOTS; k++) + { + char key[20]; + sprintf(key, "stakind%d", k); + jv = key_lookup_string(cont, key); + if (jv != NULL) + { + values[i] = jbvstr_to_int16datum(jv); + replaces[i] = true; + } + else if (newrow) + values[i] = Int16GetDatum(0); + + i++; + } + + + /* + * set staopN rows. Use staopN as a proxy for when to set stacollN + * + * collation cannot be changed in stats, only attempt to set if this is a + * new row, and set it to the attcollation - it is possible that if this + * column is an expression on an index, then the collation could be + * different but this will be reset anyway on the next autoanalyze. + */ + i = Anum_pg_statistic_staop1 - 1; + j = Anum_pg_statistic_stacoll1 - 1; + for (k = 1; k <= STATISTIC_NUM_SLOTS; k++) + { + char key[20]; + Oid res_opr; + sprintf(key, "staop%d", k); + jv = key_lookup(cont, key); + if (jv != NULL) + { + if (jv->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format: %s must be one of: '=', '<', ''", key))); + + if (jv->val.string.len != 1) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format: %s must be one of: '=', '<', ''", key))); + + if (jv->val.string.val[0] == '0') + res_opr = 0; + else if (jv->val.string.val[0] == '=') + res_opr = eq_opr; + else if (jv->val.string.val[0] == '<') + res_opr = lt_opr; + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format: %s must be one of: '=', '<', ''", key))); + + values[i] = ObjectIdGetDatum(res_opr); + replaces[i] = true; + /* staopN was set, so set stacollN if this row is new */ + if (newrow) + { + if (res_opr != 0) + values[j] = ObjectIdGetDatum(attr->attcollation); + else + values[j] = ObjectIdGetDatum(0); + } + } + else if (newrow) + { + values[i] = ObjectIdGetDatum(0); + values[j] = ObjectIdGetDatum(0); + } + + i++; + j++; + } + + for (k = 1; k <= STATISTIC_NUM_SLOTS; k++) + { + char key[20]; + int num_elements; + Datum *numdatums; + ArrayType *arry; + int n; + + sprintf(key, "stanumbers%d", k); + + /* compute offset to allow for continue bailouts */ + i = Anum_pg_statistic_stanumbers1 - 2 + k; + + jv = key_lookup(cont, key); + + if (jv == NULL) + { + /* no key set, do not modify existing row value */ + if (newrow) + { + nulls[i] = true; + values[i] = (Datum) 0; + } + continue; + } + + /* can be null or a binary array */ + if (jv->type == jbvNull) + { + /* explicitly set valuesN null */ + nulls[i] = true; + values[i] = (Datum) 0; + replaces[i] = true; + continue; + } + + if (!jbvIsBinaryArray(jv)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be a array or null", key))); + + num_elements = JsonContainerSize(jv->val.binary.data); + + if (num_elements == 0) + { + /* empty array is just null */ + nulls[i] = true; + values[i] = (Datum) 0; + replaces[i] = true; + continue; + } + + numdatums = (Datum *) palloc(num_elements * sizeof(Datum)); + for (n = 0; n < num_elements; n++) + { + JsonbValue *elem; + + elem = getIthJsonbValueFromContainer(jv->val.binary.data, n); + if (elem->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format: element %d of %s must be a string", n, key))); + + numdatums[n] = jbvstr_to_float4datum(elem); + } + arry = construct_array_builtin(numdatums, num_elements, FLOAT4OID); + values[i] = PointerGetDatum(arry); /* stanumbersN */ + replaces[i] = true; + } + + i = Anum_pg_statistic_stavalues1 - 1; + for (k = 1; k <= STATISTIC_NUM_SLOTS; k++) + { + char key[20]; + int num_elements; + Datum *numdatums; + ArrayType *arry; + int n; + + sprintf(key, "stavalues%d", k); + + /* compute offset to allow for continue bailouts */ + i = Anum_pg_statistic_stavalues1 - 2 + k; + + jv = key_lookup(cont, key); + + if (jv == NULL) + { + /* no key set, do not modify existing row value */ + if (newrow) + { + nulls[i] = true; + values[i] = (Datum) 0; + } + continue; + } + + /* can be null or a binary array */ + if (jv->type == jbvNull) + { + /* explicitly set valuesN null */ + nulls[i] = true; + values[i] = (Datum) 0; + replaces[i] = true; + continue; + } + + if (!jbvIsBinaryArray(jv)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format, %s must be a array or null", key))); + + num_elements = JsonContainerSize(jv->val.binary.data); + + if (num_elements == 0) + { + /* empty array is just null */ + nulls[i] = true; + values[i] = (Datum) 0; + replaces[i] = true; + continue; + } + + numdatums = (Datum *) palloc(num_elements * sizeof(Datum)); + + for (n = 0; n < num_elements; n++) + { + /* All elements must be of type string that is iocoerce-friendly */ + JsonbValue *elem = getIthJsonbValueFromContainer(jv->val.binary.data, n); + if (elem->type != jbvString) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format: element %d of %s must be a string", + n, key))); + numdatums[n] = jbvstr_to_attrtypedatum(elem, finfo, + input_func, + typioparams, + attr->atttypmod); + } + + arry = construct_array(numdatums, num_elements, attr->atttypid, + attr->attlen, attr->attbyval, attr->attalign); + values[i] = PointerGetDatum(arry); /* stavaluesN */ + replaces[i] = true; + } + + if (newrow) + { + /* insert new tuple */ + stup = heap_form_tuple(RelationGetDescr(sd), values, nulls); + CatalogTupleInsertWithInfo(sd, stup, indstate); + } + else + { + /* modify existing tuple */ + stup = heap_modify_tuple(tup, RelationGetDescr(sd), + values, nulls, replaces); + CatalogTupleUpdateWithInfo(sd, &stup->t_self, stup, indstate); + ReleaseSysCache(tup); + } + + heap_freetuple(stup); +} + + +/* + * Import statistics from JSONB export into relation + * to-do: pg_import_ext_stats() + */ +Datum +pg_import_rel_stats(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int32 stats_version_num = PG_GETARG_INT32(1); + Jsonb *jb = PG_ARGISNULL(4) ? NULL : PG_GETARG_JSONB_P(4); + Relation onerel; + + if (jb != NULL && !JB_ROOT_IS_OBJECT(jb)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics format"))); + + if ( stats_version_num < 80000) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics version: %d is earlier than earliest supported version", + stats_version_num))); + + /* to-do: change this to found current server version */ + if ( stats_version_num > 170000) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid statistics version: %d is greater than current version", + stats_version_num))); + + onerel = vacuum_open_relation(relid, NULL, VACOPT_ANALYZE, true, + ShareUpdateExclusiveLock); + + if (onerel == NULL) + PG_RETURN_BOOL(false); + + if (!vacuum_is_relation_owner(RelationGetRelid(onerel), + onerel->rd_rel, + VACOPT_ANALYZE)) + { + relation_close(onerel, ShareUpdateExclusiveLock); + PG_RETURN_BOOL(false); + } + + + /* only modify pg_class row if changes are to be made */ + if ( ! PG_ARGISNULL(2) || ! PG_ARGISNULL(3) ) + { + Relation pg_class_rel; + HeapTuple ctup; + Form_pg_class pgcform; + + /* + * Open the relation, getting ShareUpdateExclusiveLock to ensure that no + * other stat-setting operation can run on it concurrently. + */ + pg_class_rel = table_open(RelationRelationId, ShareUpdateExclusiveLock); + + /* leave if relation could not be opened or locked */ + if (!pg_class_rel) + PG_RETURN_BOOL(false); + + /* to-do: allow import IF FDW allows analyze */ + if (pg_class_rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot import stats to foreign table"))); + + + ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(ctup)) + elog(ERROR, "pg_class entry for relid %u vanished during statistics import", + relid); + pgcform = (Form_pg_class) GETSTRUCT(ctup); + + /* leave un-set values alone */ + if (! PG_ARGISNULL(2)) + pgcform->reltuples = PG_GETARG_FLOAT4(2); + if (! PG_ARGISNULL(3)) + pgcform->relpages = PG_GETARG_INT32(3); + + heap_inplace_update(pg_class_rel, ctup); + table_close(pg_class_rel, ShareUpdateExclusiveLock); + } + + /* Apply statistical updates, if any, to copied tuple */ + if (! PG_ARGISNULL(4)) + { + TupleDesc tupdesc; + Relation sd; + TupleDesc stupdesc; + CatalogIndexState indstate; + int i; + + tupdesc = RelationGetDescr(onerel); + sd = table_open(StatisticRelationId, RowExclusiveLock); + stupdesc = RelationGetDescr(sd); + indstate = CatalogOpenIndexes(sd); + + for (i = 0; i < tupdesc->natts; i++) + { + Form_pg_attribute attr; + char *attname; + JsonbContainer *attrcont; + JsonbContainer *inheritcont; + + Oid in_func; + Oid typioparams; + FmgrInfo finfo; + TypeCacheEntry *typentry; + + attr = TupleDescAttr(tupdesc, i); + typentry = lookup_type_cache(attr->atttypid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR); + + /* get input function for stavaluesN InputFunctionCall */ + getTypeInputInfo(attr->atttypid, &in_func, &typioparams); + fmgr_info(in_func, &finfo); + + /* Look for column key matching attname */ + attname = NameStr(attr->attname); + + attrcont = key_lookup_object(&jb->root, attname); + + if (attrcont == NULL) + continue; + + inheritcont = key_lookup_object(attrcont, "regular"); + if (inheritcont != NULL) + import_pg_statistic_rows(relid, sd, stupdesc, attr, &finfo, + in_func, typioparams, + typentry->eq_opr, typentry->lt_opr, + indstate, + inheritcont, "regular", false); + + inheritcont = key_lookup_object(attrcont, "inherited"); + if (inheritcont != NULL) + import_pg_statistic_rows(relid, sd, stupdesc, attr, &finfo, + in_func, typioparams, + typentry->eq_opr, typentry->lt_opr, + indstate, + inheritcont, "inherited", true); + } + + CatalogCloseIndexes(indstate); + table_close(sd, RowExclusiveLock); + relation_close(onerel, NoLock); + } + + PG_RETURN_BOOL(true); +} diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 5058be5411..66a56dee65 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2404,6 +2404,53 @@ pg_statio_user_tables| SELECT relid, tidx_blks_hit FROM pg_statio_all_tables WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_statistic_export| SELECT n.nspname AS schemaname, + r.relname, + (current_setting('server_version_num'::text))::integer AS server_version_num, + r.reltuples AS n_tuples, + r.relpages AS n_pages, + ( SELECT jsonb_object_agg(a.attname, ( SELECT jsonb_object_agg(attsta.inherit_type, attsta.stat_obj) AS jsonb_object_agg + FROM ( SELECT + CASE s.stainherit + WHEN true THEN 'inherited'::text + ELSE 'regular'::text + END AS inherit_type, + jsonb_build_object('attstattarget', + CASE + WHEN (a.attstattarget >= 0) THEN (a.attstattarget)::integer + ELSE (current_setting('default_statistics_target'::text))::integer + END, 'stanullfrac', (s.stanullfrac)::text, 'stawidth', (s.stawidth)::text, 'stadistinct', (s.stadistinct)::text, 'stakind1', (s.stakind1)::text, 'stakind2', (s.stakind2)::text, 'stakind3', (s.stakind3)::text, 'stakind4', (s.stakind4)::text, 'stakind5', (s.stakind5)::text, 'staop1', + CASE s.stakind1 + WHEN 0 THEN '0'::text + WHEN 1 THEN '='::text + ELSE '<'::text + END, 'staop2', + CASE s.stakind2 + WHEN 0 THEN '0'::text + WHEN 1 THEN '='::text + ELSE '<'::text + END, 'staop3', + CASE s.stakind3 + WHEN 0 THEN '0'::text + WHEN 1 THEN '='::text + ELSE '<'::text + END, 'staop4', + CASE s.stakind4 + WHEN 0 THEN '0'::text + WHEN 1 THEN '='::text + ELSE '<'::text + END, 'staop5', + CASE s.stakind5 + WHEN 0 THEN '0'::text + WHEN 1 THEN '='::text + ELSE '<'::text + END, 'stanumbers1', (s.stanumbers1)::text[], 'stanumbers2', (s.stanumbers2)::text[], 'stanumbers3', (s.stanumbers3)::text[], 'stanumbers4', (s.stanumbers4)::text[], 'stanumbers5', (s.stanumbers5)::text[], 'stavalues1', ((s.stavalues1)::text)::text[], 'stavalues2', ((s.stavalues2)::text)::text[], 'stavalues3', ((s.stavalues3)::text)::text[], 'stavalues4', ((s.stavalues4)::text)::text[], 'stavalues5', ((s.stavalues5)::text)::text[]) AS stat_obj + FROM pg_statistic s + WHERE ((s.starelid = a.attrelid) AND (s.staattnum = a.attnum))) attsta) ORDER BY a.attnum) AS jsonb_object_agg + FROM pg_attribute a + WHERE ((a.attrelid = r.oid) AND (NOT a.attisdropped) AND has_column_privilege(r.oid, a.attnum, 'SELECT'::text) AND (a.attnum > 0))) AS columns + FROM (pg_class r + JOIN pg_namespace n ON ((n.oid = r.relnamespace))); pg_stats| SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 4def90b805..cf7c4029a5 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -508,3 +508,98 @@ RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; DROP ROLE regress_vacuum; +CREATE TYPE stats_import_complex_type AS ( + a integer, + b float, + c text, + d date, + e jsonb); +CREATE TABLE stats_import_test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import_complex_type +); +INSERT INTO stats_import_test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import_complex_type +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import_complex_type +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import_complex_type +UNION ALL +SELECT 4, 'four', NULL; +ANALYZE stats_import_test; +CREATE TABLE stats_export AS +SELECT e.* +FROM pg_catalog.pg_statistic_export AS e +WHERE e.schemaname = 'public' +AND e.relname = 'stats_import_test'; +SELECT c.reltuples, c.relpages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + reltuples | relpages +-----------+---------- + 4 | 1 +(1 row) + +-- test settting tuples and pages but no columns +SELECT pg_import_rel_stats(c.oid, current_setting('server_version_num')::integer, + 1000.0, 200, NULL::jsonb) +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; +WARNING: relcache reference leak: relation "stats_import_test" not closed + pg_import_rel_stats +--------------------- + t +(1 row) + +SELECT c.reltuples, c.relpages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + reltuples | relpages +-----------+---------- + 1000 | 200 +(1 row) + +-- create a table just like stats_import_test +CREATE TABLE stats_import_clone ( LIKE stats_import_test ); +-- copy table stats to clone table +SELECT pg_import_rel_stats(c.oid, e.server_version_num, + e.n_tuples, e.n_pages, e.columns) +FROM pg_class AS c +JOIN pg_namespace AS n +ON n.oid = c.relnamespace +JOIN stats_export AS e +ON e.schemaname = 'public' +AND e.relname = 'stats_import_test' +WHERE c.oid = 'stats_import_clone'::regclass; + pg_import_rel_stats +--------------------- + t +(1 row) + +-- stats should match +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_test'::regclass +EXCEPT +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_clone'::regclass; + staattnum | 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 +-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+----- +(0 rows) + +DROP TABLE stats_export; +DROP TABLE stats_import_clone; +DROP TABLE stats_import_test; +DROP TYPE stats_import_complex_type; diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql index 51d7b1fecc..602e84dfa2 100644 --- a/src/test/regress/sql/vacuum.sql +++ b/src/test/regress/sql/vacuum.sql @@ -377,3 +377,86 @@ RESET ROLE; DROP TABLE vacowned; DROP TABLE vacowned_parted; DROP ROLE regress_vacuum; + + +CREATE TYPE stats_import_complex_type AS ( + a integer, + b float, + c text, + d date, + e jsonb); + +CREATE TABLE stats_import_test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import_complex_type +); + +INSERT INTO stats_import_test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import_complex_type +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import_complex_type +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import_complex_type +UNION ALL +SELECT 4, 'four', NULL; + +ANALYZE stats_import_test; + +CREATE TABLE stats_export AS +SELECT e.* +FROM pg_catalog.pg_statistic_export AS e +WHERE e.schemaname = 'public' +AND e.relname = 'stats_import_test'; + +SELECT c.reltuples, c.relpages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + +-- test settting tuples and pages but no columns +SELECT pg_import_rel_stats(c.oid, current_setting('server_version_num')::integer, + 1000.0, 200, NULL::jsonb) +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + +SELECT c.reltuples, c.relpages +FROM pg_class AS c +WHERE oid = 'stats_import_test'::regclass; + +-- create a table just like stats_import_test +CREATE TABLE stats_import_clone ( LIKE stats_import_test ); + +-- copy table stats to clone table +SELECT pg_import_rel_stats(c.oid, e.server_version_num, + e.n_tuples, e.n_pages, e.columns) +FROM pg_class AS c +JOIN pg_namespace AS n +ON n.oid = c.relnamespace +JOIN stats_export AS e +ON e.schemaname = 'public' +AND e.relname = 'stats_import_test' +WHERE c.oid = 'stats_import_clone'::regclass; + +-- stats should match +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_test'::regclass +EXCEPT +SELECT staattnum, stainherit, stanullfrac, stawidth, stadistinct, + stakind1, stakind2, stakind3, stakind4, stakind5, + staop1, staop2, staop3, staop4, staop5, stacoll1, stacoll2, stacoll3, stacoll4, stacoll5, + stanumbers1, stanumbers2, stanumbers3, stanumbers4, stanumbers5, + stavalues1::text AS sv1, stavalues2::text AS sv2, stavalues3::text AS sv3, + stavalues4::text AS sv4, stavalues5::text AS sv5 +FROM pg_statistic AS s +WHERE s.starelid = 'stats_import_clone'::regclass; + +DROP TABLE stats_export; +DROP TABLE stats_import_clone; +DROP TABLE stats_import_test; +DROP TYPE stats_import_complex_type; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7a0d4b9134..fbb0257593 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -27904,6 +27904,48 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset in identifying the specific disk files associated with database objects. + + Database Object Statistics Import Functions + + + + + Function + + + Description + + + + + + + + + pg_import_rel_stats + + pg_import_rel_stats ( relation regclass, server_version_num integer, num_tuples float4, num_pages integer, column_stats jsonb ) + boolean + + + Modifies the pg_class row with the + oid matching relation + to set the reltuples and + relpages fields. This is done nontransactionally. + The pg_statistic rows for the + statrelid matching relation + are replaced with the values found in column_stats, + and this is done transactionally. 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 auto-analyze. This function + is used by pg_upgrade and pg_restore + to convey the statistics from the old system version into the new one. + + + + +
+ Database Object Location Functions diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 2b35c2f91b..17430e581b 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -191,6 +191,11 @@ extended planner statistics for expressions + + pg_stats_export + planner statistics for export/upgrade purposes + + pg_tables tables -- 2.41.0