On Wed, Mar 4, 2026 at 5:29 PM Michael Paquier <[email protected]> wrote:
> On Wed, Mar 04, 2026 at 05:05:55PM -0500, Corey Huinker wrote:
> > 5. Generate the column list of the view (or set difference queries) with
> a
> > \gexec query that filters out the oids.
> >
> > All of these things are sliiightly hacky, but if we settle on one pattern
> > that will allow us to use the pattern in multiple places and thus reduce
> > surprise to the reader.
>
> I am not sure which one would be the best fit, TBH. To me, it depends
> mostly on clarity and how easier it becomes to maintain these tests.
> Perhaps I would have a slight preference on the views with quals based
> on the object names. As long as we don't maintain the same
> complicated queries more than once, I'm basically fine with any
> solution. Using json for this purpose feels a bit too much artistic.
> --
> Michael
>
Here's a proof of concept for just the pg_statistic checks. The basic
structure is:
1. Create a VIEW that has only the columns we want to set-difference, in
the datatype we want to difference them. The view will never hold data, we
just want it for the datatype (t).
2. Create a FUNCTION that fetched the stats for a given object (in this
case a table or index), keeping only the columns found in (t).
3. Another FUNCTION that takes two object names as parameters, does the
A-B,B-A set difference operators on them, and returns that as a set of
(relname,(t)).
4. The A-B,B-A tests then become just one call to that set_difference
function.
If this seems more elegant than what we have now, I'll write up a formal
patch for the three set-difference types (attstats, extended, extended
exprs).
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 61535a971dc..a4a16b50a50 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -884,113 +884,60 @@ AND c.relname IN ('test', 'test_clone', 'is_odd', 'is_odd_clone')
GROUP BY c.relname
ORDER BY c.relname;
--- check test minus test_clone
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'test' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.test'::regclass
-EXCEPT
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'test' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.test_clone'::regclass;
+-- Test to detect any new columns added to pg_statistic, which may in turn
+-- need to be added to pg_statistic_flat()
+SELECT COUNT(*)
+FROM pg_attribute
+WHERE attrelid = 'pg_catalog.pg_statistic'::regclass;
+-- Create a view that is used purely for the type
+CREATE VIEW stats_import.pgs_t AS
+ SELECT
+ a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
+ s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
+ s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
+ s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
+ s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
+ s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
+ s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
+ s.stavalues5::text AS sv5
+ FROM pg_statistic s
+ JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+ WHERE FALSE;
+-- Function to get only the set-diff comparable parts of pg_statistic
+CREATE FUNCTION stats_import.pg_statistic_flat(p_relname text)
+RETURNS SETOF stats_import.pgs_t
+BEGIN ATOMIC
+ SELECT a.attname, s.stainherit, s.stanullfrac, s.stawidth,
+ s.stadistinct, s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
+ s.staop1, s.staop2, s.staop3, s.staop4, s.staop5, s.stacoll1, s.stacoll2,
+ s.stacoll3, s.stacoll4, s.stacoll5, s.stanumbers1, s.stanumbers2,
+ s.stanumbers3, s.stanumbers4, s.stanumbers5, s.stavalues1::text,
+ s.stavalues2::text, s.stavalues3::text,
+ s.stavalues4::text, s.stavalues5::text
+ FROM pg_statistic s
+ JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
+ JOIN pg_class c ON c.oid = a.attrelid
+ WHERE c.relnamespace = 'stats_import'::regnamespace
+ AND c.relname = p_relname;
+END;
+-- pg_statistic set diff function
+CREATE FUNCTION stats_import.pg_statistic_set_difference(a text, b text)
+RETURNS TABLE (relname text, stats stats_import.pgs_t)
+BEGIN ATOMIC
+ WITH aset AS (SELECT * FROM stats_import.pg_statistic_flat(a)),
+ bset AS (SELECT * FROM stats_import.pg_statistic_flat(b))
+ SELECT a AS relname, a_minus_b::stats_import.pgs_t
+ FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b
+ UNION ALL
+ SELECT b AS relname, b_minus_a::stats_import.pgs_t
+ FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
+END;
--- check test_clone minus test
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'test_clone' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.test_clone'::regclass
-EXCEPT
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'test_clone' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.test'::regclass;
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_set_difference('test', 'test_clone') \gx
--- check is_odd minus is_odd_clone
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'is_odd' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.is_odd'::regclass
-EXCEPT
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'is_odd' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.is_odd_clone'::regclass;
-
--- check is_odd_clone minus is_odd
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'is_odd_clone' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.is_odd_clone'::regclass
-EXCEPT
-SELECT
- a.attname, s.stainherit, s.stanullfrac, s.stawidth, s.stadistinct,
- s.stakind1, s.stakind2, s.stakind3, s.stakind4, s.stakind5,
- s.staop1, s.staop2, s.staop3, s.staop4, s.staop5,
- s.stacoll1, s.stacoll2, s.stacoll3, s.stacoll4, s.stacoll5,
- s.stanumbers1, s.stanumbers2, s.stanumbers3, s.stanumbers4, s.stanumbers5,
- s.stavalues1::text AS sv1, s.stavalues2::text AS sv2,
- s.stavalues3::text AS sv3, s.stavalues4::text AS sv4,
- s.stavalues5::text AS sv5, 'is_odd_clone' AS direction
-FROM pg_statistic s
-JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum
-WHERE s.starelid = 'stats_import.is_odd'::regclass;
+SELECT relname, (stats).*
+FROM stats_import.pg_statistic_set_difference('is_odd', 'is_odd_clone') \gx
-- attribute stats exist before a clear, but not after
SELECT COUNT(*)
