diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/stats_import.out | 659 | ||||
| -rw-r--r-- | src/test/regress/sql/stats_import.sql | 545 |
2 files changed, 1203 insertions, 1 deletions
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index b50af75bb97..2868cc47f1d 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -171,10 +171,667 @@ SELECT (1 row) +-- 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: could not open relation with OID 0 +-- 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: "relation" cannot be NULL +-- 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: "attname" cannot be NULL +-- 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); +ERROR: "inherited" cannot be NULL +-- error: null_frac null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => NULL::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + pg_set_attribute_stats +------------------------ + +(1 row) + +-- error: avg_width null +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 => NULL::integer, + n_distinct => 0.3::real); + pg_set_attribute_stats +------------------------ + +(1 row) + +-- error: avg_width null +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 => NULL::real); + pg_set_attribute_stats +------------------------ + +(1 row) + +-- 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); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT stanullfrac, stawidth, stadistinct +FROM pg_statistic +WHERE starelid = 'stats_import.test'::regclass; + stanullfrac | stawidth | stadistinct +-------------+----------+------------- + 0.1 | 2 | 0.3 +(1 row) + +-- 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: "most_common_vals" must be specified when "most_common_freqs" is specified +-- 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: "most_common_freqs" must be specified when "most_common_vals" is specified +-- 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: invalid input syntax for type integer: "2023-09-30" +-- warning: 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[] + ); +ERROR: invalid input syntax for type integer: "four" +-- 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[] + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | | | | | | | | +(1 row) + +-- 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 + ); +ERROR: "histogram_bounds" array cannot contain NULL values +-- 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 + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | | | | | | | +(1 row) + +-- 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); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'id'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | id | f | 0.5 | 2 | -0.1 | {2,1,3} | {0.3,0.25,0.05} | {1,2,3,4} | 0.5 | | | | | | +(1 row) + +-- 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: unable to determine element type of attribute "id" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. +-- 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: "most_common_elem_freqs" must be specified when "most_common_elems" is specified +-- 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[] + ); +ERROR: "most_common_elems" must be specified when "most_common_elem_freqs" is specified +-- 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[] + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | | | | +(1 row) + +-- 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: unable to determine element type of attribute "id" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. +-- 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[] + ); +ERROR: "elem_count_histogram" array cannot contain NULL values +-- 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[] + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'tags'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+------------------+------------------------ + stats_import | test | tags | f | 0.5 | 2 | -0.1 | | | | | {one,three} | {0.3,0.2,0.2,0.3,0} | {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1} | | | +(1 row) + +-- 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: attribute "id" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. +-- 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" must be specified when "range_length_histogram" is specified +-- 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 + ); +ERROR: "range_length_histogram" must be specified when "range_empty_frac" is specified +-- 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 + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+------------------------ + stats_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | {399,499,Infinity} | 0.5 | +(1 row) + +-- 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 + ); +ERROR: attribute "id" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. +-- 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 + ); + pg_set_attribute_stats +------------------------ + +(1 row) + +SELECT * +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + schemaname | tablename | attname | 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 +--------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------+------------------------+------------------+-------------------------------------- + stats_import | test | arange | f | 0.5 | 2 | -0.1 | | | | | | | | {399,499,Infinity} | 0.5 | {"[-1,1)","[0,4)","[1,4)","[1,100)"} +(1 row) + +-- 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 + ); +ERROR: unable to determine element type of attribute "arange" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. +-- +-- 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 +-- stacollN values. Because oids are not stable across databases, we can only +-- test this when the source and destination are on the same database +-- instance. For that reason, we borrow and adapt a query found in fe_utils +-- and used by pg_dump/pg_upgrade. +-- +INSERT INTO stats_import.test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import.complex_type, int4range(1,4), array['red','green'] +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import.complex_type, int4range(1,4), array['blue','yellow'] +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import.complex_type, int4range(-1,1), array['"orange"', 'purple', 'cyan'] +UNION ALL +SELECT 4, 'four', NULL, int4range(0,100), NULL; +CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); +-- Generate statistics on table with data +ANALYZE stats_import.test; +CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) + WITH (autovacuum_enabled = false); +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; + schemaname | tablename | attname | inherited +--------------+-----------+---------+----------- + stats_import | is_odd | expr | f + stats_import | test | arange | f + stats_import | test | comp | f + stats_import | test | id | f + stats_import | test | name | f + stats_import | test | tags | f +(6 rows) + +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; + relname | num_stats +--------------+----------- + is_odd | 1 + is_odd_clone | 1 + test | 5 + 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 +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(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 +---------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----+-----+-----+-----+-----+----------- +(0 rows) + DROP SCHEMA stats_import CASCADE; -NOTICE: drop cascades to 5 other objects +NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to type stats_import.complex_type drop cascades to table stats_import.test drop cascades to sequence stats_import.testseq drop cascades to view stats_import.testview drop cascades to table stats_import.part_parent +drop cascades to table stats_import.test_clone diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index 126b3ab9b9c..9b6c9094862 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -121,4 +121,549 @@ SELECT 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: 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: 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); + +-- error: null_frac null +SELECT pg_catalog.pg_set_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'id'::name, + inherited => false::boolean, + null_frac => NULL::real, + avg_width => 2::integer, + n_distinct => 0.3::real); + +-- error: avg_width null +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 => NULL::integer, + n_distinct => 0.3::real); + +-- error: avg_width null +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 => NULL::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[] + ); + +-- warning: 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 + ); +-- +-- 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 +-- stacollN values. Because oids are not stable across databases, we can only +-- test this when the source and destination are on the same database +-- instance. For that reason, we borrow and adapt a query found in fe_utils +-- and used by pg_dump/pg_upgrade. +-- +INSERT INTO stats_import.test +SELECT 1, 'one', (1, 1.1, 'ONE', '2001-01-01', '{ "xkey": "xval" }')::stats_import.complex_type, int4range(1,4), array['red','green'] +UNION ALL +SELECT 2, 'two', (2, 2.2, 'TWO', '2002-02-02', '[true, 4, "six"]')::stats_import.complex_type, int4range(1,4), array['blue','yellow'] +UNION ALL +SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import.complex_type, int4range(-1,1), array['"orange"', 'purple', 'cyan'] +UNION ALL +SELECT 4, 'four', NULL, int4range(0,100), NULL; + +CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); + +-- Generate statistics on table with data +ANALYZE stats_import.test; + +CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) + WITH (autovacuum_enabled = false); + +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; + DROP SCHEMA stats_import CASCADE; |