On Mon, 2025-02-24 at 12:50 -0500, Tom Lane wrote:
> Also, while working on the attached, I couldn't help forming the
> opinion that we'd be better off to nuke pg_set_attribute_stats()
> from orbit and require people to use pg_restore_attribute_stats().
Attached a patch to do so. The docs and tests required substantial
rework, but I think it's for the better now that we aren't trying to do
in-place updates.
Regards,
Jeff Davis
From ea413ee48b10299530bafc3102395285b5ea8ce3 Mon Sep 17 00:00:00 2001
From: Jeff Davis <j...@j-davis.com>
Date: Mon, 24 Feb 2025 17:24:05 -0800
Subject: [PATCH v1] Remove redundant pg_set_*_stats() variants.
After commit f3dae2ae58, the primary purpose of separating the
pg_set_*_stats() from the pg_restore_*_stats() variants was
eliminated.
Leave pg_restore_relation_stats() and pg_restore_attribute_stats(),
which satisfy both purposes, and remove pg_set_relation_stats() and
pg_set_attribute_stats().
Discussion: https://postgr.es/m/1457469.1740419...@sss.pgh.pa.us
---
doc/src/sgml/func.sgml | 254 +++----
src/backend/catalog/system_functions.sql | 32 -
src/backend/statistics/attribute_stats.c | 98 +--
src/backend/statistics/relation_stats.c | 24 +-
src/backend/statistics/stat_utils.c | 30 +-
src/include/catalog/pg_proc.dat | 14 -
src/include/statistics/stat_utils.h | 8 +-
src/test/regress/expected/stats_import.out | 832 +--------------------
src/test/regress/sql/stats_import.sql | 648 +---------------
9 files changed, 209 insertions(+), 1731 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f0ccb751106..12206e0cfc6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -30180,66 +30180,6 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</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>
- <optional>, <parameter>relpages</parameter> <type>integer</type></optional>
- <optional>, <parameter>reltuples</parameter> <type>real</type></optional>
- <optional>, <parameter>relallvisible</parameter> <type>integer</type></optional> )
- <returnvalue>void</returnvalue>
- </para>
- <para>
- Updates relation-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>. Unspecified
- or <literal>NULL</literal> values leave the setting unchanged.
- </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>-1</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>
- </entry>
- </row>
-
- <row>
- <entry role="func_table_entry">
- <para role="func_signature">
- <indexterm>
- <primary>pg_clear_relation_stats</primary>
- </indexterm>
- <function>pg_clear_relation_stats</function> ( <parameter>relation</parameter> <type>regclass</type> )
- <returnvalue>void</returnvalue>
- </para>
- <para>
- Clears table-level statistics for the given relation, as though the
- table was newly created.
- </para>
- <para>
- The caller must have the <literal>MAINTAIN</literal> privilege on
- the table or be the owner of the database.
- </para>
- </entry>
- </row>
-
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -30248,26 +30188,25 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
<function>pg_restore_relation_stats</function> (
<literal>VARIADIC</literal> <parameter>kwargs</parameter> <type>"any"</type> )
<returnvalue>boolean</returnvalue>
- </para>
- <para>
- Similar to <function>pg_set_relation_stats()</function>, but intended
- for bulk restore of relation statistics. The tracked statistics may
- change from version to version, so the primary purpose of this
- function is to maintain a consistent function signature to avoid
- errors when restoring statistics from previous versions.
- </para>
+ </para>
<para>
- Arguments are passed as pairs of <replaceable>argname</replaceable>
- and <replaceable>argvalue</replaceable>, where
- <replaceable>argname</replaceable> corresponds to a named argument in
- <function>pg_set_relation_stats()</function> and
- <replaceable>argvalue</replaceable> is of the corresponding type.
+ Updates table-level statistics. 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 is useful after a
+ restore to enable the optimizer to choose better plans if
+ <command>ANALYZE</command> has not been run yet.
</para>
<para>
- Additionally, this function supports argument name
- <literal>version</literal> of type <type>integer</type>, which
- specifies the version from which the statistics originated, improving
- interpretation of older statistics.
+ The tracked statistics may change from version to version, so
+ arguments are passed as pairs of <replaceable>argname</replaceable>
+ and <replaceable>argvalue</replaceable> in the form:
+<programlisting>
+ SELECT pg_restore_relation_stats(
+ '<replaceable>arg1name</replaceable>', '<replaceable>arg1value</replaceable>'::<replaceable>arg1type</replaceable>,
+ '<replaceable>arg2name</replaceable>', '<replaceable>arg2value</replaceable>'::<replaceable>arg2type</replaceable>,
+ '<replaceable>arg3name</replaceable>', '<replaceable>arg3value</replaceable>'::<replaceable>arg3type</replaceable>);
+</programlisting>
</para>
<para>
For example, to set the <structname>relpages</structname> and
@@ -30277,62 +30216,37 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
SELECT pg_restore_relation_stats(
'relation', 'mytable'::regclass,
'relpages', 173::integer,
- 'reltuples', 10000::float4);
+ 'reltuples', 10000::real);
</programlisting>
</para>
<para>
- Minor errors are reported as a <literal>WARNING</literal> and
- ignored, and remaining statistics will still be restored. If all
- specified statistics are successfully restored, return
- <literal>true</literal>, otherwise <literal>false</literal>.
- </para>
- </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>
- <optional>, <parameter>null_frac</parameter> <type>real</type></optional>
- <optional>, <parameter>avg_width</parameter> <type>integer</type></optional>
- <optional>, <parameter>n_distinct</parameter> <type>real</type></optional>
- <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>
- Creates or updates attribute-level statistics for the given relation
- and attribute name to the specified values. The parameters correspond
- to attributes of the same name found in the <link
- linkend="view-pg-stats"><structname>pg_stats</structname></link>
- view.
+ The argument <literal>relation</literal> with a value of type
+ <type>regclass</type> is required, and specifies the table. Other
+ arguments are the names of statistics corresponding to certain
+ columns in <link
+ linkend="catalog-pg-class"><structname>pg_class</structname></link>.
+ The currently-supported relation statistics are
+ <literal>relpages</literal> with a value of type
+ <type>integer</type>, <literal>reltuples</literal> with a value of
+ type <type>real</type>, and <literal>relallvisible</literal> with a
+ value of type <type>integer</type>.
</para>
<para>
- Optional parameters default to <literal>NULL</literal>, which leave
- the corresponding statistic unchanged.
+ Additionally, this function supports argument name
+ <literal>version</literal> of type <type>integer</type>, which
+ specifies the version from which the statistics originated, improving
+ interpretation of statistics from older versions of
+ <productname>PostgreSQL</productname>.
</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.
+ Minor errors are reported as a <literal>WARNING</literal> and
+ ignored, and remaining statistics will still be restored. If all
+ specified statistics are successfully restored, return
+ <literal>true</literal>, otherwise <literal>false</literal>.
</para>
<para>
- The caller must have the <literal>MAINTAIN</literal> privilege on
- the table or be the owner of the database.
+ The caller must have the <literal>MAINTAIN</literal> privilege on the
+ table or be the owner of the database.
</para>
</entry>
</row>
@@ -30341,21 +30255,18 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
<entry role="func_table_entry">
<para role="func_signature">
<indexterm>
- <primary>pg_clear_attribute_stats</primary>
+ <primary>pg_clear_relation_stats</primary>
</indexterm>
- <function>pg_clear_attribute_stats</function> (
- <parameter>relation</parameter> <type>regclass</type>,
- <parameter>attname</parameter> <type>name</type>,
- <parameter>inherited</parameter> <type>boolean</type> )
+ <function>pg_clear_relation_stats</function> ( <parameter>relation</parameter> <type>regclass</type> )
<returnvalue>void</returnvalue>
</para>
<para>
- Clears table-level statistics for the given relation attribute, as
- though the table was newly created.
+ Clears table-level statistics for the given relation, as though the
+ table was newly created.
</para>
<para>
- The caller must have the <literal>MAINTAIN</literal> privilege on
- the table or be the owner of the database.
+ The caller must have the <literal>MAINTAIN</literal> privilege on the
+ table or be the owner of the database.
</para>
</entry>
</row>
@@ -30368,26 +30279,25 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
<function>pg_restore_attribute_stats</function> (
<literal>VARIADIC</literal> <parameter>kwargs</parameter> <type>"any"</type> )
<returnvalue>boolean</returnvalue>
- </para>
- <para>
- Similar to <function>pg_set_attribute_stats()</function>, but
- intended for bulk restore of attribute statistics. The tracked
- statistics may change from version to version, so the primary purpose
- of this function is to maintain a consistent function signature to
- avoid errors when restoring statistics from previous versions.
- </para>
+ </para>
<para>
- Arguments are passed as pairs of <replaceable>argname</replaceable>
- and <replaceable>argvalue</replaceable>, where
- <replaceable>argname</replaceable> corresponds to a named argument in
- <function>pg_set_attribute_stats()</function> and
- <replaceable>argvalue</replaceable> is of the corresponding type.
+ Create or update column-level statistics. 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 is useful after a
+ restore to enable the optimizer to choose better plans if
+ <command>ANALYZE</command> has not been run yet.
</para>
<para>
- Additionally, this function supports argument name
- <literal>version</literal> of type <type>integer</type>, which
- specifies the version from which the statistics originated, improving
- interpretation of older statistics.
+ The tracked statistics may change from version to version, so
+ arguments are passed as pairs of <replaceable>argname</replaceable>
+ and <replaceable>argvalue</replaceable> in the form:
+<programlisting>
+ SELECT pg_restore_attribute_stats(
+ '<replaceable>arg1name</replaceable>', '<replaceable>arg1value</replaceable>'::<replaceable>arg1type</replaceable>,
+ '<replaceable>arg2name</replaceable>', '<replaceable>arg2value</replaceable>'::<replaceable>arg2type</replaceable>,
+ '<replaceable>arg3name</replaceable>', '<replaceable>arg3value</replaceable>'::<replaceable>arg3type</replaceable>);
+</programlisting>
</para>
<para>
For example, to set the <structname>avg_width</structname> and
@@ -30403,12 +30313,56 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
'null_frac', 0.5::real);
</programlisting>
</para>
+ <para>
+ The required arguments are <literal>relation</literal> with a value
+ of type <type>regclass</type>, which specifies the table;
+ <literal>attname</literal> with a value of type <type>name</type>,
+ which specifies the column; and <literal>inherited</literal>, which
+ specifies whether the statistics includes values from child tables.
+ Other arguments are the names of statistics corresponding to columns
+ in <link
+ linkend="view-pg-stats"><structname>pg_stats</structname></link>.
+ </para>
+ <para>
+ Additionally, this function supports argument name
+ <literal>version</literal> of type <type>integer</type>, which
+ specifies the version from which the statistics originated, improving
+ interpretation of statistics from older versions of
+ <productname>PostgreSQL</productname>.
+ </para>
<para>
Minor errors are reported as a <literal>WARNING</literal> and
ignored, and remaining statistics will still be restored. If all
specified statistics are successfully restored, return
<literal>true</literal>, otherwise <literal>false</literal>.
</para>
+ <para>
+ The caller must have the <literal>MAINTAIN</literal> privilege on the
+ table or be the owner of the database.
+ </para>
+ </entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <indexterm>
+ <primary>pg_clear_attribute_stats</primary>
+ </indexterm>
+ <function>pg_clear_attribute_stats</function> (
+ <parameter>relation</parameter> <type>regclass</type>,
+ <parameter>attname</parameter> <type>name</type>,
+ <parameter>inherited</parameter> <type>boolean</type> )
+ <returnvalue>void</returnvalue>
+ </para>
+ <para>
+ Clears column-level statistics for the given relation and
+ attribute, as though the table was newly created.
+ </para>
+ <para>
+ The caller must have the <literal>MAINTAIN</literal> privilege on
+ the table or be the owner of the database.
+ </para>
</entry>
</row>
</tbody>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 591157b1d1b..86888cd3201 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -636,38 +636,6 @@ LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_slru';
-CREATE OR REPLACE FUNCTION
- pg_set_relation_stats(relation regclass,
- relpages integer DEFAULT NULL,
- reltuples real DEFAULT NULL,
- relallvisible integer DEFAULT NULL)
-RETURNS void
-LANGUAGE INTERNAL
-CALLED ON NULL INPUT VOLATILE
-AS 'pg_set_relation_stats';
-
-CREATE OR REPLACE FUNCTION
- pg_set_attribute_stats(relation regclass,
- attname name,
- inherited bool,
- null_frac real DEFAULT NULL,
- avg_width integer DEFAULT NULL,
- n_distinct real DEFAULT NULL,
- 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/attribute_stats.c b/src/backend/statistics/attribute_stats.c
index c0c398a4bb2..66a5676c810 100644
--- a/src/backend/statistics/attribute_stats.c
+++ b/src/backend/statistics/attribute_stats.c
@@ -76,16 +76,16 @@ static struct StatsArgInfo attarginfo[] =
[NUM_ATTRIBUTE_STATS_ARGS] = {0}
};
-static bool attribute_statistics_update(FunctionCallInfo fcinfo, int elevel);
+static bool attribute_statistics_update(FunctionCallInfo fcinfo);
static Node *get_attr_expr(Relation rel, int attnum);
-static void get_attr_stat_type(Oid reloid, AttrNumber attnum, int elevel,
+static void get_attr_stat_type(Oid reloid, AttrNumber attnum,
Oid *atttypid, int32 *atttypmod,
char *atttyptype, Oid *atttypcoll,
Oid *eq_opr, Oid *lt_opr);
-static bool get_elem_stat_type(Oid atttypid, char atttyptype, int elevel,
+static bool get_elem_stat_type(Oid atttypid, char atttyptype,
Oid *elemtypid, Oid *elem_eq_opr);
static Datum text_to_stavalues(const char *staname, FmgrInfo *array_in, Datum d,
- Oid typid, int32 typmod, int elevel, bool *ok);
+ Oid typid, int32 typmod, bool *ok);
static void set_stats_slot(Datum *values, bool *nulls, bool *replaces,
int16 stakind, Oid staop, Oid stacoll,
Datum stanumbers, bool stanumbers_isnull,
@@ -109,11 +109,11 @@ static void init_empty_stats_tuple(Oid reloid, int16 attnum, bool inherited,
*
* Major errors, such as the table not existing, the attribute not existing,
* or a permissions failure are always reported at ERROR. Other errors, such
- * as a conversion failure on one statistic kind, are reported at 'elevel',
+ * as a conversion failure on one statistic kind, are reported as a WARNING
* and other statistic kinds may still be updated.
*/
static bool
-attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
+attribute_statistics_update(FunctionCallInfo fcinfo)
{
Oid reloid;
Name attname;
@@ -184,33 +184,29 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
inherited = PG_GETARG_BOOL(INHERITED_ARG);
/*
- * Check argument sanity. If some arguments are unusable, emit at elevel
+ * Check argument sanity. If some arguments are unusable, emit a WARNING
* and set the corresponding argument to NULL in fcinfo.
*/
- if (!stats_check_arg_array(fcinfo, attarginfo, MOST_COMMON_FREQS_ARG,
- elevel))
+ if (!stats_check_arg_array(fcinfo, attarginfo, MOST_COMMON_FREQS_ARG))
{
do_mcv = false;
result = false;
}
- if (!stats_check_arg_array(fcinfo, attarginfo, MOST_COMMON_ELEM_FREQS_ARG,
- elevel))
+ if (!stats_check_arg_array(fcinfo, attarginfo, MOST_COMMON_ELEM_FREQS_ARG))
{
do_mcelem = false;
result = false;
}
- if (!stats_check_arg_array(fcinfo, attarginfo, ELEM_COUNT_HISTOGRAM_ARG,
- elevel))
+ if (!stats_check_arg_array(fcinfo, attarginfo, ELEM_COUNT_HISTOGRAM_ARG))
{
do_dechist = false;
result = false;
}
if (!stats_check_arg_pair(fcinfo, attarginfo,
- MOST_COMMON_VALS_ARG, MOST_COMMON_FREQS_ARG,
- elevel))
+ MOST_COMMON_VALS_ARG, MOST_COMMON_FREQS_ARG))
{
do_mcv = false;
result = false;
@@ -218,7 +214,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
if (!stats_check_arg_pair(fcinfo, attarginfo,
MOST_COMMON_ELEMS_ARG,
- MOST_COMMON_ELEM_FREQS_ARG, elevel))
+ MOST_COMMON_ELEM_FREQS_ARG))
{
do_mcelem = false;
result = false;
@@ -226,14 +222,14 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
if (!stats_check_arg_pair(fcinfo, attarginfo,
RANGE_LENGTH_HISTOGRAM_ARG,
- RANGE_EMPTY_FRAC_ARG, elevel))
+ RANGE_EMPTY_FRAC_ARG))
{
do_range_length_histogram = false;
result = false;
}
/* derive information from attribute */
- get_attr_stat_type(reloid, attnum, elevel,
+ get_attr_stat_type(reloid, attnum,
&atttypid, &atttypmod,
&atttyptype, &atttypcoll,
&eq_opr, <_opr);
@@ -241,10 +237,10 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
/* if needed, derive element type */
if (do_mcelem || do_dechist)
{
- if (!get_elem_stat_type(atttypid, atttyptype, elevel,
+ if (!get_elem_stat_type(atttypid, atttyptype,
&elemtypid, &elem_eq_opr))
{
- ereport(elevel,
+ ereport(WARNING,
(errmsg("unable to determine element type of attribute \"%s\"", NameStr(*attname)),
errdetail("Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.")));
elemtypid = InvalidOid;
@@ -259,7 +255,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
/* histogram and correlation require less-than operator */
if ((do_histogram || do_correlation) && !OidIsValid(lt_opr))
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("could not determine less-than operator for attribute \"%s\"", NameStr(*attname)),
errdetail("Cannot set STATISTIC_KIND_HISTOGRAM or STATISTIC_KIND_CORRELATION.")));
@@ -273,7 +269,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
if ((do_range_length_histogram || do_bounds_histogram) &&
!(atttyptype == TYPTYPE_RANGE || atttyptype == TYPTYPE_MULTIRANGE))
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("attribute \"%s\" is not a range type", NameStr(*attname)),
errdetail("Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.")));
@@ -322,7 +318,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
&array_in_fn,
PG_GETARG_DATUM(MOST_COMMON_VALS_ARG),
atttypid, atttypmod,
- elevel, &converted);
+ &converted);
if (converted)
{
@@ -344,7 +340,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
stavalues = text_to_stavalues("histogram_bounds",
&array_in_fn,
PG_GETARG_DATUM(HISTOGRAM_BOUNDS_ARG),
- atttypid, atttypmod, elevel,
+ atttypid, atttypmod,
&converted);
if (converted)
@@ -382,7 +378,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
&array_in_fn,
PG_GETARG_DATUM(MOST_COMMON_ELEMS_ARG),
elemtypid, atttypmod,
- elevel, &converted);
+ &converted);
if (converted)
{
@@ -422,7 +418,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
&array_in_fn,
PG_GETARG_DATUM(RANGE_BOUNDS_HISTOGRAM_ARG),
atttypid, atttypmod,
- elevel, &converted);
+ &converted);
if (converted)
{
@@ -449,7 +445,7 @@ attribute_statistics_update(FunctionCallInfo fcinfo, int elevel)
stavalues = text_to_stavalues("range_length_histogram",
&array_in_fn,
PG_GETARG_DATUM(RANGE_LENGTH_HISTOGRAM_ARG),
- FLOAT8OID, 0, elevel, &converted);
+ FLOAT8OID, 0, &converted);
if (converted)
{
@@ -517,7 +513,7 @@ get_attr_expr(Relation rel, int attnum)
* Derive type information from the attribute.
*/
static void
-get_attr_stat_type(Oid reloid, AttrNumber attnum, int elevel,
+get_attr_stat_type(Oid reloid, AttrNumber attnum,
Oid *atttypid, int32 *atttypmod,
char *atttyptype, Oid *atttypcoll,
Oid *eq_opr, Oid *lt_opr)
@@ -599,7 +595,7 @@ get_attr_stat_type(Oid reloid, AttrNumber attnum, int elevel,
* Derive element type information from the attribute type.
*/
static bool
-get_elem_stat_type(Oid atttypid, char atttyptype, int elevel,
+get_elem_stat_type(Oid atttypid, char atttyptype,
Oid *elemtypid, Oid *elem_eq_opr)
{
TypeCacheEntry *elemtypcache;
@@ -634,13 +630,13 @@ get_elem_stat_type(Oid atttypid, char atttyptype, int elevel,
/*
* Cast a text datum into an array with element type elemtypid.
*
- * If an error is encountered, capture it and re-throw at elevel, and set ok
- * to false. If the resulting array contains NULLs, raise an error at elevel
- * and set ok to false. Otherwise, set ok to true.
+ * If an error is encountered, capture it and re-throw a WARNING, and set ok
+ * to false. If the resulting array contains NULLs, raise a WARNING and set ok
+ * to false. Otherwise, set ok to true.
*/
static Datum
text_to_stavalues(const char *staname, FmgrInfo *array_in, Datum d, Oid typid,
- int32 typmod, int elevel, bool *ok)
+ int32 typmod, bool *ok)
{
LOCAL_FCINFO(fcinfo, 8);
char *s;
@@ -667,8 +663,7 @@ text_to_stavalues(const char *staname, FmgrInfo *array_in, Datum d, Oid typid,
if (escontext.error_occurred)
{
- if (elevel != ERROR)
- escontext.error_data->elevel = elevel;
+ escontext.error_data->elevel = WARNING;
ThrowErrorData(escontext.error_data);
*ok = false;
return (Datum) 0;
@@ -676,7 +671,7 @@ text_to_stavalues(const char *staname, FmgrInfo *array_in, Datum d, Oid typid,
if (array_contains_nulls(DatumGetArrayTypeP(result)))
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("\"%s\" array cannot contain NULL values", staname)));
*ok = false;
@@ -851,33 +846,6 @@ init_empty_stats_tuple(Oid reloid, int16 attnum, bool inherited,
}
}
-/*
- * Import statistics for a given relation attribute.
- *
- * Inserts or replaces a row in pg_statistic for the given relation and
- * attribute name. It takes input parameters that correspond to columns in the
- * view pg_stats.
- *
- * Parameters null_frac, avg_width, and n_distinct all correspond to NOT NULL
- * columns in pg_statistic. The remaining parameters all belong to a specific
- * stakind. Some stakinds require multiple parameters, which must be specified
- * together (or neither specified).
- *
- * Parameters are only superficially validated. Omitting a parameter or
- * passing NULL leaves the statistic unchanged.
- *
- * 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)
-{
- attribute_statistics_update(fcinfo, ERROR);
- PG_RETURN_VOID();
-}
-
/*
* Delete statistics for the given attribute.
*/
@@ -933,10 +901,10 @@ pg_restore_attribute_stats(PG_FUNCTION_ARGS)
InvalidOid, NULL, NULL);
if (!stats_fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo,
- attarginfo, WARNING))
+ attarginfo))
result = false;
- if (!attribute_statistics_update(positional_fcinfo, WARNING))
+ if (!attribute_statistics_update(positional_fcinfo))
result = false;
PG_RETURN_BOOL(result);
diff --git a/src/backend/statistics/relation_stats.c b/src/backend/statistics/relation_stats.c
index 66731290a3e..11b1ef2dbc2 100644
--- a/src/backend/statistics/relation_stats.c
+++ b/src/backend/statistics/relation_stats.c
@@ -48,13 +48,13 @@ static struct StatsArgInfo relarginfo[] =
[NUM_RELATION_STATS_ARGS] = {0}
};
-static bool relation_statistics_update(FunctionCallInfo fcinfo, int elevel);
+static bool relation_statistics_update(FunctionCallInfo fcinfo);
/*
* Internal function for modifying statistics for a relation.
*/
static bool
-relation_statistics_update(FunctionCallInfo fcinfo, int elevel)
+relation_statistics_update(FunctionCallInfo fcinfo)
{
bool result = true;
Oid reloid;
@@ -83,7 +83,7 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel)
reltuples = PG_GETARG_FLOAT4(RELTUPLES_ARG);
if (reltuples < -1.0)
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("reltuples cannot be < -1.0")));
result = false;
@@ -118,7 +118,7 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel)
ctup = SearchSysCache1(RELOID, ObjectIdGetDatum(reloid));
if (!HeapTupleIsValid(ctup))
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg("pg_class entry for relid %u not found", reloid)));
table_close(crel, RowExclusiveLock);
@@ -169,16 +169,6 @@ relation_statistics_update(FunctionCallInfo fcinfo, int elevel)
return result;
}
-/*
- * Set statistics for a given pg_class entry.
- */
-Datum
-pg_set_relation_stats(PG_FUNCTION_ARGS)
-{
- relation_statistics_update(fcinfo, ERROR);
- PG_RETURN_VOID();
-}
-
/*
* Clear statistics for a given pg_class entry; that is, set back to initial
* stats for a newly-created table.
@@ -199,7 +189,7 @@ pg_clear_relation_stats(PG_FUNCTION_ARGS)
newfcinfo->args[3].value = UInt32GetDatum(0);
newfcinfo->args[3].isnull = false;
- relation_statistics_update(newfcinfo, ERROR);
+ relation_statistics_update(newfcinfo);
PG_RETURN_VOID();
}
@@ -214,10 +204,10 @@ pg_restore_relation_stats(PG_FUNCTION_ARGS)
InvalidOid, NULL, NULL);
if (!stats_fill_fcinfo_from_arg_pairs(fcinfo, positional_fcinfo,
- relarginfo, WARNING))
+ relarginfo))
result = false;
- if (!relation_statistics_update(positional_fcinfo, WARNING))
+ if (!relation_statistics_update(positional_fcinfo))
result = false;
PG_RETURN_BOOL(result);
diff --git a/src/backend/statistics/stat_utils.c b/src/backend/statistics/stat_utils.c
index e70ea1ce738..54ead90b5bb 100644
--- a/src/backend/statistics/stat_utils.c
+++ b/src/backend/statistics/stat_utils.c
@@ -48,13 +48,13 @@ stats_check_required_arg(FunctionCallInfo fcinfo,
* Check that argument is either NULL or a one dimensional array with no
* NULLs.
*
- * If a problem is found, emit at elevel, and return false. Otherwise return
+ * If a problem is found, emit a WARNING, and return false. Otherwise return
* true.
*/
bool
stats_check_arg_array(FunctionCallInfo fcinfo,
struct StatsArgInfo *arginfo,
- int argnum, int elevel)
+ int argnum)
{
ArrayType *arr;
@@ -65,7 +65,7 @@ stats_check_arg_array(FunctionCallInfo fcinfo,
if (ARR_NDIM(arr) != 1)
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("\"%s\" cannot be a multidimensional array",
arginfo[argnum].argname)));
@@ -74,7 +74,7 @@ stats_check_arg_array(FunctionCallInfo fcinfo,
if (array_contains_nulls(arr))
{
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("\"%s\" array cannot contain NULL values",
arginfo[argnum].argname)));
@@ -89,13 +89,13 @@ stats_check_arg_array(FunctionCallInfo fcinfo,
* a particular stakind, such as most_common_vals and most_common_freqs for
* STATISTIC_KIND_MCV.
*
- * If a problem is found, emit at elevel, and return false. Otherwise return
+ * If a problem is found, emit a WARNING, and return false. Otherwise return
* true.
*/
bool
stats_check_arg_pair(FunctionCallInfo fcinfo,
struct StatsArgInfo *arginfo,
- int argnum1, int argnum2, int elevel)
+ int argnum1, int argnum2)
{
if (PG_ARGISNULL(argnum1) && PG_ARGISNULL(argnum2))
return true;
@@ -105,7 +105,7 @@ stats_check_arg_pair(FunctionCallInfo fcinfo,
int nullarg = PG_ARGISNULL(argnum1) ? argnum1 : argnum2;
int otherarg = PG_ARGISNULL(argnum1) ? argnum2 : argnum1;
- ereport(elevel,
+ ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("\"%s\" must be specified when \"%s\" is specified",
arginfo[nullarg].argname,
@@ -216,7 +216,7 @@ stats_lock_check_privileges(Oid reloid)
* found.
*/
static int
-get_arg_by_name(const char *argname, struct StatsArgInfo *arginfo, int elevel)
+get_arg_by_name(const char *argname, struct StatsArgInfo *arginfo)
{
int argnum;
@@ -224,7 +224,7 @@ get_arg_by_name(const char *argname, struct StatsArgInfo *arginfo, int elevel)
if (pg_strcasecmp(argname, arginfo[argnum].argname) == 0)
return argnum;
- ereport(elevel,
+ ereport(WARNING,
(errmsg("unrecognized argument name: \"%s\"", argname)));
return -1;
@@ -234,11 +234,11 @@ get_arg_by_name(const char *argname, struct StatsArgInfo *arginfo, int elevel)
* Ensure that a given argument matched the expected type.
*/
static bool
-stats_check_arg_type(const char *argname, Oid argtype, Oid expectedtype, int elevel)
+stats_check_arg_type(const char *argname, Oid argtype, Oid expectedtype)
{
if (argtype != expectedtype)
{
- ereport(elevel,
+ ereport(WARNING,
(errmsg("argument \"%s\" has type \"%s\", expected type \"%s\"",
argname, format_type_be(argtype),
format_type_be(expectedtype))));
@@ -260,8 +260,7 @@ stats_check_arg_type(const char *argname, Oid argtype, Oid expectedtype, int ele
bool
stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
FunctionCallInfo positional_fcinfo,
- struct StatsArgInfo *arginfo,
- int elevel)
+ struct StatsArgInfo *arginfo)
{
Datum *args;
bool *argnulls;
@@ -319,11 +318,10 @@ stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
if (pg_strcasecmp(argname, "version") == 0)
continue;
- argnum = get_arg_by_name(argname, arginfo, elevel);
+ argnum = get_arg_by_name(argname, arginfo);
if (argnum < 0 || !stats_check_arg_type(argname, types[i + 1],
- arginfo[argnum].argtype,
- elevel))
+ arginfo[argnum].argtype))
{
result = false;
continue;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index af9546de23d..9f0c676e22d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12429,13 +12429,6 @@
proargnames => '{kwargs}',
proargmodes => '{v}',
prosrc => 'pg_restore_attribute_stats' },
-{ oid => '9162',
- descr => 'set statistics on attribute',
- proname => 'pg_set_attribute_stats', provolatile => 'v', proisstrict => 'f',
- proparallel => 'u', prorettype => 'void',
- proargtypes => 'regclass name bool float4 int4 float4 text _float4 text float4 text _float4 _float4 text float4 text',
- proargnames => '{relation,attname,inherited,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds,correlation,most_common_elems,most_common_elem_freqs,elem_count_histogram,range_length_histogram,range_empty_frac,range_bounds_histogram}',
- prosrc => 'pg_set_attribute_stats' },
{ oid => '9163',
descr => 'clear statistics on attribute',
proname => 'pg_clear_attribute_stats', provolatile => 'v', proisstrict => 'f',
@@ -12443,13 +12436,6 @@
proargtypes => 'regclass name bool',
proargnames => '{relation,attname,inherited}',
prosrc => 'pg_clear_attribute_stats' },
-{ oid => '9944',
- descr => 'set statistics on relation',
- proname => 'pg_set_relation_stats', provolatile => 'v', proisstrict => 'f',
- proparallel => 'u', prorettype => 'void',
- proargtypes => 'regclass int4 float4 int4',
- proargnames => '{relation,relpages,reltuples,relallvisible}',
- prosrc => 'pg_set_relation_stats' },
{ oid => '9945',
descr => 'clear statistics on relation',
proname => 'pg_clear_relation_stats', provolatile => 'v', proisstrict => 'f',
diff --git a/src/include/statistics/stat_utils.h b/src/include/statistics/stat_utils.h
index 6edb5ea0321..0eb4decfcac 100644
--- a/src/include/statistics/stat_utils.h
+++ b/src/include/statistics/stat_utils.h
@@ -25,17 +25,15 @@ extern void stats_check_required_arg(FunctionCallInfo fcinfo,
struct StatsArgInfo *arginfo,
int argnum);
extern bool stats_check_arg_array(FunctionCallInfo fcinfo,
- struct StatsArgInfo *arginfo, int argnum,
- int elevel);
+ struct StatsArgInfo *arginfo, int argnum);
extern bool stats_check_arg_pair(FunctionCallInfo fcinfo,
struct StatsArgInfo *arginfo,
- int argnum1, int argnum2, int elevel);
+ int argnum1, int argnum2);
extern void stats_lock_check_privileges(Oid reloid);
extern bool stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
FunctionCallInfo positional_fcinfo,
- struct StatsArgInfo *arginfo,
- int elevel);
+ struct StatsArgInfo *arginfo);
#endif /* STATS_UTILS_H */
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index d6713eacc2c..7c7784efaf1 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -12,6 +12,7 @@ CREATE TABLE stats_import.test(
arange int4range,
tags text[]
) WITH (autovacuum_enabled = false);
+CREATE INDEX test_i ON stats_import.test(id);
-- starting stats
SELECT relpages, reltuples, relallvisible
FROM pg_class
@@ -21,80 +22,15 @@ WHERE oid = 'stats_import.test'::regclass;
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
--- relpages default
-SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test'::regclass,
- relpages => NULL::integer,
- reltuples => 400.0::real,
- relallvisible => 4::integer);
- pg_set_relation_stats
------------------------
-
-(1 row)
-
--- reltuples default
-SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test'::regclass,
- relpages => 17::integer,
- reltuples => NULL::real,
- relallvisible => 4::integer);
- pg_set_relation_stats
------------------------
-
-(1 row)
-
--- relallvisible default
-SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test'::regclass,
- relpages => 17::integer,
- reltuples => 400.0::real,
- relallvisible => NULL::integer);
- pg_set_relation_stats
------------------------
-
-(1 row)
-
--- 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)
-
-CREATE INDEX test_i ON stats_import.test(id);
BEGIN;
-- regular indexes have special case locking rules
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test_i'::regclass,
- relpages => 18::integer);
- pg_set_relation_stats
------------------------
-
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.test_i'::regclass,
+ 'relpages', 18::integer);
+ pg_restore_relation_stats
+---------------------------
+ t
(1 row)
SELECT mode FROM pg_locks
@@ -123,34 +59,6 @@ SELECT
t
(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)
-
-SELECT relpages, reltuples, relallvisible
-FROM pg_class
-WHERE oid = 'stats_import.test'::regclass;
- relpages | reltuples | relallvisible
-----------+-----------+---------------
- 18 | 401 | 5
-(1 row)
-
-- clear
SELECT
pg_catalog.pg_clear_relation_stats(
@@ -200,21 +108,21 @@ WHERE oid = 'stats_import.part_parent'::regclass;
-- although partitioned tables have no storage, setting relpages to a
-- positive value is still allowed
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent_i'::regclass,
- relpages => 2::integer);
- pg_set_relation_stats
------------------------
-
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent_i'::regclass,
+ 'relpages', 2::integer);
+ pg_restore_relation_stats
+---------------------------
+ t
(1 row)
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent'::regclass,
- relpages => 2::integer);
- pg_set_relation_stats
------------------------
-
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent'::regclass,
+ 'relpages', 2::integer);
+ pg_restore_relation_stats
+---------------------------
+ t
(1 row)
--
@@ -225,12 +133,12 @@ SELECT
--
BEGIN;
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent_i'::regclass,
- relpages => 2::integer);
- pg_set_relation_stats
------------------------
-
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent_i'::regclass,
+ 'relpages', 2::integer);
+ pg_restore_relation_stats
+---------------------------
+ t
(1 row)
SELECT mode FROM pg_locks
@@ -261,56 +169,14 @@ SELECT
-- nothing stops us from setting it to -1
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent'::regclass,
- relpages => -1::integer);
- pg_set_relation_stats
------------------------
-
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent'::regclass,
+ 'relpages', -1::integer);
+ pg_restore_relation_stats
+---------------------------
+ t
(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: could not open relation with OID 0
--- error: object doesn't exist
-SELECT pg_catalog.pg_clear_attribute_stats(
- relation => '0'::oid,
- attname => 'id'::name,
- inherited => false::boolean);
-ERROR: could not open relation with OID 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: attribute is system column
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_import.test'::regclass,
- attname => 'xmin'::name,
- inherited => false::boolean,
- null_frac => 0.1::real,
- avg_width => 2::integer,
- n_distinct => 0.3::real);
-ERROR: cannot modify statistics on system column "xmin"
--- error: attname doesn't exist
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_import.test'::regclass,
- attname => 'nope'::name,
- inherited => false::boolean,
- null_frac => 0.1::real,
- avg_width => 2::integer,
- n_distinct => 0.3::real);
-ERROR: column "nope" of relation "test" does not exist
-- error: attribute is system column
SELECT pg_catalog.pg_clear_attribute_stats(
relation => 'stats_import.test'::regclass,
@@ -323,432 +189,6 @@ SELECT pg_catalog.pg_clear_attribute_stats(
attname => 'nope'::name,
inherited => false::boolean);
ERROR: column "nope" of relation "test" does not exist
--- 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
--- 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)
-
--- error: 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" must be specified when "most_common_freqs" is specified
--- error: 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" must be specified when "most_common_vals" is specified
--- error: 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"
--- error: 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)
-
--- error: 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 | {2,1,3} | {0.3,0.25,0.05} | {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 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | 0.5 | | | | | |
-(1 row)
-
--- error: 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: unable to determine element type of attribute "id"
-DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
--- error: 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" must be specified when "most_common_elems" is specified
--- error: 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" must be specified when "most_common_elem_freqs" is specified
--- 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)
-
--- error: 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: unable to determine element type of attribute "id"
-DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
--- error: 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[]
- );
-ERROR: "elem_count_histogram" array cannot contain NULL values
--- 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 | | | | | {one,three} | {0.3,0.2,0.2,0.3,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 row)
-
--- error: 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: attribute "id" is not a range type
-DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
--- error: 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" must be specified when "range_length_histogram" is specified
--- error: 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" must be specified when "range_empty_frac" is specified
--- 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)
-
--- error: 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: attribute "id" is not a range type
-DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_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 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"}
-(1 row)
-
--- error: cannot set most_common_elems for range type
-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: unable to determine element type of attribute "arange"
-DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
---
--- Clear attribute stats to try again with restore functions
--- (relation stats were already cleared).
---
-SELECT
- pg_catalog.pg_clear_attribute_stats(
- 'stats_import.test'::regclass,
- s.attname,
- s.inherited)
-FROM pg_catalog.pg_stats AS s
-WHERE s.schemaname = 'stats_import'
-AND s.tablename = 'test'
-ORDER BY s.attname, s.inherited;
- pg_clear_attribute_stats
---------------------------
-
-
-
-(3 rows)
-
-- reject: argument name is NULL
SELECT pg_restore_relation_stats(
'relation', '0'::oid::regclass,
@@ -1472,216 +912,6 @@ 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)
-
---
-SELECT relpages, reltuples, relallvisible
-FROM pg_class
-WHERE oid = 'stats_import.test'::regclass;
- relpages | reltuples | relallvisible
-----------+-----------+---------------
- 1 | 4 | 0
-(1 row)
-
---
--- Clear clone stats to try again with pg_restore_attribute_stats
---
-SELECT
- pg_catalog.pg_clear_attribute_stats(
- ('stats_import.' || s.tablename)::regclass,
- s.attname,
- s.inherited)
-FROM pg_catalog.pg_stats AS s
-WHERE s.schemaname = 'stats_import'
-AND s.tablename IN ('test_clone', 'is_odd_clone')
-ORDER BY s.tablename, s.attname, s.inherited;
- pg_clear_attribute_stats
---------------------------
-
-
-
-
-
-
-(6 rows)
-
-SELECT
-SELECT COUNT(*)
-FROM pg_catalog.pg_stats AS s
-WHERE s.schemaname = 'stats_import'
-AND s.tablename IN ('test_clone', 'is_odd_clone');
-ERROR: syntax error at or near "SELECT"
-LINE 2: SELECT COUNT(*)
- ^
---
--- Copy stats from test to test_clone, and is_odd to is_odd_clone
---
SELECT s.schemaname, s.tablename, s.attname, s.inherited, r.*
FROM pg_catalog.pg_stats AS s
CROSS JOIN LATERAL
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 9740ab3ff02..f26f7857748 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -15,63 +15,19 @@ CREATE TABLE stats_import.test(
tags text[]
) WITH (autovacuum_enabled = false);
--- 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);
-
--- relpages default
-SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test'::regclass,
- relpages => NULL::integer,
- reltuples => 400.0::real,
- relallvisible => 4::integer);
-
--- reltuples default
-SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test'::regclass,
- relpages => 17::integer,
- reltuples => NULL::real,
- relallvisible => 4::integer);
-
--- relallvisible default
-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);
+CREATE INDEX test_i ON stats_import.test(id);
+-- starting stats
SELECT relpages, reltuples, relallvisible
FROM pg_class
WHERE oid = 'stats_import.test'::regclass;
-CREATE INDEX test_i ON stats_import.test(id);
-
BEGIN;
-- regular indexes have special case locking rules
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.test_i'::regclass,
- relpages => 18::integer);
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.test_i'::regclass,
+ 'relpages', 18::integer);
SELECT mode FROM pg_locks
WHERE relation = 'stats_import.test'::regclass AND
@@ -88,22 +44,6 @@ SELECT
'relation', 'stats_import.test_i'::regclass,
'relpages', 19::integer );
--- 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;
-
-SELECT relpages, reltuples, relallvisible
-FROM pg_class
-WHERE oid = 'stats_import.test'::regclass;
-
-- clear
SELECT
pg_catalog.pg_clear_relation_stats(
@@ -141,14 +81,14 @@ WHERE oid = 'stats_import.part_parent'::regclass;
-- although partitioned tables have no storage, setting relpages to a
-- positive value is still allowed
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent_i'::regclass,
- relpages => 2::integer);
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent_i'::regclass,
+ 'relpages', 2::integer);
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent'::regclass,
- relpages => 2::integer);
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent'::regclass,
+ 'relpages', 2::integer);
--
-- Partitioned indexes aren't analyzed but it is possible to set
@@ -159,9 +99,9 @@ SELECT
BEGIN;
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent_i'::regclass,
- relpages => 2::integer);
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent_i'::regclass,
+ 'relpages', 2::integer);
SELECT mode FROM pg_locks
WHERE relation = 'stats_import.part_parent'::regclass AND
@@ -180,51 +120,9 @@ SELECT
-- nothing stops us from setting it to -1
SELECT
- pg_catalog.pg_set_relation_stats(
- relation => 'stats_import.part_parent'::regclass,
- relpages => -1::integer);
-
--- 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: object doesn't exist
-SELECT pg_catalog.pg_clear_attribute_stats(
- relation => '0'::oid,
- attname => 'id'::name,
- inherited => false::boolean);
-
--- 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: attribute is system column
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_import.test'::regclass,
- attname => 'xmin'::name,
- inherited => false::boolean,
- null_frac => 0.1::real,
- avg_width => 2::integer,
- n_distinct => 0.3::real);
-
--- error: attname doesn't exist
-SELECT pg_catalog.pg_set_attribute_stats(
- relation => 'stats_import.test'::regclass,
- attname => 'nope'::name,
- inherited => false::boolean,
- null_frac => 0.1::real,
- avg_width => 2::integer,
- n_distinct => 0.3::real);
+ pg_catalog.pg_restore_relation_stats(
+ 'relation', 'stats_import.part_parent'::regclass,
+ 'relpages', -1::integer);
-- error: attribute is system column
SELECT pg_catalog.pg_clear_attribute_stats(
@@ -238,351 +136,6 @@ SELECT pg_catalog.pg_clear_attribute_stats(
attname => 'nope'::name,
inherited => false::boolean);
--- 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);
-
--- 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;
-
--- error: 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: 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: 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: 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';
-
--- error: 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';
-
--- error: 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: 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: 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';
-
--- error: 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: 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';
-
--- error: 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: 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 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';
-
--- error: 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';
-
--- error: cannot set most_common_elems for range type
-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
- );
-
---
--- Clear attribute stats to try again with restore functions
--- (relation stats were already cleared).
---
-SELECT
- pg_catalog.pg_clear_attribute_stats(
- 'stats_import.test'::regclass,
- s.attname,
- s.inherited)
-FROM pg_catalog.pg_stats AS s
-WHERE s.schemaname = 'stats_import'
-AND s.tablename = 'test'
-ORDER BY s.attname, s.inherited;
-
-- reject: argument name is NULL
SELECT pg_restore_relation_stats(
'relation', '0'::oid::regclass,
@@ -1105,173 +658,6 @@ 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;
-
---
-SELECT relpages, reltuples, relallvisible
-FROM pg_class
-WHERE oid = 'stats_import.test'::regclass;
-
---
--- Clear clone stats to try again with pg_restore_attribute_stats
---
-SELECT
- pg_catalog.pg_clear_attribute_stats(
- ('stats_import.' || s.tablename)::regclass,
- s.attname,
- s.inherited)
-FROM pg_catalog.pg_stats AS s
-WHERE s.schemaname = 'stats_import'
-AND s.tablename IN ('test_clone', 'is_odd_clone')
-ORDER BY s.tablename, s.attname, s.inherited;
-SELECT
-
-SELECT COUNT(*)
-FROM pg_catalog.pg_stats AS s
-WHERE s.schemaname = 'stats_import'
-AND s.tablename IN ('test_clone', 'is_odd_clone');
-
--
-- Copy stats from test to test_clone, and is_odd to is_odd_clone
--
--
2.34.1
