=== Applying patches on top of PostgreSQL commit ID c872516d8fe5ba3ce27e7020fad887d90d308e29 === /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider migrating to /etc/jail.conf. Wed Mar 12 05:38:43 UTC 2025 On branch cf/5517 nothing to commit, working tree clean === using 'git am' to apply patch ./v4-0001-Add-working-input-function-for-pg_ndistinct.patch === Applying: Add working input function for pg_ndistinct. === using 'git am' to apply patch ./v4-0002-Add-working-input-function-for-pg_dependencies.patch === Applying: Add working input function for pg_dependencies. === using 'git am' to apply patch ./v4-0003-Expose-attribute-statistics-functions-for-use-in-.patch === Applying: Expose attribute statistics functions for use in extended_stats. === using 'git am' to apply patch ./v4-0004-Add-extended-statistics-support-functions.patch === Applying: Add extended statistics support functions. error: sha1 information is lacking or useless (src/test/regress/expected/stats_ext.out). error: could not build fake ancestor hint: Use 'git am --show-current-patch=diff' to see the failed patch Patch failed at 0001 Add extended statistics support functions. When you have resolved this problem, run "git am --continue". If you prefer to skip this patch, run "git am --skip" instead. To restore the original branch and stop patching, run "git am --abort". === using patch(1) to apply patch ./v4-0004-Add-extended-statistics-support-functions.patch === patch: unrecognized option `--no-backup-if-mismatch' usage: patch [-bCcEeflNnRstuv] [-B backup-prefix] [-D symbol] [-d directory] [-F max-fuzz] [-i patchfile] [-o out-file] [-p strip-count] [-r rej-name] [-V t | nil | never | none] [-x number] [-z backup-ext] [--posix] [origfile [patchfile]] patch >>>>>> theirs -- Generate statistics on table with data ANALYZE stats_import.test; CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) @@@ -1208,17 -1211,597 +1229,609 @@@ SELECT pg_catalog.pg_clear_attribute_st (1 row) ++<<<<<<< ours +SELECT COUNT(*) +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; ++======= + -- + -- 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, + 'relallfrozen', 3::integer); + WARNING: argument "relation" has type "oid", expected type "regclass" + ERROR: "relation" cannot be NULL + --- error: relation not found + SELECT pg_catalog.pg_restore_relation_stats( + 'relation', 0::regclass, + 'relpages', 17::integer, + 'reltuples', 400.0::real, + 'relallvisible', 4::integer, + 'relallfrozen', 3::integer); + ERROR: could not open relation with OID 0 + -- 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); + WARNING: unrecognized argument name: "nope" + ERROR: could not open relation with OID 0 + -- 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, + 'relallfrozen', 3::integer); + ERROR: name at variadic position 5 is NULL + -- 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, + 'relallfrozen', 3::integer); + ERROR: name at variadic position 5 has type "integer", expected type "text" + -- 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, + 'relallfrozen', 3::integer, + 'relallvisible'); + ERROR: variadic arguments must be name/value pairs + HINT: Provide an even number of variadic arguments that can be divided into pairs. + -- 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, + 'relallfrozen', 3::integer); + ERROR: could not open relation with OID 0 + -- 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: could not open relation with OID 0 + -- 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: "relation" cannot be NULL + -- error: missing attname + 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: must specify either attname or attnum + -- error: both attname and attnum + SELECT pg_catalog.pg_restore_attribute_stats( + 'relation', 'stats_import.test'::regclass, + 'attname', 'id'::name, + 'attnum', 1::smallint, + 'inherited', false::boolean, + 'version', 150000::integer, + 'null_frac', 0.1::real, + 'avg_width', 2::integer, + 'n_distinct', 0.3::real); + ERROR: cannot specify both attname and attnum + -- 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: column "nope" of relation "test" does not exist + -- 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: cannot modify statistics on system column "xmin" + -- 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: "inherited" cannot be NULL + -- error: relation not found + SELECT pg_catalog.pg_clear_relation_stats( + relation => 'stats_import.nope'::regclass); + ERROR: relation "stats_import.nope" does not exist + LINE 2: 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: cannot clear statistics on system column "ctid" + -- error: attname doesn't exist + SELECT pg_catalog.pg_clear_attribute_stats( + relation => 'stats_import.test'::regclass, + attname => 'nope'::name, + inherited => false::boolean); + ERROR: column "nope" of relation "test" does not exist + -- set n_distinct using at attnum (1) that is not in the statistics object + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "1, 3, -1, -2": 4}'::pg_ndistinct + ); + WARNING: pg_ndistinct: invalid attnum for this statistics object: 1 + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- set n_distinct using at attnum that is 0 + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, 0": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4}'::pg_ndistinct + ); + WARNING: pg_ndistinct: invalid attnum for this statistics object: 0 + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- set n_distinct using at attnum that is outside the expression bounds(below -2) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -4": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "1, 3, -1, -2": 4}'::pg_ndistinct + ); + WARNING: pg_ndistinct: invalid attnum for this statistics object: -4 + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- ok + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4}'::pg_ndistinct + ); + pg_restore_extended_stats + --------------------------- + t + (1 row) + + SELECT + e.n_distinct, e.dependencies, e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false + \gx + -[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------------------------------------------------- + n_distinct | {"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4} + dependencies | + most_common_vals | + most_common_val_nulls | + most_common_freqs | + most_common_base_freqs | + + -- set dependencies using at attnum (1) that is not in the statistics object + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + WARNING: pg_dependencies: invalid attnum for this statistics object: 1 + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- set dependencies using at attnum that is 0 + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 3": 1.000000, "0 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + WARNING: pg_dependencies: invalid attnum for this statistics object: 0 + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- set dependencies using at attnum that is outside the expression bounds(below -2) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 3": 1.000000, "2 => -3": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + WARNING: pg_dependencies: invalid attnum for this statistics object: -3 + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- ok + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 3": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + pg_restore_extended_stats + --------------------------- + t + (1 row) + + SELECT + e.n_distinct, e.dependencies, e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false + \gx + -[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + n_distinct | {"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4} + dependencies | {"2 => 3": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000} + most_common_vals | + most_common_val_nulls | + most_common_freqs | + most_common_base_freqs | + + -- if any one mcv param specified, all four must be specified (part 1) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[] + ); + WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- if any one mcv param specified, all four must be specified (part 2) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[] + ); + WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- if any one mcv param specified, all four must be specified (part 3) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[] + ); + WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- if any one mcv param specified, all four must be specified (part 4) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + WARNING: MCV parameters "most_common_vals", "most_common_val_nulls", "most_common_freqs", and "most_common_base_freqs" must be all specified if any are specified + pg_restore_extended_stats + --------------------------- + f + (1 row) + + -- ok + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + pg_restore_extended_stats + --------------------------- + t + (1 row) + + SELECT + e.n_distinct, e.dependencies, e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false + \gx + -[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + n_distinct | {"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4} + dependencies | {"2 => 3": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000} + most_common_vals | {{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}} + most_common_val_nulls | {{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}} + most_common_freqs | {0.25,0.25,0.25,0.25} + most_common_base_freqs | {0.00390625,0.015625,0.00390625,0.015625} + + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[] + ); + pg_restore_extended_stats + --------------------------- + t + (1 row) + + SELECT + e.inherited, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram + FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + and e.inherited = false + \gx + -[ RECORD 1 ]----------+------- + inherited | f + null_frac | 0 + avg_width | 4 + n_distinct | -0.75 + most_common_vals | {1} + most_common_freqs | {0.5} + histogram_bounds | {-1,0} + correlation | -0.6 + most_common_elems | + most_common_elem_freqs | + elem_count_histogram | + -[ RECORD 2 ]----------+------- + inherited | f + null_frac | 0.25 + avg_width | 4 + n_distinct | -0.5 + most_common_vals | {2} + most_common_freqs | {0.5} + histogram_bounds | + correlation | 1 + most_common_elems | + most_common_elem_freqs | + elem_count_histogram | + + SELECT + pg_catalog.pg_clear_extended_stats( + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat_clone', + inherited => false); + pg_clear_extended_stats + ------------------------- + + (1 row) + + SELECT COUNT(*) + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false; ++>>>>>>> theirs count ------- 0 (1 row) ++<<<<<<< ours ++======= + SELECT COUNT(*) + FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false; + count + ------- + 0 + (1 row) + + -- + -- Copy stats from test_stat to test_stat_clone + -- + SELECT + e.statistics_name, + pg_catalog.pg_restore_extended_stats( + '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_val_nulls', e.most_common_val_nulls, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs + ) + FROM pg_stats_ext AS e + CROSS JOIN LATERAL ( + SELECT + array_agg( + ARRAY[ee.null_frac::text, ee.avg_width::text, + ee.n_distinct::text, ee.most_common_vals::text, + ee.most_common_freqs::text, ee.histogram_bounds::text, + ee.correlation::text, ee.most_common_elems::text, + ee.most_common_elem_freqs::text, + ee.elem_count_histogram::text]) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname + AND ee.statistics_name = e.statistics_name + AND ee.inherited = e.inherited + ) AS x(exprs) + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat'; + statistics_name | pg_restore_extended_stats + -----------------+--------------------------- + test_stat | t + (1 row) + + SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, 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_val_nulls, 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_val_nulls | most_common_freqs | most_common_base_freqs + -----------+------------+--------------+------------------+-----------------------+-------------------+------------------------ + (0 rows) + + SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, 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_val_nulls, 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_val_nulls | most_common_freqs | most_common_base_freqs + -----------+------------+--------------+------------------+-----------------------+-------------------+------------------------ + (0 rows) + + SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' + AND o.statistics_name = 'test_stat' + EXCEPT + SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' + AND n.statistics_name = 'test_stat_clone'; + inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram + -----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- + (0 rows) + + SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' + AND n.statistics_name = 'test_stat_clone' + EXCEPT + SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' + AND o.statistics_name = 'test_stat'; + inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram + -----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+---------------------- + (0 rows) + ++>>>>>>> theirs DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to type stats_import.complex_type diff --cc src/test/regress/sql/stats_import.sql index 0ec590688c2,84462f94deb..00000000000 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@@ -692,6 -686,19 +692,22 @@@ SELECT 4, 'four', NULL, int4range(0,100 CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); ++<<<<<<< ours ++======= + -- restoring stats on index + SELECT * FROM pg_catalog.pg_restore_relation_stats( + 'relation', 'stats_import.is_odd'::regclass, + 'version', '180000'::integer, + 'relpages', '11'::integer, + 'reltuples', '10000'::real, + 'relallvisible', '0'::integer, + 'relallfrozen', '0'::integer + ); + + CREATE STATISTICS stats_import.test_stat ON name, comp, lower(arange), array_length(tags,1) + FROM stats_import.test; + ++>>>>>>> theirs -- Generate statistics on table with data ANALYZE stats_import.test; @@@ -844,24 -854,458 +863,322 @@@ FROM pg_statistic 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, - 'relallfrozen', 3::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, - 'relallfrozen', 3::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, - 'relallfrozen', 3::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, - 'relallfrozen', 3::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, - 'relallfrozen', 3::integer, - '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, - 'relallfrozen', 3::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: missing attname -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: both attname and attnum -SELECT pg_catalog.pg_restore_attribute_stats( - 'relation', 'stats_import.test'::regclass, - 'attname', 'id'::name, - 'attnum', 1::smallint, - '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); +-- attribute stats exist before a clear, but not after +SELECT COUNT(*) +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; --- error: attribute is system column SELECT pg_catalog.pg_clear_attribute_stats( relation => 'stats_import.test'::regclass, - attname => 'ctid'::name, + attname => 'arange'::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); +SELECT COUNT(*) +FROM pg_stats +WHERE schemaname = 'stats_import' +AND tablename = 'test' +AND inherited = false +AND attname = 'arange'; + -- set n_distinct using at attnum (1) that is not in the statistics object + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "1, 3, -1, -2": 4}'::pg_ndistinct + ); + + -- set n_distinct using at attnum that is 0 + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, 0": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4}'::pg_ndistinct + ); + + -- set n_distinct using at attnum that is outside the expression bounds(below -2) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -4": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "1, 3, -1, -2": 4}'::pg_ndistinct + ); + + -- ok + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'n_distinct', '{"2, 3": 4, "2, -1": 4, "2, -2": 4, "3, -1": 4, "3, -2": 4, "-1, -2": 3, "2, 3, -1": 4, "2, 3, -2": 4, "2, -1, -2": 4, "3, -1, -2": 4, "2, 3, -1, -2": 4}'::pg_ndistinct + ); + + SELECT + e.n_distinct, e.dependencies, e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false + \gx + + -- set dependencies using at attnum (1) that is not in the statistics object + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 1": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + + -- set dependencies using at attnum that is 0 + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 3": 1.000000, "0 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + + -- set dependencies using at attnum that is outside the expression bounds(below -2) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 3": 1.000000, "2 => -3": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + + -- ok + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'dependencies', '{"2 => 3": 1.000000, "2 => -1": 1.000000, "2 => -2": 1.000000, "3 => 2": 1.000000, "3 => -1": 1.000000, "3 => -2": 1.000000, "-1 => 2": 0.500000, "-1 => 3": 0.500000, "-1 => -2": 1.000000, "-2 => 2": 0.500000, "-2 => 3": 0.500000, "-2 => -1": 1.000000, "2, 3 => -1": 1.000000, "2, 3 => -2": 1.000000, "2, -1 => 3": 1.000000, "2, -1 => -2": 1.000000, "2, -2 => 3": 1.000000, "2, -2 => -1": 1.000000, "3, -1 => 2": 1.000000, "3, -1 => -2": 1.000000, "3, -2 => 2": 1.000000, "3, -2 => -1": 1.000000, "-1, -2 => 2": 0.500000, "-1, -2 => 3": 0.500000, "2, 3, -1 => -2": 1.000000, "2, 3, -2 => -1": 1.000000, "2, -1, -2 => 3": 1.000000, "3, -1, -2 => 2": 1.000000}'::pg_dependencies + ); + + SELECT + e.n_distinct, e.dependencies, e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false + \gx + + -- if any one mcv param specified, all four must be specified (part 1) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[] + ); + + -- if any one mcv param specified, all four must be specified (part 2) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[] + ); + + -- if any one mcv param specified, all four must be specified (part 3) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[] + ); + + -- if any one mcv param specified, all four must be specified (part 4) + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + + -- ok + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'most_common_vals', '{{four,NULL,0,NULL},{one,"(1,1.1,ONE,01-01-2001,\"{\"\"xkey\"\": \"\"xval\"\"}\")",1,2},{tre,"(3,3.3,TRE,03-03-2003,)",-1,3},{two,"(2,2.2,TWO,02-02-2002,\"[true, 4, \"\"six\"\"]\")",1,2}}'::text[], + 'most_common_val_nulls', '{{f,t,f,t},{f,f,f,f},{f,f,f,f},{f,f,f,f}}'::boolean[], + 'most_common_freqs', '{0.25,0.25,0.25,0.25}'::double precision[], + 'most_common_base_freqs', '{0.00390625,0.015625,0.00390625,0.015625}'::double precision[] + ); + + SELECT + e.n_distinct, e.dependencies, e.most_common_vals, e.most_common_val_nulls, + e.most_common_freqs, e.most_common_base_freqs + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false + \gx + + SELECT + pg_catalog.pg_restore_extended_stats( + 'statistics_schemaname', 'stats_import', + 'statistics_name', 'test_stat_clone', + 'inherited', false, + 'exprs', '{{0,4,-0.75,"{1}","{0.5}","{-1,0}",-0.6,NULL,NULL,NULL},{0.25,4,-0.5,"{2}","{0.5}",NULL,1,NULL,NULL,NULL}}'::text[] + ); + + SELECT + e.inherited, e.null_frac, e.avg_width, e.n_distinct, e.most_common_vals, + e.most_common_freqs, e.histogram_bounds, e.correlation, + e.most_common_elems, e.most_common_elem_freqs, e.elem_count_histogram + FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + and e.inherited = false + \gx + + SELECT + pg_catalog.pg_clear_extended_stats( + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat_clone', + inherited => false); + + SELECT COUNT(*) + FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false; + + SELECT COUNT(*) + FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat_clone' + AND e.inherited = false; + + -- + -- Copy stats from test_stat to test_stat_clone + -- + SELECT + e.statistics_name, + pg_catalog.pg_restore_extended_stats( + '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_val_nulls', e.most_common_val_nulls, + 'most_common_freqs', e.most_common_freqs, + 'most_common_base_freqs', e.most_common_base_freqs, + 'exprs', x.exprs + ) + FROM pg_stats_ext AS e + CROSS JOIN LATERAL ( + SELECT + array_agg( + ARRAY[ee.null_frac::text, ee.avg_width::text, + ee.n_distinct::text, ee.most_common_vals::text, + ee.most_common_freqs::text, ee.histogram_bounds::text, + ee.correlation::text, ee.most_common_elems::text, + ee.most_common_elem_freqs::text, + ee.elem_count_histogram::text]) + FROM pg_stats_ext_exprs AS ee + WHERE ee.statistics_schemaname = e.statistics_schemaname + AND ee.statistics_name = e.statistics_name + AND ee.inherited = e.inherited + ) AS x(exprs) + WHERE e.statistics_schemaname = 'stats_import' + AND e.statistics_name = 'test_stat'; + + SELECT o.inherited, + o.n_distinct, o.dependencies, o.most_common_vals, + o.most_common_val_nulls, 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_val_nulls, 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'; + + SELECT n.inherited, + n.n_distinct, n.dependencies, n.most_common_vals, + n.most_common_val_nulls, 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_val_nulls, o.most_common_freqs, + o.most_common_base_freqs + FROM pg_stats_ext AS o + WHERE o.statistics_schemaname = 'stats_import' + AND o.statistics_name = 'test_stat'; + + SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' + AND o.statistics_name = 'test_stat' + EXCEPT + SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' + AND n.statistics_name = 'test_stat_clone'; + + SELECT n.inherited, + n.null_frac, n.avg_width, n.n_distinct, + n.most_common_vals::text AS most_common_vals, + n.most_common_freqs, + n.histogram_bounds::text AS histogram_bounds, + n.correlation, + n.most_common_elems::text AS most_common_elems, + n.most_common_elem_freqs, n.elem_count_histogram + FROM pg_stats_ext_exprs AS n + WHERE n.statistics_schemaname = 'stats_import' + AND n.statistics_name = 'test_stat_clone' + EXCEPT + SELECT o.inherited, + o.null_frac, o.avg_width, o.n_distinct, + o.most_common_vals::text AS most_common_vals, + o.most_common_freqs, + o.histogram_bounds::text AS histogram_bounds, + o.correlation, + o.most_common_elems::text AS most_common_elems, + o.most_common_elem_freqs, o.elem_count_histogram + FROM pg_stats_ext_exprs AS o + WHERE o.statistics_schemaname = 'stats_import' + AND o.statistics_name = 'test_stat'; + DROP SCHEMA stats_import CASCADE;