> > Extending this concept to all of them should mean close to three times > the same amount of code shaved. >
Fairly close +307, -603
From 0c734f6dcf07c38bb4de732b44c059aec5a15d36 Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> Date: Fri, 6 Mar 2026 15:21:37 -0500 Subject: [PATCH v1 1/3] Reorganize pg_statistic set difference tests in stats_import The existing set-difference tests for checking rows in pg_statistic after a call to pg_restore_attribute_stats() are a bit wordy. This is by necessity because pg_statistic contains both oids which wouldn't match when comparing stats on an object and its clone, and also contains ANYARRAY types which must be cast to text before being used in a set difference (i.e. a EXCEPT b, b EXECPT a) test. We accomplish this by first creating a view of all of the columns in pg_statistic that we want to compare src/dest and have them casted to text where necessary, but without any columns that we would expect to be different when comparing stats from a table and its clone. Next we create a _flat() function to extract those values for a given table, and finally we create a set_difference function which takes the names of two tables, fetched the _flat data from each, does A-B, B-A set difference comparisions, and then emits that as a single result set. This allows us to be a bit more clear about what set difference tests are being done, and consolidates a lot of repetitive query text that may have to be changed when new statistics types are added. --- src/test/regress/expected/stats_import.out | 188 ++++++++------------- src/test/regress/sql/stats_import.sql | 171 +++++++------------ 2 files changed, 135 insertions(+), 224 deletions(-) diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 1f24e306f5b..2ccd225618d 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1,4 +1,63 @@ CREATE SCHEMA stats_import; +-- +-- Setup set-difference convenience functions +-- +-- 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; + count +------- + 37 +(1 row) + +-- Create a view that is used purely for the type +CREATE VIEW stats_import.pg_statistic_flat_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.pg_statistic_flat_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.pg_statistic_flat_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.pg_statistic_flat_t + FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b + UNION ALL + SELECT b AS relname, b_minus_a::stats_import.pg_statistic_flat_t + FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a; +END; +-- +-- Schema setup. +-- CREATE TYPE stats_import.complex_type AS ( a integer, b real, @@ -1220,124 +1279,14 @@ ORDER BY c.relname; test_clone | 5 (4 rows) --- 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; - attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction ----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +SELECT relname, (stats).* +FROM stats_import.pg_statistic_set_difference('test', 'test_clone') +\gx (0 rows) --- 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; - attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction ----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- -(0 rows) - --- 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; - attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction ----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- -(0 rows) - --- 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; - attname | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3 | stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 | sv1 | sv2 | sv3 | sv4 | sv5 | direction ----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +SELECT relname, (stats).* +FROM stats_import.pg_statistic_set_difference('is_odd', 'is_odd_clone') +\gx (0 rows) -- attribute stats exist before a clear, but not after @@ -3506,8 +3455,11 @@ SELECT COUNT(*) FROM stats_import.test_range_expr_null (1 row) DROP SCHEMA stats_import CASCADE; -NOTICE: drop cascades to 9 other objects -DETAIL: drop cascades to type stats_import.complex_type +NOTICE: drop cascades to 12 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 type stats_import.complex_type drop cascades to table stats_import.test drop cascades to table stats_import.test_mr drop cascades to table stats_import.part_parent diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 61535a971dc..c512913e045 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1,5 +1,64 @@ CREATE SCHEMA stats_import; +-- +-- Setup set-difference convenience functions +-- + +-- 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.pg_statistic_flat_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.pg_statistic_flat_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.pg_statistic_flat_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.pg_statistic_flat_t + FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b + UNION ALL + SELECT b AS relname, b_minus_a::stats_import.pg_statistic_flat_t + FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a; +END; + +-- +-- Schema setup. +-- CREATE TYPE stats_import.complex_type AS ( a integer, b real, @@ -884,113 +943,13 @@ 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; +SELECT relname, (stats).* +FROM stats_import.pg_statistic_set_difference('test', 'test_clone') +\gx --- 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; - --- 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(*) base-commit: e982331b52083ee81f5f62f9872c874cbc1862c1 -- 2.53.0
From 39dc469edfdc31ed563301dd8e3878b56af4178a Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> 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
From 4caff6015dd07b75e222389fee8efa641092df0b Mon Sep 17 00:00:00 2001 From: Corey Huinker <[email protected]> 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
