v1-0003-Reorganize-pg_stats_ext_exprs-set-difference-test.patch

From 4caff6015dd07b75e222389fee8efa641092df0b Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Sat, 7 Mar 2026 01:02:33 -0500
Subject: [PATCH v1 3/3] Reorganize pg_stats_ext_exprs set difference tests in
 stats_import

This is a follow-on patch to the previous patch that dealt with
pg_statistic rows and set difference comparisons, but instead deals with
extended statistics expressions (pg_stats_ext_exprs).

The pattern (create view for its type, _flat() function,
_set_difference() function) is otherwise identical.
---
 src/test/regress/expected/stats_import.out | 191 +++++++--------------
 src/test/regress/sql/stats_import.sql      | 174 ++++++-------------
 2 files changed, 111 insertions(+), 254 deletions(-)

diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 5fb07134335..9e1d39557e5 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -93,6 +93,54 @@ BEGIN ATOMIC
   SELECT b AS relname, b_minus_a::stats_import.pg_stats_ext_flat_t
   FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
 END;
+-- Test to detect any new columns added to pg_stats_ext_exprs, which may in turn
+-- need to be added to pg_stats_ext_exprs_flat()
+SELECT COUNT(*)
+FROM pg_attribute
+WHERE attrelid = 'pg_catalog.pg_stats_ext_exprs'::regclass;
+ count 
+-------
+    20
+(1 row)
+
+-- Create a view that is used purely for the type
+CREATE VIEW stats_import.pg_stats_ext_exprs_flat_t AS
+  SELECT inherited, null_frac, avg_width, n_distinct,
+       most_common_vals::text AS most_common_vals,
+       most_common_freqs, histogram_bounds::text AS histogram_bounds,
+       correlation, most_common_elems::text AS most_common_elems,
+       most_common_elem_freqs, elem_count_histogram,
+       range_length_histogram::text AS range_length_histogram,
+       range_empty_frac, range_bounds_histogram::text AS range_bounds_histogram
+  FROM pg_stats_ext_exprs AS n
+  WHERE FALSE;
+-- Function to get only the set-diff comparable parts of pg_stats_ext_exprs
+CREATE FUNCTION stats_import.pg_stats_ext_exprs_flat(p_statname text)
+RETURNS SETOF stats_import.pg_stats_ext_exprs_flat_t
+BEGIN ATOMIC
+  SELECT inherited, null_frac, avg_width, n_distinct,
+       most_common_vals::text AS most_common_vals,
+       most_common_freqs, histogram_bounds::text AS histogram_bounds,
+       correlation, most_common_elems::text AS most_common_elems,
+       most_common_elem_freqs, elem_count_histogram,
+       range_length_histogram::text AS range_length_histogram,
+       range_empty_frac, range_bounds_histogram::text AS range_bounds_histogram
+  FROM pg_stats_ext_exprs AS n
+  WHERE n.statistics_schemaname = 'stats_import' AND
+    n.statistics_name = p_statname;
+END;
+-- pg_stats_ext_exprs set diff function
+CREATE FUNCTION stats_import.pg_stats_ext_exprs_set_difference(a text, b text)
+RETURNS TABLE (statname text, stats stats_import.pg_stats_ext_exprs_flat_t)
+BEGIN ATOMIC
+  WITH aset AS (SELECT * FROM stats_import.pg_stats_ext_exprs_flat(a)),
+       bset AS (SELECT * FROM stats_import.pg_stats_ext_exprs_flat(b))
+  SELECT a AS relname, a_minus_b::stats_import.pg_stats_ext_exprs_flat_t
+  FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b
+  UNION ALL
+  SELECT b AS relname, b_minus_a::stats_import.pg_stats_ext_exprs_flat_t
+  FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
+END;
 --
 -- Schema setup.
 --
@@ -3161,72 +3209,9 @@ FROM stats_import.pg_stats_ext_set_difference('test_stat', 'test_stat_clone')
 \gx
 (0 rows)
 
--- Set difference for exprs: old MINUS new.
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat'
-EXCEPT
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone';
- 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 
------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
-(0 rows)
-
--- Set difference for exprs: new MINUS old.
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat';
- 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 
------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_exprs_set_difference('test_stat', 'test_stat_clone')
+\gx
 (0 rows)
 
 ANALYZE stats_import.test_mr;
@@ -3277,72 +3262,9 @@ FROM stats_import.pg_stats_ext_set_difference('test_mr_stat', 'test_mr_stat_clon
 \gx
 (0 rows)
 
--- Set difference for exprs: old MINUS new.
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat'
-EXCEPT
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone';
- 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 
------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
-(0 rows)
-
--- Set difference for exprs: new MINUS old.
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat';
- 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 
------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_exprs_set_difference('test_mr_stat', 'test_mr_stat_clone')
+\gx
 (0 rows)
 
 -- range_length_histogram, range_empty_frac, and range_bounds_histogram
@@ -3431,13 +3353,16 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null
 (1 row)
 
 DROP SCHEMA stats_import CASCADE;
-NOTICE:  drop cascades to 15 other objects
+NOTICE:  drop cascades to 18 other objects
 DETAIL:  drop cascades to view stats_import.pg_statistic_flat_t
 drop cascades to function stats_import.pg_statistic_flat(text)
 drop cascades to function stats_import.pg_statistic_set_difference(text,text)
 drop cascades to view stats_import.pg_stats_ext_flat_t
 drop cascades to function stats_import.pg_stats_ext_flat(text)
 drop cascades to function stats_import.pg_stats_ext_set_difference(text,text)
+drop cascades to view stats_import.pg_stats_ext_exprs_flat_t
+drop cascades to function stats_import.pg_stats_ext_exprs_flat(text)
+drop cascades to function stats_import.pg_stats_ext_exprs_set_difference(text,text)
 drop cascades to type stats_import.complex_type
 drop cascades to table stats_import.test
 drop cascades to table stats_import.test_mr
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 22a16c3f669..2034e4bc67b 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -93,6 +93,53 @@ BEGIN ATOMIC
   FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
 END;
 
+-- Test to detect any new columns added to pg_stats_ext_exprs, which may in turn
+-- need to be added to pg_stats_ext_exprs_flat()
+SELECT COUNT(*)
+FROM pg_attribute
+WHERE attrelid = 'pg_catalog.pg_stats_ext_exprs'::regclass;
+
+-- Create a view that is used purely for the type
+CREATE VIEW stats_import.pg_stats_ext_exprs_flat_t AS
+  SELECT inherited, null_frac, avg_width, n_distinct,
+       most_common_vals::text AS most_common_vals,
+       most_common_freqs, histogram_bounds::text AS histogram_bounds,
+       correlation, most_common_elems::text AS most_common_elems,
+       most_common_elem_freqs, elem_count_histogram,
+       range_length_histogram::text AS range_length_histogram,
+       range_empty_frac, range_bounds_histogram::text AS range_bounds_histogram
+  FROM pg_stats_ext_exprs AS n
+  WHERE FALSE;
+
+-- Function to get only the set-diff comparable parts of pg_stats_ext_exprs
+CREATE FUNCTION stats_import.pg_stats_ext_exprs_flat(p_statname text)
+RETURNS SETOF stats_import.pg_stats_ext_exprs_flat_t
+BEGIN ATOMIC
+  SELECT inherited, null_frac, avg_width, n_distinct,
+       most_common_vals::text AS most_common_vals,
+       most_common_freqs, histogram_bounds::text AS histogram_bounds,
+       correlation, most_common_elems::text AS most_common_elems,
+       most_common_elem_freqs, elem_count_histogram,
+       range_length_histogram::text AS range_length_histogram,
+       range_empty_frac, range_bounds_histogram::text AS range_bounds_histogram
+  FROM pg_stats_ext_exprs AS n
+  WHERE n.statistics_schemaname = 'stats_import' AND
+    n.statistics_name = p_statname;
+END;
+
+-- pg_stats_ext_exprs set diff function
+CREATE FUNCTION stats_import.pg_stats_ext_exprs_set_difference(a text, b text)
+RETURNS TABLE (statname text, stats stats_import.pg_stats_ext_exprs_flat_t)
+BEGIN ATOMIC
+  WITH aset AS (SELECT * FROM stats_import.pg_stats_ext_exprs_flat(a)),
+       bset AS (SELECT * FROM stats_import.pg_stats_ext_exprs_flat(b))
+  SELECT a AS relname, a_minus_b::stats_import.pg_stats_ext_exprs_flat_t
+  FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b
+  UNION ALL
+  SELECT b AS relname, b_minus_a::stats_import.pg_stats_ext_exprs_flat_t
+  FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
+END;
+
 --
 -- Schema setup.
 --
@@ -2171,67 +2218,10 @@ SELECT statname, (stats).*
 FROM stats_import.pg_stats_ext_set_difference('test_stat', 'test_stat_clone')
 \gx
 
--- Set difference for exprs: old MINUS new.
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat'
-EXCEPT
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone';
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_exprs_set_difference('test_stat', 'test_stat_clone')
+\gx
 
--- Set difference for exprs: new MINUS old.
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat';
 
 ANALYZE stats_import.test_mr;
 
@@ -2277,67 +2267,9 @@ SELECT statname, (stats).*
 FROM stats_import.pg_stats_ext_set_difference('test_mr_stat', 'test_mr_stat_clone')
 \gx
 
--- Set difference for exprs: old MINUS new.
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat'
-EXCEPT
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone';
-
--- Set difference for exprs: new MINUS old.
-SELECT n.inherited,
-       n.null_frac, n.avg_width, n.n_distinct,
-       n.most_common_vals::text AS most_common_vals,
-       n.most_common_freqs,
-       n.histogram_bounds::text AS histogram_bounds,
-       n.correlation,
-       n.most_common_elems::text AS most_common_elems,
-       n.most_common_elem_freqs, n.elem_count_histogram,
-       n.range_length_histogram::text AS range_length_histogram,
-       n.range_empty_frac,
-       n.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.null_frac, o.avg_width, o.n_distinct,
-       o.most_common_vals::text AS most_common_vals,
-       o.most_common_freqs,
-       o.histogram_bounds::text AS histogram_bounds,
-       o.correlation,
-       o.most_common_elems::text AS most_common_elems,
-       o.most_common_elem_freqs, o.elem_count_histogram,
-       o.range_length_histogram::text AS range_length_histogram,
-       o.range_empty_frac,
-       o.range_bounds_histogram::text AS range_bounds_histogram
-  FROM pg_stats_ext_exprs AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat';
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_exprs_set_difference('test_mr_stat', 'test_mr_stat_clone')
+\gx
 
 -- range_length_histogram, range_empty_frac, and range_bounds_histogram
 -- have been added to pg_stat_ext_exprs in PostgreSQL 19.  When dumping
-- 
2.53.0