v24-0001-Create-pg_set_relation_stats-function.patch

From 804d765e9cb10fad4aae029b0e89f243263af62f Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 17 Jul 2024 23:23:23 -0400
Subject: [PATCH v24 1/5] Create pg_set_relation_stats function.

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 next parameter, version, is a version number corresponding to
the PG_VERSION_NUM of the system from which the remaining values were
drawn. A NULL value means to use the PG_VERSION_NUM of the current
system. Currently, there is no difference in how the stats are imported
for any supported versions, however, versions below 9.2 will be
rejected.

The remaining parameters correspond to the statistics attributes in
pg_class: relpages, reltuples, and relallisvible.

This function allows the user to tweak pg_class values in-place,
allowing the user to inflate rowcounts, table size, and visibility to see
what effect those changes will have on the the query planner.

The updates to pg_class are NON-transactional. This is done to mimic the
existig behavior of ANALYZE, which does so to avoid bloating pg_class.
---
 src/include/catalog/pg_proc.dat               |   8 +
 src/include/statistics/statistics.h           |   1 +
 src/backend/statistics/Makefile               |   3 +-
 src/backend/statistics/meson.build            |   1 +
 src/backend/statistics/statistics.c           | 231 ++++++++++++++++++
 .../regress/expected/stats_export_import.out  | 160 ++++++++++++
 src/test/regress/parallel_schedule            |   2 +-
 src/test/regress/sql/stats_export_import.sql  | 107 ++++++++
 doc/src/sgml/func.sgml                        |  77 ++++++
 9 files changed, 588 insertions(+), 2 deletions(-)
 create mode 100644 src/backend/statistics/statistics.c
 create mode 100644 src/test/regress/expected/stats_export_import.out
 create mode 100644 src/test/regress/sql/stats_export_import.sql

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..468fe6549c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12208,4 +12208,12 @@
   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 => 'bool',
+  proargtypes => 'oid int4 int4 float4 int4',
+  proargnames => '{relation,version,relpages,reltuples,relallvisible}',
+  prosrc => 'pg_set_relation_stats' }
 ]
diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h
index 7f2bf18716..02e9ad024e 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -127,4 +127,5 @@ 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..27fcdb4717
--- /dev/null
+++ b/src/backend/statistics/statistics.c
@@ -0,0 +1,231 @@
+/*-------------------------------------------------------------------------
+ * statistics.c
+ *
+ *	  POSTGRES 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 "catalog/indexing.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/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);
+}
+
+/*
+ * Set 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.
+ *
+ */
+Datum
+pg_set_relation_stats(PG_FUNCTION_ARGS)
+{
+
+	Datum		relation_datum = PG_GETARG_DATUM(0);
+	bool		relation_isnull = PG_ARGISNULL(0);
+	Oid			relation;
+
+	Datum		version_datum = PG_GETARG_DATUM(1);
+	bool		version_isnull = PG_ARGISNULL(1);
+	int			version;
+
+	Datum		relpages_datum = PG_GETARG_DATUM(2);
+	bool		relpages_isnull = PG_ARGISNULL(2);
+	int			relpages;
+
+	Datum		reltuples_datum = PG_GETARG_DATUM(3);
+	bool		reltuples_isnull = PG_ARGISNULL(3);
+	float4		reltuples;
+
+	Datum		relallvisible_datum = PG_GETARG_DATUM(4);
+	bool		relallvisible_isnull = PG_ARGISNULL(4);
+	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(WARNING,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("Parameter %s cannot be null", relation_name)));
+		PG_RETURN_BOOL(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(WARNING,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("Cannot export statistics prior to version 9.2")));
+			PG_RETURN_BOOL(false);
+		}
+	}
+
+	if (relpages_isnull)
+	{
+		ereport(WARNING,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("%s cannot be NULL", relpages_name)));
+		PG_RETURN_BOOL(false);
+	}
+	else
+	{
+		relpages = DatumGetInt32(relpages_datum);
+		if (relpages < -1)
+		{
+			ereport(WARNING,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("%s cannot be < -1", relpages_name)));
+			PG_RETURN_BOOL(false);
+		}
+	}
+
+	if (reltuples_isnull)
+	{
+		ereport(WARNING,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("%s cannot be NULL", reltuples_name)));
+		PG_RETURN_BOOL(false);
+	}
+	else
+	{
+		reltuples = DatumGetFloat4(reltuples_datum);
+		if (reltuples < -1.0)
+		{
+			ereport(WARNING,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("%s cannot be < -1.0", reltuples_name)));
+			PG_RETURN_BOOL(false);
+		}
+	}
+
+	if (relallvisible_isnull)
+	{
+		ereport(WARNING,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("%s cannot be NULL", relallvisible_name)));
+		PG_RETURN_BOOL(false);
+	}
+	else
+	{
+		relallvisible = DatumGetInt32(relallvisible_datum);
+		if (relallvisible < -1)
+		{
+			ereport(WARNING,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("%s cannot be < -1", relallvisible_name)));
+			PG_RETURN_BOOL(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(WARNING,
+				(errcode(ERRCODE_OBJECT_IN_USE),
+				 errmsg("pg_class entry for relid %u not found", relation)));
+		table_close(rel, NoLock);
+		PG_RETURN_BOOL(false);
+	}
+
+	pgcform = (Form_pg_class) GETSTRUCT(ctup);
+
+	if (!can_modify_relation(rel))
+	{
+		ereport(WARNING,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied for relation %s",
+						RelationGetRelationName(rel))));
+		table_close(rel, NoLock);
+		PG_RETURN_BOOL(false);
+	}
+
+	/* Only update pg_class if there is a meaningful change */
+	if ((pgcform->reltuples != reltuples)
+		|| (pgcform->relpages != relpages)
+		|| (pgcform->relallvisible != relallvisible))
+	{
+		pgcform->relpages = relpages;
+		pgcform->reltuples = reltuples;
+		pgcform->relallvisible = relallvisible;
+
+		heap_inplace_update(rel, ctup);
+	}
+
+	table_close(rel, NoLock);
+
+	PG_RETURN_BOOL(true);
+}
diff --git a/src/test/regress/expected/stats_export_import.out b/src/test/regress/expected/stats_export_import.out
new file mode 100644
index 0000000000..9559f8d524
--- /dev/null
+++ b/src/test/regress/expected/stats_export_import.out
@@ -0,0 +1,160 @@
+CREATE SCHEMA stats_export_import;
+CREATE TYPE stats_export_import.complex_type AS (
+    a integer,
+    b real,
+    c text,
+    d date,
+    e jsonb);
+CREATE TABLE stats_export_import.test(
+    id INTEGER PRIMARY KEY,
+    name text,
+    comp stats_export_import.complex_type,
+    arange int4range,
+    tags text[]
+);
+-- starting stats
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible 
+----------+-----------+---------------
+        0 |        -1 |             0
+(1 row)
+
+-- false: regclass not found
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 0::Oid,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+WARNING:  pg_class entry for relid 0 not found
+ pg_set_relation_stats 
+-----------------------
+ f
+(1 row)
+
+-- false: relpages NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => NULL::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+WARNING:  relpages cannot be NULL
+ pg_set_relation_stats 
+-----------------------
+ f
+(1 row)
+
+-- false: reltuples NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => NULL::real,
+        relallvisible => 4::integer);
+WARNING:  reltuples cannot be NULL
+ pg_set_relation_stats 
+-----------------------
+ f
+(1 row)
+
+-- false: relallvisible NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => NULL::integer);
+WARNING:  relallvisible cannot be NULL
+ pg_set_relation_stats 
+-----------------------
+ f
+(1 row)
+
+-- false: version too old
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 2::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+WARNING:  Cannot export statistics prior to version 9.2
+ pg_set_relation_stats 
+-----------------------
+ f
+(1 row)
+
+-- true: all named
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+ pg_set_relation_stats 
+-----------------------
+ t
+(1 row)
+
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible 
+----------+-----------+---------------
+       17 |       400 |             4
+(1 row)
+
+-- true: all positional
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        'stats_export_import.test'::regclass,
+        150000::integer,
+        18::integer,
+        401.0::real,
+        5::integer);
+ pg_set_relation_stats 
+-----------------------
+ t
+(1 row)
+
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible 
+----------+-----------+---------------
+       18 |       401 |             5
+(1 row)
+
+-- true: version NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => NULL,
+        relpages => 19::integer,
+        reltuples => 402.0::real,
+        relallvisible => 6::integer);
+ pg_set_relation_stats 
+-----------------------
+ t
+(1 row)
+
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+ relpages | reltuples | relallvisible 
+----------+-----------+---------------
+       19 |       402 |             6
+(1 row)
+
+DROP SCHEMA stats_export_import CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to type stats_export_import.complex_type
+drop cascades to table stats_export_import.test
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2429ec2bba..ea99302741 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_export_import
 
 # ----------
 # Load huge amounts of data
