From fc365e4fccc46d2991ab9614198aa6d71b3838d2 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Thu, 29 Jan 2026 13:22:07 +0900 Subject: [PATCH] Add test doing some cloning of extended statistics data The test added in this commit copies the data of an ANALYZE run on one relation to a secondary relation with the same attribute definitions and extended statistics objects. Once the clone is done, the target and origin should have the same extended statistics information, with no differences. This test would have been able to catch e3094679b983, for example, as we expect the full range of statistics to be copied over, with no differences generated between the results of an ANALYZE and the data copied to the cloned relation. Note that this new test should remain at the bottom of stats_import.sql, so as any additions in the main relation and its clone are automatically covered when copying their statistics, so as it would work as a sanity check in the future. Author: Corey Huinker Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/CADkLM=dpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqho=qN3kX0Zg@mail.gmail.com --- src/test/regress/expected/stats_import.out | 65 ++++++++++++++++++++++ src/test/regress/sql/stats_import.sql | 55 ++++++++++++++++++ 2 files changed, 120 insertions(+) diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 7c30d5e2b4a..284a49bbc4e 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -2191,6 +2191,71 @@ most_common_val_nulls | {{f,f,f},{f,f,f},{f,f,f}} most_common_freqs | {0.3333333333333333,0.3333333333333333,0.3333333333333333} most_common_base_freqs | {0.1111111111111111,0.1111111111111111,0.1111111111111111} +-- Test the ability of pg_restore_extended_stats() to import all of the +-- statistic values from an extended statistic object that has been +-- populated via a regular ANALYZE. This checks after the statistics +-- kinds supported by pg_restore_extended_stats(). +-- +-- Note: Keep this test at the bottom of the file, so as the amount of +-- statistics data handled is maximized. +ANALYZE stats_import.test; +-- Copy stats from test_stat to test_stat_clone +SELECT e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'schemaname', e.statistics_schemaname::text, + 'relname', 'test_clone', + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs) +FROM pg_stats_ext AS e +WHERE e.statistics_schemaname = 'stats_import' +AND e.statistics_name = 'test_stat'; + statistics_name | pg_restore_extended_stats +-----------------+--------------------------- + 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 +-----------+------------+--------------+------------------+-------------------+------------------------ +(0 rows) + DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 7 other objects DETAIL: drop cascades to type stats_import.complex_type diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 1cdcdb3054b..8db7cd93b88 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -1557,4 +1557,59 @@ WHERE e.statistics_schemaname = 'stats_import' AND e.inherited = false \gx +-- Test the ability of pg_restore_extended_stats() to import all of the +-- statistic values from an extended statistic object that has been +-- populated via a regular ANALYZE. This checks after the statistics +-- kinds supported by pg_restore_extended_stats(). +-- +-- Note: Keep this test at the bottom of the file, so as the amount of +-- statistics data handled is maximized. +ANALYZE stats_import.test; + +-- Copy stats from test_stat to test_stat_clone +SELECT e.statistics_name, + pg_catalog.pg_restore_extended_stats( + 'schemaname', e.statistics_schemaname::text, + 'relname', 'test_clone', + 'statistics_schemaname', e.statistics_schemaname::text, + 'statistics_name', 'test_stat_clone', + 'inherited', e.inherited, + 'n_distinct', e.n_distinct, + 'dependencies', e.dependencies, + 'most_common_vals', e.most_common_vals, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs) +FROM pg_stats_ext AS e +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'; + DROP SCHEMA stats_import CASCADE; -- 2.47.3