diff options
Diffstat (limited to 'src/test/regress/sql/stats_import.sql')
| -rw-r--r-- | src/test/regress/sql/stats_import.sql | 1025 |
1 files changed, 283 insertions, 742 deletions
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 9740ab3ff02..57422750b90 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -15,63 +15,19 @@ CREATE TABLE stats_import.test( tags text[] ) WITH (autovacuum_enabled = false); --- starting stats -SELECT relpages, reltuples, relallvisible -FROM pg_class -WHERE oid = 'stats_import.test'::regclass; - --- error: regclass not found -SELECT - pg_catalog.pg_set_relation_stats( - relation => 0::Oid, - relpages => 17::integer, - reltuples => 400.0::real, - relallvisible => 4::integer); - --- relpages default -SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, - relpages => NULL::integer, - reltuples => 400.0::real, - relallvisible => 4::integer); - --- reltuples default -SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, - relpages => 17::integer, - reltuples => NULL::real, - relallvisible => 4::integer); - --- relallvisible default -SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, - relpages => 17::integer, - reltuples => 400.0::real, - relallvisible => NULL::integer); - --- named arguments -SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test'::regclass, - relpages => 17::integer, - reltuples => 400.0::real, - relallvisible => 4::integer); +CREATE INDEX test_i ON stats_import.test(id); +-- starting stats SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_import.test'::regclass; -CREATE INDEX test_i ON stats_import.test(id); - BEGIN; -- regular indexes have special case locking rules SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.test_i'::regclass, - relpages => 18::integer); + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.test_i'::regclass, + 'relpages', 18::integer); SELECT mode FROM pg_locks WHERE relation = 'stats_import.test'::regclass AND @@ -88,22 +44,6 @@ SELECT 'relation', 'stats_import.test_i'::regclass, 'relpages', 19::integer ); --- positional arguments -SELECT - pg_catalog.pg_set_relation_stats( - 'stats_import.test'::regclass, - 18::integer, - 401.0::real, - 5::integer); - -SELECT relpages, reltuples, relallvisible -FROM pg_class -WHERE oid = 'stats_import.test'::regclass; - -SELECT relpages, reltuples, relallvisible -FROM pg_class -WHERE oid = 'stats_import.test'::regclass; - -- clear SELECT pg_catalog.pg_clear_relation_stats( @@ -113,16 +53,6 @@ SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_import.test'::regclass; --- invalid relkinds for statistics -CREATE SEQUENCE stats_import.testseq; -CREATE VIEW stats_import.testview AS SELECT * FROM stats_import.test; -SELECT - pg_catalog.pg_clear_relation_stats( - 'stats_import.testseq'::regclass); -SELECT - pg_catalog.pg_clear_relation_stats( - 'stats_import.testview'::regclass); - -- relpages may be -1 for partitioned tables CREATE TABLE stats_import.part_parent ( i integer ) PARTITION BY RANGE(i); CREATE TABLE stats_import.part_child_1 @@ -141,14 +71,14 @@ WHERE oid = 'stats_import.part_parent'::regclass; -- although partitioned tables have no storage, setting relpages to a -- positive value is still allowed SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.part_parent_i'::regclass, - relpages => 2::integer); + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.part_parent_i'::regclass, + 'relpages', 2::integer); SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.part_parent'::regclass, - relpages => 2::integer); + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.part_parent'::regclass, + 'relpages', 2::integer); -- -- Partitioned indexes aren't analyzed but it is possible to set @@ -159,9 +89,9 @@ SELECT BEGIN; SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.part_parent_i'::regclass, - relpages => 2::integer); + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.part_parent_i'::regclass, + 'relpages', 2::integer); SELECT mode FROM pg_locks WHERE relation = 'stats_import.part_parent'::regclass AND @@ -180,440 +110,9 @@ SELECT -- nothing stops us from setting it to -1 SELECT - pg_catalog.pg_set_relation_stats( - relation => 'stats_import.part_parent'::regclass, - relpages => -1::integer); - --- error: object doesn't exist -SELECT pg_catalog.pg_set_attribute_stats( - relation => '0'::oid, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - --- error: object doesn't exist -SELECT pg_catalog.pg_clear_attribute_stats( - relation => '0'::oid, - attname => 'id'::name, - inherited => false::boolean); - --- error: relation null -SELECT pg_catalog.pg_set_attribute_stats( - relation => NULL::oid, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - --- error: attribute is system column -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'xmin'::name, - inherited => false::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - --- error: attname doesn't exist -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'nope'::name, - inherited => false::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - --- error: attribute is system column -SELECT pg_catalog.pg_clear_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'ctid'::name, - inherited => false::boolean); - --- error: attname doesn't exist -SELECT pg_catalog.pg_clear_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'nope'::name, - inherited => false::boolean); - --- error: attname null -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => NULL::name, - inherited => false::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - --- error: inherited null -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => NULL::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - --- ok: no stakinds -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.1::real, - avg_width => 2::integer, - n_distinct => 0.3::real); - -SELECT stanullfrac, stawidth, stadistinct -FROM pg_statistic -WHERE starelid = 'stats_import.test'::regclass; - --- error: mcv / mcf null mismatch -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_freqs => '{0.1,0.2,0.3}'::real[] - ); - --- error: mcv / mcf null mismatch part 2 -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_vals => '{1,2,3}'::text - ); - --- error: mcv / mcf type mismatch -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_vals => '{2023-09-30,2024-10-31,3}'::text, - most_common_freqs => '{0.2,0.1}'::real[] - ); - --- error: mcv cast failure -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_vals => '{2,four,3}'::text, - most_common_freqs => '{0.3,0.25,0.05}'::real[] - ); - --- ok: mcv+mcf -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_vals => '{2,1,3}'::text, - most_common_freqs => '{0.3,0.25,0.05}'::real[] - ); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'id'; - --- error: histogram elements null value --- this generates no warnings, but perhaps it should -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - histogram_bounds => '{1,NULL,3,4}'::text - ); - --- ok: histogram_bounds -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - histogram_bounds => '{1,2,3,4}'::text - ); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'id'; - --- ok: correlation -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - correlation => 0.5::real); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'id'; - --- error: scalars can't have mcelem -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_elems => '{1,3}'::text, - most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[] - ); - --- error: mcelem / mcelem mismatch -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'tags'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_elems => '{one,two}'::text - ); - --- error: mcelem / mcelem null mismatch part 2 -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'tags'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_elem_freqs => '{0.3,0.2,0.2,0.3}'::real[] - ); - --- ok: mcelem -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'tags'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_elems => '{one,three}'::text, - most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[] - ); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'tags'; - --- error: scalars can't have elem_count_histogram -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - elem_count_histogram => '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] - ); --- error: elem_count_histogram null element -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'tags'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - elem_count_histogram => '{1,1,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] - ); --- ok: elem_count_histogram -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'tags'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - elem_count_histogram => '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] - ); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'tags'; - --- error: scalars can't have range stats -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - range_empty_frac => 0.5::real, - range_length_histogram => '{399,499,Infinity}'::text - ); --- error: range_empty_frac range_length_hist null mismatch -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'arange'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - range_length_histogram => '{399,499,Infinity}'::text - ); --- error: range_empty_frac range_length_hist null mismatch part 2 -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'arange'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - range_empty_frac => 0.5::real - ); --- ok: range_empty_frac + range_length_hist -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'arange'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - range_empty_frac => 0.5::real, - range_length_histogram => '{399,499,Infinity}'::text - ); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'arange'; - --- error: scalars can't have range stats -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'id'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text - ); --- ok: range_bounds_histogram -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'arange'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text - ); - -SELECT * -FROM pg_stats -WHERE schemaname = 'stats_import' -AND tablename = 'test' -AND inherited = false -AND attname = 'arange'; - --- error: cannot set most_common_elems for range type -SELECT pg_catalog.pg_set_attribute_stats( - relation => 'stats_import.test'::regclass, - attname => 'arange'::name, - inherited => false::boolean, - null_frac => 0.5::real, - avg_width => 2::integer, - n_distinct => -0.1::real, - most_common_vals => '{"[2,3)","[1,2)","[3,4)"}'::text, - most_common_freqs => '{0.3,0.25,0.05}'::real[], - histogram_bounds => '{"[1,2)","[2,3)","[3,4)","[4,5)"}'::text, - correlation => 1.1::real, - most_common_elems => '{3,1}'::text, - most_common_elem_freqs => '{0.3,0.2,0.2,0.3,0.0}'::real[], - range_empty_frac => -0.5::real, - range_length_histogram => '{399,499,Infinity}'::text, - range_bounds_histogram => '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text - ); - --- --- Clear attribute stats to try again with restore functions --- (relation stats were already cleared). --- -SELECT - pg_catalog.pg_clear_attribute_stats( - 'stats_import.test'::regclass, - s.attname, - s.inherited) -FROM pg_catalog.pg_stats AS s -WHERE s.schemaname = 'stats_import' -AND s.tablename = 'test' -ORDER BY s.attname, s.inherited; - --- reject: argument name is NULL -SELECT pg_restore_relation_stats( - 'relation', '0'::oid::regclass, - 'version', 150000::integer, - NULL, '17'::integer, - 'reltuples', 400::real, - 'relallvisible', 4::integer); - --- reject: argument name is an integer -SELECT pg_restore_relation_stats( - 'relation', '0'::oid::regclass, - 'version', 150000::integer, - 17, '17'::integer, - 'reltuples', 400::real, - 'relallvisible', 4::integer); - --- reject: odd number of variadic arguments cannot be pairs -SELECT pg_restore_relation_stats( - 'relation', '0'::oid::regclass, - 'version', 150000::integer, - 'relpages', '17'::integer, - 'reltuples', 400::real, - 'relallvisible'); - --- reject: object doesn't exist -SELECT pg_restore_relation_stats( - 'relation', '0'::oid::regclass, - 'version', 150000::integer, - 'relpages', '17'::integer, - 'reltuples', 400::real, - 'relallvisible', 4::integer); + pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.part_parent'::regclass, + 'relpages', -1::integer); -- ok: set all stats SELECT pg_restore_relation_stats( @@ -657,14 +156,6 @@ SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_import.test'::regclass; --- warn and error: unrecognized argument name -SELECT pg_restore_relation_stats( - 'relation', '0'::oid::regclass, - 'version', 150000::integer, - 'relpages', '17'::integer, - 'reltuples', 400::real, - 'nope', 4::integer); - -- warn: bad relpages type SELECT pg_restore_relation_stats( 'relation', 'stats_import.test'::regclass, @@ -677,55 +168,15 @@ SELECT relpages, reltuples, relallvisible FROM pg_class WHERE oid = 'stats_import.test'::regclass; --- error: object does not exist -SELECT pg_catalog.pg_restore_attribute_stats( - 'relation', '0'::oid::regclass, - 'attname', 'id'::name, - 'inherited', false::boolean, - 'version', 150000::integer, - 'null_frac', 0.1::real, - 'avg_width', 2::integer, - 'n_distinct', 0.3::real); - --- error: relation null -SELECT pg_catalog.pg_restore_attribute_stats( - 'relation', NULL::oid, - 'attname', 'id'::name, - 'inherited', false::boolean, - 'version', 150000::integer, - 'null_frac', 0.1::real, - 'avg_width', 2::integer, - 'n_distinct', 0.3::real); - --- error: attname null -SELECT pg_catalog.pg_restore_attribute_stats( - 'relation', 'stats_import.test'::regclass, - 'attname', NULL::name, - 'inherited', false::boolean, - 'version', 150000::integer, - 'null_frac', 0.1::real, - 'avg_width', 2::integer, - 'n_distinct', 0.3::real); - --- error: attname doesn't exist -SELECT pg_catalog.pg_restore_attribute_stats( - 'relation', 'stats_import.test'::regclass, - 'attname', 'nope'::name, - 'inherited', false::boolean, - 'version', 150000::integer, - 'null_frac', 0.1::real, - 'avg_width', 2::integer, - 'n_distinct', 0.3::real); - --- error: inherited null -SELECT pg_catalog.pg_restore_attribute_stats( - 'relation', 'stats_import.test'::regclass, - 'attname', 'id'::name, - 'inherited', NULL::boolean, - 'version', 150000::integer, - 'null_frac', 0.1::real, - 'avg_width', 2::integer, - 'n_distinct', 0.3::real); +-- invalid relkinds for statistics +CREATE SEQUENCE stats_import.testseq; +CREATE VIEW stats_import.testview AS SELECT * FROM stats_import.test; +SELECT + pg_catalog.pg_clear_relation_stats( + 'stats_import.testseq'::regclass); +SELECT + pg_catalog.pg_clear_relation_stats( + 'stats_import.testview'::regclass); -- ok: no stakinds SELECT pg_catalog.pg_restore_attribute_stats( @@ -1050,6 +501,117 @@ AND tablename = 'test' AND inherited = false AND attname = 'arange'; +-- warn: cannot set most_common_elems for range type +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'arange'::name, + 'inherited', false::boolean, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_vals', '{"[2,3)","[1,2)","[3,4)"}'::text, + 'most_common_freqs', '{0.3,0.25,0.05}'::real[], + 'histogram_bounds', '{"[1,2)","[2,3)","[3,4)","[4,5)"}'::text, + 'correlation', 1.1::real, + 'most_common_elems', '{3,1}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[], + 'range_empty_frac', -0.5::real, + 'range_length_histogram', '{399,499,Infinity}'::text, + 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + +-- warn: scalars can't have mcelem +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_elems', '{1,3}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + +-- warn: mcelem / mcelem mismatch +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_elems', '{one,two}'::text + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- warn: mcelem / mcelem null mismatch part 2 +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3}'::real[] + ); + +-- ok: mcelem +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'tags'::name, + 'inherited', false::boolean, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'most_common_elems', '{one,three}'::text, + 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + +-- warn: scalars can't have elem_count_histogram +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'null_frac', 0.5::real, + 'avg_width', 2::integer, + 'n_distinct', -0.1::real, + 'elem_count_histogram', '{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::real[] + ); + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + -- warn: too many stat kinds SELECT pg_catalog.pg_restore_attribute_stats( 'relation', 'stats_import.test'::regclass, @@ -1069,6 +631,13 @@ SELECT pg_catalog.pg_restore_attribute_stats( 'range_length_histogram', '{399,499,Infinity}'::text, 'range_bounds_histogram', '{"[-1,1)","[0,4)","[1,4)","[1,100)"}'::text); +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + -- -- Test the ability to exactly copy data from one table to an identical table, -- correctly reconstructing the stakind order as well as the staopN and @@ -1108,173 +677,6 @@ CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); -- -- Copy stats from test to test_clone, and is_odd to is_odd_clone -- -SELECT s.schemaname, s.tablename, s.attname, s.inherited -FROM pg_catalog.pg_stats AS s -CROSS JOIN LATERAL - pg_catalog.pg_set_attribute_stats( - relation => ('stats_import.' || s.tablename || '_clone')::regclass::oid, - attname => s.attname, - inherited => s.inherited, - null_frac => s.null_frac, - avg_width => s.avg_width, - n_distinct => s.n_distinct, - most_common_vals => s.most_common_vals::text, - most_common_freqs => s.most_common_freqs, - histogram_bounds => s.histogram_bounds::text, - correlation => s.correlation, - most_common_elems => s.most_common_elems::text, - most_common_elem_freqs => s.most_common_elem_freqs, - elem_count_histogram => s.elem_count_histogram, - range_bounds_histogram => s.range_bounds_histogram::text, - range_empty_frac => s.range_empty_frac, - range_length_histogram => s.range_length_histogram::text) AS r -WHERE s.schemaname = 'stats_import' -AND s.tablename IN ('test', 'is_odd') -ORDER BY s.tablename, s.attname, s.inherited; - -SELECT c.relname, COUNT(*) AS num_stats -FROM pg_class AS c -JOIN pg_statistic s ON s.starelid = c.oid -WHERE c.relnamespace = 'stats_import'::regnamespace -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; - --- 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 relpages, reltuples, relallvisible -FROM pg_class -WHERE oid = 'stats_import.test'::regclass; - --- --- Clear clone stats to try again with pg_restore_attribute_stats --- -SELECT - pg_catalog.pg_clear_attribute_stats( - ('stats_import.' || s.tablename)::regclass, - s.attname, - s.inherited) -FROM pg_catalog.pg_stats AS s -WHERE s.schemaname = 'stats_import' -AND s.tablename IN ('test_clone', 'is_odd_clone') -ORDER BY s.tablename, s.attname, s.inherited; -SELECT - -SELECT COUNT(*) -FROM pg_catalog.pg_stats AS s -WHERE s.schemaname = 'stats_import' -AND s.tablename IN ('test_clone', 'is_odd_clone'); - --- --- Copy stats from test to test_clone, and is_odd to is_odd_clone --- SELECT s.schemaname, s.tablename, s.attname, s.inherited, r.* FROM pg_catalog.pg_stats AS s CROSS JOIN LATERAL @@ -1416,4 +818,143 @@ 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; +-- ok +SELECT pg_catalog.pg_clear_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'arange'::name, + inherited => false::boolean); + +-- +-- Negative tests +-- + +--- error: relation is wrong type +SELECT pg_catalog.pg_restore_relation_stats( + 'relation', 0::oid, + 'relpages', 17::integer, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); + +--- error: relation not found +SELECT pg_catalog.pg_restore_relation_stats( + 'relation', 0::regclass, + 'relpages', 17::integer, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer); + +-- warn and error: unrecognized argument name +SELECT pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'nope', 4::integer); + +-- error: argument name is NULL +SELECT pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + NULL, '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + +-- error: argument name is an integer +SELECT pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + 17, '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + +-- error: odd number of variadic arguments cannot be pairs +SELECT pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible'); + +-- error: object doesn't exist +SELECT pg_restore_relation_stats( + 'relation', '0'::oid::regclass, + 'version', 150000::integer, + 'relpages', '17'::integer, + 'reltuples', 400::real, + 'relallvisible', 4::integer); + +-- error: object does not exist +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', '0'::oid::regclass, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: relation null +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', NULL::oid, + 'attname', 'id'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: attname null +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', NULL::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: attname doesn't exist +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'nope'::name, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: attribute is system column +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'xmin'::name, + 'inherited', false::boolean, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: inherited null +SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'inherited', NULL::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + +-- error: relation not found +SELECT pg_catalog.pg_clear_relation_stats( + relation => 'stats_import.nope'::regclass); + +-- error: attribute is system column +SELECT pg_catalog.pg_clear_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'ctid'::name, + inherited => false::boolean); + +-- error: attname doesn't exist +SELECT pg_catalog.pg_clear_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'nope'::name, + inherited => false::boolean); + DROP SCHEMA stats_import CASCADE; |