diff --git a/src/test/regress/sql/stats_export_import.sql b/src/test/regress/sql/stats_export_import.sql
new file mode 100644
index 0000000000..65d647830c
--- /dev/null
+++ b/src/test/regress/sql/stats_export_import.sql
@@ -0,0 +1,107 @@
+CREATE SCHEMA stats_export_import;
+
+CREATE TYPE stats_export_import.complex_type AS (
+    a integer,
+    b real,
+    c text,
+    d date,
+    e jsonb);
+
+CREATE TABLE stats_export_import.test(
+    id INTEGER PRIMARY KEY,
+    name text,
+    comp stats_export_import.complex_type,
+    arange int4range,
+    tags text[]
+);
+
+-- starting stats
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+
+-- false: regclass not found
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 0::Oid,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+
+-- false: relpages NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => NULL::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+
+-- false: reltuples NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => NULL::real,
+        relallvisible => 4::integer);
+
+-- false: relallvisible NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => NULL::integer);
+
+-- false: version too old
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 2::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+
+-- true: all named
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => 150000::integer,
+        relpages => 17::integer,
+        reltuples => 400.0::real,
+        relallvisible => 4::integer);
+
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+
+-- true: all positional
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        'stats_export_import.test'::regclass,
+        150000::integer,
+        18::integer,
+        401.0::real,
+        5::integer);
+
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+
+-- true: version NULL
+SELECT
+    pg_catalog.pg_set_relation_stats(
+        relation => 'stats_export_import.test'::regclass,
+        version => NULL,
+        relpages => 19::integer,
+        reltuples => 402.0::real,
+        relallvisible => 6::integer);
+
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_export_import.test'::regclass;
+
+DROP SCHEMA stats_export_import CASCADE;
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index fd5699f4d8..4ee0cdb47b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -29772,6 +29772,83 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
     in identifying the specific disk files associated with database objects.
    </para>
 
+   <table id="functions-admin-statsimport">
+    <title>Database Object Statistics Import Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry">
+         <para role="func_signature">
+           <indexterm>
+            <primary>pg_set_relation_stats</primary>
+           </indexterm>
+           <function>pg_set_relation_stats</function> (
+            <parameter>relation</parameter> <type>regclass</type>
+            , <parameter>version</parameter> <type>integer</type>,
+            , <parameter>relpages</parameter> <type>integer</type>,
+            , <parameter>reltuples</parameter> <type>real</type>,
+            , <parameter>relallvisible</parameter> <type>integer</type> )
+           <returnvalue>void</returnvalue>
+         </para>
+         <para>
+          Updates the <structname>pg_class</structname> row for the specified
+          <parameter>relation</parameter>, setting the values for the columns
+          <structfield>reltuples</structfield>,
+          <structfield>relpages</structfield>, and
+          <structfield>relallvisible</structfield>.
+          To avoid table bloat in <structname>pg_class</structname>, this change
+          is made with an in-place update, and therefore cannot be rolled back
+          through normal transaction processing.
+         </para>
+         <para>
+          This function mimics the behavior of <command>ANALYZE</command> in its
+          effects on the values in <structname>pg_class</structname>, except that
+          the values are supplied as parameters rather than derived from table
+          sampling.
+         </para>
+         <para>
+          The caller must either be the owner of the relation, or have superuser
+          privileges.
+         </para>
+         <para>
+          The value of <structfield>version</structfield> represents the
+          integer <varname>SERVER_VERSION_NUM</varname> of the
+          database that was the source of these values. A value of
+          <literal>NULL</literal> means to use the
+          <varname>SERVER_VERSION_NUM</varname> of the
+          current database. It should be noted that presently this value does
+          not alter the behavior of the function, but it could in future
+          versions.
+         </para>
+         <para>
+          The value of <structfield>relpages</structfield> must not be less than
+          0.
+         </para>
+         <para>
+          The value of <structfield>reltuples</structfield> must not be less than
+          -1.0.
+         </para>
+         <para>
+          The value of <structfield>relallvisible</structfield> must not be less
+          than 0.
+         </para>
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
    <table id="functions-admin-dblocation">
     <title>Database Object Location Functions</title>
     <tgroup cols="1">
-- 
2.45.2