public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Davis <[email protected]>
To: Corey Huinker <[email protected]>
Cc: Matthias van de Meent <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Nathan Bossart <[email protected]>
Cc: Magnus Hagander <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: [email protected]
Subject: Re: Statistics Import and Export
Date: Thu, 15 Aug 2024 01:57:03 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CADkLM=e0jM7m0GFV44nNtvL22CtnFUu+pekppCVE=DOBe58RTQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CADkLM=dA0tOc_fGDoiRsF41XFFH2_S3D5kibcbw9H948KdsS-A@mail.gmail.com>
<CADkLM=eE2BD-40uGMOpJs19inYJ2PSY+21srXT0K49NseBe30w@mail.gmail.com>
<20240417165053.GA3253364@nathanxps13>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAEze2WiX_YWEhe4iLSLHEkSJimE0=wU1cGhmPikurZGOTrh1tg@mail.gmail.com>
<[email protected]>
<CAEze2WjM8QDZUJa7G4_Om2B76nFMw5RYz+1D+J=VMpr6aN3BJQ@mail.gmail.com>
<CADkLM=dHYkDa-raQrahd5-PkO_=aw1RuffUxVAfEnEyX5ZOB5A@mail.gmail.com>
<CADkLM=cQgghMJOS8EcAVBwRO4s1dUVtxGZv5gLPfZkQ1nL1gzA@mail.gmail.com>
<[email protected]>
<CADkLM=dse9b6VYkgt8MMe3htt=tD1bZOdk3KvH=3Gt17LqSVXA@mail.gmail.com>
<[email protected]>
<CADkLM=dNQzmdsWfXVVDBqv7gHsa-7dk3v13AqrAzBoJWTMrWEA@mail.gmail.com>
<CADkLM=ccvjLJeGnWzbH8N=2aBNpnL5HnwC4Ny2LqFVJyGH5ZKw@mail.gmail.com>
<[email protected]>
<CADkLM=c9gnYMkQCyh1+R56VrCkVWbu8-SCpmHepkbAJHMcVuuA@mail.gmail.com>
<CADkLM=f_SNcxfPC-XbwcHgCdS6FPSNo-=UZq08AWFYoBCDcxOg@mail.gmail.com>
<[email protected]>
<CADkLM=evyx0L96XxrLSUDVS+iXgHszLbWMrGwVbBnNVO1ejfJA@mail.gmail.com>
<CADkLM=cJMEYrP8o4dbAqm_mW=ks63sXi1VXy94ybev0BNLm7_g@mail.gmail.com>
<[email protected]>
<CADkLM=chb8YT+OD40aDDj1MGz52FMw6-SW3atgEpsw6Rp-3mhg@mail.gmail.com>
<[email protected]>
<CADkLM=e0jM7m0GFV44nNtvL22CtnFUu+pekppCVE=DOBe58RTQ@mail.gmail.com>
On Sun, 2024-08-04 at 01:09 -0400, Corey Huinker wrote:
>
> > I believe 0001 and 0002 are in good shape API-wise, and I can start
> > getting those committed. I will try to clean up the code in the
> > process.
Attached v26j.
I'm slowly refactoring it and rediscovering some of the interesting
corners in deriving the right information to store the stats. There's
still a ways to go, though. The error paths could also use some work.
Regards,
Jeff Davis
Attachments:
[text/x-patch] v26j-0001-Create-function-pg_set_relation_stats.patch (17.4K, ../[email protected]/2-v26j-0001-Create-function-pg_set_relation_stats.patch)
download | inline diff:
From d38008b0e60e0241c86f583c951763cc01d1e270 Mon Sep 17 00:00:00 2001
From: Corey Huinker <[email protected]>
Date: Wed, 24 Jul 2024 23:45:26 -0400
Subject: [PATCH v26j 1/2] 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.
Author: Corey Huinker
Discussion: https://postgr.es/m/CADkLM=e0jM7m0GFV44nNtvL22CtnFUu+pekppCVE=DOBe58RTQ@mail.gmail.com
---
doc/src/sgml/func.sgml | 65 +++++++
src/backend/statistics/Makefile | 1 +
src/backend/statistics/import_stats.c | 212 +++++++++++++++++++++
src/backend/statistics/meson.build | 1 +
src/include/catalog/pg_proc.dat | 9 +
src/test/regress/expected/stats_import.out | 99 ++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/stats_import.sql | 79 ++++++++
8 files changed, 467 insertions(+), 1 deletion(-)
create mode 100644 src/backend/statistics/import_stats.c
create mode 100644 src/test/regress/expected/stats_import.out
create mode 100644 src/test/regress/sql/stats_import.sql
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5dd95d73a1a..02d93f6c925 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -29938,6 +29938,71 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a
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>relpages</parameter> <type>integer</type>, <parameter>reltuples</parameter> <type>real</type>, <parameter>relallvisible</parameter> <type>integer</type> )
+ <returnvalue>void</returnvalue>
+ </para>
+ <para>
+ Updates table-level statistics for the given relation to the
+ specified values. The parameters correspond to columns in <link
+ linkend="catalog-pg-class"><structname>pg_class</structname></link>.
+ </para>
+ <para>
+ Ordinarily, these statistics are collected automatically or updated
+ as a part of <xref linkend="sql-vacuum"/> or <xref
+ linkend="sql-analyze"/>, so it's not necessary to call this
+ function. However, it may be useful when testing the effects of
+ statistics on the planner to understand or anticipate plan changes.
+ </para>
+ <para>
+ The caller must have the <literal>MAINTAIN</literal> privilege on
+ the table or be the owner of the database.
+ </para>
+ <para>
+ The value of <structfield>relpages</structfield> must be greater than
+ or equal to <literal>0</literal>,
+ <structfield>reltuples</structfield> must be greater than or equal to
+ <literal>-1.0</literal>, and <structfield>relallvisible</structfield>
+ must be greater than or equal to <literal>0</literal>.
+ </para>
+ <warning>
+ <para>
+ Changes made by this function are likely to be overwritten by <link
+ linkend="autovacuum">autovacuum</link> (or manual
+ <command>VACUUM</command> or <command>ANALYZE</command>) and should
+ be considered temporary.
+ </para>
+ <para>
+ The signature of this function may change in new major releases if
+ there are changes to the statistics being tracked.
+ </para>
+ </warning>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<table id="functions-admin-dblocation">
<title>Database Object Location Functions</title>
<tgroup cols="1">
diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makefile
index 89cf8c27973..5e776c02218 100644
--- a/src/backend/statistics/Makefile
+++ b/src/backend/statistics/Makefile
@@ -15,6 +15,7 @@ include $(top_builddir)/src/Makefile.global
OBJS = \
dependencies.o \
extended_stats.o \
+ import_stats.o \
mcv.o \
mvdistinct.o
diff --git a/src/backend/statistics/import_stats.c b/src/backend/statistics/import_stats.c
new file mode 100644
index 00000000000..8c0a2bab305
--- /dev/null
+++ b/src/backend/statistics/import_stats.c
@@ -0,0 +1,212 @@
+/*-------------------------------------------------------------------------
+ * import_stats.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/import_stats.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/heapam.h"
+#include "catalog/indexing.h"
+#include "catalog/pg_database.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/syscache.h"
+
+/*
+ * 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
+check_can_update_stats(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(Oid reloid, int version, int32 relpages,
+ float4 reltuples, int32 relallvisible, int elevel)
+{
+ Relation relation;
+ Relation crel;
+ HeapTuple ctup;
+ Form_pg_class pgcform;
+
+ if (relpages < -1)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relpages cannot be < -1")));
+ return false;
+ }
+
+ if (reltuples < -1.0)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("reltuples cannot be < -1.0")));
+ return false;
+ }
+
+ if (relallvisible < -1)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relallvisible cannot be < -1")));
+ return false;
+ }
+
+ /*
+ * Open relation with ShareUpdateExclusiveLock, consistent with
+ * ANALYZE. Only needed for permission check and then we close it (but
+ * retain the lock).
+ */
+ relation = try_table_open(reloid, ShareUpdateExclusiveLock);
+
+ if (!relation)
+ {
+ elog(elevel, "could not open relation with OID %u", reloid);
+ return false;
+ }
+
+ if (!check_can_update_stats(relation))
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for relation %s",
+ RelationGetRelationName(relation))));
+ table_close(relation, NoLock);
+ return false;
+ }
+
+ table_close(relation, NoLock);
+
+ /*
+ * Take RowExclusiveLock on pg_class, consistent with
+ * vac_update_relstats().
+ */
+ crel = table_open(RelationRelationId, RowExclusiveLock);
+
+ ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
+ if (!HeapTupleIsValid(ctup))
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_OBJECT_IN_USE),
+ errmsg("pg_class entry for relid %u not found", reloid)));
+ return false;
+ }
+
+ pgcform = (Form_pg_class) GETSTRUCT(ctup);
+
+ /* only update pg_class if there is a meaningful change */
+ if (pgcform->relpages != relpages ||
+ pgcform->reltuples != reltuples ||
+ pgcform->relallvisible != relallvisible)
+ {
+ int cols[3] = {
+ Anum_pg_class_relpages,
+ Anum_pg_class_reltuples,
+ Anum_pg_class_relallvisible,
+ };
+ Datum values[3] = {
+ Int32GetDatum(relpages),
+ Float4GetDatum(reltuples),
+ Int32GetDatum(relallvisible),
+ };
+ bool nulls[3] = {false, false, false};
+
+ TupleDesc tupdesc = RelationGetDescr(crel);
+ HeapTuple newtup;
+
+ CatalogIndexState indstate = CatalogOpenIndexes(crel);
+
+ newtup = heap_modify_tuple_by_cols(ctup, tupdesc, 3, cols, values,
+ nulls);
+
+ CatalogTupleUpdateWithInfo(crel, &newtup->t_self, newtup, indstate);
+ heap_freetuple(newtup);
+ CatalogCloseIndexes(indstate);
+ }
+
+ /* release the lock, consistent with vac_update_relstats() */
+ table_close(crel, RowExclusiveLock);
+
+ PG_RETURN_BOOL(true);
+}
+
+/*
+ * Set statistics for a given pg_class entry.
+ *
+ * Use a transactional update, and assume statistics come from the current
+ * server version.
+ *
+ * Not intended for bulk import of statistics from older versions.
+ */
+Datum
+pg_set_relation_stats(PG_FUNCTION_ARGS)
+{
+ Oid reloid;
+ int version = PG_VERSION_NUM;
+ int elevel = ERROR;
+ int32 relpages;
+ float reltuples;
+ int32 relallvisible;
+
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relation cannot be NULL")));
+ else
+ reloid = PG_GETARG_OID(0);
+
+ if (PG_ARGISNULL(1))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relpages cannot be NULL")));
+ else
+ relpages = PG_GETARG_INT32(1);
+
+ if (PG_ARGISNULL(2))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("reltuples cannot be NULL")));
+ else
+ reltuples = PG_GETARG_FLOAT4(2);
+
+ if (PG_ARGISNULL(3))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relallvisible cannot be NULL")));
+ else
+ relallvisible = PG_GETARG_INT32(3);
+
+
+ relation_statistics_update(reloid, version, relpages, reltuples,
+ relallvisible, elevel);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/backend/statistics/meson.build b/src/backend/statistics/meson.build
index 73b29a3d50a..849df3bf323 100644
--- a/src/backend/statistics/meson.build
+++ b/src/backend/statistics/meson.build
@@ -3,6 +3,7 @@
backend_sources += files(
'dependencies.c',
'extended_stats.c',
+ 'import_stats.c',
'mcv.c',
'mvdistinct.c',
)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4abc6d95262..d700dd50f7b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12255,4 +12255,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/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
new file mode 100644
index 00000000000..f727cce9765
--- /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: could not open relation with OID 0
+-- 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
+-- named arguments
+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)
+
+-- positional arguments
+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 2429ec2bbaa..85fc85bfa03 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 00000000000..effd5b892bf
--- /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);
+
+-- named arguments
+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;
+
+-- positional arguments
+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;
--
2.34.1
[text/x-patch] v26j-0002-Create-function-pg_set_attribute_stats.patch (87.4K, ../[email protected]/3-v26j-0002-Create-function-pg_set_attribute_stats.patch)
download | inline diff:
From 66e6e735d7e7c3aeb2f374812259864d4c4e2f1e Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 8 Aug 2024 17:56:22 -0700
Subject: [PATCH v26j 2/2] Create function pg_set_attribute_stats.
---
doc/src/sgml/func.sgml | 60 ++
src/backend/catalog/system_functions.sql | 22 +
src/backend/statistics/import_stats.c | 957 ++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 7 +
src/include/statistics/statistics.h | 3 +
src/test/regress/expected/stats_import.out | 645 +++++++++++++-
src/test/regress/sql/stats_import.sql | 544 ++++++++++++
7 files changed, 2236 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 02d93f6c925..402a14fdb1f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -29999,6 +29999,66 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a
</warning>
</entry>
</row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <indexterm>
+ <primary>pg_set_attribute_stats</primary>
+ </indexterm>
+ <function>pg_set_attribute_stats</function> (
+ <parameter>relation</parameter> <type>regclass</type>,
+ <parameter>attname</parameter> <type>name</type>,
+ <parameter>inherited</parameter> <type>boolean</type>,
+ <parameter>null_frac</parameter> <type>real</type>,
+ <parameter>avg_width</parameter> <type>integer</type>,
+ <parameter>n_distinct</parameter> <type>real</type>
+ <optional>, <parameter>most_common_vals</parameter> <type>text</type>, <parameter>most_common_freqs</parameter> <type>real[]</type> </optional>
+ <optional>, <parameter>histogram_bounds</parameter> <type>text</type> </optional>
+ <optional>, <parameter>correlation</parameter> <type>real</type> </optional>
+ <optional>, <parameter>most_common_elems</parameter> <type>text</type>, <parameter>most_common_elem_freqs</parameter> <type>real[]</type> </optional>
+ <optional>, <parameter>elem_count_histogram</parameter> <type>real[]</type> </optional>
+ <optional>, <parameter>range_length_histogram</parameter> <type>text</type> </optional>
+ <optional>, <parameter>range_empty_frac</parameter> <type>real</type> </optional>
+ <optional>, <parameter>range_bounds_histogram</parameter> <type>text</type> </optional> )
+ <returnvalue>void</returnvalue>
+ </para>
+ <para>
+ Updates attribute-level statistics for the given relation and
+ attribute name to the specified values. The parameters correspond to
+ to attributes of the same name found in the <link
+ linkend="view-pg-stats"><structname>pg_stats</structname></link>
+ view, and the values supplied in the parameter must meet the
+ requirements of the corresponding attribute.
+ </para>
+ <para>
+ Any optional parameters left unspecified will cause the corresponding
+ statistics to be set to <literal>NULL</literal>.
+ </para>
+ <para>
+ Ordinarily, these statistics are collected automatically or updated
+ as a part of <xref linkend="sql-vacuum"/> or <xref
+ linkend="sql-analyze"/>, so it's not necessary to call this
+ function. However, it may be useful when testing the effects of
+ statistics on the planner to understand or anticipate plan changes.
+ </para>
+ <para>
+ The caller must have the <literal>MAINTAIN</literal> privilege on
+ the table or be the owner of the database.
+ </para>
+ <warning>
+ <para>
+ Changes made by this function are likely to be overwritten by <link
+ linkend="autovacuum">autovacuum</link> (or manual
+ <command>VACUUM</command> or <command>ANALYZE</command>) and should
+ be considered temporary.
+ </para>
+ <para>
+ The signature of this function may change in new major releases if
+ there are changes to the statistics being tracked.
+ </para>
+ </warning>
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 623b9539b15..49f0d0dab2d 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -639,6 +639,28 @@ LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_slru';
+CREATE OR REPLACE FUNCTION
+ pg_set_attribute_stats(relation oid,
+ attname name,
+ inherited bool,
+ null_frac real,
+ avg_width integer,
+ n_distinct real,
+ most_common_vals text DEFAULT NULL,
+ most_common_freqs real[] DEFAULT NULL,
+ histogram_bounds text DEFAULT NULL,
+ correlation real DEFAULT NULL,
+ most_common_elems text DEFAULT NULL,
+ most_common_elem_freqs real[] DEFAULT NULL,
+ elem_count_histogram real[] DEFAULT NULL,
+ range_length_histogram text DEFAULT NULL,
+ range_empty_frac real DEFAULT NULL,
+ range_bounds_histogram text DEFAULT NULL)
+RETURNS void
+LANGUAGE INTERNAL
+CALLED ON NULL INPUT VOLATILE
+AS 'pg_set_attribute_stats';
+
--
-- The default permissions for functions mean that anyone can execute them.
-- A number of functions shouldn't be executable by just anyone, but rather
diff --git a/src/backend/statistics/import_stats.c b/src/backend/statistics/import_stats.c
index 8c0a2bab305..3a09b880a38 100644
--- a/src/backend/statistics/import_stats.c
+++ b/src/backend/statistics/import_stats.c
@@ -20,18 +20,61 @@
#include "access/heapam.h"
#include "catalog/indexing.h"
+#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
+#include "catalog/pg_operator.h"
#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
+#include "statistics/statistics.h"
#include "utils/acl.h"
+#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/lsyscache.h"
#include "utils/syscache.h"
+static void update_pg_statistic(Datum values[], bool nulls[]);
+static Datum cast_stavalues(FmgrInfo *flinfo, Datum d, Oid typid,
+ int32 typmod, int elevel, bool *ok);
+static bool array_check(Datum datum, int one_dim, const char *statname,
+ int elevel);
+static bool type_is_scalar(Oid typid);
+static TypeCacheEntry *get_attr_stat_type(Relation rel, AttrNumber attnum,
+ int elevel, int32 *typmod, Oid *typcoll);
+static void use_stats_slot(Datum *values, bool *nulls, int slotidx,
+ int16 stakind, Oid staop, Oid stacoll,
+ Datum stanumbers, bool stanumbers_isnull,
+ Datum stavalues, bool stavalues_isnull);
+
+/*
+ * Names of parameters found in the functions pg_set_relation_stats and
+ * pg_set_attribute_stats
+ */
+const char *relation_name = "relation";
+const char *relpages_name = "relpages";
+const char *reltuples_name = "reltuples";
+const char *relallvisible_name = "relallvisible";
+const char *attname_name = "attname";
+const char *inherited_name = "inherited";
+const char *null_frac_name = "null_frac";
+const char *avg_width_name = "avg_width";
+const char *n_distinct_name = "n_distinct";
+const char *mc_vals_name = "most_common_vals";
+const char *mc_freqs_name = "most_common_freqs";
+const char *histogram_bounds_name = "histogram_bounds";
+const char *correlation_name = "correlation";
+const char *mc_elems_name = "most_common_elems";
+const char *mc_elem_freqs_name = "most_common_elem_freqs";
+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";
+
/*
* 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
check_can_update_stats(Relation rel)
@@ -158,6 +201,760 @@ relation_statistics_update(Oid reloid, int version, int32 relpages,
PG_RETURN_BOOL(true);
}
+/*
+ * Insert or Update Attribute Statistics
+ *
+ * See pg_statistic.h for an explanation of how each statistic kind is
+ * stored. Custom statistics kinds are not supported.
+ *
+ * Depending on the statistics kind, we need to derive information from the
+ * attribute for which we're storing the stats. For instance, the MCVs are
+ * stored as an anyarray, and the representation of the array needs to store
+ * the correct element type, which must be derived from the attribute.
+ */
+static bool
+attribute_statistics_update(Oid reloid, AttrNumber attnum, int version,
+ int elevel, bool inherited, float null_frac,
+ int avg_width, float n_distinct,
+ Datum mc_vals_datum, bool mc_vals_isnull,
+ Datum mc_freqs_datum, bool mc_freqs_isnull,
+ Datum histogram_bounds_datum, bool histogram_bounds_isnull,
+ Datum correlation_datum, bool correlation_isnull,
+ Datum mc_elems_datum, bool mc_elems_isnull,
+ Datum mc_elem_freqs_datum, bool mc_elem_freqs_isnull,
+ Datum elem_count_hist_datum, bool elem_count_hist_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)
+{
+ Relation rel;
+
+ TypeCacheEntry *typcache;
+ TypeCacheEntry *elemtypcache = NULL;
+
+ int32 typmod;
+ Oid typcoll;
+
+ FmgrInfo finfo;
+
+ Datum values[Natts_pg_statistic];
+ bool nulls[Natts_pg_statistic];
+
+ /*
+ * The statkind index, we have only STATISTIC_NUM_SLOTS to hold these stats
+ */
+ int stakindidx = 0;
+ char *attname = get_attname(reloid, attnum, false);
+
+ /*
+ * Initialize nulls array to be false for all non-NULL attributes, and
+ * true for all nullable attributes.
+ */
+ for (int i = 0; i < Natts_pg_statistic; i++)
+ {
+ values[i] = (Datum) 0;
+ if (i < Anum_pg_statistic_stanumbers1 - 1)
+ nulls[i] = false;
+ else
+ nulls[i] = true;
+ }
+
+ /*
+ * Some parameters are linked, should both be NULL or NOT NULL.
+ * Disagreement means that the statistic pair will fail so the
+ * NOT NULL one must be abandoned (set NULL) after an
+ * ERROR/WARNING. By ensuring that the values are aligned it is
+ * possible to use one as a proxy for the other later.
+ */
+
+ /*
+ * STATISTIC_KIND_MCV = mc_vals + mc_freqs
+ */
+ if (mc_vals_isnull != mc_freqs_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot be NULL when %s is NOT NULL",
+ mc_vals_isnull ? mc_vals_name : mc_freqs_name,
+ !mc_vals_isnull ? mc_vals_name : mc_freqs_name)));
+
+ mc_vals_isnull = true;
+ mc_freqs_isnull = true;
+ }
+
+ /*
+ * STATISTIC_KIND_MCELEM = mc_elems + mc_elem_freqs
+ */
+ if (mc_elems_isnull != mc_elem_freqs_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot be NULL when %s is NOT NULL",
+ mc_elems_isnull ? mc_elems_name : mc_elem_freqs_name,
+ !mc_elems_isnull ? mc_elems_name : mc_elem_freqs_name)));
+ mc_elems_isnull = true;
+ mc_elem_freqs_isnull = true;
+ }
+
+ /*
+ * STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM =
+ * range_length_histogram + range_empty_frac
+ */
+ else if (range_length_hist_isnull != range_empty_frac_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot be NULL when %s is NOT NULL",
+ range_length_hist_isnull ?
+ range_length_hist_name : range_empty_frac_name,
+ !range_length_hist_isnull ?
+ range_length_hist_name : range_empty_frac_name)));
+ range_length_hist_isnull = true;
+ range_empty_frac_isnull = true;
+ }
+
+ rel = try_relation_open(reloid, ShareUpdateExclusiveLock);
+
+ if (rel == NULL)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Parameter relation OID %u is invalid", reloid)));
+ return false;
+ }
+
+ /*
+ * Many of the values that are set for a particular stakind are entirely
+ * derived from the attribute itself, or it's expression.
+ */
+ typcache = get_attr_stat_type(rel, attnum, elevel, &typmod, &typcoll);
+ if (typcache == NULL)
+ {
+ relation_close(rel, NoLock);
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unexpected typecache error")));
+ return false;
+ }
+
+ /*
+ * Derive element type if we have stat kinds that need it.
+ *
+ * This duplicates some type-specific logic found in various typanalyze
+ * functions which are called from vacuum's examine_attribute(), but using
+ * that directly has proven awkward.
+ */
+ if (!mc_elems_isnull || !elem_count_hist_isnull)
+ {
+ Oid elemtypid;
+
+ if (typcache->type_id == TSVECTOROID)
+ {
+ /*
+ * tsvectors always have a text oid base type and default
+ * collation
+ */
+ elemtypid = TEXTOID;
+ typcoll = DEFAULT_COLLATION_OID;
+ }
+ else if (typcache->typtype == TYPTYPE_RANGE)
+ elemtypid = get_range_subtype(typcache->type_id);
+ else
+ elemtypid = get_base_element_type(typcache->type_id);
+
+ /* not finding a basetype means we already had it */
+ if (elemtypid == InvalidOid)
+ elemtypid = typcache->type_id;
+
+ /* The stats need the eq_opr, any validation would need the lt_opr */
+ elemtypcache = lookup_type_cache(elemtypid, TYPECACHE_EQ_OPR);
+ if (elemtypcache == NULL)
+ {
+ /* warn and ignore any stats that can't be fulfilled */
+ if (!mc_elems_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot accept %s stats, ignored",
+ attname,
+ mc_elems_name)));
+ mc_elems_isnull = true;
+ mc_elem_freqs_isnull = true;
+ }
+
+ if (!elem_count_hist_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot accept %s stats, ignored",
+ attname,
+ elem_count_hist_name)));
+ elem_count_hist_isnull = true;
+ }
+ }
+ }
+
+ /*
+ * histogram_bounds and correlation must have a type < operator
+ */
+ if (typcache->lt_opr == InvalidOid)
+ {
+ if (!histogram_bounds_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Relation %s attname %s cannot "
+ "have stats of type %s, ignored.",
+ RelationGetRelationName(rel),
+ attname,
+ histogram_bounds_name)));
+ histogram_bounds_isnull = true;
+ }
+
+ if (!correlation_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Relation %s attname %s cannot "
+ "have stats of type %s, ignored.",
+ RelationGetRelationName(rel),
+ attname,
+ correlation_name)));
+ correlation_isnull = true;
+ }
+ }
+
+ /*
+ * Scalar types can't have most_common_elems, most_common_elem_freqs, or
+ * element_count_histogram
+ */
+ if (type_is_scalar(typcache->type_id))
+ {
+ /* warn and ignore any stats that can't be fulfilled */
+ if (!mc_elems_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Relation %s attname %s is a scalar type, "
+ "cannot have stats of type %s, ignored",
+ RelationGetRelationName(rel),
+ attname,
+ mc_elems_name)));
+ mc_elems_isnull = true;
+ mc_elem_freqs_isnull = true;
+ }
+
+ if (!elem_count_hist_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Relation %s attname %s is a scalar type, "
+ "cannot have stats of type %s, ignored",
+ RelationGetRelationName(rel),
+ attname,
+ elem_count_hist_name)));
+ elem_count_hist_isnull = true;
+ }
+ }
+
+ /* Only range types can have range stats */
+ if ((typcache->typtype != TYPTYPE_RANGE) &&
+ (typcache->typtype != TYPTYPE_MULTIRANGE))
+ {
+ /* warn and ignore any stats that can't be fulfilled */
+ if (!range_length_hist_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Relation %s attname %s is not a range type, "
+ "cannot have stats of type %s",
+ RelationGetRelationName(rel),
+ attname,
+ range_length_hist_name)));
+ range_length_hist_isnull = true;
+ range_empty_frac_isnull = true;
+ }
+
+ if (!range_bounds_hist_isnull)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Relation %s attname %s is not a range type, "
+ "cannot have stats of type %s",
+ RelationGetRelationName(rel),
+ attname,
+ range_bounds_hist_name)));
+ range_bounds_hist_isnull = true;
+ }
+ }
+
+ if (!check_can_update_stats(rel))
+ {
+ relation_close(rel, NoLock);
+ ereport(elevel,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for relation %s",
+ RelationGetRelationName(rel))));
+ return false;
+ }
+
+ /* Populate pg_statistic tuple */
+ values[Anum_pg_statistic_starelid - 1] = ObjectIdGetDatum(reloid);
+ values[Anum_pg_statistic_staattnum - 1] = Int16GetDatum(attnum);
+ values[Anum_pg_statistic_stainherit - 1] = BoolGetDatum(inherited);
+ values[Anum_pg_statistic_stanullfrac - 1] = Float4GetDatum(null_frac);
+ values[Anum_pg_statistic_stawidth - 1] = Int32GetDatum(avg_width);
+ values[Anum_pg_statistic_stadistinct - 1] = Float4GetDatum(n_distinct);
+
+ fmgr_info(F_ARRAY_IN, &finfo);
+
+ /*
+ * STATISTIC_KIND_MCV
+ *
+ * most_common_vals: ANYARRAY::text most_common_freqs: real[]
+ */
+ if (!mc_vals_isnull)
+ {
+ bool converted = false;
+ Datum stanumbers = mc_freqs_datum;
+ Datum stavalues = cast_stavalues(&finfo, mc_vals_datum,
+ typcache->type_id, typmod,
+ elevel, &converted);
+
+ if (converted &&
+ array_check(stavalues, false, mc_vals_name, elevel) &&
+ array_check(stanumbers, true, mc_freqs_name, elevel))
+ {
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_MCV,
+ typcache->eq_opr, typcoll,
+ stanumbers, false, stavalues, false);
+ }
+ else
+ {
+ /* Mark as skipped */
+ mc_vals_isnull = true;
+ mc_freqs_isnull = true;
+ }
+ }
+
+ /*
+ * STATISTIC_KIND_HISTOGRAM
+ *
+ * histogram_bounds: ANYARRAY::text
+ */
+ if (!histogram_bounds_isnull)
+ {
+ Datum stavalues;
+ bool converted = false;
+
+ stavalues = cast_stavalues(&finfo, histogram_bounds_datum,
+ typcache->type_id, typmod, elevel,
+ &converted);
+
+ if (converted &&
+ array_check(stavalues, false, histogram_bounds_name, elevel))
+ {
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_HISTOGRAM,
+ typcache->lt_opr, typcoll,
+ 0, true, stavalues, false);
+ }
+ else
+ histogram_bounds_isnull = true;
+ }
+
+ /*
+ * STATISTIC_KIND_CORRELATION
+ *
+ * correlation: real
+ */
+ if (!correlation_isnull)
+ {
+ Datum elems[] = {correlation_datum};
+ ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID);
+ Datum stanumbers = PointerGetDatum(arry);
+
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_CORRELATION,
+ typcache->lt_opr, typcoll,
+ stanumbers, false, 0, true);
+ }
+
+ /*
+ * STATISTIC_KIND_MCELEM
+ *
+ * most_common_elem_freqs: real[]
+ *
+ * most_common_elems : ANYARRAY::text
+ */
+ if (!mc_elems_isnull)
+ {
+ Datum stanumbers = mc_elem_freqs_datum;
+ bool converted = false;
+ Datum stavalues;
+
+ stavalues = cast_stavalues(&finfo, mc_elems_datum,
+ elemtypcache->type_id, typmod,
+ elevel, &converted);
+
+ if (converted &&
+ array_check(stavalues, false, mc_elems_name, elevel) &&
+ array_check(stanumbers, true, mc_elem_freqs_name, elevel))
+ {
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_MCELEM,
+ elemtypcache->eq_opr, typcoll,
+ stanumbers, false, stavalues, false);
+ }
+ else
+ {
+ /* the mc_elem stat did not write */
+ mc_elems_isnull = true;
+ mc_elem_freqs_isnull = true;
+ }
+ }
+
+ /*
+ * STATISTIC_KIND_DECHIST
+ *
+ * elem_count_histogram: real[]
+ */
+ if (!elem_count_hist_isnull)
+ {
+ Datum stanumbers = elem_count_hist_datum;
+
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_DECHIST,
+ elemtypcache->eq_opr, typcoll,
+ stanumbers, false, 0, true);
+ }
+
+ /*
+ * STATISTIC_KIND_BOUNDS_HISTOGRAM
+ *
+ * range_bounds_histogram: ANYARRAY::text
+ *
+ * This stakind appears before STATISTIC_KIND_BOUNDS_HISTOGRAM even though
+ * 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.
+ */
+ if (!range_bounds_hist_isnull)
+ {
+ bool converted = false;
+ Datum stavalues;
+
+ stavalues = cast_stavalues(&finfo, range_bounds_hist_datum,
+ typcache->type_id, typmod,
+ elevel, &converted);
+
+ if (converted &&
+ array_check(stavalues, false, "range_bounds_histogram", elevel))
+ {
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_BOUNDS_HISTOGRAM,
+ InvalidOid, InvalidOid,
+ 0, true, stavalues, false);
+ }
+ else
+ range_bounds_hist_isnull = true;
+ }
+
+ /*
+ * STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM
+ *
+ * range_empty_frac: real
+ *
+ * range_length_histogram: double precision[]::text
+ */
+ if (!range_length_hist_isnull)
+ {
+ /* The anyarray is always a float8[] for this stakind */
+ Datum elems[] = {range_empty_frac_datum};
+ ArrayType *arry = construct_array_builtin(elems, 1, FLOAT4OID);
+ Datum stanumbers = PointerGetDatum(arry);
+
+ bool converted = false;
+ Datum stavalues;
+
+ stavalues = cast_stavalues(&finfo, range_length_hist_datum, FLOAT8OID,
+ 0, elevel, &converted);
+
+ if (converted &&
+ array_check(stavalues, false, range_length_hist_name, elevel))
+ {
+ use_stats_slot(values, nulls, stakindidx++,
+ STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM,
+ Float8LessOperator, InvalidOid,
+ stanumbers, false, stavalues, false);
+ }
+ else
+ {
+ range_empty_frac_isnull = true;
+ range_length_hist_isnull = true;
+ }
+ }
+
+ update_pg_statistic(values, nulls);
+
+ relation_close(rel, NoLock);
+
+ return true;
+}
+
+/*
+ * Test if the type is a scalar for MCELEM purposes
+ */
+static bool
+type_is_scalar(Oid typid)
+{
+ HeapTuple tp;
+ bool result = false;
+
+ tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
+
+ result = (!OidIsValid(typtup->typanalyze));
+ ReleaseSysCache(tp);
+ }
+ return result;
+}
+
+/*
+ * If this relation is an index and that index has expressions in it, and
+ * the attnum specified is known to be an expression, then we must walk
+ * the list attributes up to the specified attnum to get the right
+ * expression.
+ */
+static Node *
+get_attr_expr(Relation rel, int attnum)
+{
+ if ((rel->rd_rel->relkind == RELKIND_INDEX
+ || (rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX))
+ && (rel->rd_indexprs != NIL)
+ && (rel->rd_index->indkey.values[attnum - 1] == 0))
+ {
+ ListCell *indexpr_item = list_head(rel->rd_indexprs);
+
+ for (int i = 0; i < attnum - 1; i++)
+ if (rel->rd_index->indkey.values[i] == 0)
+ indexpr_item = lnext(rel->rd_indexprs, indexpr_item);
+
+ if (indexpr_item == NULL) /* shouldn't happen */
+ elog(ERROR, "too few entries in indexprs list");
+
+ return (Node *) lfirst(indexpr_item);
+ }
+ return NULL;
+}
+
+/*
+ * Fetch datatype information, this is needed to derive the proper staopN
+ * and stacollN values.
+ *
+ */
+static TypeCacheEntry *
+get_attr_stat_type(Relation rel, AttrNumber attnum, int elevel,
+ int32 *typmod, Oid *typcoll)
+{
+ Oid relid = RelationGetRelid(rel);
+ Form_pg_attribute attr;
+ HeapTuple atup;
+ Oid typid;
+ Node *expr;
+
+ atup = SearchSysCache2(ATTNUM, ObjectIdGetDatum(relid),
+ Int16GetDatum(attnum));
+
+ /* Attribute not found */
+ if (!HeapTupleIsValid(atup))
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("Relation %s has no attribute %d",
+ RelationGetRelationName(rel), attnum)));
+ return NULL;
+ }
+
+ attr = (Form_pg_attribute) GETSTRUCT(atup);
+ if (attr->attisdropped)
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("Relation %s attribute %d is dropped",
+ RelationGetRelationName(rel),
+ attnum)));
+ return NULL;
+ }
+
+ expr = get_attr_expr(rel, attr->attnum);
+
+ if (expr == NULL)
+ {
+ /* regular attribute */
+ typid = attr->atttypid;
+ *typmod = attr->atttypmod;
+ *typcoll = attr->attcollation;
+ }
+ else
+ {
+ typid = exprType(expr);
+ *typmod = exprTypmod(expr);
+
+ /*
+ * If a collation has been specified for the index column, use that in
+ * preference to anything else; but if not, fall back to whatever we
+ * can get from the expression.
+ */
+ if (OidIsValid(attr->attcollation))
+ *typcoll = attr->attcollation;
+ else
+ *typcoll = exprCollation(expr);
+ }
+ ReleaseSysCache(atup);
+
+ /* if it's a multirange, step down to the range type */
+ if (type_is_multirange(typid))
+ typid = get_multirange_range(typid);
+
+ return lookup_type_cache(typid,
+ TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR);
+}
+
+/*
+ * Perform the cast of a known TextDatum into the type specified.
+ *
+ * If no errors are found, ok is set to true.
+ *
+ * Otherwise, set ok to false, capture the error found, and re-throw at the
+ * level specified by elevel.
+ */
+static Datum
+cast_stavalues(FmgrInfo *flinfo, Datum d, Oid typid, int32 typmod,
+ int elevel, bool *ok)
+{
+ LOCAL_FCINFO(fcinfo, 8);
+ char *s;
+ Datum result;
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+ escontext.details_wanted = true;
+
+ s = TextDatumGetCString(d);
+
+ InitFunctionCallInfoData(*fcinfo, flinfo, 3, InvalidOid,
+ (Node *) &escontext, NULL);
+
+ fcinfo->args[0].value = CStringGetDatum(s);
+ fcinfo->args[0].isnull = false;
+ fcinfo->args[1].value = ObjectIdGetDatum(typid);
+ fcinfo->args[1].isnull = false;
+ fcinfo->args[2].value = Int32GetDatum(typmod);
+ fcinfo->args[2].isnull = false;
+
+ result = FunctionCallInvoke(fcinfo);
+
+ if (SOFT_ERROR_OCCURRED(&escontext))
+ {
+ if (elevel != ERROR)
+ escontext.error_data->elevel = elevel;
+ ThrowErrorData(escontext.error_data);
+ *ok = false;
+ }
+ else
+ *ok = true;
+
+ pfree(s);
+
+ return result;
+}
+
+
+/*
+ * Check array for any NULLs, and optionally for one-dimensionality.
+ *
+ * Report any failures at the level of elevel.
+ */
+static bool
+array_check(Datum datum, int one_dim, const char *statname, int elevel)
+{
+ ArrayType *arr = DatumGetArrayTypeP(datum);
+ int16 elmlen;
+ char elmalign;
+ bool elembyval;
+ Datum *values;
+ bool *nulls;
+ int nelems;
+
+ if (one_dim && (arr->ndim != 1))
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s cannot be a multidimensional array", statname)));
+ return false;
+ }
+
+ get_typlenbyvalalign(ARR_ELEMTYPE(arr), &elmlen, &elembyval, &elmalign);
+
+ deconstruct_array(arr, ARR_ELEMTYPE(arr), elmlen, elembyval, elmalign,
+ &values, &nulls, &nelems);
+
+ for (int i = 0; i < nelems; i++)
+ if (nulls[i])
+ {
+ ereport(elevel,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("%s array cannot contain NULL values", statname)));
+ return false;
+ }
+ return true;
+}
+
+/*
+ * Update the pg_statistic record.
+ */
+static void
+update_pg_statistic(Datum values[], bool nulls[])
+{
+ Relation sd = table_open(StatisticRelationId, RowExclusiveLock);
+ CatalogIndexState indstate = CatalogOpenIndexes(sd);
+ HeapTuple oldtup;
+ HeapTuple stup;
+
+ /* Is there already a pg_statistic tuple for this attribute? */
+ oldtup = SearchSysCache3(STATRELATTINH,
+ values[Anum_pg_statistic_starelid - 1],
+ values[Anum_pg_statistic_staattnum - 1],
+ values[Anum_pg_statistic_stainherit - 1]);
+
+ if (HeapTupleIsValid(oldtup))
+ {
+ /* Yes, replace it */
+ bool replaces[Natts_pg_statistic];
+
+ for (int i = 0; i < Natts_pg_statistic; i++)
+ replaces[i] = true;
+
+ stup = heap_modify_tuple(oldtup, RelationGetDescr(sd),
+ values, nulls, replaces);
+ ReleaseSysCache(oldtup);
+ CatalogTupleUpdateWithInfo(sd, &stup->t_self, stup, indstate);
+ }
+ else
+ {
+ /* No, insert new tuple */
+ stup = heap_form_tuple(RelationGetDescr(sd), values, nulls);
+ CatalogTupleInsertWithInfo(sd, stup, indstate);
+ }
+
+ heap_freetuple(stup);
+ CatalogCloseIndexes(indstate);
+ table_close(sd, NoLock);
+}
+
/*
* Set statistics for a given pg_class entry.
*
@@ -210,3 +1007,161 @@ pg_set_relation_stats(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+
+static
+void use_stats_slot(Datum *values, bool *nulls, int slotidx,
+ int16 stakind, Oid staop, Oid stacoll,
+ Datum stanumbers, bool stanumbers_isnull,
+ Datum stavalues, bool stavalues_isnull)
+{
+ if (slotidx >= STATISTIC_NUM_SLOTS)
+ ereport(ERROR,
+ (errmsg("maximum number of statistics slots exceeded: %d", slotidx + 1)));
+
+ /* slot should not be taken */
+ Assert(values[Anum_pg_statistic_stakind1 - 1 + slotidx] == (Datum) 0);
+ Assert(values[Anum_pg_statistic_staop1 - 1 + slotidx] == (Datum) 0);
+ Assert(values[Anum_pg_statistic_stacoll1 - 1 + slotidx] == (Datum) 0);
+ Assert(values[Anum_pg_statistic_stanumbers1 - 1 + slotidx] == (Datum) 0);
+ Assert(values[Anum_pg_statistic_stavalues1 - 1 + slotidx] == (Datum) 0);
+
+ /* nulls should be false for non-NULL attributes, true for nullable */
+ Assert(!nulls[Anum_pg_statistic_stakind1 - 1 + slotidx]);
+ Assert(!nulls[Anum_pg_statistic_staop1 - 1 + slotidx]);
+ Assert(!nulls[Anum_pg_statistic_stacoll1 - 1 + slotidx]);
+ Assert(nulls[Anum_pg_statistic_stanumbers1 - 1 + slotidx]);
+ Assert(nulls[Anum_pg_statistic_stavalues1 - 1 + slotidx]);
+
+ values[Anum_pg_statistic_stakind1 - 1 + slotidx] = stakind;
+ values[Anum_pg_statistic_staop1 - 1 + slotidx] = staop;
+ values[Anum_pg_statistic_stacoll1 - 1 + slotidx] = stacoll;
+
+ if (!stanumbers_isnull)
+ {
+ values[Anum_pg_statistic_stanumbers1 - 1 + slotidx] = stanumbers;
+ nulls[Anum_pg_statistic_stanumbers1 - 1 + slotidx] = false;
+ }
+ if (!stavalues_isnull)
+ {
+ values[Anum_pg_statistic_stavalues1 - 1 + slotidx] = stavalues;
+ nulls[Anum_pg_statistic_stavalues1 - 1 + slotidx] = false;
+ }
+}
+
+/*
+ * Import statistics for a given relation attribute.
+ *
+ * This will insert/replace a row in pg_statistic for the given relation and
+ * attribute name.
+ *
+ * The function takes input parameters that correspond to columns in the view
+ * pg_stats.
+ *
+ * Of those, the columns attname, inherited, null_frac, avg_width, and
+ * n_distinct all correspond to NOT NULL columns in pg_statistic. These
+ * parameters have no default value and passing NULL to them will result
+ * in an error.
+ *
+ * If there is no attribute with a matching attname in the relation, the
+ * function will raise an error. Likewise for setting inherited statistics
+ * on a table that is not partitioned.
+ *
+ * The remaining parameters all belong to a specific stakind. Some stakinds
+ * have multiple parameters, and in those cases both parameters must be
+ * NOT NULL or both NULL, otherwise an error will be raised.
+ *
+ * Omitting a parameter or explicitly passing NULL means that that particular
+ * stakind is not associated with the attribute.
+ *
+ * Parameters that are NOT NULL will be inspected for consistency checks,
+ * any of which can raise an error.
+ *
+ * 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. Any error generated by the array_in() function will
+ * in turn fail the function.
+ */
+Datum
+pg_set_attribute_stats(PG_FUNCTION_ARGS)
+{
+ Oid reloid;
+ Name attname;
+ AttrNumber attnum;
+ int version = PG_VERSION_NUM;
+ int elevel = ERROR;
+ bool inherited;
+ float null_frac;
+ int avg_width;
+ float n_distinct;
+
+ if (PG_ARGISNULL(0))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("relation cannot be NULL")));
+ return false;
+ }
+ reloid = PG_GETARG_OID(0);
+
+ if (PG_ARGISNULL(1))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("attname cannot be NULL")));
+ return false;
+ }
+ attname = PG_GETARG_NAME(1);
+ attnum = get_attnum(reloid, NameStr(*attname));
+
+ if (PG_ARGISNULL(2))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("inherited cannot be NULL")));
+ return false;
+ }
+ inherited = PG_GETARG_BOOL(2);
+
+ if (PG_ARGISNULL(3))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("null_frac cannot be NULL")));
+ return false;
+ }
+ null_frac = PG_GETARG_FLOAT4(3);
+
+ if (PG_ARGISNULL(4))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("avg_width cannot be NULL")));
+ return false;
+ }
+ avg_width = PG_GETARG_INT32(4);
+
+ if (PG_ARGISNULL(5))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("n_distinct cannot be NULL")));
+ return false;
+ }
+ n_distinct = PG_GETARG_FLOAT4(5);
+
+ attribute_statistics_update(
+ reloid, attnum, version, elevel, inherited,
+ null_frac, avg_width, n_distinct,
+ PG_GETARG_DATUM(6), PG_ARGISNULL(6),
+ PG_GETARG_DATUM(7), PG_ARGISNULL(7),
+ PG_GETARG_DATUM(8), PG_ARGISNULL(8),
+ PG_GETARG_DATUM(9), PG_ARGISNULL(9),
+ PG_GETARG_DATUM(10), PG_ARGISNULL(10),
+ PG_GETARG_DATUM(11), PG_ARGISNULL(11),
+ PG_GETARG_DATUM(12), PG_ARGISNULL(12),
+ PG_GETARG_DATUM(13), PG_ARGISNULL(13),
+ PG_GETARG_DATUM(14), PG_ARGISNULL(14),
+ PG_GETARG_DATUM(15), PG_ARGISNULL(15));
+
+ PG_RETURN_VOID();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d700dd50f7b..fbd1a8a384d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12263,5 +12263,12 @@
proargtypes => 'oid int4 float4 int4',
proargnames => '{relation,relpages,reltuples,relallvisible}',
prosrc => 'pg_set_relation_stats' },
+{ oid => '8049',
+ descr => 'set statistics on attribute',
+ proname => 'pg_set_attribute_stats', provolatile => 'v', proisstrict => 'f',
+ proparallel => 'u', prorettype => 'void',
+ 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' },
]
diff --git a/src/include/statistics/statistics.h b/src/include/statistics/statistics.h
index 7f2bf18716d..73d3b541dda 100644
--- a/src/include/statistics/statistics.h
+++ b/src/include/statistics/statistics.h
@@ -127,4 +127,7 @@ 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);
+extern Datum pg_set_attribute_stats(PG_FUNCTION_ARGS);
+
#endif /* STATISTICS_H */
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index f727cce9765..3a26b1f7d67 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -93,7 +93,650 @@ WHERE oid = 'stats_import.test'::regclass;
18 | 401 | 5
(1 row)
+-- error: object doesn't exist
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => '0'::oid,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+ERROR: cache lookup failed for attribute 0 of relation 0
+-- error: relation null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => NULL::oid,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+ERROR: relation cannot be NULL
+-- error: attname null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => NULL::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+ERROR: attname cannot be NULL
+-- error: inherited null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => NULL::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+ERROR: inherited cannot be NULL
+-- error: null_frac null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => NULL::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+ERROR: null_frac cannot be NULL
+-- error: avg_width null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ 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
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => NULL::real);
+ERROR: n_distinct cannot be NULL
+-- ok: no stakinds
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+ pg_set_attribute_stats
+------------------------
+
+(1 row)
+
+SELECT stanullfrac, stawidth, stadistinct
+FROM pg_statistic
+WHERE starelid = 'stats_import.test'::regclass;
+ stanullfrac | stawidth | stadistinct
+-------------+----------+-------------
+ 0.1 | 2 | 0.3
+(1 row)
+
+-- warn: mcv / mcf null mismatch
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_freqs => '{0.1,0.2,0.3}'::real[]
+ );
+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,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{1,2,3}'::text
+ );
+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,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{2023-09-30,2024-10-31,3}'::text,
+ most_common_freqs => '{0.2,0.1}'::real[]
+ );
+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,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{2,four,3}'::text,
+ most_common_freqs => '{0.3,0.25,0.05}'::real[]
+ );
+ERROR: invalid input syntax for type integer: "four"
+-- ok: mcv+mcf
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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[]
+ );
+ pg_set_attribute_stats
+------------------------
+
+(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: histogram elements null value
+-- this generates no warnings, but perhaps it should
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ histogram_bounds => '{1,NULL,3,4}'::text
+ );
+ERROR: histogram_bounds array cannot contain NULL values
+-- ok: histogram_bounds
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ histogram_bounds => '{1,2,3,4}'::text
+ );
+ pg_set_attribute_stats
+------------------------
+
+(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)
+
+-- ok: correlation
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ correlation => 0.5::real);
+ pg_set_attribute_stats
+------------------------
+
+(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 | | | | 0.5 | | | | | |
+(1 row)
+
+-- warn: scalars can't have mcelem
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elems => '{1,3}'::text,
+ most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+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,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elems => '{one,two}'::text
+ );
+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,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elem_freqs => '{0.3,0.2,0.2,0.3}'::real[]
+ );
+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,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elems => '{one,three}'::text,
+ most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+ pg_set_attribute_stats
+------------------------
+
+(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 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | | | |
+(1 row)
+
+-- warn: scalars can't have elem_count_histogram
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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[]
+ );
+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,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ 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[]
+ );
+ pg_set_attribute_stats
+------------------------
+
+(1 row)
+
+-- ok: elem_count_histogram
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ 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[]
+ );
+ pg_set_attribute_stats
+------------------------
+
+(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)
+
+-- warn: scalars can't have range stats
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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
+ );
+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,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ range_length_histogram => '{399,499,Infinity}'::text
+ );
+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,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ range_empty_frac => 0.5::real
+ );
+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,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ 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
+ );
+ pg_set_attribute_stats
+------------------------
+
+(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: scalars can't have range stats
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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
+ );
+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,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ 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
+ );
+ pg_set_attribute_stats
+------------------------
+
+(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: exceed STATISTIC_NUM_SLOTS
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{"[2,3)","[1,2)","[3,4)"}'::text,
+ most_common_freqs => '{0.3,0.25,0.05}'::real[],
+ histogram_bounds => '{"[1,2)","[2,3)","[3,4)","[4,5)"}'::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
+ );
+ERROR: maximum number of statistics slots exceeded: 6
+--
+-- Test the ability to exactly copy data from one table to an identical table,
+-- correctly reconstructing the stakind order as well as the staopN and
+-- stacollN values. Because oids are not stable across databases, we can only
+-- test this when the source and destination are on the same database
+-- instance. For that reason, we borrow and adapt a query found in fe_utils
+-- and used by pg_dump/pg_upgrade.
+--
+INSERT INTO stats_import.test
+SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import.complex_type, int4range(1,4), array['red','green']
+UNION ALL
+SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import.complex_type, int4range(1,4), array['blue','yellow']
+UNION ALL
+SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import.complex_type, int4range(-1,1), array['"orange"', 'purple', 'cyan']
+UNION ALL
+SELECT 4, 'four', NULL, int4range(0,100), NULL;
+CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1));
+-- Generate statistics on table with data
+ANALYZE stats_import.test;
+CREATE TABLE stats_import.test_clone ( LIKE stats_import.test );
+CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1));
+--
+-- Copy stats from test to test_clone, and is_odd to is_odd_clone
+--
+SELECT s.schemaname, s.tablename, s.attname, s.inherited
+FROM pg_catalog.pg_stats AS s
+CROSS JOIN LATERAL
+ pg_catalog.pg_set_attribute_stats(
+ relation => ('stats_import.' || s.tablename || '_clone')::regclass::oid,
+ attname => s.attname,
+ inherited => s.inherited,
+ 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
+--------------+-----------+---------+-----------
+ stats_import | is_odd | expr | f
+ stats_import | test | arange | f
+ stats_import | test | comp | f
+ stats_import | test | id | f
+ stats_import | test | name | f
+ stats_import | test | tags | f
+(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 2 other objects
+NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to type stats_import.complex_type
drop cascades to table stats_import.test
+drop cascades to table stats_import.test_clone
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index effd5b892bf..4fa6ea41519 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -76,4 +76,548 @@ SELECT relpages, reltuples, relallvisible
FROM pg_class
WHERE oid = 'stats_import.test'::regclass;
+-- error: object doesn't exist
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => '0'::oid,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+
+-- error: relation null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => NULL::oid,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+
+-- error: attname null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => NULL::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+
+-- error: inherited null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => NULL::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+
+-- error: null_frac null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => NULL::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+
+-- error: avg_width null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => NULL::integer,
+ n_distinct => 0.3::real);
+
+-- error: avg_width null
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => NULL::real);
+
+-- ok: no stakinds
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.1::real,
+ avg_width => 2::integer,
+ n_distinct => 0.3::real);
+
+SELECT stanullfrac, stawidth, stadistinct
+FROM pg_statistic
+WHERE starelid = 'stats_import.test'::regclass;
+
+-- warn: mcv / mcf null mismatch
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_freqs => '{0.1,0.2,0.3}'::real[]
+ );
+
+-- warn: mcv / mcf null mismatch part 2
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{1,2,3}'::text
+ );
+
+-- warn: mcv / mcf type mismatch
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{2023-09-30,2024-10-31,3}'::text,
+ most_common_freqs => '{0.2,0.1}'::real[]
+ );
+
+-- warning: mcv cast failure
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{2,four,3}'::text,
+ most_common_freqs => '{0.3,0.25,0.05}'::real[]
+ );
+
+-- ok: mcv+mcf
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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: histogram elements null value
+-- this generates no warnings, but perhaps it should
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ histogram_bounds => '{1,NULL,3,4}'::text
+ );
+
+-- ok: histogram_bounds
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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';
+
+-- ok: correlation
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ correlation => 0.5::real);
+
+SELECT *
+FROM pg_stats
+WHERE schemaname = 'stats_import'
+AND tablename = 'test'
+AND inherited = false
+AND attname = 'id';
+
+-- warn: scalars can't have mcelem
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elems => '{1,3}'::text,
+ most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+
+-- warn: mcelem / mcelem mismatch
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elems => '{one,two}'::text
+ );
+
+-- warn: mcelem / mcelem null mismatch part 2
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elem_freqs => '{0.3,0.2,0.2,0.3}'::real[]
+ );
+
+-- ok: mcelem
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_elems => '{one,three}'::text,
+ most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[]
+ );
+
+SELECT *
+FROM pg_stats
+WHERE schemaname = 'stats_import'
+AND tablename = 'test'
+AND inherited = false
+AND attname = 'tags';
+
+-- warn: scalars can't have elem_count_histogram
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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[]
+ );
+-- warn: elem_count_histogram null element
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ 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[]
+ );
+-- ok: elem_count_histogram
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'tags'::name,
+ inherited => false::boolean,
+ 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';
+
+-- warn: scalars can't have range stats
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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
+ );
+-- warn: range_empty_frac range_length_hist null mismatch
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ 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,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ 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,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ 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: scalars can't have range stats
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'id'::name,
+ inherited => false::boolean,
+ 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
+ );
+-- ok: range_bounds_histogram
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ 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: exceed STATISTIC_NUM_SLOTS
+SELECT pg_catalog.pg_set_attribute_stats(
+ relation => 'stats_import.test'::regclass,
+ attname => 'arange'::name,
+ inherited => false::boolean,
+ null_frac => 0.5::real,
+ avg_width => 2::integer,
+ n_distinct => -0.1::real,
+ most_common_vals => '{"[2,3)","[1,2)","[3,4)"}'::text,
+ most_common_freqs => '{0.3,0.25,0.05}'::real[],
+ histogram_bounds => '{"[1,2)","[2,3)","[3,4)","[4,5)"}'::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
+ );
+--
+-- Test the ability to exactly copy data from one table to an identical table,
+-- correctly reconstructing the stakind order as well as the staopN and
+-- stacollN values. Because oids are not stable across databases, we can only
+-- test this when the source and destination are on the same database
+-- instance. For that reason, we borrow and adapt a query found in fe_utils
+-- and used by pg_dump/pg_upgrade.
+--
+INSERT INTO stats_import.test
+SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import.complex_type, int4range(1,4), array['red','green']
+UNION ALL
+SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import.complex_type, int4range(1,4), array['blue','yellow']
+UNION ALL
+SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import.complex_type, int4range(-1,1), array['"orange"', 'purple', 'cyan']
+UNION ALL
+SELECT 4, 'four', NULL, int4range(0,100), NULL;
+
+CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1));
+
+-- Generate statistics on table with data
+ANALYZE stats_import.test;
+
+CREATE TABLE stats_import.test_clone ( LIKE stats_import.test );
+
+CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1));
+
+--
+-- Copy stats from test to test_clone, and is_odd to is_odd_clone
+--
+SELECT s.schemaname, s.tablename, s.attname, s.inherited
+FROM pg_catalog.pg_stats AS s
+CROSS JOIN LATERAL
+ pg_catalog.pg_set_attribute_stats(
+ relation => ('stats_import.' || s.tablename || '_clone')::regclass::oid,
+ attname => s.attname,
+ inherited => s.inherited,
+ 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;
--
2.34.1
view thread (55+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Statistics Import and Export
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox