agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
14+ messages / 5 participants
[nested] [flat]
* BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
@ 2026-09-22 16:10 PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: PG Bug reporting form @ 2026-09-22 16:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
The following bug has been logged on the website:
Bug reference: 19715
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux/amd64
Description:
pg_restore_attribute_stats() rejects range length and bounds histograms for
a column whose type is a domain over int4multirange. It returns false and
warns that the column is not a range type. However, ANALYZE generates both
range-specific statistic kinds 6 and 7 for a column of the same domain type.
As a result, statistics exported for a domain over a multirange type cannot
be faithfully restored to an equivalent column.
Steps to reproduce
------------------
Run the following input with psql -X:
\set ON_ERROR_STOP on
CREATE DOMAIN restore_stats_mr AS int4multirange;
CREATE TABLE restore_stats_src (v restore_stats_mr);
CREATE TABLE restore_stats_dst (v restore_stats_mr);
INSERT INTO restore_stats_src VALUES
('{[1,3)}'), ('{[5,9)}'), ('{[11,15)}');
ANALYZE restore_stats_src;
SELECT array_agg(k ORDER BY k) AS analyze_range_kinds
FROM (
SELECT unnest(ARRAY[stakind1, stakind2, stakind3, stakind4, stakind5]) AS
k
FROM pg_statistic
WHERE starelid = 'restore_stats_src'::regclass
AND staattnum = 1
) s
WHERE k IN (6, 7);
SELECT pg_catalog.pg_restore_attribute_stats(
'schemaname', 'public',
'relname', 'restore_stats_dst',
'attname', 'v',
'inherited', false,
'range_length_histogram', '{2,4,4}'::text,
'range_empty_frac', 0::real,
'range_bounds_histogram', ARRAY['[1,3)', '[5,9)', '[11,15)']::text
) AS restore_ok;
SELECT count(*) = 2 AS restored_both_range_kinds
FROM (
SELECT unnest(ARRAY[stakind1, stakind2, stakind3, stakind4, stakind5]) AS
k
FROM pg_statistic
WHERE starelid = 'restore_stats_dst'::regclass
AND staattnum = 1
) s
WHERE k IN (6, 7);
Actual result
-------------
analyze_range_kinds
---------------------
{6,7}
WARNING: column "v" is not a range type
DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or
STATISTIC_KIND_BOUNDS_HISTOGRAM.
restore_ok
------------
f
restored_both_range_kinds
---------------------------
f
Expected result
---------------
pg_restore_attribute_stats() should return true and restore statistic kinds
6 and 7. ANALYZE produces those range statistics for the same domain type,
so the restoration path should not reject them as belonging to a non-range
column.
Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel and PostgreSQL 18.6.
PostgreSQL 17.11 does not provide pg_restore_attribute_stats().
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-23 19:02 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 14+ messages in thread
From: Corey Huinker @ 2026-09-23 19:02 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
I'm looking into this.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
@ 2026-09-24 04:22 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2026-09-24 04:22 UTC (permalink / raw)
To: Corey Huinker <corey.huinker@gmail.com>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
@ 2026-09-24 05:56 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:13 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:16 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
0 siblings, 3 replies; 14+ messages in thread
From: Corey Huinker @ 2026-09-24 05:56 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 24, 2026 at 12:22 AM Michael Paquier <michael@paquier.xyz>
wrote:
> 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
>
Here's what I was just about to post to the list, only to see that you
already posted something.
Will begin reviewing yours immediately.
Attachments:
[text/x-patch] v1-0001-Fix-import-of-range-statistics-for-domains.patch (16.8K, ../../CADkLM=ci+m6KW1r1RutSOV-OOvv9FTNrOU0+7wNq8R2+p-91Nw@mail.gmail.com/3-v1-0001-Fix-import-of-range-statistics-for-domains.patch)
download | inline diff:
From d0836e27fb4e3163eccee51e2580fac1fac071b9 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Thu, 24 Sep 2026 01:53:04 -0400
Subject: [PATCH v1] Fix import of range statistics for domains.
WIP
---
src/backend/statistics/attribute_stats.c | 59 +++++---
src/backend/statistics/extended_stats_funcs.c | 56 +++++---
src/test/regress/expected/stats_import.out | 130 +++++++++++++++++-
src/test/regress/sql/stats_import.sql | 109 +++++++++++++++
4 files changed, 314 insertions(+), 40 deletions(-)
diff --git a/src/backend/statistics/attribute_stats.c b/src/backend/statistics/attribute_stats.c
index c35892ce6d0..2539e097ac3 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) &&
@@ -333,18 +335,47 @@ 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))
+ if (do_range_length_histogram || do_bounds_histogram)
{
- ereport(WARNING,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("column \"%s\" is not a range type", attname),
- errdetail("Cannot set %s or %s.",
- "STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM", "STATISTIC_KIND_BOUNDS_HISTOGRAM")));
+ char bounds_typtype = atttyptype;
- do_bounds_histogram = false;
- do_range_length_histogram = false;
- result = false;
+ bounds_typid = atttypid;
+
+ /*
+ * If attribute type is a domain, step down to the base type, which
+ * should be the expected range or multirange type.
+ */
+ if (bounds_typtype == TYPTYPE_DOMAIN)
+ {
+ bounds_typid = getBaseType(bounds_typid);
+ bounds_typtype = get_typtype(bounds_typid);
+ }
+
+ switch (bounds_typtype)
+ {
+ case TYPTYPE_RANGE:
+ /* Yes, it can have range stats */
+ break;
+ case TYPTYPE_MULTIRANGE:
+
+ /*
+ * Yes, but we need to step down to the range type, as is done
+ * by multirange_typanalyze().
+ */
+ bounds_typid = get_multirange_range(bounds_typid);
+ break;
+ default:
+ ereport(WARNING,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("column \"%s\" is not a range type", attname),
+ errdetail("Cannot set %s or %s.",
+ "STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM",
+ "STATISTIC_KIND_BOUNDS_HISTOGRAM"));
+
+ do_bounds_histogram = false;
+ do_range_length_histogram = false;
+ result = false;
+ }
}
fmgr_info(F_ARRAY_IN, &array_in_fn);
@@ -498,14 +529,6 @@ 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);
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 cb965fdb868..b62b10a0dea 100644
--- a/src/backend/statistics/extended_stats_funcs.c
+++ b/src/backend/statistics/extended_stats_funcs.c
@@ -1125,6 +1125,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
Oid elemeqopr = InvalidOid;
bool found[NUM_ATTRIBUTE_STATS_ELEMS] = {0};
JsonbValue val[NUM_ATTRIBUTE_STATS_ELEMS] = {0};
+ Oid bounds_typid = InvalidOid;
/* Assume the worst by default. */
*pg_statistic_ok = false;
@@ -1253,24 +1254,45 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
/*
* These three fields can only be set if dealing with a range or
- * multi-range type.
+ * multi-range type, or a domain of either.
*/
if (found[RANGE_LENGTH_HISTOGRAM_ELEM] ||
found[RANGE_EMPTY_FRAC_ELEM] ||
found[RANGE_BOUNDS_HISTOGRAM_ELEM])
{
- if (typcache->typtype != TYPTYPE_RANGE &&
- typcache->typtype != TYPTYPE_MULTIRANGE)
+ char bounds_typtype = typcache->typtype;
+
+ bounds_typid = typid;
+
+ if (bounds_typtype == TYPTYPE_DOMAIN)
{
- ereport(WARNING,
- errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("could not parse \"%s\": invalid data in expression %d",
- argname, exprnum),
- errhint("\"%s\", \"%s\", and \"%s\" can only be set for a range type.",
- extexprargname[RANGE_LENGTH_HISTOGRAM_ELEM],
- extexprargname[RANGE_EMPTY_FRAC_ELEM],
- extexprargname[RANGE_BOUNDS_HISTOGRAM_ELEM]));
- goto pg_statistic_error;
+ bounds_typid = getBaseType(typid);
+ bounds_typtype = get_typtype(bounds_typid);
+ }
+
+ switch (bounds_typtype)
+ {
+ case TYPTYPE_RANGE:
+ bounds_typid = typid;
+ break;
+ case TYPTYPE_MULTIRANGE:
+
+ /*
+ * If it's a multirange, step down to the range type, as is
+ * done by multirange_typanalyze().
+ */
+ bounds_typid = get_multirange_range(bounds_typid);
+ break;
+ default:
+ ereport(WARNING,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not parse \"%s\": invalid data in expression %d",
+ argname, exprnum),
+ errhint("\"%s\", \"%s\", and \"%s\" can only be set for a range type.",
+ extexprargname[RANGE_LENGTH_HISTOGRAM_ELEM],
+ extexprargname[RANGE_EMPTY_FRAC_ELEM],
+ extexprargname[RANGE_BOUNDS_HISTOGRAM_ELEM]));
+ goto pg_statistic_error;
}
}
@@ -1476,18 +1498,10 @@ 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);
s = jbv_string_get_cstr(&val[RANGE_BOUNDS_HISTOGRAM_ELEM]);
- stavalues = array_in_safe(array_in_fn, s, rtypid, typmod, exprnum,
+ stavalues = array_in_safe(array_in_fn, s, bounds_typid, typmod, exprnum,
extexprargname[RANGE_BOUNDS_HISTOGRAM_ELEM],
&val_ok);
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 4ce176c2667..f615ec94ed1 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -3827,8 +3827,133 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null
19
(1 row)
+-- BUG #19715
+CREATE DOMAIN stats_import.restore_stats_mr AS int4multirange;
+CREATE TABLE stats_import.mr_domain (u integer, v stats_import.restore_stats_mr);
+CREATE TABLE stats_import.mr_domain_clone (u integer, v stats_import.restore_stats_mr);
+-- Create statistics that force an expression
+CREATE STATISTICS stats_import.mr_domain_stat
+ON u, (stats_import.restore_stats_mr(v + '{[-10,-5)}'::stats_import.restore_stats_mr))
+FROM stats_import.mr_domain;
+CREATE STATISTICS stats_import.mr_domain_stat_clone
+ON u, (stats_import.restore_stats_mr(v + '{[-10,-5)}'::stats_import.restore_stats_mr))
+FROM stats_import.mr_domain_clone;
+INSERT INTO stats_import.mr_domain VALUES
+ (1, '{[1,3)}'), (2, '{[5,9)}'), (3, '{[11,15)}');
+ANALYZE stats_import.mr_domain;
+-- Confirm both table and expression index have rows
+SELECT COUNT(*)
+FROM pg_stats
+WHERE schemaname = 'stats_import'
+AND tablename = 'mr_domain'
+AND inherited = false;
+ count
+-------
+ 2
+(1 row)
+
+SELECT COUNT(*)
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import'
+AND e.statistics_name = 'mr_domain_stat';
+ count
+-------
+ 1
+(1 row)
+
+--
+-- Copy stats from test to mr_domain to mr_domain_clone
+--
+SELECT s.schemaname, s.tablename, 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', 'mr_domain_clone',
+ 'attname', s.attname::text,
+ 'inherited', s.inherited,
+ 'version', 150000,
+ '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 ('mr_domain')
+ORDER BY s.tablename, s.attname, s.inherited;
+ schemaname | tablename | attname | inherited | r
+--------------+-----------+---------+-----------+---
+ stats_import | mr_domain | u | f | t
+ stats_import | mr_domain | v | f | t
+(2 rows)
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('mr_domain', 'mr_domain_clone')
+\gx
+(0 rows)
+
+-- Copy stats from mr_domain_stat to mr_domain_stat_clone
+SELECT e.statistics_name,
+ pg_catalog.pg_restore_extended_stats(
+ 'schemaname', e.statistics_schemaname::text,
+ 'relname', 'mr_domain_clone',
+ 'statistics_schemaname', e.statistics_schemaname::text,
+ 'statistics_name', 'mr_domain_stat_clone',
+ 'inherited', e.inherited,
+ 'n_distinct', e.n_distinct,
+ 'dependencies', e.dependencies,
+ 'most_common_vals', e.most_common_vals,
+ 'most_common_freqs', e.most_common_freqs,
+ 'most_common_base_freqs', e.most_common_base_freqs,
+ 'exprs', x.exprs)
+FROM pg_stats_ext AS e
+CROSS JOIN LATERAL (
+ SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object(
+ 'null_frac', ee.null_frac::text,
+ 'avg_width', ee.avg_width::text,
+ 'n_distinct', ee.n_distinct::text,
+ 'most_common_vals', ee.most_common_vals::text,
+ 'most_common_freqs', ee.most_common_freqs::text,
+ 'histogram_bounds', ee.histogram_bounds::text,
+ 'correlation', ee.correlation::text,
+ 'most_common_elems', ee.most_common_elems::text,
+ 'most_common_elem_freqs', ee.most_common_elem_freqs::text,
+ 'elem_count_histogram', ee.elem_count_histogram::text,
+ 'range_length_histogram', ee.range_length_histogram::text,
+ 'range_empty_frac', ee.range_empty_frac::text,
+ 'range_bounds_histogram', ee.range_bounds_histogram::text)))
+ FROM pg_stats_ext_exprs AS ee
+ WHERE ee.statistics_schemaname = e.statistics_schemaname AND
+ ee.statistics_name = e.statistics_name AND
+ ee.inherited = e.inherited
+ ) AS x(exprs)
+WHERE e.statistics_schemaname = 'stats_import'
+AND e.statistics_name = 'mr_domain_stat';
+ statistics_name | pg_restore_extended_stats
+-----------------+---------------------------
+ mr_domain_stat | t
+(1 row)
+
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_get_difference('mr_domain_stat', 'mr_domain_stat_clone')
+\gx
+(0 rows)
+
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_exprs_get_difference('mr_domain_stat', 'mr_domain_stat_clone')
+\gx
+(0 rows)
+
DROP SCHEMA stats_import CASCADE;
-NOTICE: drop cascades to 19 other objects
+NOTICE: drop cascades to 22 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)
@@ -3848,3 +3973,6 @@ drop cascades to view stats_import.testview
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
+drop cascades to type stats_import.restore_stats_mr
+drop cascades to table stats_import.mr_domain
+drop cascades to table stats_import.mr_domain_clone
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 748c9a2e000..74012ff5678 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -2647,4 +2647,113 @@ SELECT * FROM stats_import.test_range_expr_null
SELECT COUNT(*) FROM stats_import.test_range_expr_null
WHERE (rng * int4range(50, 150)) && '[60,70)'::int4range;
+-- BUG #19715
+CREATE DOMAIN stats_import.restore_stats_mr AS int4multirange;
+CREATE TABLE stats_import.mr_domain (u integer, v stats_import.restore_stats_mr);
+CREATE TABLE stats_import.mr_domain_clone (u integer, v stats_import.restore_stats_mr);
+
+-- Create statistics that force an expression
+CREATE STATISTICS stats_import.mr_domain_stat
+ON u, (stats_import.restore_stats_mr(v + '{[-10,-5)}'::stats_import.restore_stats_mr))
+FROM stats_import.mr_domain;
+CREATE STATISTICS stats_import.mr_domain_stat_clone
+ON u, (stats_import.restore_stats_mr(v + '{[-10,-5)}'::stats_import.restore_stats_mr))
+FROM stats_import.mr_domain_clone;
+
+INSERT INTO stats_import.mr_domain VALUES
+ (1, '{[1,3)}'), (2, '{[5,9)}'), (3, '{[11,15)}');
+ANALYZE stats_import.mr_domain;
+
+-- Confirm both table and expression index have rows
+SELECT COUNT(*)
+FROM pg_stats
+WHERE schemaname = 'stats_import'
+AND tablename = 'mr_domain'
+AND inherited = false;
+
+SELECT COUNT(*)
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import'
+AND e.statistics_name = 'mr_domain_stat';
+
+
+--
+-- Copy stats from test to mr_domain to mr_domain_clone
+--
+SELECT s.schemaname, s.tablename, 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', 'mr_domain_clone',
+ 'attname', s.attname::text,
+ 'inherited', s.inherited,
+ 'version', 150000,
+ '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 ('mr_domain')
+ORDER BY s.tablename, s.attname, s.inherited;
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('mr_domain', 'mr_domain_clone')
+\gx
+
+-- Copy stats from mr_domain_stat to mr_domain_stat_clone
+SELECT e.statistics_name,
+ pg_catalog.pg_restore_extended_stats(
+ 'schemaname', e.statistics_schemaname::text,
+ 'relname', 'mr_domain_clone',
+ 'statistics_schemaname', e.statistics_schemaname::text,
+ 'statistics_name', 'mr_domain_stat_clone',
+ 'inherited', e.inherited,
+ 'n_distinct', e.n_distinct,
+ 'dependencies', e.dependencies,
+ 'most_common_vals', e.most_common_vals,
+ 'most_common_freqs', e.most_common_freqs,
+ 'most_common_base_freqs', e.most_common_base_freqs,
+ 'exprs', x.exprs)
+FROM pg_stats_ext AS e
+CROSS JOIN LATERAL (
+ SELECT jsonb_agg(jsonb_strip_nulls(jsonb_build_object(
+ 'null_frac', ee.null_frac::text,
+ 'avg_width', ee.avg_width::text,
+ 'n_distinct', ee.n_distinct::text,
+ 'most_common_vals', ee.most_common_vals::text,
+ 'most_common_freqs', ee.most_common_freqs::text,
+ 'histogram_bounds', ee.histogram_bounds::text,
+ 'correlation', ee.correlation::text,
+ 'most_common_elems', ee.most_common_elems::text,
+ 'most_common_elem_freqs', ee.most_common_elem_freqs::text,
+ 'elem_count_histogram', ee.elem_count_histogram::text,
+ 'range_length_histogram', ee.range_length_histogram::text,
+ 'range_empty_frac', ee.range_empty_frac::text,
+ 'range_bounds_histogram', ee.range_bounds_histogram::text)))
+ FROM pg_stats_ext_exprs AS ee
+ WHERE ee.statistics_schemaname = e.statistics_schemaname AND
+ ee.statistics_name = e.statistics_name AND
+ ee.inherited = e.inherited
+ ) AS x(exprs)
+WHERE e.statistics_schemaname = 'stats_import'
+AND e.statistics_name = 'mr_domain_stat';
+
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_get_difference('mr_domain_stat', 'mr_domain_stat_clone')
+\gx
+
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_exprs_get_difference('mr_domain_stat', 'mr_domain_stat_clone')
+\gx
+
DROP SCHEMA stats_import CASCADE;
base-commit: 4545cee303c257e58195e3d033c05bf38e2cd4d6
--
2.55.0
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
@ 2026-09-24 06:13 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:34 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Corey Huinker @ 2026-09-24 06:13 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 24, 2026 at 1:56 AM Corey Huinker <corey.huinker@gmail.com>
wrote:
>
>
> On Thu, Sep 24, 2026 at 12:22 AM Michael Paquier <michael@paquier.xyz>
> wrote:
>
>> 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
>>
>
> Here's what I was just about to post to the list, only to see that you
> already posted something.
>
> Will begin reviewing yours immediately.
>
So it seems we came to the very similar conclusions.
I had a plan to add something like statatt_get_range_type() as a follow-up
patch, but wanted to get the fix working first.
As for test cases, mine are based on the existing "are foo_clone stats a
faithful copy of foo stats" tests, rather than hardcoding stats. My way
feels more future proof, but the future in which the hardcoded way breaks
is a long way off.
Similarly, after adding those foo->foo_clone tests, it seemed like the
regression test could stand to have helper functions for restore stats from
object a into object a_clone, but that too was for a follow-up patch as I
didn't want to distract from the fix itself.
The additional test cases for trying to cram range stats into non-range
attributes appear correct and necessary.
Overall, it's a +1 from me.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:13 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
@ 2026-09-24 06:34 ` Corey Huinker <corey.huinker@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Corey Huinker @ 2026-09-24 06:34 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
>
> As for test cases, mine are based on the existing "are foo_clone stats a
> faithful copy of foo stats" tests, rather than hardcoding stats. My way
> feels more future proof, but the future in which the hardcoded way breaks
> is a long way off.
>
Nevermind, I see that you did the clone tests as well.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
@ 2026-09-24 06:16 ` Michael Paquier <michael@paquier.xyz>
2 siblings, 0 replies; 14+ messages in thread
From: Michael Paquier @ 2026-09-24 06:16 UTC (permalink / raw)
To: Corey Huinker <corey.huinker@gmail.com>; +Cc: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 24, 2026 at 01:56:29AM -0400, Corey Huinker wrote:
> Here's what I was just about to post to the list, only to see that you
> already posted something.
As far as I can see, we are doing exactly the same thing, except that
my patch results in less lines of C and does not duplicate the same
pattern across attribute and extended stats.
> Will begin reviewing yours immediately.
Thanks.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arTALmEVWDsyRp_Z@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
@ 2026-09-24 06:44 ` jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2 siblings, 1 reply; 14+ messages in thread
From: jian he @ 2026-09-24 06:44 UTC (permalink / raw)
To: Corey Huinker <corey.huinker@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 24, 2026 at 1:56 PM Corey Huinker <corey.huinker@gmail.com> wrote:
>
> Here's what I was just about to post to the list, only to see that you already posted something.
>
Hi.
I only took a brief look.
maybe related:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=4edd6036d69ce42ac1af236f659f20daed65c8d4
src/backend/statistics/attribute_stats.c
I wonder in statatt_get_type, can we do:
typcache = lookup_type_cache(*atttypid, TYPECACHE_LT_OPR |
TYPECACHE_EQ_OPR | TYPECACHE_DOMAIN_BASE_INFO);
if (OidIsValid(typcache->domainBaseType))
*atttyptype = get_typtype(typcache->domainBaseType);
else
*atttyptype = typcache->typtype;
Similar in import_pg_statistic.
change to
typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR |
TYPECACHE_EQ_OPR | TYPECACHE_DOMAIN_BASE_INFO);
Disclaimer: Since I saw both of you actively working on this issue, I
haven't tried this myself.
--
jian
https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
@ 2026-09-24 22:52 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 23:46 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 14+ messages in thread
From: Michael Paquier @ 2026-09-24 22:52 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Corey Huinker <corey.huinker@gmail.com>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 24, 2026 at 02:44:19PM +0800, jian he wrote:
> Similar in import_pg_statistic.
> change to
> typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR |
> TYPECACHE_EQ_OPR | TYPECACHE_DOMAIN_BASE_INFO);
>
> Disclaimer: Since I saw both of you actively working on this issue, I
> haven't tried this myself.
Yes, I was wondering about that a bit, feeding a single typcache entry
across the board. And while looking at the code, I was reminded about
the following exceptions:
extended_stats_funcs.c: if (typid == TSVECTOROID)
stat_utils.c: if (*atttypid == TSVECTOROID)
stat_utils.c: if (atttypid == TSVECTOROID)
I think that the existing code is also broken when defining a domain
over tsvector if we don't feed a domain base type in these three
spots.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arWpl7GpyleJ-HjT@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
@ 2026-09-24 23:46 ` Michael Paquier <michael@paquier.xyz>
2026-09-25 01:04 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Manu <manuelreyesbravo@gmail.com>
2026-09-25 19:45 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Michael Paquier @ 2026-09-24 23:46 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Corey Huinker <corey.huinker@gmail.com>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Fri, Sep 25, 2026 at 07:52:07AM +0900, Michael Paquier wrote:
> Yes, I was wondering about that a bit, feeding a single typcache entry
> across the board. And while looking at the code, I was reminded about
> the following exceptions:
> extended_stats_funcs.c: if (typid == TSVECTOROID)
> stat_utils.c: if (*atttypid == TSVECTOROID)
> stat_utils.c: if (atttypid == TSVECTOROID)
>
> I think that the existing code is also broken when defining a domain
> over tsvector if we don't feed a domain base type in these three
> spots.
With all that in mind, I am getting down to the point that we should
accept that the early stages of attribute and extended stats restore
should try to fetch the typcache data of the base types, then roll it
around. This leads to the attached, taking care of the range,
multirange and tsvector cases.
I'd certainly welcome more eyes here. Please note that this applies
on HEAD cleanly, and should mostly apply cleanly on v19. The v18
flavor would be much more localized, of course.
--
Michael
From 8106950aceac31043fd13fb3c4d10da8cdaed863 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Fri, 25 Sep 2026 08:34:31 +0900
Subject: [PATCH v2] Fix import of statistics for domains over [multi]range
types and tsvector
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.
The same issue existed for most_common_elems and elem_count_histogram
for a domain over tsvector.
pg_restore_extended_stats() had the same set of problems for expressions
whose type is a domain over a range, a multirange type, or tsvector.
This problem is resolved by being more aggressive with the fetch of the
base type of a domain in the early phases of restore for attribute and
extended stats (special tip to Jian He for pointing out the unnecessary
the typcache lookups), reflecting on the surrounding helper routines
shared by both code paths. The fix for v18 is more local, as only
attribute stats need to be touched.
Tests are included in a fashion consistent with the surroundings. The
consequence of this issue was the rejection of stats that ANALYZE was
able to build, which was not critical but annoying as it would lead to a
gap in the stats restored.
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 | 8 +-
src/backend/statistics/attribute_stats.c | 19 +-
src/backend/statistics/extended_stats_funcs.c | 33 +-
src/backend/statistics/stat_utils.c | 61 +++-
src/test/regress/expected/stats_import.out | 332 +++++++++++++++++-
src/test/regress/sql/stats_import.sql | 251 +++++++++++++
6 files changed, 658 insertions(+), 46 deletions(-)
diff --git a/src/include/statistics/stat_utils.h b/src/include/statistics/stat_utils.h
index 15e962dbb7c8..aa034cc71971 100644
--- a/src/include/statistics/stat_utils.h
+++ b/src/include/statistics/stat_utils.h
@@ -18,6 +18,8 @@
/* avoid including primnodes.h here */
typedef struct RangeVar RangeVar;
+/* avoid including typcache.h here */
+typedef struct TypeCacheEntry TypeCacheEntry;
struct StatsArgInfo
{
@@ -43,7 +45,7 @@ extern bool stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
extern void statatt_get_type(Oid reloid, AttrNumber attnum,
Oid *atttypid, int32 *atttypmod,
- char *atttyptype, Oid *atttypcoll,
+ TypeCacheEntry **basetypcache, Oid *atttypcoll,
Oid *eq_opr, Oid *lt_opr);
extern void statatt_init_empty_tuple(Oid reloid, int16 attnum, bool inherited,
Datum *values, bool *nulls, bool *replaces);
@@ -55,8 +57,10 @@ extern void statatt_set_slot(Datum *values, bool *nulls, bool *replaces,
extern Datum statatt_build_stavalues(const char *staname, FmgrInfo *array_in, Datum d,
Oid typid, int32 typmod, bool *ok);
-extern bool statatt_get_elem_type(Oid atttypid, char atttyptype,
+extern bool statatt_get_elem_type(TypeCacheEntry *basetypcache,
Oid *elemtypid, Oid *elem_eq_opr);
+extern bool statatt_get_range_type(TypeCacheEntry *basetypcache,
+ 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..25d8a73e5739 100644
--- a/src/backend/statistics/attribute_stats.c
+++ b/src/backend/statistics/attribute_stats.c
@@ -221,7 +221,7 @@ attribute_statistics_update_internal(Oid reloid,
Oid atttypid = InvalidOid;
int32 atttypmod;
- char atttyptype;
+ TypeCacheEntry *basetypcache;
Oid atttypcoll = InvalidOid;
Oid eq_opr = InvalidOid;
Oid lt_opr = InvalidOid;
@@ -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) &&
@@ -296,14 +298,13 @@ attribute_statistics_update_internal(Oid reloid,
/* derive information from attribute */
statatt_get_type(reloid, attnum,
&atttypid, &atttypmod,
- &atttyptype, &atttypcoll,
+ &basetypcache, &atttypcoll,
&eq_opr, <_opr);
/* if needed, derive element type */
if (do_mcelem || do_dechist)
{
- if (!statatt_get_elem_type(atttypid, atttyptype,
- &elemtypid, &elem_eq_opr))
+ if (!statatt_get_elem_type(basetypcache, &elemtypid, &elem_eq_opr))
{
ereport(WARNING,
(errmsg("could not determine element type of column \"%s\"", attname),
@@ -334,7 +335,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(basetypcache, &bounds_typid))
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -498,14 +499,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..00b8dbe33668 100644
--- a/src/backend/statistics/extended_stats_funcs.c
+++ b/src/backend/statistics/extended_stats_funcs.c
@@ -1115,7 +1115,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
bool *pg_statistic_ok)
{
const char *argname = extarginfo[EXPRESSIONS_ARG].argname;
- TypeCacheEntry *typcache;
+ TypeCacheEntry *basetypcache;
Datum values[Natts_pg_statistic];
bool nulls[Natts_pg_statistic];
bool replaces[Natts_pg_statistic];
@@ -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};
@@ -1221,7 +1222,13 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
}
/* This finds the right operators even if atttypid is a domain */
- typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR);
+ basetypcache = lookup_type_cache(typid, TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR |
+ TYPECACHE_DOMAIN_BASE_INFO);
+ if (OidIsValid(basetypcache->domainBaseType))
+ basetypcache = lookup_type_cache(basetypcache->domainBaseType,
+ TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR);
statatt_init_empty_tuple(InvalidOid, InvalidAttrNumber, false,
values, nulls, replaces);
@@ -1230,7 +1237,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
* Special case: collation for tsvector is DEFAULT_COLLATION_OID. See
* compute_tsvector_stats().
*/
- if (typid == TSVECTOROID)
+ if (basetypcache->type_id == TSVECTOROID)
typcoll = DEFAULT_COLLATION_OID;
/*
@@ -1240,8 +1247,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
*/
if (found[MOST_COMMON_ELEMS_ELEM] || found[ELEM_COUNT_HISTOGRAM_ELEM])
{
- if (!statatt_get_elem_type(typid, typcache->typtype,
- &elemtypid, &elemeqopr))
+ if (!statatt_get_elem_type(basetypcache, &elemtypid, &elemeqopr))
{
ereport(WARNING,
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1259,8 +1265,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(basetypcache, &rtypid))
{
ereport(WARNING,
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1364,7 +1369,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
statatt_set_slot(values, nulls, replaces,
STATISTIC_KIND_MCV,
- typcache->eq_opr, typcoll,
+ basetypcache->eq_opr, typcoll,
stanumbers, false, stavalues, false);
}
else
@@ -1386,7 +1391,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
if (val_ok)
statatt_set_slot(values, nulls, replaces,
STATISTIC_KIND_HISTOGRAM,
- typcache->lt_opr, typcoll,
+ basetypcache->lt_opr, typcoll,
0, true, stavalues, false);
else
goto pg_statistic_error;
@@ -1405,7 +1410,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
statatt_set_slot(values, nulls, replaces,
STATISTIC_KIND_CORRELATION,
- typcache->lt_opr, typcoll,
+ basetypcache->lt_opr, typcoll,
stanumbers, false, 0, true);
}
else
@@ -1476,14 +1481,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..a36b2775595f 100644
--- a/src/backend/statistics/stat_utils.c
+++ b/src/backend/statistics/stat_utils.c
@@ -442,14 +442,13 @@ stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
void
statatt_get_type(Oid reloid, AttrNumber attnum,
Oid *atttypid, int32 *atttypmod,
- char *atttyptype, Oid *atttypcoll,
+ TypeCacheEntry **basetypcache, Oid *atttypcoll,
Oid *eq_opr, Oid *lt_opr)
{
Relation rel = relation_open(reloid, AccessShareLock);
Form_pg_attribute attr;
HeapTuple atup;
Node *expr;
- TypeCacheEntry *typcache;
atup = SearchSysCache2(ATTNUM, ObjectIdGetDatum(reloid),
Int16GetDatum(attnum));
@@ -496,35 +495,42 @@ statatt_get_type(Oid reloid, AttrNumber attnum,
ReleaseSysCache(atup);
/* finds the right operators even if atttypid is a domain */
- typcache = lookup_type_cache(*atttypid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR);
- *atttyptype = typcache->typtype;
- *eq_opr = typcache->eq_opr;
- *lt_opr = typcache->lt_opr;
+ *basetypcache = lookup_type_cache(*atttypid, TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR |
+ TYPECACHE_DOMAIN_BASE_INFO);
+ if (OidIsValid((*basetypcache)->domainBaseType))
+ *basetypcache = lookup_type_cache((*basetypcache)->domainBaseType,
+ TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR);
+
+ *eq_opr = (*basetypcache)->eq_opr;
+ *lt_opr = (*basetypcache)->lt_opr;
/*
* Special case: collation for tsvector is DEFAULT_COLLATION_OID. See
* compute_tsvector_stats().
*/
- if (*atttypid == TSVECTOROID)
+ if ((*basetypcache)->type_id == TSVECTOROID)
*atttypcoll = DEFAULT_COLLATION_OID;
relation_close(rel, NoLock);
}
/*
- * Derive element type information from the attribute type. This information
- * is needed when the given type is one that contains elements of other types.
+ * Derive element type information from the base type of an attribute. This
+ * information is needed when the given type is one that contains elements of
+ * other types.
*
- * The atttypid and atttyptype should be derived from a previous call to
+ * The type cache entry should be derived from a previous call to
* statatt_get_type().
*/
bool
-statatt_get_elem_type(Oid atttypid, char atttyptype,
+statatt_get_elem_type(TypeCacheEntry *basetypcache,
Oid *elemtypid, Oid *elem_eq_opr)
{
TypeCacheEntry *elemtypcache;
- if (atttypid == TSVECTOROID)
+ if (basetypcache->type_id == TSVECTOROID)
{
/*
* Special case: element type for tsvector is text. See
@@ -534,8 +540,8 @@ statatt_get_elem_type(Oid atttypid, char atttyptype,
}
else
{
- /* find underlying element type through any domain */
- *elemtypid = get_base_element_type(atttypid);
+ /* find the underlying element type */
+ *elemtypid = get_element_type(basetypcache->type_id);
}
if (!OidIsValid(*elemtypid))
@@ -551,6 +557,33 @@ 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.
+ *
+ * For a multirange type, we step down to its range type, because
+ * compute_range_stats() stores range bounds even when analyzing a multirange
+ * column (see also range_typanalyze() and multirange_typanalyze()).
+ *
+ * The type cache entry should be derived from a previous call to
+ * statatt_get_type(), so that any domain has already been looked through.
+ */
+bool
+statatt_get_range_type(TypeCacheEntry *basetypcache, Oid *rangetypid)
+{
+ if (basetypcache->typtype == TYPTYPE_MULTIRANGE)
+ *rangetypid = get_multirange_range(basetypcache->type_id);
+ else if (basetypcache->typtype == TYPTYPE_RANGE)
+ *rangetypid = basetypcache->type_id;
+ 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..8b54e2606260 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -1495,6 +1495,231 @@ 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 for a domain over tsvector.
+CREATE DOMAIN stats_import.dom_tsvector AS tsvector;
+CREATE TABLE stats_import.test_dom_ts(
+ id int,
+ v stats_import.dom_tsvector
+) WITH (autovacuum_enabled = false);
+INSERT INTO stats_import.test_dom_ts
+SELECT g, to_tsvector('english', 'the quick brown fox ' || g)
+FROM generate_series(1, 20) AS g;
+-- ok: mcelem and elem_count_histogram for a domain over tsvector
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'attname', 'v',
+ 'inherited', false,
+ 'most_common_elems', '{brown,fox,quick}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
+ 'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom_ts'
+AND inherited = false
+AND attname = 'v';
+ 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_ts | v | f | 0 | 0 | 0 | | | | | {brown,fox,quick} | {0.3,0.2,0.2,0.3,0} | {4,4,4,4,4,4,4,4,4,4} | | |
+(1 row)
+
+--
+-- Check that the statistics that ANALYZE generates for a domain over
+-- tsvector can be restored exactly.
+--
+ANALYZE stats_import.test_dom_ts;
+CREATE TABLE stats_import.test_dom_ts_clone ( LIKE stats_import.test_dom_ts )
+ 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_ts_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,
+ 'most_common_elems', s.most_common_elems::text,
+ 'most_common_elem_freqs', s.most_common_elem_freqs,
+ 'elem_count_histogram', s.elem_count_histogram) AS r
+WHERE s.schemaname = 'stats_import'
+AND s.tablename = 'test_dom_ts'
+ORDER BY s.attname, s.inherited;
+ attname | inherited | r
+---------+-----------+---
+ id | f | t
+ v | f | t
+(2 rows)
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_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 +2962,103 @@ 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)"}
+
+-- Check import of MCELEM stats for an expression whose type is a domain
+-- over tsvector.
+CREATE STATISTICS stats_import.test_dom_ts_stat
+ ON id, (strip(v)::stats_import.dom_tsvector)
+ FROM stats_import.test_dom_ts;
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_ts_stat',
+ 'inherited', false,
+ 'exprs', '[{"most_common_elems": "{brown,fox,quick}",
+ "most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}",
+ "elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb);
+ pg_restore_extended_stats
+---------------------------
+ t
+(1 row)
+
+SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs,
+ e.elem_count_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_ts_stat' AND
+ e.inherited = false
+\gx
+-[ RECORD 1 ]----------+--------------------------------------------------
+expr | (strip((v)::tsvector))::stats_import.dom_tsvector
+most_common_elems | {brown,fox,quick}
+most_common_elem_freqs | {0.3,0.2,0.2,0.3,0}
+elem_count_histogram | {4,4,4,4,4,4,4,4,4,4}
+
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
@@ -3828,7 +4150,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 27 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 +4167,14 @@ 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 type stats_import.dom_tsvector
+drop cascades to table stats_import.test_dom_ts
+drop cascades to table stats_import.test_dom_ts_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..20ec479ae22b 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -1082,6 +1082,188 @@ 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 for a domain over tsvector.
+CREATE DOMAIN stats_import.dom_tsvector AS tsvector;
+
+CREATE TABLE stats_import.test_dom_ts(
+ id int,
+ v stats_import.dom_tsvector
+) WITH (autovacuum_enabled = false);
+
+INSERT INTO stats_import.test_dom_ts
+SELECT g, to_tsvector('english', 'the quick brown fox ' || g)
+FROM generate_series(1, 20) AS g;
+
+-- ok: mcelem and elem_count_histogram for a domain over tsvector
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'attname', 'v',
+ 'inherited', false,
+ 'most_common_elems', '{brown,fox,quick}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
+ 'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom_ts'
+AND inherited = false
+AND attname = 'v';
+
+--
+-- Check that the statistics that ANALYZE generates for a domain over
+-- tsvector can be restored exactly.
+--
+ANALYZE stats_import.test_dom_ts;
+
+CREATE TABLE stats_import.test_dom_ts_clone ( LIKE stats_import.test_dom_ts )
+ 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_ts_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,
+ 'most_common_elems', s.most_common_elems::text,
+ 'most_common_elem_freqs', s.most_common_elem_freqs,
+ 'elem_count_histogram', s.elem_count_histogram) AS r
+WHERE s.schemaname = 'stats_import'
+AND s.tablename = 'test_dom_ts'
+ORDER BY s.attname, s.inherited;
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_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 +2116,75 @@ 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
+
+-- Check import of MCELEM stats for an expression whose type is a domain
+-- over tsvector.
+CREATE STATISTICS stats_import.test_dom_ts_stat
+ ON id, (strip(v)::stats_import.dom_tsvector)
+ FROM stats_import.test_dom_ts;
+
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_ts_stat',
+ 'inherited', false,
+ 'exprs', '[{"most_common_elems": "{brown,fox,quick}",
+ "most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}",
+ "elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb);
+
+SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs,
+ e.elem_count_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_ts_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] v2-0001-Fix-import-of-statistics-for-domains-over-multi-r.patch (38.8K, ../../arW2T9Oa5k6Wpq-O@paquier.xyz/2-v2-0001-Fix-import-of-statistics-for-domains-over-multi-r.patch)
download | inline diff:
From 8106950aceac31043fd13fb3c4d10da8cdaed863 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Fri, 25 Sep 2026 08:34:31 +0900
Subject: [PATCH v2] Fix import of statistics for domains over [multi]range
types and tsvector
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.
The same issue existed for most_common_elems and elem_count_histogram
for a domain over tsvector.
pg_restore_extended_stats() had the same set of problems for expressions
whose type is a domain over a range, a multirange type, or tsvector.
This problem is resolved by being more aggressive with the fetch of the
base type of a domain in the early phases of restore for attribute and
extended stats (special tip to Jian He for pointing out the unnecessary
the typcache lookups), reflecting on the surrounding helper routines
shared by both code paths. The fix for v18 is more local, as only
attribute stats need to be touched.
Tests are included in a fashion consistent with the surroundings. The
consequence of this issue was the rejection of stats that ANALYZE was
able to build, which was not critical but annoying as it would lead to a
gap in the stats restored.
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 | 8 +-
src/backend/statistics/attribute_stats.c | 19 +-
src/backend/statistics/extended_stats_funcs.c | 33 +-
src/backend/statistics/stat_utils.c | 61 +++-
src/test/regress/expected/stats_import.out | 332 +++++++++++++++++-
src/test/regress/sql/stats_import.sql | 251 +++++++++++++
6 files changed, 658 insertions(+), 46 deletions(-)
diff --git a/src/include/statistics/stat_utils.h b/src/include/statistics/stat_utils.h
index 15e962dbb7c8..aa034cc71971 100644
--- a/src/include/statistics/stat_utils.h
+++ b/src/include/statistics/stat_utils.h
@@ -18,6 +18,8 @@
/* avoid including primnodes.h here */
typedef struct RangeVar RangeVar;
+/* avoid including typcache.h here */
+typedef struct TypeCacheEntry TypeCacheEntry;
struct StatsArgInfo
{
@@ -43,7 +45,7 @@ extern bool stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
extern void statatt_get_type(Oid reloid, AttrNumber attnum,
Oid *atttypid, int32 *atttypmod,
- char *atttyptype, Oid *atttypcoll,
+ TypeCacheEntry **basetypcache, Oid *atttypcoll,
Oid *eq_opr, Oid *lt_opr);
extern void statatt_init_empty_tuple(Oid reloid, int16 attnum, bool inherited,
Datum *values, bool *nulls, bool *replaces);
@@ -55,8 +57,10 @@ extern void statatt_set_slot(Datum *values, bool *nulls, bool *replaces,
extern Datum statatt_build_stavalues(const char *staname, FmgrInfo *array_in, Datum d,
Oid typid, int32 typmod, bool *ok);
-extern bool statatt_get_elem_type(Oid atttypid, char atttyptype,
+extern bool statatt_get_elem_type(TypeCacheEntry *basetypcache,
Oid *elemtypid, Oid *elem_eq_opr);
+extern bool statatt_get_range_type(TypeCacheEntry *basetypcache,
+ 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..25d8a73e5739 100644
--- a/src/backend/statistics/attribute_stats.c
+++ b/src/backend/statistics/attribute_stats.c
@@ -221,7 +221,7 @@ attribute_statistics_update_internal(Oid reloid,
Oid atttypid = InvalidOid;
int32 atttypmod;
- char atttyptype;
+ TypeCacheEntry *basetypcache;
Oid atttypcoll = InvalidOid;
Oid eq_opr = InvalidOid;
Oid lt_opr = InvalidOid;
@@ -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) &&
@@ -296,14 +298,13 @@ attribute_statistics_update_internal(Oid reloid,
/* derive information from attribute */
statatt_get_type(reloid, attnum,
&atttypid, &atttypmod,
- &atttyptype, &atttypcoll,
+ &basetypcache, &atttypcoll,
&eq_opr, <_opr);
/* if needed, derive element type */
if (do_mcelem || do_dechist)
{
- if (!statatt_get_elem_type(atttypid, atttyptype,
- &elemtypid, &elem_eq_opr))
+ if (!statatt_get_elem_type(basetypcache, &elemtypid, &elem_eq_opr))
{
ereport(WARNING,
(errmsg("could not determine element type of column \"%s\"", attname),
@@ -334,7 +335,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(basetypcache, &bounds_typid))
{
ereport(WARNING,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -498,14 +499,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..00b8dbe33668 100644
--- a/src/backend/statistics/extended_stats_funcs.c
+++ b/src/backend/statistics/extended_stats_funcs.c
@@ -1115,7 +1115,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
bool *pg_statistic_ok)
{
const char *argname = extarginfo[EXPRESSIONS_ARG].argname;
- TypeCacheEntry *typcache;
+ TypeCacheEntry *basetypcache;
Datum values[Natts_pg_statistic];
bool nulls[Natts_pg_statistic];
bool replaces[Natts_pg_statistic];
@@ -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};
@@ -1221,7 +1222,13 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
}
/* This finds the right operators even if atttypid is a domain */
- typcache = lookup_type_cache(typid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR);
+ basetypcache = lookup_type_cache(typid, TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR |
+ TYPECACHE_DOMAIN_BASE_INFO);
+ if (OidIsValid(basetypcache->domainBaseType))
+ basetypcache = lookup_type_cache(basetypcache->domainBaseType,
+ TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR);
statatt_init_empty_tuple(InvalidOid, InvalidAttrNumber, false,
values, nulls, replaces);
@@ -1230,7 +1237,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
* Special case: collation for tsvector is DEFAULT_COLLATION_OID. See
* compute_tsvector_stats().
*/
- if (typid == TSVECTOROID)
+ if (basetypcache->type_id == TSVECTOROID)
typcoll = DEFAULT_COLLATION_OID;
/*
@@ -1240,8 +1247,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
*/
if (found[MOST_COMMON_ELEMS_ELEM] || found[ELEM_COUNT_HISTOGRAM_ELEM])
{
- if (!statatt_get_elem_type(typid, typcache->typtype,
- &elemtypid, &elemeqopr))
+ if (!statatt_get_elem_type(basetypcache, &elemtypid, &elemeqopr))
{
ereport(WARNING,
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1259,8 +1265,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(basetypcache, &rtypid))
{
ereport(WARNING,
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1364,7 +1369,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
statatt_set_slot(values, nulls, replaces,
STATISTIC_KIND_MCV,
- typcache->eq_opr, typcoll,
+ basetypcache->eq_opr, typcoll,
stanumbers, false, stavalues, false);
}
else
@@ -1386,7 +1391,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
if (val_ok)
statatt_set_slot(values, nulls, replaces,
STATISTIC_KIND_HISTOGRAM,
- typcache->lt_opr, typcoll,
+ basetypcache->lt_opr, typcoll,
0, true, stavalues, false);
else
goto pg_statistic_error;
@@ -1405,7 +1410,7 @@ import_pg_statistic(Relation pgsd, JsonbContainer *cont,
statatt_set_slot(values, nulls, replaces,
STATISTIC_KIND_CORRELATION,
- typcache->lt_opr, typcoll,
+ basetypcache->lt_opr, typcoll,
stanumbers, false, 0, true);
}
else
@@ -1476,14 +1481,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..a36b2775595f 100644
--- a/src/backend/statistics/stat_utils.c
+++ b/src/backend/statistics/stat_utils.c
@@ -442,14 +442,13 @@ stats_fill_fcinfo_from_arg_pairs(FunctionCallInfo pairs_fcinfo,
void
statatt_get_type(Oid reloid, AttrNumber attnum,
Oid *atttypid, int32 *atttypmod,
- char *atttyptype, Oid *atttypcoll,
+ TypeCacheEntry **basetypcache, Oid *atttypcoll,
Oid *eq_opr, Oid *lt_opr)
{
Relation rel = relation_open(reloid, AccessShareLock);
Form_pg_attribute attr;
HeapTuple atup;
Node *expr;
- TypeCacheEntry *typcache;
atup = SearchSysCache2(ATTNUM, ObjectIdGetDatum(reloid),
Int16GetDatum(attnum));
@@ -496,35 +495,42 @@ statatt_get_type(Oid reloid, AttrNumber attnum,
ReleaseSysCache(atup);
/* finds the right operators even if atttypid is a domain */
- typcache = lookup_type_cache(*atttypid, TYPECACHE_LT_OPR | TYPECACHE_EQ_OPR);
- *atttyptype = typcache->typtype;
- *eq_opr = typcache->eq_opr;
- *lt_opr = typcache->lt_opr;
+ *basetypcache = lookup_type_cache(*atttypid, TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR |
+ TYPECACHE_DOMAIN_BASE_INFO);
+ if (OidIsValid((*basetypcache)->domainBaseType))
+ *basetypcache = lookup_type_cache((*basetypcache)->domainBaseType,
+ TYPECACHE_LT_OPR |
+ TYPECACHE_EQ_OPR);
+
+ *eq_opr = (*basetypcache)->eq_opr;
+ *lt_opr = (*basetypcache)->lt_opr;
/*
* Special case: collation for tsvector is DEFAULT_COLLATION_OID. See
* compute_tsvector_stats().
*/
- if (*atttypid == TSVECTOROID)
+ if ((*basetypcache)->type_id == TSVECTOROID)
*atttypcoll = DEFAULT_COLLATION_OID;
relation_close(rel, NoLock);
}
/*
- * Derive element type information from the attribute type. This information
- * is needed when the given type is one that contains elements of other types.
+ * Derive element type information from the base type of an attribute. This
+ * information is needed when the given type is one that contains elements of
+ * other types.
*
- * The atttypid and atttyptype should be derived from a previous call to
+ * The type cache entry should be derived from a previous call to
* statatt_get_type().
*/
bool
-statatt_get_elem_type(Oid atttypid, char atttyptype,
+statatt_get_elem_type(TypeCacheEntry *basetypcache,
Oid *elemtypid, Oid *elem_eq_opr)
{
TypeCacheEntry *elemtypcache;
- if (atttypid == TSVECTOROID)
+ if (basetypcache->type_id == TSVECTOROID)
{
/*
* Special case: element type for tsvector is text. See
@@ -534,8 +540,8 @@ statatt_get_elem_type(Oid atttypid, char atttyptype,
}
else
{
- /* find underlying element type through any domain */
- *elemtypid = get_base_element_type(atttypid);
+ /* find the underlying element type */
+ *elemtypid = get_element_type(basetypcache->type_id);
}
if (!OidIsValid(*elemtypid))
@@ -551,6 +557,33 @@ 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.
+ *
+ * For a multirange type, we step down to its range type, because
+ * compute_range_stats() stores range bounds even when analyzing a multirange
+ * column (see also range_typanalyze() and multirange_typanalyze()).
+ *
+ * The type cache entry should be derived from a previous call to
+ * statatt_get_type(), so that any domain has already been looked through.
+ */
+bool
+statatt_get_range_type(TypeCacheEntry *basetypcache, Oid *rangetypid)
+{
+ if (basetypcache->typtype == TYPTYPE_MULTIRANGE)
+ *rangetypid = get_multirange_range(basetypcache->type_id);
+ else if (basetypcache->typtype == TYPTYPE_RANGE)
+ *rangetypid = basetypcache->type_id;
+ 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..8b54e2606260 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -1495,6 +1495,231 @@ 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 for a domain over tsvector.
+CREATE DOMAIN stats_import.dom_tsvector AS tsvector;
+CREATE TABLE stats_import.test_dom_ts(
+ id int,
+ v stats_import.dom_tsvector
+) WITH (autovacuum_enabled = false);
+INSERT INTO stats_import.test_dom_ts
+SELECT g, to_tsvector('english', 'the quick brown fox ' || g)
+FROM generate_series(1, 20) AS g;
+-- ok: mcelem and elem_count_histogram for a domain over tsvector
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'attname', 'v',
+ 'inherited', false,
+ 'most_common_elems', '{brown,fox,quick}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
+ 'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]);
+ pg_restore_attribute_stats
+----------------------------
+ t
+(1 row)
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom_ts'
+AND inherited = false
+AND attname = 'v';
+ 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_ts | v | f | 0 | 0 | 0 | | | | | {brown,fox,quick} | {0.3,0.2,0.2,0.3,0} | {4,4,4,4,4,4,4,4,4,4} | | |
+(1 row)
+
+--
+-- Check that the statistics that ANALYZE generates for a domain over
+-- tsvector can be restored exactly.
+--
+ANALYZE stats_import.test_dom_ts;
+CREATE TABLE stats_import.test_dom_ts_clone ( LIKE stats_import.test_dom_ts )
+ 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_ts_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,
+ 'most_common_elems', s.most_common_elems::text,
+ 'most_common_elem_freqs', s.most_common_elem_freqs,
+ 'elem_count_histogram', s.elem_count_histogram) AS r
+WHERE s.schemaname = 'stats_import'
+AND s.tablename = 'test_dom_ts'
+ORDER BY s.attname, s.inherited;
+ attname | inherited | r
+---------+-----------+---
+ id | f | t
+ v | f | t
+(2 rows)
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_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 +2962,103 @@ 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)"}
+
+-- Check import of MCELEM stats for an expression whose type is a domain
+-- over tsvector.
+CREATE STATISTICS stats_import.test_dom_ts_stat
+ ON id, (strip(v)::stats_import.dom_tsvector)
+ FROM stats_import.test_dom_ts;
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_ts_stat',
+ 'inherited', false,
+ 'exprs', '[{"most_common_elems": "{brown,fox,quick}",
+ "most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}",
+ "elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb);
+ pg_restore_extended_stats
+---------------------------
+ t
+(1 row)
+
+SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs,
+ e.elem_count_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_ts_stat' AND
+ e.inherited = false
+\gx
+-[ RECORD 1 ]----------+--------------------------------------------------
+expr | (strip((v)::tsvector))::stats_import.dom_tsvector
+most_common_elems | {brown,fox,quick}
+most_common_elem_freqs | {0.3,0.2,0.2,0.3,0}
+elem_count_histogram | {4,4,4,4,4,4,4,4,4,4}
+
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
'schemaname', 'stats_import',
@@ -3828,7 +4150,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 27 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 +4167,14 @@ 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 type stats_import.dom_tsvector
+drop cascades to table stats_import.test_dom_ts
+drop cascades to table stats_import.test_dom_ts_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..20ec479ae22b 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -1082,6 +1082,188 @@ 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 for a domain over tsvector.
+CREATE DOMAIN stats_import.dom_tsvector AS tsvector;
+
+CREATE TABLE stats_import.test_dom_ts(
+ id int,
+ v stats_import.dom_tsvector
+) WITH (autovacuum_enabled = false);
+
+INSERT INTO stats_import.test_dom_ts
+SELECT g, to_tsvector('english', 'the quick brown fox ' || g)
+FROM generate_series(1, 20) AS g;
+
+-- ok: mcelem and elem_count_histogram for a domain over tsvector
+SELECT pg_catalog.pg_restore_attribute_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'attname', 'v',
+ 'inherited', false,
+ 'most_common_elems', '{brown,fox,quick}'::text,
+ 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
+ 'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]);
+
+SELECT *
+FROM stats_import.pg_stats_stable
+WHERE schemaname = 'stats_import'
+AND tablename = 'test_dom_ts'
+AND inherited = false
+AND attname = 'v';
+
+--
+-- Check that the statistics that ANALYZE generates for a domain over
+-- tsvector can be restored exactly.
+--
+ANALYZE stats_import.test_dom_ts;
+
+CREATE TABLE stats_import.test_dom_ts_clone ( LIKE stats_import.test_dom_ts )
+ 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_ts_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,
+ 'most_common_elems', s.most_common_elems::text,
+ 'most_common_elem_freqs', s.most_common_elem_freqs,
+ 'elem_count_histogram', s.elem_count_histogram) AS r
+WHERE s.schemaname = 'stats_import'
+AND s.tablename = 'test_dom_ts'
+ORDER BY s.attname, s.inherited;
+
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_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 +2116,75 @@ 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
+
+-- Check import of MCELEM stats for an expression whose type is a domain
+-- over tsvector.
+CREATE STATISTICS stats_import.test_dom_ts_stat
+ ON id, (strip(v)::stats_import.dom_tsvector)
+ FROM stats_import.test_dom_ts;
+
+SELECT pg_catalog.pg_restore_extended_stats(
+ 'schemaname', 'stats_import',
+ 'relname', 'test_dom_ts',
+ 'statistics_schemaname', 'stats_import',
+ 'statistics_name', 'test_dom_ts_stat',
+ 'inherited', false,
+ 'exprs', '[{"most_common_elems": "{brown,fox,quick}",
+ "most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}",
+ "elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb);
+
+SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs,
+ e.elem_count_histogram
+FROM pg_stats_ext_exprs AS e
+WHERE e.statistics_schemaname = 'stats_import' AND
+ e.statistics_name = 'test_dom_ts_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, ../../arW2T9Oa5k6Wpq-O@paquier.xyz/3-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 23:46 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
@ 2026-09-25 01:04 ` Manu <manuelreyesbravo@gmail.com>
2026-09-25 19:35 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Manu @ 2026-09-25 01:04 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jian he <jian.universality@gmail.com>; Corey Huinker <corey.huinker@gmail.com>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
Hi,
Michael Paquier <michael@paquier.xyz> wrote:
> I'd certainly welcome more eyes here. Please note that this applies
> on HEAD cleanly, and should mostly apply cleanly on v19.
It applies with git am on both master (2c10c2ce4d7) and REL_19_STABLE
(2c5cd772b90), and make check passes on both. With only the test
changes of v2 and not the C ones, stats_import fails on master with
the expected warnings, so the new tests catch the bug.
I also checked the path users hit, pg_dump --statistics-only into a
database with the same schema, and compared every field of pg_stats
and pg_stats_ext_exprs before and after. The table has 17 columns and
the statistics object 8 expressions, the cases of the report plus a
few beyond the ones in the tests:
- a domain over a domain, for range, multirange and tsvector
- a domain over int4range with a CHECK constraint
- a domain over a range type with its own collation (text, "C")
- expressions of those types, next to one over a domain over int4[]
Results:
- master: 15 warnings. Range stats are lost for 6 domain columns,
including the nested, CHECK and collated ones, and MCELEM for the 2
tsvector domains. All 8 expressions lose all their stats.
- master + v2: no warnings, all 25 identical after the restore.
- REL_19_STABLE + v2: the same.
- REL_18_STABLE (66d1de70c84): the same 8 columns fail as on master.
I can run the same script on the v18 flavor when you post it.
A domain over int4[] and an array of a domain over int4 already come
through intact without the patch, in case that question comes up.
One thing outside this bug, about why all 8 expressions were lost.
import_expressions() stores a failed expression as NULL and keeps the
others, per its comments, but extended_statistics_update() then drops
the whole stxdexpr array when exprs_is_perfect is false. With one
statistics object on the d_arr expression plus a domain-over-range
one, master restores nothing for either; with the d_arr expression
next to a plain one, it is restored. Is dropping all of them
intended? I may be missing the reason. v2 removes the cause here,
so this only matters for other rejections.
The scripts and outputs are in the attachment.
Regards,
Manu
Review of v2 for bug #19715: statistics round trip through pg_dump
Builds: master 2c10c2ce4d7, REL_19_STABLE 2c5cd772b90, REL_18_STABLE 66d1de70c84
(--enable-cassert --enable-debug); v2 applied with git am on master and REL_19.
"===== roundtrip_setup.sql ====="
-- Source database for the pg_dump --statistics round trip.
-- One column per case: the base type, a domain over it, a domain over that
-- domain, and a domain with a CHECK constraint, for each kind of statistics
-- that depends on the type (range, multirange, tsvector, array elements).
-- Plus an array of a domain, a range with its own collation, and a domain
-- over a collatable type.
CREATE DOMAIN d_r AS int4range;
CREATE DOMAIN dd_r AS d_r;
CREATE DOMAIN dc_r AS int4range CHECK (NOT isempty(VALUE) OR VALUE IS NULL OR true);
CREATE DOMAIN d_mr AS int4multirange;
CREATE DOMAIN dd_mr AS d_mr;
CREATE DOMAIN d_tsv AS tsvector;
CREATE DOMAIN dd_tsv AS d_tsv;
CREATE DOMAIN d_arr AS int4[];
CREATE DOMAIN dd_arr AS d_arr;
CREATE DOMAIN d_int AS int4;
CREATE TYPE crange AS RANGE (subtype = text, collation = "C");
CREATE DOMAIN d_crange AS crange;
CREATE DOMAIN d_ctext AS text COLLATE "C";
CREATE TABLE t (
r int4range, dr d_r, ddr dd_r, dcr dc_r,
mr int4multirange, dmr d_mr, ddmr dd_mr,
tsv tsvector, dtsv d_tsv, ddtsv dd_tsv,
arr int4[], darr d_arr, ddarr dd_arr,
arrd d_int[],
cr crange, dcrange d_crange,
ctext d_ctext
);
INSERT INTO t
SELECT int4range(g % 97, g % 97 + 1 + g % 13),
int4range(g % 97, g % 97 + 1 + g % 13),
int4range(g % 97, g % 97 + 1 + g % 13),
CASE WHEN g % 10 = 0 THEN 'empty'::int4range ELSE int4range(g % 89, g % 89 + 3) END,
int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)),
int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)),
int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)),
to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3),
to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3),
to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3),
ARRAY[g % 30, g % 7, g % 3],
ARRAY[g % 30, g % 7, g % 3],
ARRAY[g % 30, g % 7, g % 3],
ARRAY[g % 30, g % 7, g % 3]::d_int[],
crange('k' || g % 60, 'k' || g % 60 || 'z'),
crange('k' || g % 60, 'k' || g % 60 || 'z'),
'v' || g % 300
FROM generate_series(1, 5000) g;
-- Expression statistics over the same domains (extended stats restore path).
-- A bare cast of the column folds back into the column, so each expression
-- goes through a function first, as in the tests of v2.
CREATE STATISTICS t_exprs ON
(range_merge(dr, dr)::d_r),
(range_merge(ddr, ddr)::dd_r),
((dmr + '{}'::int4multirange)::d_mr),
((ddmr + '{}'::int4multirange)::dd_mr),
(strip(dtsv)::d_tsv),
(strip(ddtsv)::dd_tsv),
((darr || '{}'::int4[])::d_arr),
(range_merge(dcrange, dcrange)::d_crange)
FROM t;
ANALYZE t;
"===== roundtrip.sh ====="
#!/bin/bash
# Round trip of statistics through pg_dump --statistics-only for the columns
# in roundtrip_setup.sql, on each build. For every column (pg_stats) and
# every statistics expression (pg_stats_ext_exprs), each field is compared
# between the source database and the restored one. Prints, per build, the
# restore WARNINGs and the fields that did not come through.
# roundtrip.sh [build...] (default: 18 head v2 19v2)
set -u
A=$(cd "$(dirname "$0")" && pwd)
BUILDS=${*:-18 head v2 19v2}
OUT=$A/roundtrip.out; : > $OUT
for B in $BUILDS; do
I=$HOME/pg19715/i-$B/bin
D=$(mktemp -d /tmp/claude-1000/rt.XXXX); P=55460
"$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null
printf "port = $P\nunix_socket_directories = '/tmp'\n" >> $D/postgresql.conf
"$I/pg_ctl" -D $D -l $D/log -w start >/dev/null
q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; }
q -c "CREATE DATABASE src" -c "CREATE DATABASE dst"
q -d src -v ON_ERROR_STOP=1 -f $A/roundtrip_setup.sql > $D/setup.log 2>&1 || { echo "$B: setup failed"; cat $D/setup.log; }
"$I/pg_dump" -h /tmp -p $P -U postgres --schema-only src | q -d dst > /dev/null 2>&1
"$I/pg_dump" -h /tmp -p $P -U postgres --statistics-only src > $D/stats.sql
q -d dst -f $D/stats.sql > $D/restore.out 2> $D/restore.err
# One line per (object, field, value); objects are columns and expressions.
flat="
SELECT 'col ' || attname AS obj, f.key, f.value
FROM pg_stats s, jsonb_each_text(to_jsonb(s) - 'tableid' - 'schemaname' - 'tablename' - 'attname' - 'inherited') f
WHERE tablename = 't'
UNION ALL
SELECT 'expr ' || expr, f.key, f.value
FROM pg_stats_ext_exprs s, jsonb_each_text(to_jsonb(s) - 'tableid' - 'schemaname' - 'tablename'
- 'statistics_schemaname' - 'statistics_name'
- 'statistics_owner' - 'statistics_id' - 'expr' - 'inherited') f
WHERE tablename = 't'
ORDER BY 1, 2"
q -d src -F $'\t' -c "$flat" > $D/src.tsv 2>/dev/null
q -d dst -F $'\t' -c "$flat" > $D/dst.tsv 2>/dev/null
{
echo "===== $B: $("$I/postgres" --version)"
echo "restore WARNINGs: $(grep -c WARNING $D/restore.err)"
grep -A1 WARNING $D/restore.err | grep -v '^--$' | sed 's/^psql:[^ ]* //' | sort | uniq -c | sed 's/^/ /'
echo "fields that differ after the round trip (object: field):"
diff <(sort $D/src.tsv) <(sort $D/dst.tsv) | grep '^<' | cut -f1,2 | sed 's/^< / /; s/\t/: /' | sort -u
echo "objects with statistics: src $(cut -f1 $D/src.tsv | sort -u | wc -l), dst $(cut -f1 $D/dst.tsv | sort -u | wc -l)"
echo
} | tee -a $OUT
"$I/pg_ctl" -D $D -m fast -w stop >/dev/null
rm -rf $D
done
===== ./roundtrip.sh 18 head v2 19v2 =====
===== 18: postgres (PostgreSQL) 18.6
restore WARNINGs: 8
2 DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
6 DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
1 WARNING: column "dcrange" is not a range type
1 WARNING: column "dcr" is not a range type
1 WARNING: column "ddmr" is not a range type
1 WARNING: column "ddr" is not a range type
1 WARNING: column "dmr" is not a range type
1 WARNING: column "dr" is not a range type
1 WARNING: could not determine element type of column "ddtsv"
1 WARNING: could not determine element type of column "dtsv"
fields that differ after the round trip (object: field):
col dcrange: range_bounds_histogram
col dcrange: range_empty_frac
col dcrange: range_length_histogram
col dcr: range_bounds_histogram
col dcr: range_empty_frac
col dcr: range_length_histogram
col ddmr: range_bounds_histogram
col ddmr: range_empty_frac
col ddmr: range_length_histogram
col ddr: range_bounds_histogram
col ddr: range_empty_frac
col ddr: range_length_histogram
col ddtsv: most_common_elem_freqs
col ddtsv: most_common_elems
col dmr: range_bounds_histogram
col dmr: range_empty_frac
col dmr: range_length_histogram
col dr: range_bounds_histogram
col dr: range_empty_frac
col dr: range_length_histogram
col dtsv: most_common_elem_freqs
col dtsv: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: avg_width
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: correlation
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: elem_count_histogram
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: histogram_bounds
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elem_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_vals
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: n_distinct
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: null_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: avg_width
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: n_distinct
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: null_frac
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: avg_width
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: n_distinct
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: null_frac
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: avg_width
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: n_distinct
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: null_frac
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: avg_width
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: n_distinct
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: null_frac
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: avg_width
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: n_distinct
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: null_frac
expr (strip((ddtsv)::tsvector))::dd_tsv: avg_width
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elem_freqs
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elems
expr (strip((ddtsv)::tsvector))::dd_tsv: n_distinct
expr (strip((ddtsv)::tsvector))::dd_tsv: null_frac
expr (strip((dtsv)::tsvector))::d_tsv: avg_width
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elem_freqs
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elems
expr (strip((dtsv)::tsvector))::d_tsv: n_distinct
expr (strip((dtsv)::tsvector))::d_tsv: null_frac
objects with statistics: src 25, dst 25
===== head: postgres (PostgreSQL) 20devel
restore WARNINGs: 15
2 DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
6 DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
5 HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
1 WARNING: column "dcrange" is not a range type
1 WARNING: column "dcr" is not a range type
1 WARNING: column "ddmr" is not a range type
1 WARNING: column "ddr" is not a range type
1 WARNING: column "dmr" is not a range type
1 WARNING: column "dr" is not a range type
1 WARNING: could not determine element type of column "ddtsv"
1 WARNING: could not determine element type of column "dtsv"
1 WARNING: could not parse "exprs": invalid data in expression -1
1 WARNING: could not parse "exprs": invalid data in expression -2
1 WARNING: could not parse "exprs": invalid data in expression -3
1 WARNING: could not parse "exprs": invalid data in expression -4
1 WARNING: could not parse "exprs": invalid data in expression -8
1 WARNING: could not parse "exprs": invalid element type in expression -5
1 WARNING: could not parse "exprs": invalid element type in expression -6
fields that differ after the round trip (object: field):
col dcrange: range_bounds_histogram
col dcrange: range_empty_frac
col dcrange: range_length_histogram
col dcr: range_bounds_histogram
col dcr: range_empty_frac
col dcr: range_length_histogram
col ddmr: range_bounds_histogram
col ddmr: range_empty_frac
col ddmr: range_length_histogram
col ddr: range_bounds_histogram
col ddr: range_empty_frac
col ddr: range_length_histogram
col ddtsv: most_common_elem_freqs
col ddtsv: most_common_elems
col dmr: range_bounds_histogram
col dmr: range_empty_frac
col dmr: range_length_histogram
col dr: range_bounds_histogram
col dr: range_empty_frac
col dr: range_length_histogram
col dtsv: most_common_elem_freqs
col dtsv: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: avg_width
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: correlation
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: elem_count_histogram
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: histogram_bounds
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elem_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_vals
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: n_distinct
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: null_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: avg_width
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: n_distinct
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: null_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_bounds_histogram
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_empty_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_length_histogram
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: avg_width
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: n_distinct
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: null_frac
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_bounds_histogram
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_empty_frac
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_length_histogram
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: avg_width
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: n_distinct
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: null_frac
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_bounds_histogram
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_empty_frac
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_length_histogram
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: avg_width
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: n_distinct
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: null_frac
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_bounds_histogram
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_empty_frac
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_length_histogram
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: avg_width
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: n_distinct
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: null_frac
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_bounds_histogram
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_empty_frac
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_length_histogram
expr (strip((ddtsv)::tsvector))::dd_tsv: avg_width
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elem_freqs
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elems
expr (strip((ddtsv)::tsvector))::dd_tsv: n_distinct
expr (strip((ddtsv)::tsvector))::dd_tsv: null_frac
expr (strip((dtsv)::tsvector))::d_tsv: avg_width
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elem_freqs
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elems
expr (strip((dtsv)::tsvector))::d_tsv: n_distinct
expr (strip((dtsv)::tsvector))::d_tsv: null_frac
objects with statistics: src 25, dst 25
===== v2: postgres (PostgreSQL) 20devel
restore WARNINGs: 0
fields that differ after the round trip (object: field):
objects with statistics: src 25, dst 25
===== 19v2: postgres (PostgreSQL) 19beta4
restore WARNINGs: 0
fields that differ after the round trip (object: field):
objects with statistics: src 25, dst 25
"===== one_expr.sh ====="
#!/bin/bash
# On a build without the fix: is the d_arr expression lost by itself, or
# only when it shares a statistics object with a rejected expression?
# one_expr.sh [build] (default: head)
set -u
B=${1:-head}
I=$HOME/pg19715/i-$B/bin
D=$(mktemp -d /tmp/claude-1000/oe.XXXX); P=55462
"$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null
printf "port = $P\nunix_socket_directories = '/tmp'\n" >> $D/postgresql.conf
"$I/pg_ctl" -D $D -l $D/log -w start >/dev/null
q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; }
q -c "CREATE DATABASE src" -c "CREATE DATABASE dst"
q -d src <<'EOF'
CREATE DOMAIN d_r AS int4range;
CREATE DOMAIN d_arr AS int4[];
CREATE TABLE t (dr d_r, darr d_arr);
INSERT INTO t SELECT int4range(g % 97, g % 97 + 5), ARRAY[g % 30, g % 7] FROM generate_series(1, 5000) g;
-- s_arr: only the array expression. s_mix: the same plus a range one.
CREATE STATISTICS s_arr ON ((darr || '{}'::int4[])::d_arr), (dr IS NULL) FROM t;
CREATE STATISTICS s_mix ON ((darr || '{}'::int4[])::d_arr), (range_merge(dr, dr)::d_r) FROM t;
ANALYZE t;
EOF
"$I/pg_dump" -h /tmp -p $P -U postgres --schema-only src | q -d dst >/dev/null 2>&1
"$I/pg_dump" -h /tmp -p $P -U postgres --statistics-only src | q -d dst 2>&1 >/dev/null | grep WARNING
chk="SELECT statistics_name, left(expr, 40), null_frac IS NOT NULL AS has_null_frac,
most_common_elems IS NOT NULL AS has_mcelem
FROM pg_stats_ext_exprs WHERE tablename = 't' ORDER BY 1, 2"
echo "--- src"; q -d src -c "$chk"
echo "--- dst"; q -d dst -c "$chk"
"$I/pg_ctl" -D $D -m fast -w stop >/dev/null
rm -rf $D
===== ./one_expr.sh head =====
WARNING: column "dr" is not a range type
WARNING: could not parse "exprs": invalid data in expression -2
--- src
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t
s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f
--- dst
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|f|f
s_mix|(range_merge((dr)::int4range, (dr)::int4|f|f
===== ./one_expr.sh v2 =====
--- src
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t
s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f
--- dst
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t
s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f
===== make check =====
master + v2: All 239 tests passed.
REL_19_STABLE + v2: All 239 tests passed.
master + only the v2 test changes: stats_import fails; regression.diffs:
diff -U3 /home/manu/pg19715/src-head/src/test/regress/expected/stats_import.out /home/manu/pg19715/b-head/src/test/regress/results/stats_import.out
--- /home/manu/pg19715/src-head/src/test/regress/expected/stats_import.out 2026-09-24 22:01:12.592950036 -0300
+++ /home/manu/pg19715/b-head/src/test/regress/results/stats_import.out 2026-09-24 22:01:23.325666884 -0300
@@ -1547,9 +1547,11 @@
'range_empty_frac', '0'::real,
'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
);
+WARNING: column "drange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
pg_restore_attribute_stats
----------------------------
- t
+ f
(1 row)
SELECT *
@@ -1558,9 +1560,9 @@
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)"}
+ 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 | | | | | | | | | |
(1 row)
-- ok: range stats for a domain over a multirange type.
@@ -1573,9 +1575,11 @@
'range_empty_frac', '0'::real,
'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
);
+WARNING: column "dmrange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
pg_restore_attribute_stats
----------------------------
- t
+ f
(1 row)
SELECT *
@@ -1584,9 +1588,9 @@
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)"}
+ 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 | | | | | | | | | |
(1 row)
-- warn: multirange values in the bounds histogram of a domain. These
@@ -1600,8 +1604,8 @@
'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.
+WARNING: column "dmrange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
pg_restore_attribute_stats
----------------------------
f
@@ -1635,17 +1639,148 @@
WHERE s.schemaname = 'stats_import'
AND s.tablename = 'test_dom'
ORDER BY s.attname, s.inherited;
+WARNING: column "drange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
+WARNING: column "dmrange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
attname | inherited | r
---------+-----------+---
- dmrange | f | t
- drange | f | t
+ dmrange | f | f
+ drange | f | f
id | f | t
(3 rows)
SELECT relname, (stats).*
FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone')
\gx
-(0 rows)
+-[ RECORD 1 ]--------------------------------
+relname | test_dom
+attname | drange
+stainherit | f
+stanullfrac | 0
+stawidth | 14
+stadistinct | -1
+stakind1 | 7
+stakind2 | 6
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 672
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 | {0}
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 | {"[1,3)","[5,9)","[11,15)"}
+sv2 | {2,4,4}
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 2 ]--------------------------------
+relname | test_dom
+attname | dmrange
+stainherit | f
+stanullfrac | 0
+stawidth | 45
+stadistinct | -1
+stakind1 | 7
+stakind2 | 6
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 672
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 | {0}
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 | {"[1,30)","[11,30)","[21,130)"}
+sv2 | {19,29,109}
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 3 ]--------------------------------
+relname | test_dom_clone
+attname | dmrange
+stainherit | f
+stanullfrac | 0
+stawidth | 45
+stadistinct | -1
+stakind1 | 0
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 |
+sv2 |
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 4 ]--------------------------------
+relname | test_dom_clone
+attname | drange
+stainherit | f
+stanullfrac | 0
+stawidth | 14
+stadistinct | -1
+stakind1 | 0
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 |
+sv2 |
+sv3 |
+sv4 |
+sv5 |
-- test for a domain over tsvector.
CREATE DOMAIN stats_import.dom_tsvector AS tsvector;
@@ -1665,9 +1800,11 @@
'most_common_elems', '{brown,fox,quick}'::text,
'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]);
+WARNING: could not determine element type of column "v"
+DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
pg_restore_attribute_stats
----------------------------
- t
+ f
(1 row)
SELECT *
@@ -1676,9 +1813,9 @@
AND tablename = 'test_dom_ts'
AND inherited = false
AND attname = 'v';
- 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_ts | v | f | 0 | 0 | 0 | | | | | {brown,fox,quick} | {0.3,0.2,0.2,0.3,0} | {4,4,4,4,4,4,4,4,4,4} | | |
+ 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_ts | v | f | 0 | 0 | 0 | | | | | | | | | |
(1 row)
--
@@ -1709,16 +1846,81 @@
WHERE s.schemaname = 'stats_import'
AND s.tablename = 'test_dom_ts'
ORDER BY s.attname, s.inherited;
+WARNING: could not determine element type of column "v"
+DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
attname | inherited | r
---------+-----------+---
id | f | t
- v | f | t
+ v | f | f
(2 rows)
SELECT relname, (stats).*
FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_clone')
\gx
-(0 rows)
+-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------
+relname | test_dom_ts
+attname | v
+stainherit | f
+stanullfrac | 0
+stawidth | 55
+stadistinct | -1
+stakind1 | 4
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 98
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 100
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 | {0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,1,1,1,0.05,1}
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,fox,brown,quick}
+sv2 |
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------
+relname | test_dom_ts_clone
+attname | v
+stainherit | f
+stanullfrac | 0
+stawidth | 55
+stadistinct | -1
+stakind1 | 0
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 |
+sv2 |
+sv3 |
+sv4 |
+sv5 |
--
-- Test the ability to exactly copy data from one table to an identical table,
@@ -2983,9 +3185,10 @@
{"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.
+WARNING: could not parse "exprs": invalid data in expression -1
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
+WARNING: could not parse "exprs": invalid data in expression -2
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
pg_restore_extended_stats
---------------------------
f
@@ -3004,9 +3207,13 @@
{"range_length_histogram": "{29,29,109}",
"range_empty_frac": "0",
"range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb);
+WARNING: could not parse "exprs": invalid data in expression -1
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
+WARNING: could not parse "exprs": invalid data in expression -2
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
pg_restore_extended_stats
---------------------------
- t
+ f
(1 row)
SELECT e.expr, e.range_length_histogram, e.range_empty_frac,
@@ -3018,14 +3225,14 @@
\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)"}
+range_length_histogram |
+range_empty_frac |
+range_bounds_histogram |
-[ 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)"}
+range_length_histogram |
+range_empty_frac |
+range_bounds_histogram |
-- Check import of MCELEM stats for an expression whose type is a domain
-- over tsvector.
@@ -3041,9 +3248,10 @@
'exprs', '[{"most_common_elems": "{brown,fox,quick}",
"most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}",
"elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb);
+WARNING: could not parse "exprs": invalid element type in expression -1
pg_restore_extended_stats
---------------------------
- t
+ f
(1 row)
SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs,
@@ -3055,9 +3263,9 @@
\gx
-[ RECORD 1 ]----------+--------------------------------------------------
expr | (strip((v)::tsvector))::stats_import.dom_tsvector
-most_common_elems | {brown,fox,quick}
-most_common_elem_freqs | {0.3,0.2,0.2,0.3,0}
-elem_count_histogram | {4,4,4,4,4,4,4,4,4,4}
+most_common_elems |
+most_common_elem_freqs |
+elem_count_histogram |
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
Attachments:
[text/plain] nocfbot-19715-v2-roundtrip.txt (34.3K, ../../179029828172.110035.12127078526565339171@gmail.com/2-nocfbot-19715-v2-roundtrip.txt)
download | inline:
Review of v2 for bug #19715: statistics round trip through pg_dump
Builds: master 2c10c2ce4d7, REL_19_STABLE 2c5cd772b90, REL_18_STABLE 66d1de70c84
(--enable-cassert --enable-debug); v2 applied with git am on master and REL_19.
"===== roundtrip_setup.sql ====="
-- Source database for the pg_dump --statistics round trip.
-- One column per case: the base type, a domain over it, a domain over that
-- domain, and a domain with a CHECK constraint, for each kind of statistics
-- that depends on the type (range, multirange, tsvector, array elements).
-- Plus an array of a domain, a range with its own collation, and a domain
-- over a collatable type.
CREATE DOMAIN d_r AS int4range;
CREATE DOMAIN dd_r AS d_r;
CREATE DOMAIN dc_r AS int4range CHECK (NOT isempty(VALUE) OR VALUE IS NULL OR true);
CREATE DOMAIN d_mr AS int4multirange;
CREATE DOMAIN dd_mr AS d_mr;
CREATE DOMAIN d_tsv AS tsvector;
CREATE DOMAIN dd_tsv AS d_tsv;
CREATE DOMAIN d_arr AS int4[];
CREATE DOMAIN dd_arr AS d_arr;
CREATE DOMAIN d_int AS int4;
CREATE TYPE crange AS RANGE (subtype = text, collation = "C");
CREATE DOMAIN d_crange AS crange;
CREATE DOMAIN d_ctext AS text COLLATE "C";
CREATE TABLE t (
r int4range, dr d_r, ddr dd_r, dcr dc_r,
mr int4multirange, dmr d_mr, ddmr dd_mr,
tsv tsvector, dtsv d_tsv, ddtsv dd_tsv,
arr int4[], darr d_arr, ddarr dd_arr,
arrd d_int[],
cr crange, dcrange d_crange,
ctext d_ctext
);
INSERT INTO t
SELECT int4range(g % 97, g % 97 + 1 + g % 13),
int4range(g % 97, g % 97 + 1 + g % 13),
int4range(g % 97, g % 97 + 1 + g % 13),
CASE WHEN g % 10 = 0 THEN 'empty'::int4range ELSE int4range(g % 89, g % 89 + 3) END,
int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)),
int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)),
int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)),
to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3),
to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3),
to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3),
ARRAY[g % 30, g % 7, g % 3],
ARRAY[g % 30, g % 7, g % 3],
ARRAY[g % 30, g % 7, g % 3],
ARRAY[g % 30, g % 7, g % 3]::d_int[],
crange('k' || g % 60, 'k' || g % 60 || 'z'),
crange('k' || g % 60, 'k' || g % 60 || 'z'),
'v' || g % 300
FROM generate_series(1, 5000) g;
-- Expression statistics over the same domains (extended stats restore path).
-- A bare cast of the column folds back into the column, so each expression
-- goes through a function first, as in the tests of v2.
CREATE STATISTICS t_exprs ON
(range_merge(dr, dr)::d_r),
(range_merge(ddr, ddr)::dd_r),
((dmr + '{}'::int4multirange)::d_mr),
((ddmr + '{}'::int4multirange)::dd_mr),
(strip(dtsv)::d_tsv),
(strip(ddtsv)::dd_tsv),
((darr || '{}'::int4[])::d_arr),
(range_merge(dcrange, dcrange)::d_crange)
FROM t;
ANALYZE t;
"===== roundtrip.sh ====="
#!/bin/bash
# Round trip of statistics through pg_dump --statistics-only for the columns
# in roundtrip_setup.sql, on each build. For every column (pg_stats) and
# every statistics expression (pg_stats_ext_exprs), each field is compared
# between the source database and the restored one. Prints, per build, the
# restore WARNINGs and the fields that did not come through.
# roundtrip.sh [build...] (default: 18 head v2 19v2)
set -u
A=$(cd "$(dirname "$0")" && pwd)
BUILDS=${*:-18 head v2 19v2}
OUT=$A/roundtrip.out; : > $OUT
for B in $BUILDS; do
I=$HOME/pg19715/i-$B/bin
D=$(mktemp -d /tmp/claude-1000/rt.XXXX); P=55460
"$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null
printf "port = $P\nunix_socket_directories = '/tmp'\n" >> $D/postgresql.conf
"$I/pg_ctl" -D $D -l $D/log -w start >/dev/null
q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; }
q -c "CREATE DATABASE src" -c "CREATE DATABASE dst"
q -d src -v ON_ERROR_STOP=1 -f $A/roundtrip_setup.sql > $D/setup.log 2>&1 || { echo "$B: setup failed"; cat $D/setup.log; }
"$I/pg_dump" -h /tmp -p $P -U postgres --schema-only src | q -d dst > /dev/null 2>&1
"$I/pg_dump" -h /tmp -p $P -U postgres --statistics-only src > $D/stats.sql
q -d dst -f $D/stats.sql > $D/restore.out 2> $D/restore.err
# One line per (object, field, value); objects are columns and expressions.
flat="
SELECT 'col ' || attname AS obj, f.key, f.value
FROM pg_stats s, jsonb_each_text(to_jsonb(s) - 'tableid' - 'schemaname' - 'tablename' - 'attname' - 'inherited') f
WHERE tablename = 't'
UNION ALL
SELECT 'expr ' || expr, f.key, f.value
FROM pg_stats_ext_exprs s, jsonb_each_text(to_jsonb(s) - 'tableid' - 'schemaname' - 'tablename'
- 'statistics_schemaname' - 'statistics_name'
- 'statistics_owner' - 'statistics_id' - 'expr' - 'inherited') f
WHERE tablename = 't'
ORDER BY 1, 2"
q -d src -F $'\t' -c "$flat" > $D/src.tsv 2>/dev/null
q -d dst -F $'\t' -c "$flat" > $D/dst.tsv 2>/dev/null
{
echo "===== $B: $("$I/postgres" --version)"
echo "restore WARNINGs: $(grep -c WARNING $D/restore.err)"
grep -A1 WARNING $D/restore.err | grep -v '^--$' | sed 's/^psql:[^ ]* //' | sort | uniq -c | sed 's/^/ /'
echo "fields that differ after the round trip (object: field):"
diff <(sort $D/src.tsv) <(sort $D/dst.tsv) | grep '^<' | cut -f1,2 | sed 's/^< / /; s/\t/: /' | sort -u
echo "objects with statistics: src $(cut -f1 $D/src.tsv | sort -u | wc -l), dst $(cut -f1 $D/dst.tsv | sort -u | wc -l)"
echo
} | tee -a $OUT
"$I/pg_ctl" -D $D -m fast -w stop >/dev/null
rm -rf $D
done
===== ./roundtrip.sh 18 head v2 19v2 =====
===== 18: postgres (PostgreSQL) 18.6
restore WARNINGs: 8
2 DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
6 DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
1 WARNING: column "dcrange" is not a range type
1 WARNING: column "dcr" is not a range type
1 WARNING: column "ddmr" is not a range type
1 WARNING: column "ddr" is not a range type
1 WARNING: column "dmr" is not a range type
1 WARNING: column "dr" is not a range type
1 WARNING: could not determine element type of column "ddtsv"
1 WARNING: could not determine element type of column "dtsv"
fields that differ after the round trip (object: field):
col dcrange: range_bounds_histogram
col dcrange: range_empty_frac
col dcrange: range_length_histogram
col dcr: range_bounds_histogram
col dcr: range_empty_frac
col dcr: range_length_histogram
col ddmr: range_bounds_histogram
col ddmr: range_empty_frac
col ddmr: range_length_histogram
col ddr: range_bounds_histogram
col ddr: range_empty_frac
col ddr: range_length_histogram
col ddtsv: most_common_elem_freqs
col ddtsv: most_common_elems
col dmr: range_bounds_histogram
col dmr: range_empty_frac
col dmr: range_length_histogram
col dr: range_bounds_histogram
col dr: range_empty_frac
col dr: range_length_histogram
col dtsv: most_common_elem_freqs
col dtsv: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: avg_width
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: correlation
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: elem_count_histogram
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: histogram_bounds
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elem_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_vals
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: n_distinct
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: null_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: avg_width
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: n_distinct
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: null_frac
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: avg_width
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: n_distinct
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: null_frac
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: avg_width
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: n_distinct
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: null_frac
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: avg_width
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: n_distinct
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: null_frac
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: avg_width
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: n_distinct
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: null_frac
expr (strip((ddtsv)::tsvector))::dd_tsv: avg_width
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elem_freqs
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elems
expr (strip((ddtsv)::tsvector))::dd_tsv: n_distinct
expr (strip((ddtsv)::tsvector))::dd_tsv: null_frac
expr (strip((dtsv)::tsvector))::d_tsv: avg_width
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elem_freqs
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elems
expr (strip((dtsv)::tsvector))::d_tsv: n_distinct
expr (strip((dtsv)::tsvector))::d_tsv: null_frac
objects with statistics: src 25, dst 25
===== head: postgres (PostgreSQL) 20devel
restore WARNINGs: 15
2 DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
6 DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
5 HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
1 WARNING: column "dcrange" is not a range type
1 WARNING: column "dcr" is not a range type
1 WARNING: column "ddmr" is not a range type
1 WARNING: column "ddr" is not a range type
1 WARNING: column "dmr" is not a range type
1 WARNING: column "dr" is not a range type
1 WARNING: could not determine element type of column "ddtsv"
1 WARNING: could not determine element type of column "dtsv"
1 WARNING: could not parse "exprs": invalid data in expression -1
1 WARNING: could not parse "exprs": invalid data in expression -2
1 WARNING: could not parse "exprs": invalid data in expression -3
1 WARNING: could not parse "exprs": invalid data in expression -4
1 WARNING: could not parse "exprs": invalid data in expression -8
1 WARNING: could not parse "exprs": invalid element type in expression -5
1 WARNING: could not parse "exprs": invalid element type in expression -6
fields that differ after the round trip (object: field):
col dcrange: range_bounds_histogram
col dcrange: range_empty_frac
col dcrange: range_length_histogram
col dcr: range_bounds_histogram
col dcr: range_empty_frac
col dcr: range_length_histogram
col ddmr: range_bounds_histogram
col ddmr: range_empty_frac
col ddmr: range_length_histogram
col ddr: range_bounds_histogram
col ddr: range_empty_frac
col ddr: range_length_histogram
col ddtsv: most_common_elem_freqs
col ddtsv: most_common_elems
col dmr: range_bounds_histogram
col dmr: range_empty_frac
col dmr: range_length_histogram
col dr: range_bounds_histogram
col dr: range_empty_frac
col dr: range_length_histogram
col dtsv: most_common_elem_freqs
col dtsv: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: avg_width
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: correlation
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: elem_count_histogram
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: histogram_bounds
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elem_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elems
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_freqs
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_vals
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: n_distinct
expr (((darr)::integer[] || '{}'::integer[]))::d_arr: null_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: avg_width
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: n_distinct
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: null_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_bounds_histogram
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_empty_frac
expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_length_histogram
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: avg_width
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: n_distinct
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: null_frac
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_bounds_histogram
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_empty_frac
expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_length_histogram
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: avg_width
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: n_distinct
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: null_frac
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_bounds_histogram
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_empty_frac
expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_length_histogram
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: avg_width
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: n_distinct
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: null_frac
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_bounds_histogram
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_empty_frac
expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_length_histogram
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: avg_width
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: n_distinct
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: null_frac
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_bounds_histogram
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_empty_frac
expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_length_histogram
expr (strip((ddtsv)::tsvector))::dd_tsv: avg_width
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elem_freqs
expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elems
expr (strip((ddtsv)::tsvector))::dd_tsv: n_distinct
expr (strip((ddtsv)::tsvector))::dd_tsv: null_frac
expr (strip((dtsv)::tsvector))::d_tsv: avg_width
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elem_freqs
expr (strip((dtsv)::tsvector))::d_tsv: most_common_elems
expr (strip((dtsv)::tsvector))::d_tsv: n_distinct
expr (strip((dtsv)::tsvector))::d_tsv: null_frac
objects with statistics: src 25, dst 25
===== v2: postgres (PostgreSQL) 20devel
restore WARNINGs: 0
fields that differ after the round trip (object: field):
objects with statistics: src 25, dst 25
===== 19v2: postgres (PostgreSQL) 19beta4
restore WARNINGs: 0
fields that differ after the round trip (object: field):
objects with statistics: src 25, dst 25
"===== one_expr.sh ====="
#!/bin/bash
# On a build without the fix: is the d_arr expression lost by itself, or
# only when it shares a statistics object with a rejected expression?
# one_expr.sh [build] (default: head)
set -u
B=${1:-head}
I=$HOME/pg19715/i-$B/bin
D=$(mktemp -d /tmp/claude-1000/oe.XXXX); P=55462
"$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null
printf "port = $P\nunix_socket_directories = '/tmp'\n" >> $D/postgresql.conf
"$I/pg_ctl" -D $D -l $D/log -w start >/dev/null
q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; }
q -c "CREATE DATABASE src" -c "CREATE DATABASE dst"
q -d src <<'EOF'
CREATE DOMAIN d_r AS int4range;
CREATE DOMAIN d_arr AS int4[];
CREATE TABLE t (dr d_r, darr d_arr);
INSERT INTO t SELECT int4range(g % 97, g % 97 + 5), ARRAY[g % 30, g % 7] FROM generate_series(1, 5000) g;
-- s_arr: only the array expression. s_mix: the same plus a range one.
CREATE STATISTICS s_arr ON ((darr || '{}'::int4[])::d_arr), (dr IS NULL) FROM t;
CREATE STATISTICS s_mix ON ((darr || '{}'::int4[])::d_arr), (range_merge(dr, dr)::d_r) FROM t;
ANALYZE t;
EOF
"$I/pg_dump" -h /tmp -p $P -U postgres --schema-only src | q -d dst >/dev/null 2>&1
"$I/pg_dump" -h /tmp -p $P -U postgres --statistics-only src | q -d dst 2>&1 >/dev/null | grep WARNING
chk="SELECT statistics_name, left(expr, 40), null_frac IS NOT NULL AS has_null_frac,
most_common_elems IS NOT NULL AS has_mcelem
FROM pg_stats_ext_exprs WHERE tablename = 't' ORDER BY 1, 2"
echo "--- src"; q -d src -c "$chk"
echo "--- dst"; q -d dst -c "$chk"
"$I/pg_ctl" -D $D -m fast -w stop >/dev/null
rm -rf $D
===== ./one_expr.sh head =====
WARNING: column "dr" is not a range type
WARNING: could not parse "exprs": invalid data in expression -2
--- src
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t
s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f
--- dst
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|f|f
s_mix|(range_merge((dr)::int4range, (dr)::int4|f|f
===== ./one_expr.sh v2 =====
--- src
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t
s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f
--- dst
s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t
s_arr|(dr IS NULL)|t|f
s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t
s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f
===== make check =====
master + v2: All 239 tests passed.
REL_19_STABLE + v2: All 239 tests passed.
master + only the v2 test changes: stats_import fails; regression.diffs:
diff -U3 /home/manu/pg19715/src-head/src/test/regress/expected/stats_import.out /home/manu/pg19715/b-head/src/test/regress/results/stats_import.out
--- /home/manu/pg19715/src-head/src/test/regress/expected/stats_import.out 2026-09-24 22:01:12.592950036 -0300
+++ /home/manu/pg19715/b-head/src/test/regress/results/stats_import.out 2026-09-24 22:01:23.325666884 -0300
@@ -1547,9 +1547,11 @@
'range_empty_frac', '0'::real,
'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text
);
+WARNING: column "drange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
pg_restore_attribute_stats
----------------------------
- t
+ f
(1 row)
SELECT *
@@ -1558,9 +1560,9 @@
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)"}
+ 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 | | | | | | | | | |
(1 row)
-- ok: range stats for a domain over a multirange type.
@@ -1573,9 +1575,11 @@
'range_empty_frac', '0'::real,
'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text
);
+WARNING: column "dmrange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
pg_restore_attribute_stats
----------------------------
- t
+ f
(1 row)
SELECT *
@@ -1584,9 +1588,9 @@
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)"}
+ 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 | | | | | | | | | |
(1 row)
-- warn: multirange values in the bounds histogram of a domain. These
@@ -1600,8 +1604,8 @@
'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.
+WARNING: column "dmrange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
pg_restore_attribute_stats
----------------------------
f
@@ -1635,17 +1639,148 @@
WHERE s.schemaname = 'stats_import'
AND s.tablename = 'test_dom'
ORDER BY s.attname, s.inherited;
+WARNING: column "drange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
+WARNING: column "dmrange" is not a range type
+DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM.
attname | inherited | r
---------+-----------+---
- dmrange | f | t
- drange | f | t
+ dmrange | f | f
+ drange | f | f
id | f | t
(3 rows)
SELECT relname, (stats).*
FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone')
\gx
-(0 rows)
+-[ RECORD 1 ]--------------------------------
+relname | test_dom
+attname | drange
+stainherit | f
+stanullfrac | 0
+stawidth | 14
+stadistinct | -1
+stakind1 | 7
+stakind2 | 6
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 672
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 | {0}
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 | {"[1,3)","[5,9)","[11,15)"}
+sv2 | {2,4,4}
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 2 ]--------------------------------
+relname | test_dom
+attname | dmrange
+stainherit | f
+stanullfrac | 0
+stawidth | 45
+stadistinct | -1
+stakind1 | 7
+stakind2 | 6
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 672
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 | {0}
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 | {"[1,30)","[11,30)","[21,130)"}
+sv2 | {19,29,109}
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 3 ]--------------------------------
+relname | test_dom_clone
+attname | dmrange
+stainherit | f
+stanullfrac | 0
+stawidth | 45
+stadistinct | -1
+stakind1 | 0
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 |
+sv2 |
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 4 ]--------------------------------
+relname | test_dom_clone
+attname | drange
+stainherit | f
+stanullfrac | 0
+stawidth | 14
+stadistinct | -1
+stakind1 | 0
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 |
+sv2 |
+sv3 |
+sv4 |
+sv5 |
-- test for a domain over tsvector.
CREATE DOMAIN stats_import.dom_tsvector AS tsvector;
@@ -1665,9 +1800,11 @@
'most_common_elems', '{brown,fox,quick}'::text,
'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[],
'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]);
+WARNING: could not determine element type of column "v"
+DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
pg_restore_attribute_stats
----------------------------
- t
+ f
(1 row)
SELECT *
@@ -1676,9 +1813,9 @@
AND tablename = 'test_dom_ts'
AND inherited = false
AND attname = 'v';
- 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_ts | v | f | 0 | 0 | 0 | | | | | {brown,fox,quick} | {0.3,0.2,0.2,0.3,0} | {4,4,4,4,4,4,4,4,4,4} | | |
+ 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_ts | v | f | 0 | 0 | 0 | | | | | | | | | |
(1 row)
--
@@ -1709,16 +1846,81 @@
WHERE s.schemaname = 'stats_import'
AND s.tablename = 'test_dom_ts'
ORDER BY s.attname, s.inherited;
+WARNING: could not determine element type of column "v"
+DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST.
attname | inherited | r
---------+-----------+---
id | f | t
- v | f | t
+ v | f | f
(2 rows)
SELECT relname, (stats).*
FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_clone')
\gx
-(0 rows)
+-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------
+relname | test_dom_ts
+attname | v
+stainherit | f
+stanullfrac | 0
+stawidth | 55
+stadistinct | -1
+stakind1 | 4
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 98
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 100
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 | {0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,1,1,1,0.05,1}
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,fox,brown,quick}
+sv2 |
+sv3 |
+sv4 |
+sv5 |
+-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------
+relname | test_dom_ts_clone
+attname | v
+stainherit | f
+stanullfrac | 0
+stawidth | 55
+stadistinct | -1
+stakind1 | 0
+stakind2 | 0
+stakind3 | 0
+stakind4 | 0
+stakind5 | 0
+staop1 | 0
+staop2 | 0
+staop3 | 0
+staop4 | 0
+staop5 | 0
+stacoll1 | 0
+stacoll2 | 0
+stacoll3 | 0
+stacoll4 | 0
+stacoll5 | 0
+stanumbers1 |
+stanumbers2 |
+stanumbers3 |
+stanumbers4 |
+stanumbers5 |
+sv1 |
+sv2 |
+sv3 |
+sv4 |
+sv5 |
--
-- Test the ability to exactly copy data from one table to an identical table,
@@ -2983,9 +3185,10 @@
{"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.
+WARNING: could not parse "exprs": invalid data in expression -1
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
+WARNING: could not parse "exprs": invalid data in expression -2
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
pg_restore_extended_stats
---------------------------
f
@@ -3004,9 +3207,13 @@
{"range_length_histogram": "{29,29,109}",
"range_empty_frac": "0",
"range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb);
+WARNING: could not parse "exprs": invalid data in expression -1
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
+WARNING: could not parse "exprs": invalid data in expression -2
+HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type.
pg_restore_extended_stats
---------------------------
- t
+ f
(1 row)
SELECT e.expr, e.range_length_histogram, e.range_empty_frac,
@@ -3018,14 +3225,14 @@
\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)"}
+range_length_histogram |
+range_empty_frac |
+range_bounds_histogram |
-[ 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)"}
+range_length_histogram |
+range_empty_frac |
+range_bounds_histogram |
-- Check import of MCELEM stats for an expression whose type is a domain
-- over tsvector.
@@ -3041,9 +3248,10 @@
'exprs', '[{"most_common_elems": "{brown,fox,quick}",
"most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}",
"elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb);
+WARNING: could not parse "exprs": invalid element type in expression -1
pg_restore_extended_stats
---------------------------
- t
+ f
(1 row)
SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs,
@@ -3055,9 +3263,9 @@
\gx
-[ RECORD 1 ]----------+--------------------------------------------------
expr | (strip((v)::tsvector))::stats_import.dom_tsvector
-most_common_elems | {brown,fox,quick}
-most_common_elem_freqs | {0.3,0.2,0.2,0.3,0}
-elem_count_histogram | {4,4,4,4,4,4,4,4,4,4}
+most_common_elems |
+most_common_elem_freqs |
+elem_count_histogram |
-- Incorrect extended stats kind, exprs not supported
SELECT pg_catalog.pg_restore_extended_stats(
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 23:46 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-25 01:04 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Manu <manuelreyesbravo@gmail.com>
@ 2026-09-25 19:35 ` Corey Huinker <corey.huinker@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Corey Huinker @ 2026-09-25 19:35 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; jian he <jian.universality@gmail.com>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
>
> One thing outside this bug, about why all 8 expressions were lost.
> import_expressions() stores a failed expression as NULL and keeps the
> others, per its comments, but extended_statistics_update() then drops
> the whole stxdexpr array when exprs_is_perfect is false. With one
> statistics object on the d_arr expression plus a domain-over-range
> one, master restores nothing for either; with the d_arr expression
> next to a plain one, it is restored. Is dropping all of them
> intended? I may be missing the reason. v2 removes the cause here,
> so this only matters for other rejections.
>
It's an unfortunate consequence of how expression stats are stored.
The pg_statistic_ext_data.stxdexpr array must be the same length as the
number of negative elements in pg_statistic_ext.stxkeys... so a stxkeys of
[2,4,-1,-3,-4] correlates stats from the attnum=2, attnum=4, and the first,
third, and fourth expressions defined for this very complicated and
hypothetical extended statistic.
In addition to being difficult to unpack (you can't just skip to the Nth
element, you have to count the expression and then stop on the Nth one)
[1], it also presents a problem when building such an array, as we either
could not store a NULL value for an element in the stxdexprs array or we
could not then reliably re-extract that data [2], or both, and in either
case the stats were incomplete, thus necessitating that they be rebuilt
post-upgrade anyway, reducing the benefit of making them
slightly-less-incomplete [3].
[1] It should be noted that other work on Join Statistics in v20 is
proposing to change this format, which will make regular stats import
marginally simpler.
[2] a good starting point in thread [4]:
https://www.postgresql.org/message-id/CADkLM=cPB_V+oV5d+5bfZGA_1Loa7iMmqXWj+SM-YGzxoUYrcQ@mail.gmail...
[3] the whole thread:
https://www.postgresql.org/message-id/flat/CADkLM%3DcPB_V%2BoV5d%2B5bfZGA_1Loa7iMmqXWj%2BSM-YGzxoUYr...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 23:46 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
@ 2026-09-25 19:45 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-25 23:53 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 14+ messages in thread
From: Corey Huinker @ 2026-09-25 19:45 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: jian he <jian.universality@gmail.com>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
>
> With all that in mind, I am getting down to the point that we should
> accept that the early stages of attribute and extended stats restore
> should try to fetch the typcache data of the base types, then roll it
> around. This leads to the attached, taking care of the range,
> multirange and tsvector cases.
>
This is a good move, as it further opens the door to making stats import
work with custom stakinds, but a lot of things need to happen [1][2][3]
before we can even attempt that.
> I'd certainly welcome more eyes here. Please note that this applies
> on HEAD cleanly, and should mostly apply cleanly on v19. The v18
> flavor would be much more localized, of course.
>
It does apply cleanly to 19, and yes, the v18 is a bigger change, and will
look much more like my patch unless we decide to backport
statatt_get_type()...which probably isn't worth it.
[1] pg_stats needs a way to expose non-builtin stakinds
[2] pg_type needs to add an oid for typimport(), or the custom basel/elem
oid and collation stuff would have to be calculated in the existing custom
FOO_typanalyze()
[3] pg_dump needs to be made aware of this
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:44 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-24 23:46 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Michael Paquier <michael@paquier.xyz>
2026-09-25 19:45 ` Re: BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange Corey Huinker <corey.huinker@gmail.com>
@ 2026-09-25 23:53 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 14+ messages in thread
From: Michael Paquier @ 2026-09-25 23:53 UTC (permalink / raw)
To: Corey Huinker <corey.huinker@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Fri, Sep 25, 2026 at 03:45:10PM -0400, Corey Huinker wrote:
> This is a good move, as it further opens the door to making stats import
> work with custom stakinds, but a lot of things need to happen [1][2][3]
> before we can even attempt that.
I don't know much about these parts, only about the problem at hand,
which is enough for me for the moment.. :)
> It does apply cleanly to 19, and yes, the v18 is a bigger change, and will
> look much more like my patch unless we decide to backport
> statatt_get_type()...which probably isn't worth it.
The backpatch of v18 is not that bad, isolated within
attribute_stats.c, so the ABI argument does not apply.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arcJiWiWBrkGn2Ig@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2026-09-25 23:53 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-22 16:10 BUG #19715: pg_restore_attribute_stats() rejects range statistics for a domain over int4multirange PG Bug reporting form <noreply@postgresql.org>
2026-09-23 19:02 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 04:22 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 05:56 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:13 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:34 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-24 06:16 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 06:44 ` jian he <jian.universality@gmail.com>
2026-09-24 22:52 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 23:46 ` Michael Paquier <michael@paquier.xyz>
2026-09-25 01:04 ` Manu <manuelreyesbravo@gmail.com>
2026-09-25 19:35 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-25 19:45 ` Corey Huinker <corey.huinker@gmail.com>
2026-09-25 23:53 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox