v1-0002-Reorganize-pg_stats_ext-set-difference-tests-in-s.patch

From 39dc469edfdc31ed563301dd8e3878b56af4178a Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Fri, 6 Mar 2026 23:13:06 -0500
Subject: [PATCH v1 2/3] Reorganize pg_stats_ext 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 (pg_stats_ext) but not the expressions which are
stored at a different grain and exposed in a different view
(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 | 117 +++++++++------------
 src/test/regress/sql/stats_import.sql      |  99 ++++++++---------
 2 files changed, 91 insertions(+), 125 deletions(-)

diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 2ccd225618d..5fb07134335 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -55,6 +55,44 @@ BEGIN ATOMIC
   SELECT b AS relname, b_minus_a::stats_import.pg_statistic_flat_t
   FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
 END;
+-- Test to detect any new columns added to pg_stats_ext, which may in turn
+-- need to be added to pg_stats_ext_flat()
+SELECT COUNT(*)
+FROM pg_attribute
+WHERE attrelid = 'pg_catalog.pg_stats_ext'::regclass;
+ count 
+-------
+    15
+(1 row)
+
+-- Create a view that is used purely for the type
+CREATE VIEW stats_import.pg_stats_ext_flat_t AS
+  SELECT inherited, n_distinct, dependencies, most_common_vals,
+         most_common_freqs, most_common_base_freqs
+  FROM pg_stats_ext
+  WHERE FALSE;
+-- Function to get only the set-diff comparable parts of pg_stats_ext
+CREATE FUNCTION stats_import.pg_stats_ext_flat(p_statname text)
+RETURNS SETOF stats_import.pg_stats_ext_flat_t
+BEGIN ATOMIC
+  SELECT inherited, n_distinct, dependencies, most_common_vals,
+         most_common_freqs, most_common_base_freqs
+  FROM pg_stats_ext
+  WHERE statistics_schemaname = 'stats_import'
+  AND statistics_name = p_statname;
+END;
+-- pg_stats_ext set diff function
+CREATE FUNCTION stats_import.pg_stats_ext_set_difference(a text, b text)
+RETURNS TABLE (statname text, stats stats_import.pg_stats_ext_flat_t)
+BEGIN ATOMIC
+  WITH aset AS (SELECT * FROM stats_import.pg_stats_ext_flat(a)),
+       bset AS (SELECT * FROM stats_import.pg_stats_ext_flat(b))
+  SELECT a AS relname, a_minus_b::stats_import.pg_stats_ext_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_flat_t
+  FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
+END;
 --
 -- Schema setup.
 --
@@ -3118,40 +3156,9 @@ AND e.statistics_name = 'test_stat';
  test_stat       | t
 (1 row)
 
--- Set difference old MINUS new.
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat'
-EXCEPT
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone';
- inherited | n_distinct | dependencies | most_common_vals | most_common_freqs | most_common_base_freqs 
------------+------------+--------------+------------------+-------------------+------------------------
-(0 rows)
-
--- Set difference new MINUS old.
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat';
- inherited | n_distinct | dependencies | most_common_vals | most_common_freqs | most_common_base_freqs 
------------+------------+--------------+------------------+-------------------+------------------------
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_set_difference('test_stat', 'test_stat_clone')
+\gx
 (0 rows)
 
 -- Set difference for exprs: old MINUS new.
@@ -3265,40 +3272,9 @@ AND e.statistics_name = 'test_mr_stat';
  test_mr_stat    | t
 (1 row)
 
--- Set difference old MINUS new.
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat'
-EXCEPT
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone';
- inherited | n_distinct | dependencies | most_common_vals | most_common_freqs | most_common_base_freqs 
------------+------------+--------------+------------------+-------------------+------------------------
-(0 rows)
-
--- Set difference new MINUS old.
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat';
- inherited | n_distinct | dependencies | most_common_vals | most_common_freqs | most_common_base_freqs 
------------+------------+--------------+------------------+-------------------+------------------------
+SELECT statname, (stats).*
+FROM stats_import.pg_stats_ext_set_difference('test_mr_stat', 'test_mr_stat_clone')
+\gx
 (0 rows)
 
 -- Set difference for exprs: old MINUS new.
@@ -3455,10 +3431,13 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null
 (1 row)
 
 DROP SCHEMA stats_import CASCADE;
-NOTICE:  drop cascades to 12 other objects
+NOTICE:  drop cascades to 15 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 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 c512913e045..22a16c3f669 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -56,6 +56,43 @@ BEGIN ATOMIC
   FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
 END;
 
+-- Test to detect any new columns added to pg_stats_ext, which may in turn
+-- need to be added to pg_stats_ext_flat()
+SELECT COUNT(*)
+FROM pg_attribute
+WHERE attrelid = 'pg_catalog.pg_stats_ext'::regclass;
+
+-- Create a view that is used purely for the type
+CREATE VIEW stats_import.pg_stats_ext_flat_t AS
+  SELECT inherited, n_distinct, dependencies, most_common_vals,
+         most_common_freqs, most_common_base_freqs
+  FROM pg_stats_ext
+  WHERE FALSE;
+
+-- Function to get only the set-diff comparable parts of pg_stats_ext
+CREATE FUNCTION stats_import.pg_stats_ext_flat(p_statname text)
+RETURNS SETOF stats_import.pg_stats_ext_flat_t
+BEGIN ATOMIC
+  SELECT inherited, n_distinct, dependencies, most_common_vals,
+         most_common_freqs, most_common_base_freqs
+  FROM pg_stats_ext
+  WHERE statistics_schemaname = 'stats_import'
+  AND statistics_name = p_statname;
+END;
+
+-- pg_stats_ext set diff function
+CREATE FUNCTION stats_import.pg_stats_ext_set_difference(a text, b text)
+RETURNS TABLE (statname text, stats stats_import.pg_stats_ext_flat_t)
+BEGIN ATOMIC
+  WITH aset AS (SELECT * FROM stats_import.pg_stats_ext_flat(a)),
+       bset AS (SELECT * FROM stats_import.pg_stats_ext_flat(b))
+  SELECT a AS relname, a_minus_b::stats_import.pg_stats_ext_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_flat_t
+  FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
+END;
+
 --
 -- Schema setup.
 --
@@ -2130,34 +2167,9 @@ CROSS JOIN LATERAL (
 WHERE e.statistics_schemaname = 'stats_import'
 AND e.statistics_name = 'test_stat';
 
--- Set difference old MINUS new.
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat'
-EXCEPT
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone';
--- Set difference new MINUS old.
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_stat';
+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,
@@ -2261,34 +2273,9 @@ CROSS JOIN LATERAL (
 WHERE e.statistics_schemaname = 'stats_import'
 AND e.statistics_name = 'test_mr_stat';
 
--- Set difference old MINUS new.
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat'
-EXCEPT
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone';
--- Set difference new MINUS old.
-SELECT n.inherited,
-       n.n_distinct, n.dependencies, n.most_common_vals,
-       n.most_common_freqs, n.most_common_base_freqs
-  FROM pg_stats_ext AS n
-  WHERE n.statistics_schemaname = 'stats_import' AND
-    n.statistics_name = 'test_mr_stat_clone'
-EXCEPT
-SELECT o.inherited,
-       o.n_distinct, o.dependencies, o.most_common_vals,
-       o.most_common_freqs, o.most_common_base_freqs
-  FROM pg_stats_ext AS o
-  WHERE o.statistics_schemaname = 'stats_import' AND
-    o.statistics_name = 'test_mr_stat';
+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,
-- 
2.53.0