From 5755338b53cac18d893b0b4bff24d2e6ccfbc8bb Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Wed, 24 Jul 2024 23:45:26 -0400 Subject: [PATCH v25 1/5] Create function pg_set_relation_stats. This function is used to tweak statistics on any relation that the user owns. The first parameter, relation, is used to identify the the relation to be modified. The remaining parameters correspond to the statistics attributes in pg_class: relpages, reltuples, and relallisvible. This function allows the user to tweak pg_class statistics values, allowing the user to inflate rowcounts, table size, and visibility to see what effect those changes will have on the the query planner. The function has no return value. --- src/include/catalog/pg_proc.dat | 9 + src/include/statistics/statistics.h | 2 + src/backend/statistics/Makefile | 3 +- src/backend/statistics/meson.build | 1 + src/backend/statistics/statistics.c | 278 +++++++++++++++++++++ src/test/regress/expected/stats_import.out | 99 ++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/stats_import.sql | 79 ++++++ doc/src/sgml/func.sgml | 63 +++++ 9 files changed, 534 insertions(+), 2 deletions(-) create mode 100644 src/backend/statistics/statistics.c create mode 100644 src/test/regress/expected/stats_import.out create mode 100644 src/test/regress/sql/stats_import.sql diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 06b2f4ba66..7ebcf612ca 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12235,4 +12235,13 @@ proargnames => '{summarized_tli,summarized_lsn,pending_lsn,summarizer_pid}', prosrc => 'pg_get_wal_summarizer_state' }, +# Statistics Import +{ oid => '8048', + descr => 'set statistics on relation', + proname => 'pg_set_relation_stats', provolatile => 'v', proisstrict => 'f', + proparallel => 'u', prorettype => 'void', + proargtypes => 'oid int4 float4 int4', + proargnames => '{relation,relpages,reltuples,relallvisible}', + prosrc => 'pg_set_relation_stats' }, + ] diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h index 7f2bf18716..68441dfc16 100644 --- a/src/include/statistics/statistics.h +++ b/src/include/statistics/statistics.h @@ -127,4 +127,6 @@ extern StatisticExtInfo *choose_best_statistics(List *stats, char requiredkind, int nclauses); extern HeapTuple statext_expressions_load(Oid stxoid, bool inh, int idx); +extern Datum pg_set_relation_stats(PG_FUNCTION_ARGS); + #endif /* STATISTICS_H */ diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makefile index 89cf8c2797..e4f8ab7c4f 100644 --- a/src/backend/statistics/Makefile +++ b/src/backend/statistics/Makefile @@ -16,6 +16,7 @@ OBJS = \ dependencies.o \ extended_stats.o \ mcv.o \ - mvdistinct.o + mvdistinct.o \ + statistics.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/statistics/meson.build b/src/backend/statistics/meson.build index 73b29a3d50..331e82c776 100644 --- a/src/backend/statistics/meson.build +++ b/src/backend/statistics/meson.build @@ -5,4 +5,5 @@ backend_sources += files( 'extended_stats.c', 'mcv.c', 'mvdistinct.c', + 'statistics.c', ) diff --git a/src/backend/statistics/statistics.c b/src/backend/statistics/statistics.c new file mode 100644 index 0000000000..3c72633686 --- /dev/null +++ b/src/backend/statistics/statistics.c @@ -0,0 +1,278 @@ +/*------------------------------------------------------------------------- + * statistics.c + * + * PostgreSQL statistics import + * + * Code supporting the direct importation of relation statistics, similar to + * what is done by the ANALYZE command. + * + * + * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/statistics/statistics.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/heapam.h" +#include "access/htup_details.h" +#include "catalog/indexing.h" +#include "catalog/pg_class_d.h" +#include "catalog/pg_collation.h" +#include "catalog/pg_database.h" +#include "catalog/pg_operator.h" +#include "catalog/pg_type.h" +#include "fmgr.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "nodes/nodeFuncs.h" +#include "statistics/statistics.h" +#include "storage/lockdefs.h" +#include "utils/acl.h" +#include "utils/array.h" +#include "utils/builtins.h" +#include "utils/elog.h" +#include "utils/float.h" +#include "utils/fmgroids.h" +#include "utils/lsyscache.h" +#include "utils/rangetypes.h" +#include "utils/rel.h" +#include "utils/syscache.h" +#include "utils/typcache.h" + +/* + * Names of parameters found in the function pg_set_relation_stats. + */ +const char *relation_name = "relation"; +const char *relpages_name = "relpages"; +const char *reltuples_name = "reltuples"; +const char *relallvisible_name = "relallvisible"; + +/* + * A role has privileges to set statistics on the relation if any of the + * following are true: + * - the role owns the current database and the relation is not shared + * - the role has the MAINTAIN privilege on the relation + * + */ +static bool +can_modify_relation(Relation rel) +{ + Oid relid = RelationGetRelid(rel); + Form_pg_class reltuple = rel->rd_rel; + + return ((object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) + && !reltuple->relisshared) || + pg_class_aclcheck(relid, GetUserId(), ACL_MAINTAIN) == ACLCHECK_OK); +} + +/* + * Internal function for modifying statistics for a relation. + */ +static bool +relation_statistics_update(Datum relation_datum, bool relation_isnull, + Datum version_datum, bool version_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) +{ + int elevel = (raise_errors) ? ERROR : WARNING; + Oid relation; + int version; + int relpages; + float4 reltuples; + int relallvisible; + + Relation rel; + HeapTuple ctup; + Form_pg_class pgcform; + + /* If we don't know what relation we're modifying, give up */ + if (relation_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Parameter %s cannot be null", relation_name))); + return false; + } + else + relation = DatumGetObjectId(relation_datum); + + /* NULL version means assume current server version */ + if (version_isnull) + version = PG_VERSION_NUM; + else + { + version = DatumGetInt32(version_datum); + if (version < 90200) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Cannot export statistics prior to version 9.2"))); + return false; + } + } + + if (relpages_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL", relpages_name))); + return false; + } + else + { + relpages = DatumGetInt32(relpages_datum); + if (relpages < -1) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be < -1", relpages_name))); + return false; + } + } + + if (reltuples_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL", reltuples_name))); + return false; + } + else + { + reltuples = DatumGetFloat4(reltuples_datum); + if (reltuples < -1.0) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be < -1.0", reltuples_name))); + return false; + } + } + + if (relallvisible_isnull) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be NULL", relallvisible_name))); + return false; + } + else + { + relallvisible = DatumGetInt32(relallvisible_datum); + if (relallvisible < -1) + { + ereport(elevel, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("%s cannot be < -1", relallvisible_name))); + return false; + } + } + + /* + * Open the relation, getting ShareUpdateExclusiveLock to ensure that no + * other stat-setting operation can run on it concurrently. + */ + rel = table_open(RelationRelationId, ShareUpdateExclusiveLock); + + ctup = SearchSysCacheCopy1(RELOID, relation_datum); + if (!HeapTupleIsValid(ctup)) + { + ereport(elevel, + (errcode(ERRCODE_OBJECT_IN_USE), + errmsg("pg_class entry for relid %u not found", relation))); + table_close(rel, NoLock); + return false; + } + + pgcform = (Form_pg_class) GETSTRUCT(ctup); + + if (!can_modify_relation(rel)) + { + ereport(elevel, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied for relation %s", + RelationGetRelationName(rel)))); + table_close(rel, NoLock); + return false; + } + + /* Only update pg_class if there is a meaningful change */ + if ((pgcform->reltuples != reltuples) + || (pgcform->relpages != relpages) + || (pgcform->relallvisible != relallvisible)) + { + if (in_place) + { + /* + * In place update for upgrades when we expect to touch most + * relations in the database and don't want to bloat pg_class. + */ + pgcform->relpages = relpages; + pgcform->reltuples = reltuples; + pgcform->relallvisible = relallvisible; + heap_inplace_update(rel, ctup); + } + else + { + /* + * Regular transactional update. + */ + int cols[3] = {Anum_pg_class_relpages, + Anum_pg_class_reltuples, + Anum_pg_class_relallvisible}; + + Datum values[3] = {relpages_datum, reltuples_datum, + relallvisible_datum}; + + bool nulls[3] = {false, false, false}; + + TupleDesc tupdesc = RelationGetDescr(rel); + HeapTuple ntup; + + CatalogIndexState indstate = CatalogOpenIndexes(rel); + + ntup = heap_modify_tuple_by_cols(ctup, tupdesc, 3, cols, values, nulls); + + CatalogTupleUpdateWithInfo(rel, &ntup->t_self, ntup, indstate); + heap_freetuple(ntup); + CatalogCloseIndexes(indstate); + } + } + + table_close(rel, NoLock); + + PG_RETURN_BOOL(true); +} + +/* + * Set statistics for a given pg_class entry. + * + * Use a regular, transactional update. + * + * Statistics are assumed to be presented in format friendly to the current + * server version. + */ +Datum +pg_set_relation_stats(PG_FUNCTION_ARGS) +{ + Datum version_datum = (Datum) 0; + bool version_isnull = true; + bool raise_errors = true; + bool in_place = 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); + + PG_RETURN_VOID(); +} diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out new file mode 100644 index 0000000000..e596e858d6 --- /dev/null +++ b/src/test/regress/expected/stats_import.out @@ -0,0 +1,99 @@ +CREATE SCHEMA stats_import; +CREATE TYPE stats_import.complex_type AS ( + a integer, + b real, + c text, + d date, + e jsonb); +CREATE TABLE stats_import.test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import.complex_type, + arange int4range, + tags text[] +); +-- starting stats +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 0 | -1 | 0 +(1 row) + +-- ERROR: regclass not found +SELECT + pg_catalog.pg_set_relation_stats( + relation => 0::Oid, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); +ERROR: pg_class entry for relid 0 not found +-- ERROR: relpages NULL +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => NULL::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); +ERROR: relpages cannot be NULL +-- ERROR: reltuples NULL +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => NULL::real, + relallvisible => 4::integer); +ERROR: reltuples cannot be NULL +-- ERROR: relallvisible NULL +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => NULL::integer); +ERROR: relallvisible cannot be NULL +-- true: all named +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + pg_set_relation_stats +----------------------- + +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 17 | 400 | 4 +(1 row) + +-- true: all positional +SELECT + pg_catalog.pg_set_relation_stats( + 'stats_import.test'::regclass, + 18::integer, + 401.0::real, + 5::integer); + pg_set_relation_stats +----------------------- + +(1 row) + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + relpages | reltuples | relallvisible +----------+-----------+--------------- + 18 | 401 | 5 +(1 row) + +DROP SCHEMA stats_import CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to type stats_import.complex_type +drop cascades to table stats_import.test diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 2429ec2bba..85fc85bfa0 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -28,7 +28,7 @@ test: strings md5 numerology point lseg line box path polygon circle date time t # geometry depends on point, lseg, line, box, path, polygon, circle # horology depends on date, time, timetz, timestamp, timestamptz, interval # ---------- -test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc database +test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc database stats_import # ---------- # Load huge amounts of data diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql new file mode 100644 index 0000000000..282c031335 --- /dev/null +++ b/src/test/regress/sql/stats_import.sql @@ -0,0 +1,79 @@ +CREATE SCHEMA stats_import; + +CREATE TYPE stats_import.complex_type AS ( + a integer, + b real, + c text, + d date, + e jsonb); + +CREATE TABLE stats_import.test( + id INTEGER PRIMARY KEY, + name text, + comp stats_import.complex_type, + arange int4range, + tags text[] +); + +-- starting stats +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- ERROR: regclass not found +SELECT + pg_catalog.pg_set_relation_stats( + relation => 0::Oid, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + +-- ERROR: relpages NULL +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => NULL::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + +-- ERROR: reltuples NULL +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => NULL::real, + relallvisible => 4::integer); + +-- ERROR: relallvisible NULL +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => NULL::integer); + +-- true: all named +SELECT + pg_catalog.pg_set_relation_stats( + relation => 'stats_import.test'::regclass, + relpages => 17::integer, + reltuples => 400.0::real, + relallvisible => 4::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +-- true: all positional +SELECT + pg_catalog.pg_set_relation_stats( + 'stats_import.test'::regclass, + 18::integer, + 401.0::real, + 5::integer); + +SELECT relpages, reltuples, relallvisible +FROM pg_class +WHERE oid = 'stats_import.test'::regclass; + +DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b669ab7f97..eda8a039e5 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -29778,6 +29778,69 @@ 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_set_relation_stats + + pg_set_relation_stats ( + relation regclass + , relpages integer, + , reltuples real, + , relallvisible integer ) + void + + + Updates the pg_class row for the specified + relation, setting the values for the columns + reltuples, + relpages, and + relallvisible. + + + This function mimics the behavior of ANALYZE in its + effects on the values in pg_class, except that + the values are supplied as parameters rather than derived from table + sampling. + + + The caller must either be the owner of the relation, or have superuser + privileges. + + + The value of relpages must not be less than + 0. + + + The value of reltuples must not be less than + -1.0. + + + The value of relallvisible must not be less + than 0. + + + + + +
+ Database Object Location Functions -- 2.45.2