agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Michael Paquier <michael@paquier.xyz>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: imchifan@163.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
Date: Thu, 24 Sep 2026 13:22:10 +0900
Message-ID: <arSlchrpWvs8m8M2@paquier.xyz> (raw)
In-Reply-To: <CADkLM=exLKefGZjw+6HZ4TaVr0ra2+t44=64DpRs5eX4wdqfuw@mail.gmail.com>
References: <19715-b8be35083016f289@postgresql.org>
<CADkLM=exLKefGZjw+6HZ4TaVr0ra2+t44=64DpRs5eX4wdqfuw@mail.gmail.com>
On Wed, Sep 23, 2026 at 03:02:30PM -0400, Corey Huinker wrote:
> I'm looking into this.
I have begun looking at this before you had sent this reply, and we
are handling the base type of a domain in an incorrect way, assuming
that for attribute and extended stats we should just always check for
TYPTYPE_[MULTI]RANGE, but domains don't map with that at all. I think
that we are missing an extra getBaseType(), like
[multi]range_typanalyze(), where we use a [multi]range_get_typcache()
to cope with domains (getBaseTypeAndTypmod() does the job in the
typcache). That's also mentioned in the code.
And the same can be said for expressions in extended stats where a
domain that has a [multi]range type is involved. We would be better
getting rid of these hardcoded TYPTYPE values, IMO.
Spoiler: the tests are boring, still required. And fortunately, the
only damage is stats data not restored but skipped. Annoying, but not
as annoying as in the class of problems labelled like "I corrupt the
catalogs".
What do you think?
--
Michael
From 5c2245a2144f5218b4a104043052ae4f76be2bca Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Thu, 24 Sep 2026 13:04:53 +0900
Subject: [PATCH] Fix import of statistics for domains over [multi]range types
pg_restore_attribute_stats() rejected range_length_histogram,
range_empty_frac and range_bounds_histogram for a column whose type is a
domain over a range or a multirange type. ANALYZE is able to generate
such stats, incorporating the knowledge to handle domains in the in-core
typanalyze callbacks.
pg_restore_extended_stats() had the same problem for expressions whose
type is a domain over a range or a multirange type, assuming that it was
safe to rely on a hardcoded TYPTYPE_RANGE or TYPTYPE_MULTIRANGE.
This problem is resolved with the introduction of a new routine that
englobes the decision of the type to use, statatt_get_range_type(), able
to work for domains, range and multirange types. The base type is
resolved first.
Tests are included in a fashion consistent with the surroundings. The
consequence of this issue was the rejection of stats that ANALYZE is
able to build, not critical, still annoying.
Reported-by: Qifan Liu <imchifan@163.com>
Discussion: https://postgr.es/m/19715-b8be35083016f289@postgresql.org
Backpatch-through: 18
---
src/include/statistics/stat_utils.h | 1 +
src/backend/statistics/attribute_stats.c | 12 +-
src/backend/statistics/extended_stats_funcs.c | 12 +-
src/backend/statistics/stat_utils.c | 30 +++
src/test/regress/expected/stats_import.out | 224 +++++++++++++++++-
src/test/regress/sql/stats_import.sql | 163 +++++++++++++
6 files changed, 424 insertions(+), 18 deletions(-)
diff --git a/src/include/statistics/stat_utils.h b/src/include/statistics/stat_utils.h
index 15e962dbb7c8..8ade6b140aa9 100644
--- a/src/include/statistics/stat_utils.h
+++ b/src/include/statistics/stat_utils.h
@@ -57,6 +57,7 @@ extern Datum statatt_build_stavalues(const char *staname, FmgrInfo *array_in, Da
Oid typid, int32 typmod, bool *ok);
extern bool statatt_get_elem_type(Oid atttypid, char atttyptype,
Oid *elemtypid, Oid *elem_eq_opr);
+extern bool statatt_get_range_type(Oid atttypid, Oid *rangetypid);
extern bool statatt_check_bounds_histogram(Datum arrayval);
diff --git a/src/backend/statistics/attribute_stats.c b/src/backend/statistics/attribute_stats.c
index c35892ce6d0b..d5596d50f802 100644
--- a/src/backend/statistics/attribute_stats.c
+++ b/src/backend/statistics/attribute_stats.c
@@ -229,6 +229,8 @@ attribute_statistics_update_internal(Oid reloid,
Oid elemtypid = InvalidOid;
Oid elem_eq_opr = InvalidOid;
+ Oid bounds_typid = InvalidOid;
+
FmgrInfo array_in_fn;
bool do_mcv = !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) &&
@@ -334,7 +336,7 @@ attribute_statistics_update_internal(Oid reloid,
/* only range types can have range stats */
if ((do_range_length_histogram || do_bounds_histogram) &&
- !(atttyptype == TYPTYPE_RANGE || atttyptype == TYPTYPE_MULTIRANGE))
+ !statatt_get_range_type(atttypid, &bounds_typid))
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -498,14 +500,8 @@ attribute_statistics_update_internal(Oid reloid,
{
bool converted = false;
Datum stavalues;
- Oid bounds_typid = atttypid;
- /*
- * If it's a multirange, step down to the range type, as is done by
- * multirange_typanalyze().
- */
- if (type_is_multirange(atttypid))
- bounds_typid = get_multirange_range(atttypid);
+ Assert(OidIsValid(bounds_typid));
stavalues = statatt_build_stavalues("range_bounds_histogram",
&array_in_fn,
diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c
index cb965fdb8680..37013883d465 100644
--- a/src/backend/statistics/extended_stats_funcs.c
+++ b/src/backend/statistics/extended_stats_funcs.c
@@ -1123,6 +1123,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
Datum pgstdat = (Datum) 0;
Oid elemtypid = InvalidOid;
Oid elemeqopr = InvalidOid;
+ Oid rtypid = InvalidOid;
bool found[NUM_ATTRIBUTE_STATS_ELEMS] = {0};
JsonbValue val[NUM_ATTRIBUTE_STATS_ELEMS] = {0};
@@ -1259,8 +1260,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
found[RANGE_EMPTY_FRAC_ELEM] ||
found[RANGE_BOUNDS_HISTOGRAM_ELEM])
{
- if (typcache->typtype != TYPTYPE_RANGE &&
- typcache->typtype != TYPTYPE_MULTIRANGE)
+ if (!statatt_get_range_type(typid, &rtypid))
{
ereport(WARNING,
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1476,14 +1476,8 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
Datum stavalues;
bool val_ok = false;
char *s;
- Oid rtypid = typid;
- /*
- * If it's a multirange, step down to the range type, as is done by
- * multirange_typanalyze().
- */
- if (type_is_multirange(typid))
- rtypid = get_multirange_range(typid);
+ Assert(OidIsValid(rtypid));
s = jbv_string_get_cstr(&val[RANGE_BOUNDS_HISTOGRAM_ELEM]);
diff --git a/src/backend/statistics/stat_utils.c b/src/backend/statistics/stat_utils.c
index f4ff9ab9b20c..5e0e8b9b3256 100644
--- a/src/backend/statistics/stat_utils.c
+++ b/src/backend/statistics/stat_utils.c
@@ -551,6 +551,36 @@ statatt_get_elem_type(Oid atttypid, char atttyptype,
return true;
}
+/*
+ * Derive the range type to use from the attribute type, returning false if
+ * the attribute cannot have range statistics at all.
+ *
+ * The attribute type may be a domain, in which case its base type decides
+ * whether range statistics apply (see also range_typanalyze() and
+ * multirange_typanalyze()). For a multirange type, we step down to its
+ * range type, because compute_range_stats() stores range bounds even when
+ * analyzing a multirange column.
+ *
+ * The atttypid should be derived from a previous call to statatt_get_type().
+ */
+bool
+statatt_get_range_type(Oid atttypid, Oid *rangetypid)
+{
+ Oid basetypid = getBaseType(atttypid);
+
+ if (type_is_multirange(basetypid))
+ *rangetypid = get_multirange_range(basetypid);
+ else if (type_is_range(basetypid))
+ *rangetypid = basetypid;
+ else
+ {
+ *rangetypid = InvalidOid;
+ return false;
+ }
+
+ return true;
+}
+
/*
* Build an array with element type typid from a text datum, used as
* value of an attribute in a tuple to-be-inserted into pg_statistic.
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 4ce176c26678..e1de8d00d5b4 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -1495,6 +1495,158 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
+-- test for domains over range and multirange types
+CREATE DOMAIN stats_import.dom_int4 AS int4;
+CREATE DOMAIN stats_import.dom_range AS int4range;
+CREATE DOMAIN stats_import.dom_mrange AS int4multirange;
+CREATE TABLE stats_import.test_dom(
+ id stats_import.dom_int4,
+ drange stats_import.dom_range,
+ dmrange stats_import.dom_mrange
+) WITH (autovacuum_enabled = false);
+INSERT INTO stats_import.test_dom
+VALUES (1, '[1,3)', '{[1,3),[5,9),[20,30)}'),
+ (2, '[5,9)', '{[11,13),[15,19),[20,30)}'),
+ (3, '[11,15)', '{[21,23),[25,29),[120,130)}');
+-- warn: domain a scalar type cannot have range stats
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'id',
+ 'inherited', false,
+ 'null_frac', 0.25::real,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+WARNING: column "id" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+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_dom | id | f | 0.25 | 0 | 0 | | | | | | | | | |
+(1 row)
+
+-- ok: range stats for a domain over a range type
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'drange',
+ 'inherited', false,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'drange';
+ 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_dom | drange | f | 0 | 0 | 0 | | | | | | | | {2,4,4} | 0 | {"[1,3)","[5,9)","[11,15)"}
+(1 row)
+
+-- ok: range stats for a domain over a multirange type.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
+);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'dmrange';
+ 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_dom | dmrange | f | 0 | 0 | 0 | | | | | | | | {29,29,109} | 0 | {"[1,30)","[11,30)","[21,130)"}
+(1 row)
+
+-- warn: multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"{[1,30)}","{[11,30)}"}'::text
+);
+WARNING: malformed range literal: "{[1,30)}"
+DETAIL: Missing left parenthesis or bracket.
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+--
+-- Check that the range stats that ANALYZE generates for domains over range
+-- and multirange types can be restored exactly.
+--
+ANALYZE stats_import.test_dom;
+CREATE TABLE stats_import.test_dom_clone ( LIKE stats_import.test_dom )
+ WITH (autovacuum_enabled = false);
+SELECT s.attname, s.inherited, r.*
+FROM pg_catalog.pg_stats AS s
+CROSS JOIN LATERAL
+ pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_clone',
+ 'attname', s.attname::text,
+ '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,
+ '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 = 'test_dom'
+ORDER BY s.attname, s.inherited;
+ attname | inherited | r
+---------+-----------+---
+ dmrange | f | t
+ drange | f | t
+ id | f | t
+(3 rows)
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone')
+\gx
+(0 rows)
+
--
-- Test the ability to exactly copy data from one table to an identical table,
-- correctly reconstructing the stakind order as well as the staopN and
@@ -2737,6 +2889,71 @@ range_length_histogram | {10179,10189,10199}
range_empty_frac | 0
range_bounds_histogram | {"[1,10200)","[11,10200)","[21,10200)"}
+-- Check import of range stats for expressions whose type is a domain over a
+-- range or a multirange type.
+CREATE STATISTICS stats_import.test_dom_stat
+ ON id,
+ (range_merge(drange, drange)::stats_import.dom_range),
+ ((dmrange + '{}'::int4multirange)::stats_import.dom_mrange)
+ FROM stats_import.test_dom;
+-- warn: reject multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"{[1,30)}\",\"{[11,30)}\"}"}]'::jsonb);
+WARNING: malformed range literal: "{[1,30)}"
+DETAIL: Missing left parenthesis or bracket.
+HINT: Element "range_bounds_histogram" in expression -2 could not be parsed.
+ pg_restore_extended_stats
+---------------------------
+ f
+(1 row)
+
+-- ok: range stats for domains over range and multirange types
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb);
+ pg_restore_extended_stats
+---------------------------
+ t
+(1 row)
+
+SELECT e.expr, e.range_length_histogram, e.range_empty_frac,
+ e.range_bounds_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_stat' AND
+ e.inherited = false
+\gx
+-[ RECORD 1 ]----------+--------------------------------------------------------------------------------
+expr | (range_merge((drange)::int4range, (drange)::int4range))::stats_import.dom_range
+range_length_histogram | {2,4,4}
+range_empty_frac | 0
+range_bounds_histogram | {"[1,3)","[5,9)","[11,15)"}
+-[ RECORD 2 ]----------+--------------------------------------------------------------------------------
+expr | (((dmrange)::int4multirange + '{}'::int4multirange))::stats_import.dom_mrange
+range_length_histogram | {29,29,109}
+range_empty_frac | 0
+range_bounds_histogram | {"[1,30)","[11,30)","[21,130)"}
+
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
@@ -3828,7 +4045,7 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null
(1 row)
DROP SCHEMA stats_import CASCADE;
-NOTICE: drop cascades to 19 other objects
+NOTICE: drop cascades to 24 other objects
DETAIL: drop cascades to view stats_import.pg_stats_stable
drop cascades to view stats_import.pg_statistic_flat_t
drop cascades to function stats_import.pg_statistic_flat(text)
@@ -3845,6 +4062,11 @@ drop cascades to table stats_import.test_mr
drop cascades to table stats_import.part_parent
drop cascades to sequence stats_import.testseq
drop cascades to view stats_import.testview
+drop cascades to type stats_import.dom_int4
+drop cascades to type stats_import.dom_range
+drop cascades to type stats_import.dom_mrange
+drop cascades to table stats_import.test_dom
+drop cascades to table stats_import.test_dom_clone
drop cascades to table stats_import.test_clone
drop cascades to table stats_import.test_mr_clone
drop cascades to table stats_import.test_range_expr_null
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 748c9a2e0000..417bcb025339 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -1082,6 +1082,124 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
);
+-- test for domains over range and multirange types
+CREATE DOMAIN stats_import.dom_int4 AS int4;
+CREATE DOMAIN stats_import.dom_range AS int4range;
+CREATE DOMAIN stats_import.dom_mrange AS int4multirange;
+
+CREATE TABLE stats_import.test_dom(
+ id stats_import.dom_int4,
+ drange stats_import.dom_range,
+ dmrange stats_import.dom_mrange
+) WITH (autovacuum_enabled = false);
+
+INSERT INTO stats_import.test_dom
+VALUES (1, '[1,3)', '{[1,3),[5,9),[20,30)}'),
+ (2, '[5,9)', '{[11,13),[15,19),[20,30)}'),
+ (3, '[11,15)', '{[21,23),[25,29),[120,130)}');
+
+-- warn: domain a scalar type cannot have range stats
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'id',
+ 'inherited', false,
+ 'null_frac', 0.25::real,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'id';
+
+-- ok: range stats for a domain over a range type
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'drange',
+ 'inherited', false,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'drange';
+
+-- ok: range stats for a domain over a multirange type.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
+);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'dmrange';
+
+-- warn: multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"{[1,30)}","{[11,30)}"}'::text
+);
+
+--
+-- Check that the range stats that ANALYZE generates for domains over range
+-- and multirange types can be restored exactly.
+--
+ANALYZE stats_import.test_dom;
+
+CREATE TABLE stats_import.test_dom_clone ( LIKE stats_import.test_dom )
+ WITH (autovacuum_enabled = false);
+
+SELECT s.attname, s.inherited, r.*
+FROM pg_catalog.pg_stats AS s
+CROSS JOIN LATERAL
+ pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_clone',
+ 'attname', s.attname::text,
+ '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,
+ '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 = 'test_dom'
+ORDER BY s.attname, s.inherited;
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone')
+\gx
+
--
-- Test the ability to exactly copy data from one table to an identical table,
-- correctly reconstructing the stakind order as well as the staopN and
@@ -1934,6 +2052,51 @@ WHERE e.statistics_schemaname = 'stats_import' AND
e.inherited = false
\gx
+-- Check import of range stats for expressions whose type is a domain over a
+-- range or a multirange type.
+CREATE STATISTICS stats_import.test_dom_stat
+ ON id,
+ (range_merge(drange, drange)::stats_import.dom_range),
+ ((dmrange + '{}'::int4multirange)::stats_import.dom_mrange)
+ FROM stats_import.test_dom;
+
+-- warn: reject multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"{[1,30)}\",\"{[11,30)}\"}"}]'::jsonb);
+
+-- ok: range stats for domains over range and multirange types
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb);
+
+SELECT e.expr, e.range_length_histogram, e.range_empty_frac,
+ e.range_bounds_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_stat' AND
+ e.inherited = false
+\gx
+
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
--
2.55.0
Attachments:
[text/plain] 0001-Fix-import-of-statistics-for-domains-over-multi-rang.patch (24.1K, ../arSlchrpWvs8m8M2@paquier.xyz/2-0001-Fix-import-of-statistics-for-domains-over-multi-rang.patch)
download | inline diff:
From 5c2245a2144f5218b4a104043052ae4f76be2bca Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Thu, 24 Sep 2026 13:04:53 +0900
Subject: [PATCH] Fix import of statistics for domains over [multi]range types
pg_restore_attribute_stats() rejected range_length_histogram,
range_empty_frac and range_bounds_histogram for a column whose type is a
domain over a range or a multirange type. ANALYZE is able to generate
such stats, incorporating the knowledge to handle domains in the in-core
typanalyze callbacks.
pg_restore_extended_stats() had the same problem for expressions whose
type is a domain over a range or a multirange type, assuming that it was
safe to rely on a hardcoded TYPTYPE_RANGE or TYPTYPE_MULTIRANGE.
This problem is resolved with the introduction of a new routine that
englobes the decision of the type to use, statatt_get_range_type(), able
to work for domains, range and multirange types. The base type is
resolved first.
Tests are included in a fashion consistent with the surroundings. The
consequence of this issue was the rejection of stats that ANALYZE is
able to build, not critical, still annoying.
Reported-by: Qifan Liu <imchifan@163.com>
Discussion: https://postgr.es/m/19715-b8be35083016f289@postgresql.org
Backpatch-through: 18
---
src/include/statistics/stat_utils.h | 1 +
src/backend/statistics/attribute_stats.c | 12 +-
src/backend/statistics/extended_stats_funcs.c | 12 +-
src/backend/statistics/stat_utils.c | 30 +++
src/test/regress/expected/stats_import.out | 224 +++++++++++++++++-
src/test/regress/sql/stats_import.sql | 163 +++++++++++++
6 files changed, 424 insertions(+), 18 deletions(-)
diff --git a/src/include/statistics/stat_utils.h b/src/include/statistics/stat_utils.h
index 15e962dbb7c8..8ade6b140aa9 100644
--- a/src/include/statistics/stat_utils.h
+++ b/src/include/statistics/stat_utils.h
@@ -57,6 +57,7 @@ extern Datum statatt_build_stavalues(const char *staname, FmgrInfo *array_in, Da
Oid typid, int32 typmod, bool *ok);
extern bool statatt_get_elem_type(Oid atttypid, char atttyptype,
Oid *elemtypid, Oid *elem_eq_opr);
+extern bool statatt_get_range_type(Oid atttypid, Oid *rangetypid);
extern bool statatt_check_bounds_histogram(Datum arrayval);
diff --git a/src/backend/statistics/attribute_stats.c b/src/backend/statistics/attribute_stats.c
index c35892ce6d0b..d5596d50f802 100644
--- a/src/backend/statistics/attribute_stats.c
+++ b/src/backend/statistics/attribute_stats.c
@@ -229,6 +229,8 @@ attribute_statistics_update_internal(Oid reloid,
Oid elemtypid = InvalidOid;
Oid elem_eq_opr = InvalidOid;
+ Oid bounds_typid = InvalidOid;
+
FmgrInfo array_in_fn;
bool do_mcv = !PG_ARGISNULL(MOST_COMMON_FREQS_ARG) &&
@@ -334,7 +336,7 @@ attribute_statistics_update_internal(Oid reloid,
/* only range types can have range stats */
if ((do_range_length_histogram || do_bounds_histogram) &&
- !(atttyptype == TYPTYPE_RANGE || atttyptype == TYPTYPE_MULTIRANGE))
+ !statatt_get_range_type(atttypid, &bounds_typid))
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -498,14 +500,8 @@ attribute_statistics_update_internal(Oid reloid,
{
bool converted = false;
Datum stavalues;
- Oid bounds_typid = atttypid;
- /*
- * If it's a multirange, step down to the range type, as is done by
- * multirange_typanalyze().
- */
- if (type_is_multirange(atttypid))
- bounds_typid = get_multirange_range(atttypid);
+ Assert(OidIsValid(bounds_typid));
stavalues = statatt_build_stavalues("range_bounds_histogram",
&array_in_fn,
diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c
index cb965fdb8680..37013883d465 100644
--- a/src/backend/statistics/extended_stats_funcs.c
+++ b/src/backend/statistics/extended_stats_funcs.c
@@ -1123,6 +1123,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
Datum pgstdat = (Datum) 0;
Oid elemtypid = InvalidOid;
Oid elemeqopr = InvalidOid;
+ Oid rtypid = InvalidOid;
bool found[NUM_ATTRIBUTE_STATS_ELEMS] = {0};
JsonbValue val[NUM_ATTRIBUTE_STATS_ELEMS] = {0};
@@ -1259,8 +1260,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
found[RANGE_EMPTY_FRAC_ELEM] ||
found[RANGE_BOUNDS_HISTOGRAM_ELEM])
{
- if (typcache->typtype != TYPTYPE_RANGE &&
- typcache->typtype != TYPTYPE_MULTIRANGE)
+ if (!statatt_get_range_type(typid, &rtypid))
{
ereport(WARNING,
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1476,14 +1476,8 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
Datum stavalues;
bool val_ok = false;
char *s;
- Oid rtypid = typid;
- /*
- * If it's a multirange, step down to the range type, as is done by
- * multirange_typanalyze().
- */
- if (type_is_multirange(typid))
- rtypid = get_multirange_range(typid);
+ Assert(OidIsValid(rtypid));
s = jbv_string_get_cstr(&val[RANGE_BOUNDS_HISTOGRAM_ELEM]);
diff --git a/src/backend/statistics/stat_utils.c b/src/backend/statistics/stat_utils.c
index f4ff9ab9b20c..5e0e8b9b3256 100644
--- a/src/backend/statistics/stat_utils.c
+++ b/src/backend/statistics/stat_utils.c
@@ -551,6 +551,36 @@ statatt_get_elem_type(Oid atttypid, char atttyptype,
return true;
}
+/*
+ * Derive the range type to use from the attribute type, returning false if
+ * the attribute cannot have range statistics at all.
+ *
+ * The attribute type may be a domain, in which case its base type decides
+ * whether range statistics apply (see also range_typanalyze() and
+ * multirange_typanalyze()). For a multirange type, we step down to its
+ * range type, because compute_range_stats() stores range bounds even when
+ * analyzing a multirange column.
+ *
+ * The atttypid should be derived from a previous call to statatt_get_type().
+ */
+bool
+statatt_get_range_type(Oid atttypid, Oid *rangetypid)
+{
+ Oid basetypid = getBaseType(atttypid);
+
+ if (type_is_multirange(basetypid))
+ *rangetypid = get_multirange_range(basetypid);
+ else if (type_is_range(basetypid))
+ *rangetypid = basetypid;
+ else
+ {
+ *rangetypid = InvalidOid;
+ return false;
+ }
+
+ return true;
+}
+
/*
* Build an array with element type typid from a text datum, used as
* value of an attribute in a tuple to-be-inserted into pg_statistic.
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 4ce176c26678..e1de8d00d5b4 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -1495,6 +1495,158 @@ SELECT pg_catalog.pg_restore_attribute_stats(
t
(1 row)
+-- test for domains over range and multirange types
+CREATE DOMAIN stats_import.dom_int4 AS int4;
+CREATE DOMAIN stats_import.dom_range AS int4range;
+CREATE DOMAIN stats_import.dom_mrange AS int4multirange;
+CREATE TABLE stats_import.test_dom(
+ id stats_import.dom_int4,
+ drange stats_import.dom_range,
+ dmrange stats_import.dom_mrange
+) WITH (autovacuum_enabled = false);
+INSERT INTO stats_import.test_dom
+VALUES (1, '[1,3)', '{[1,3),[5,9),[20,30)}'),
+ (2, '[5,9)', '{[11,13),[15,19),[20,30)}'),
+ (3, '[11,15)', '{[21,23),[25,29),[120,130)}');
+-- warn: domain a scalar type cannot have range stats
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'id',
+ 'inherited', false,
+ 'null_frac', 0.25::real,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+WARNING: column "id" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+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_dom | id | f | 0.25 | 0 | 0 | | | | | | | | | |
+(1 row)
+
+-- ok: range stats for a domain over a range type
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'drange',
+ 'inherited', false,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'drange';
+ 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_dom | drange | f | 0 | 0 | 0 | | | | | | | | {2,4,4} | 0 | {"[1,3)","[5,9)","[11,15)"}
+(1 row)
+
+-- ok: range stats for a domain over a multirange type.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
+);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'dmrange';
+ 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_dom | dmrange | f | 0 | 0 | 0 | | | | | | | | {29,29,109} | 0 | {"[1,30)","[11,30)","[21,130)"}
+(1 row)
+
+-- warn: multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"{[1,30)}","{[11,30)}"}'::text
+);
+WARNING: malformed range literal: "{[1,30)}"
+DETAIL: Missing left parenthesis or bracket.
+ pg_restore_attribute_stats
+----------------------------
+ f
+(1 row)
+
+--
+-- Check that the range stats that ANALYZE generates for domains over range
+-- and multirange types can be restored exactly.
+--
+ANALYZE stats_import.test_dom;
+CREATE TABLE stats_import.test_dom_clone ( LIKE stats_import.test_dom )
+ WITH (autovacuum_enabled = false);
+SELECT s.attname, s.inherited, r.*
+FROM pg_catalog.pg_stats AS s
+CROSS JOIN LATERAL
+ pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_clone',
+ 'attname', s.attname::text,
+ '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,
+ '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 = 'test_dom'
+ORDER BY s.attname, s.inherited;
+ attname | inherited | r
+---------+-----------+---
+ dmrange | f | t
+ drange | f | t
+ id | f | t
+(3 rows)
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone')
+\gx
+(0 rows)
+
--
-- Test the ability to exactly copy data from one table to an identical table,
-- correctly reconstructing the stakind order as well as the staopN and
@@ -2737,6 +2889,71 @@ range_length_histogram | {10179,10189,10199}
range_empty_frac | 0
range_bounds_histogram | {"[1,10200)","[11,10200)","[21,10200)"}
+-- Check import of range stats for expressions whose type is a domain over a
+-- range or a multirange type.
+CREATE STATISTICS stats_import.test_dom_stat
+ ON id,
+ (range_merge(drange, drange)::stats_import.dom_range),
+ ((dmrange + '{}'::int4multirange)::stats_import.dom_mrange)
+ FROM stats_import.test_dom;
+-- warn: reject multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"{[1,30)}\",\"{[11,30)}\"}"}]'::jsonb);
+WARNING: malformed range literal: "{[1,30)}"
+DETAIL: Missing left parenthesis or bracket.
+HINT: Element "range_bounds_histogram" in expression -2 could not be parsed.
+ pg_restore_extended_stats
+---------------------------
+ f
+(1 row)
+
+-- ok: range stats for domains over range and multirange types
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb);
+ pg_restore_extended_stats
+---------------------------
+ t
+(1 row)
+
+SELECT e.expr, e.range_length_histogram, e.range_empty_frac,
+ e.range_bounds_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_stat' AND
+ e.inherited = false
+\gx
+-[ RECORD 1 ]----------+--------------------------------------------------------------------------------
+expr | (range_merge((drange)::int4range, (drange)::int4range))::stats_import.dom_range
+range_length_histogram | {2,4,4}
+range_empty_frac | 0
+range_bounds_histogram | {"[1,3)","[5,9)","[11,15)"}
+-[ RECORD 2 ]----------+--------------------------------------------------------------------------------
+expr | (((dmrange)::int4multirange + '{}'::int4multirange))::stats_import.dom_mrange
+range_length_histogram | {29,29,109}
+range_empty_frac | 0
+range_bounds_histogram | {"[1,30)","[11,30)","[21,130)"}
+
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
@@ -3828,7 +4045,7 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null
(1 row)
DROP SCHEMA stats_import CASCADE;
-NOTICE: drop cascades to 19 other objects
+NOTICE: drop cascades to 24 other objects
DETAIL: drop cascades to view stats_import.pg_stats_stable
drop cascades to view stats_import.pg_statistic_flat_t
drop cascades to function stats_import.pg_statistic_flat(text)
@@ -3845,6 +4062,11 @@ drop cascades to table stats_import.test_mr
drop cascades to table stats_import.part_parent
drop cascades to sequence stats_import.testseq
drop cascades to view stats_import.testview
+drop cascades to type stats_import.dom_int4
+drop cascades to type stats_import.dom_range
+drop cascades to type stats_import.dom_mrange
+drop cascades to table stats_import.test_dom
+drop cascades to table stats_import.test_dom_clone
drop cascades to table stats_import.test_clone
drop cascades to table stats_import.test_mr_clone
drop cascades to table stats_import.test_range_expr_null
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 748c9a2e0000..417bcb025339 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -1082,6 +1082,124 @@ SELECT pg_catalog.pg_restore_attribute_stats(
'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
);
+-- test for domains over range and multirange types
+CREATE DOMAIN stats_import.dom_int4 AS int4;
+CREATE DOMAIN stats_import.dom_range AS int4range;
+CREATE DOMAIN stats_import.dom_mrange AS int4multirange;
+
+CREATE TABLE stats_import.test_dom(
+ id stats_import.dom_int4,
+ drange stats_import.dom_range,
+ dmrange stats_import.dom_mrange
+) WITH (autovacuum_enabled = false);
+
+INSERT INTO stats_import.test_dom
+VALUES (1, '[1,3)', '{[1,3),[5,9),[20,30)}'),
+ (2, '[5,9)', '{[11,13),[15,19),[20,30)}'),
+ (3, '[11,15)', '{[21,23),[25,29),[120,130)}');
+
+-- warn: domain a scalar type cannot have range stats
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'id',
+ 'inherited', false,
+ 'null_frac', 0.25::real,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'id';
+
+-- ok: range stats for a domain over a range type
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'drange',
+ 'inherited', false,
+ 'range_length_histogram', '{2,4,4}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
+);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'drange';
+
+-- ok: range stats for a domain over a multirange type.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
+);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom'
+AND inherited = false
+AND attname = 'dmrange';
+
+-- warn: multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'attname', 'dmrange',
+ 'inherited', false,
+ 'range_length_histogram', '{29,29,109}'::text,
+ 'range_empty_frac', '0'::real,
+ 'range_bounds_histogram', '{"{[1,30)}","{[11,30)}"}'::text
+);
+
+--
+-- Check that the range stats that ANALYZE generates for domains over range
+-- and multirange types can be restored exactly.
+--
+ANALYZE stats_import.test_dom;
+
+CREATE TABLE stats_import.test_dom_clone ( LIKE stats_import.test_dom )
+ WITH (autovacuum_enabled = false);
+
+SELECT s.attname, s.inherited, r.*
+FROM pg_catalog.pg_stats AS s
+CROSS JOIN LATERAL
+ pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_clone',
+ 'attname', s.attname::text,
+ '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,
+ '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 = 'test_dom'
+ORDER BY s.attname, s.inherited;
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone')
+\gx
+
--
-- Test the ability to exactly copy data from one table to an identical table,
-- correctly reconstructing the stakind order as well as the staopN and
@@ -1934,6 +2052,51 @@ WHERE e.statistics_schemaname = 'stats_import' AND
e.inherited = false
\gx
+-- Check import of range stats for expressions whose type is a domain over a
+-- range or a multirange type.
+CREATE STATISTICS stats_import.test_dom_stat
+ ON id,
+ (range_merge(drange, drange)::stats_import.dom_range),
+ ((dmrange + '{}'::int4multirange)::stats_import.dom_mrange)
+ FROM stats_import.test_dom;
+
+-- warn: reject multirange values in the bounds histogram of a domain. These
+-- must be ranges.
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"{[1,30)}\",\"{[11,30)}\"}"}]'::jsonb);
+
+-- ok: range stats for domains over range and multirange types
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_stat',
+ 'inherited', false,
+ 'exprs', '[{"range_length_histogram": "{2,4,4}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,3)\",\"[5,9)\",\"[11,15)\"}"},
+ {"range_length_histogram": "{29,29,109}",
+ "range_empty_frac": "0",
+ "range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb);
+
+SELECT e.expr, e.range_length_histogram, e.range_empty_frac,
+ e.range_bounds_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_stat' AND
+ e.inherited = false
+\gx
+
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
--
2.55.0
[application/pgp-signature] signature.asc (832B, ../arSlchrpWvs8m8M2@paquier.xyz/3-signature.asc)
download
view thread (14+ messages) latest in thread
Message-ID: <arSlchrpWvs8m8M2@paquier.xyz>
Permalink: ../arSlchrpWvs8m8M2@paquier.xyz/
Also on: postgresql.org/message-id/arSlchrpWvs8m8M2@paquier.xyz
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: michael@paquier.xyz, corey.huinker@gmail.com, imchifan@163.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
In-Reply-To: <arSlchrpWvs8m8M2@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox