Review of v2 for bug #19715: statistics round trip through pg_dump Builds: master 2c10c2ce4d7, REL_19_STABLE 2c5cd772b90, REL_18_STABLE 66d1de70c84 (--enable-cassert --enable-debug); v2 applied with git am on master and REL_19. "===== roundtrip_setup.sql =====" -- Source database for the pg_dump --statistics round trip. -- One column per case: the base type, a domain over it, a domain over that -- domain, and a domain with a CHECK constraint, for each kind of statistics -- that depends on the type (range, multirange, tsvector, array elements). -- Plus an array of a domain, a range with its own collation, and a domain -- over a collatable type. CREATE DOMAIN d_r AS int4range; CREATE DOMAIN dd_r AS d_r; CREATE DOMAIN dc_r AS int4range CHECK (NOT isempty(VALUE) OR VALUE IS NULL OR true); CREATE DOMAIN d_mr AS int4multirange; CREATE DOMAIN dd_mr AS d_mr; CREATE DOMAIN d_tsv AS tsvector; CREATE DOMAIN dd_tsv AS d_tsv; CREATE DOMAIN d_arr AS int4[]; CREATE DOMAIN dd_arr AS d_arr; CREATE DOMAIN d_int AS int4; CREATE TYPE crange AS RANGE (subtype = text, collation = "C"); CREATE DOMAIN d_crange AS crange; CREATE DOMAIN d_ctext AS text COLLATE "C"; CREATE TABLE t ( r int4range, dr d_r, ddr dd_r, dcr dc_r, mr int4multirange, dmr d_mr, ddmr dd_mr, tsv tsvector, dtsv d_tsv, ddtsv dd_tsv, arr int4[], darr d_arr, ddarr dd_arr, arrd d_int[], cr crange, dcrange d_crange, ctext d_ctext ); INSERT INTO t SELECT int4range(g % 97, g % 97 + 1 + g % 13), int4range(g % 97, g % 97 + 1 + g % 13), int4range(g % 97, g % 97 + 1 + g % 13), CASE WHEN g % 10 = 0 THEN 'empty'::int4range ELSE int4range(g % 89, g % 89 + 3) END, int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)), int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)), int4multirange(int4range(g % 50, g % 50 + 2), int4range(g % 50 + 10, g % 50 + 15)), to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3), to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3), to_tsvector('simple', 'w' || g % 40 || ' w' || g % 7 || ' x' || g % 3), ARRAY[g % 30, g % 7, g % 3], ARRAY[g % 30, g % 7, g % 3], ARRAY[g % 30, g % 7, g % 3], ARRAY[g % 30, g % 7, g % 3]::d_int[], crange('k' || g % 60, 'k' || g % 60 || 'z'), crange('k' || g % 60, 'k' || g % 60 || 'z'), 'v' || g % 300 FROM generate_series(1, 5000) g; -- Expression statistics over the same domains (extended stats restore path). -- A bare cast of the column folds back into the column, so each expression -- goes through a function first, as in the tests of v2. CREATE STATISTICS t_exprs ON (range_merge(dr, dr)::d_r), (range_merge(ddr, ddr)::dd_r), ((dmr + '{}'::int4multirange)::d_mr), ((ddmr + '{}'::int4multirange)::dd_mr), (strip(dtsv)::d_tsv), (strip(ddtsv)::dd_tsv), ((darr || '{}'::int4[])::d_arr), (range_merge(dcrange, dcrange)::d_crange) FROM t; ANALYZE t; "===== roundtrip.sh =====" #!/bin/bash # Round trip of statistics through pg_dump --statistics-only for the columns # in roundtrip_setup.sql, on each build. For every column (pg_stats) and # every statistics expression (pg_stats_ext_exprs), each field is compared # between the source database and the restored one. Prints, per build, the # restore WARNINGs and the fields that did not come through. # roundtrip.sh [build...] (default: 18 head v2 19v2) set -u A=$(cd "$(dirname "$0")" && pwd) BUILDS=${*:-18 head v2 19v2} OUT=$A/roundtrip.out; : > $OUT for B in $BUILDS; do I=$HOME/pg19715/i-$B/bin D=$(mktemp -d /tmp/claude-1000/rt.XXXX); P=55460 "$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null printf "port = $P\nunix_socket_directories = '/tmp'\n" >> $D/postgresql.conf "$I/pg_ctl" -D $D -l $D/log -w start >/dev/null q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; } q -c "CREATE DATABASE src" -c "CREATE DATABASE dst" q -d src -v ON_ERROR_STOP=1 -f $A/roundtrip_setup.sql > $D/setup.log 2>&1 || { echo "$B: setup failed"; cat $D/setup.log; } "$I/pg_dump" -h /tmp -p $P -U postgres --schema-only src | q -d dst > /dev/null 2>&1 "$I/pg_dump" -h /tmp -p $P -U postgres --statistics-only src > $D/stats.sql q -d dst -f $D/stats.sql > $D/restore.out 2> $D/restore.err # One line per (object, field, value); objects are columns and expressions. flat=" SELECT 'col ' || attname AS obj, f.key, f.value FROM pg_stats s, jsonb_each_text(to_jsonb(s) - 'tableid' - 'schemaname' - 'tablename' - 'attname' - 'inherited') f WHERE tablename = 't' UNION ALL SELECT 'expr ' || expr, f.key, f.value FROM pg_stats_ext_exprs s, jsonb_each_text(to_jsonb(s) - 'tableid' - 'schemaname' - 'tablename' - 'statistics_schemaname' - 'statistics_name' - 'statistics_owner' - 'statistics_id' - 'expr' - 'inherited') f WHERE tablename = 't' ORDER BY 1, 2" q -d src -F $'\t' -c "$flat" > $D/src.tsv 2>/dev/null q -d dst -F $'\t' -c "$flat" > $D/dst.tsv 2>/dev/null { echo "===== $B: $("$I/postgres" --version)" echo "restore WARNINGs: $(grep -c WARNING $D/restore.err)" grep -A1 WARNING $D/restore.err | grep -v '^--$' | sed 's/^psql:[^ ]* //' | sort | uniq -c | sed 's/^/ /' echo "fields that differ after the round trip (object: field):" diff <(sort $D/src.tsv) <(sort $D/dst.tsv) | grep '^<' | cut -f1,2 | sed 's/^< / /; s/\t/: /' | sort -u echo "objects with statistics: src $(cut -f1 $D/src.tsv | sort -u | wc -l), dst $(cut -f1 $D/dst.tsv | sort -u | wc -l)" echo } | tee -a $OUT "$I/pg_ctl" -D $D -m fast -w stop >/dev/null rm -rf $D done ===== ./roundtrip.sh 18 head v2 19v2 ===== ===== 18: postgres (PostgreSQL) 18.6 restore WARNINGs: 8 2 DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. 6 DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. 1 WARNING: column "dcrange" is not a range type 1 WARNING: column "dcr" is not a range type 1 WARNING: column "ddmr" is not a range type 1 WARNING: column "ddr" is not a range type 1 WARNING: column "dmr" is not a range type 1 WARNING: column "dr" is not a range type 1 WARNING: could not determine element type of column "ddtsv" 1 WARNING: could not determine element type of column "dtsv" fields that differ after the round trip (object: field): col dcrange: range_bounds_histogram col dcrange: range_empty_frac col dcrange: range_length_histogram col dcr: range_bounds_histogram col dcr: range_empty_frac col dcr: range_length_histogram col ddmr: range_bounds_histogram col ddmr: range_empty_frac col ddmr: range_length_histogram col ddr: range_bounds_histogram col ddr: range_empty_frac col ddr: range_length_histogram col ddtsv: most_common_elem_freqs col ddtsv: most_common_elems col dmr: range_bounds_histogram col dmr: range_empty_frac col dmr: range_length_histogram col dr: range_bounds_histogram col dr: range_empty_frac col dr: range_length_histogram col dtsv: most_common_elem_freqs col dtsv: most_common_elems expr (((darr)::integer[] || '{}'::integer[]))::d_arr: avg_width expr (((darr)::integer[] || '{}'::integer[]))::d_arr: correlation expr (((darr)::integer[] || '{}'::integer[]))::d_arr: elem_count_histogram expr (((darr)::integer[] || '{}'::integer[]))::d_arr: histogram_bounds expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elem_freqs expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elems expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_freqs expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_vals expr (((darr)::integer[] || '{}'::integer[]))::d_arr: n_distinct expr (((darr)::integer[] || '{}'::integer[]))::d_arr: null_frac expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: avg_width expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: n_distinct expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: null_frac expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: avg_width expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: n_distinct expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: null_frac expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: avg_width expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: n_distinct expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: null_frac expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: avg_width expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: n_distinct expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: null_frac expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: avg_width expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: n_distinct expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: null_frac expr (strip((ddtsv)::tsvector))::dd_tsv: avg_width expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elem_freqs expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elems expr (strip((ddtsv)::tsvector))::dd_tsv: n_distinct expr (strip((ddtsv)::tsvector))::dd_tsv: null_frac expr (strip((dtsv)::tsvector))::d_tsv: avg_width expr (strip((dtsv)::tsvector))::d_tsv: most_common_elem_freqs expr (strip((dtsv)::tsvector))::d_tsv: most_common_elems expr (strip((dtsv)::tsvector))::d_tsv: n_distinct expr (strip((dtsv)::tsvector))::d_tsv: null_frac objects with statistics: src 25, dst 25 ===== head: postgres (PostgreSQL) 20devel restore WARNINGs: 15 2 DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. 6 DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. 5 HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type. 1 WARNING: column "dcrange" is not a range type 1 WARNING: column "dcr" is not a range type 1 WARNING: column "ddmr" is not a range type 1 WARNING: column "ddr" is not a range type 1 WARNING: column "dmr" is not a range type 1 WARNING: column "dr" is not a range type 1 WARNING: could not determine element type of column "ddtsv" 1 WARNING: could not determine element type of column "dtsv" 1 WARNING: could not parse "exprs": invalid data in expression -1 1 WARNING: could not parse "exprs": invalid data in expression -2 1 WARNING: could not parse "exprs": invalid data in expression -3 1 WARNING: could not parse "exprs": invalid data in expression -4 1 WARNING: could not parse "exprs": invalid data in expression -8 1 WARNING: could not parse "exprs": invalid element type in expression -5 1 WARNING: could not parse "exprs": invalid element type in expression -6 fields that differ after the round trip (object: field): col dcrange: range_bounds_histogram col dcrange: range_empty_frac col dcrange: range_length_histogram col dcr: range_bounds_histogram col dcr: range_empty_frac col dcr: range_length_histogram col ddmr: range_bounds_histogram col ddmr: range_empty_frac col ddmr: range_length_histogram col ddr: range_bounds_histogram col ddr: range_empty_frac col ddr: range_length_histogram col ddtsv: most_common_elem_freqs col ddtsv: most_common_elems col dmr: range_bounds_histogram col dmr: range_empty_frac col dmr: range_length_histogram col dr: range_bounds_histogram col dr: range_empty_frac col dr: range_length_histogram col dtsv: most_common_elem_freqs col dtsv: most_common_elems expr (((darr)::integer[] || '{}'::integer[]))::d_arr: avg_width expr (((darr)::integer[] || '{}'::integer[]))::d_arr: correlation expr (((darr)::integer[] || '{}'::integer[]))::d_arr: elem_count_histogram expr (((darr)::integer[] || '{}'::integer[]))::d_arr: histogram_bounds expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elem_freqs expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_elems expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_freqs expr (((darr)::integer[] || '{}'::integer[]))::d_arr: most_common_vals expr (((darr)::integer[] || '{}'::integer[]))::d_arr: n_distinct expr (((darr)::integer[] || '{}'::integer[]))::d_arr: null_frac expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: avg_width expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: n_distinct expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: null_frac expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_bounds_histogram expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_empty_frac expr (((ddmr)::int4multirange + '{}'::int4multirange))::dd_mr: range_length_histogram expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: avg_width expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: n_distinct expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: null_frac expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_bounds_histogram expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_empty_frac expr (((dmr)::int4multirange + '{}'::int4multirange))::d_mr: range_length_histogram expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: avg_width expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: n_distinct expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: null_frac expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_bounds_histogram expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_empty_frac expr (range_merge((dcrange)::crange, (dcrange)::crange))::d_crange: range_length_histogram expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: avg_width expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: n_distinct expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: null_frac expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_bounds_histogram expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_empty_frac expr (range_merge((ddr)::int4range, (ddr)::int4range))::dd_r: range_length_histogram expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: avg_width expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: n_distinct expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: null_frac expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_bounds_histogram expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_empty_frac expr (range_merge((dr)::int4range, (dr)::int4range))::d_r: range_length_histogram expr (strip((ddtsv)::tsvector))::dd_tsv: avg_width expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elem_freqs expr (strip((ddtsv)::tsvector))::dd_tsv: most_common_elems expr (strip((ddtsv)::tsvector))::dd_tsv: n_distinct expr (strip((ddtsv)::tsvector))::dd_tsv: null_frac expr (strip((dtsv)::tsvector))::d_tsv: avg_width expr (strip((dtsv)::tsvector))::d_tsv: most_common_elem_freqs expr (strip((dtsv)::tsvector))::d_tsv: most_common_elems expr (strip((dtsv)::tsvector))::d_tsv: n_distinct expr (strip((dtsv)::tsvector))::d_tsv: null_frac objects with statistics: src 25, dst 25 ===== v2: postgres (PostgreSQL) 20devel restore WARNINGs: 0 fields that differ after the round trip (object: field): objects with statistics: src 25, dst 25 ===== 19v2: postgres (PostgreSQL) 19beta4 restore WARNINGs: 0 fields that differ after the round trip (object: field): objects with statistics: src 25, dst 25 "===== one_expr.sh =====" #!/bin/bash # On a build without the fix: is the d_arr expression lost by itself, or # only when it shares a statistics object with a rejected expression? # one_expr.sh [build] (default: head) set -u B=${1:-head} I=$HOME/pg19715/i-$B/bin D=$(mktemp -d /tmp/claude-1000/oe.XXXX); P=55462 "$I/initdb" -D $D -A trust --no-sync -U postgres >/dev/null printf "port = $P\nunix_socket_directories = '/tmp'\n" >> $D/postgresql.conf "$I/pg_ctl" -D $D -l $D/log -w start >/dev/null q() { "$I/psql" -X -qAt -h /tmp -p $P -U postgres "$@"; } q -c "CREATE DATABASE src" -c "CREATE DATABASE dst" q -d src <<'EOF' CREATE DOMAIN d_r AS int4range; CREATE DOMAIN d_arr AS int4[]; CREATE TABLE t (dr d_r, darr d_arr); INSERT INTO t SELECT int4range(g % 97, g % 97 + 5), ARRAY[g % 30, g % 7] FROM generate_series(1, 5000) g; -- s_arr: only the array expression. s_mix: the same plus a range one. CREATE STATISTICS s_arr ON ((darr || '{}'::int4[])::d_arr), (dr IS NULL) FROM t; CREATE STATISTICS s_mix ON ((darr || '{}'::int4[])::d_arr), (range_merge(dr, dr)::d_r) FROM t; ANALYZE t; EOF "$I/pg_dump" -h /tmp -p $P -U postgres --schema-only src | q -d dst >/dev/null 2>&1 "$I/pg_dump" -h /tmp -p $P -U postgres --statistics-only src | q -d dst 2>&1 >/dev/null | grep WARNING chk="SELECT statistics_name, left(expr, 40), null_frac IS NOT NULL AS has_null_frac, most_common_elems IS NOT NULL AS has_mcelem FROM pg_stats_ext_exprs WHERE tablename = 't' ORDER BY 1, 2" echo "--- src"; q -d src -c "$chk" echo "--- dst"; q -d dst -c "$chk" "$I/pg_ctl" -D $D -m fast -w stop >/dev/null rm -rf $D ===== ./one_expr.sh head ===== WARNING: column "dr" is not a range type WARNING: could not parse "exprs": invalid data in expression -2 --- src s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t s_arr|(dr IS NULL)|t|f s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f --- dst s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t s_arr|(dr IS NULL)|t|f s_mix|(((darr)::integer[] || '{}'::integer[]))|f|f s_mix|(range_merge((dr)::int4range, (dr)::int4|f|f ===== ./one_expr.sh v2 ===== --- src s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t s_arr|(dr IS NULL)|t|f s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f --- dst s_arr|(((darr)::integer[] || '{}'::integer[]))|t|t s_arr|(dr IS NULL)|t|f s_mix|(((darr)::integer[] || '{}'::integer[]))|t|t s_mix|(range_merge((dr)::int4range, (dr)::int4|t|f ===== make check ===== master + v2: All 239 tests passed. REL_19_STABLE + v2: All 239 tests passed. master + only the v2 test changes: stats_import fails; regression.diffs: diff -U3 /home/manu/pg19715/src-head/src/test/regress/expected/stats_import.out /home/manu/pg19715/b-head/src/test/regress/results/stats_import.out --- /home/manu/pg19715/src-head/src/test/regress/expected/stats_import.out 2026-09-24 22:01:12.592950036 -0300 +++ /home/manu/pg19715/b-head/src/test/regress/results/stats_import.out 2026-09-24 22:01:23.325666884 -0300 @@ -1547,9 +1547,11 @@ 'range_empty_frac', '0'::real, 'range_bounds_histogram', '{"[1,3)","[5,9)","[11,15)"}'::text ); +WARNING: column "drange" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. pg_restore_attribute_stats ---------------------------- - t + f (1 row) SELECT * @@ -1558,9 +1560,9 @@ AND tablename = 'test_dom' AND inherited = false AND attname = 'drange'; - 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_dom | drange | f | 0 | 0 | 0 | | | | | | | | {2,4,4} | 0 | {"[1,3)","[5,9)","[11,15)"} + 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_dom | drange | f | 0 | 0 | 0 | | | | | | | | | | (1 row) -- ok: range stats for a domain over a multirange type. @@ -1573,9 +1575,11 @@ 'range_empty_frac', '0'::real, 'range_bounds_histogram', '{"[1,30)","[11,30)","[21,130)"}'::text ); +WARNING: column "dmrange" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. pg_restore_attribute_stats ---------------------------- - t + f (1 row) SELECT * @@ -1584,9 +1588,9 @@ AND tablename = 'test_dom' AND inherited = false AND attname = 'dmrange'; - 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_dom | dmrange | f | 0 | 0 | 0 | | | | | | | | {29,29,109} | 0 | {"[1,30)","[11,30)","[21,130)"} + 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_dom | dmrange | f | 0 | 0 | 0 | | | | | | | | | | (1 row) -- warn: multirange values in the bounds histogram of a domain. These @@ -1600,8 +1604,8 @@ 'range_empty_frac', '0'::real, 'range_bounds_histogram', '{"{[1,30)}","{[11,30)}"}'::text ); -WARNING: malformed range literal: "{[1,30)}" -DETAIL: Missing left parenthesis or bracket. +WARNING: column "dmrange" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. pg_restore_attribute_stats ---------------------------- f @@ -1635,17 +1639,148 @@ WHERE s.schemaname = 'stats_import' AND s.tablename = 'test_dom' ORDER BY s.attname, s.inherited; +WARNING: column "drange" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. +WARNING: column "dmrange" is not a range type +DETAIL: Cannot set STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM or STATISTIC_KIND_BOUNDS_HISTOGRAM. attname | inherited | r ---------+-----------+--- - dmrange | f | t - drange | f | t + dmrange | f | f + drange | f | f id | f | t (3 rows) SELECT relname, (stats).* FROM stats_import.pg_statistic_get_difference('test_dom', 'test_dom_clone') \gx -(0 rows) +-[ RECORD 1 ]-------------------------------- +relname | test_dom +attname | drange +stainherit | f +stanullfrac | 0 +stawidth | 14 +stadistinct | -1 +stakind1 | 7 +stakind2 | 6 +stakind3 | 0 +stakind4 | 0 +stakind5 | 0 +staop1 | 0 +staop2 | 672 +staop3 | 0 +staop4 | 0 +staop5 | 0 +stacoll1 | 0 +stacoll2 | 0 +stacoll3 | 0 +stacoll4 | 0 +stacoll5 | 0 +stanumbers1 | +stanumbers2 | {0} +stanumbers3 | +stanumbers4 | +stanumbers5 | +sv1 | {"[1,3)","[5,9)","[11,15)"} +sv2 | {2,4,4} +sv3 | +sv4 | +sv5 | +-[ RECORD 2 ]-------------------------------- +relname | test_dom +attname | dmrange +stainherit | f +stanullfrac | 0 +stawidth | 45 +stadistinct | -1 +stakind1 | 7 +stakind2 | 6 +stakind3 | 0 +stakind4 | 0 +stakind5 | 0 +staop1 | 0 +staop2 | 672 +staop3 | 0 +staop4 | 0 +staop5 | 0 +stacoll1 | 0 +stacoll2 | 0 +stacoll3 | 0 +stacoll4 | 0 +stacoll5 | 0 +stanumbers1 | +stanumbers2 | {0} +stanumbers3 | +stanumbers4 | +stanumbers5 | +sv1 | {"[1,30)","[11,30)","[21,130)"} +sv2 | {19,29,109} +sv3 | +sv4 | +sv5 | +-[ RECORD 3 ]-------------------------------- +relname | test_dom_clone +attname | dmrange +stainherit | f +stanullfrac | 0 +stawidth | 45 +stadistinct | -1 +stakind1 | 0 +stakind2 | 0 +stakind3 | 0 +stakind4 | 0 +stakind5 | 0 +staop1 | 0 +staop2 | 0 +staop3 | 0 +staop4 | 0 +staop5 | 0 +stacoll1 | 0 +stacoll2 | 0 +stacoll3 | 0 +stacoll4 | 0 +stacoll5 | 0 +stanumbers1 | +stanumbers2 | +stanumbers3 | +stanumbers4 | +stanumbers5 | +sv1 | +sv2 | +sv3 | +sv4 | +sv5 | +-[ RECORD 4 ]-------------------------------- +relname | test_dom_clone +attname | drange +stainherit | f +stanullfrac | 0 +stawidth | 14 +stadistinct | -1 +stakind1 | 0 +stakind2 | 0 +stakind3 | 0 +stakind4 | 0 +stakind5 | 0 +staop1 | 0 +staop2 | 0 +staop3 | 0 +staop4 | 0 +staop5 | 0 +stacoll1 | 0 +stacoll2 | 0 +stacoll3 | 0 +stacoll4 | 0 +stacoll5 | 0 +stanumbers1 | +stanumbers2 | +stanumbers3 | +stanumbers4 | +stanumbers5 | +sv1 | +sv2 | +sv3 | +sv4 | +sv5 | -- test for a domain over tsvector. CREATE DOMAIN stats_import.dom_tsvector AS tsvector; @@ -1665,9 +1800,11 @@ 'most_common_elems', '{brown,fox,quick}'::text, 'most_common_elem_freqs', '{0.3,0.2,0.2,0.3,0.0}'::real[], 'elem_count_histogram', '{4,4,4,4,4,4,4,4,4,4}'::real[]); +WARNING: could not determine element type of column "v" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. pg_restore_attribute_stats ---------------------------- - t + f (1 row) SELECT * @@ -1676,9 +1813,9 @@ AND tablename = 'test_dom_ts' AND inherited = false AND attname = 'v'; - 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_dom_ts | v | f | 0 | 0 | 0 | | | | | {brown,fox,quick} | {0.3,0.2,0.2,0.3,0} | {4,4,4,4,4,4,4,4,4,4} | | | + 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_dom_ts | v | f | 0 | 0 | 0 | | | | | | | | | | (1 row) -- @@ -1709,16 +1846,81 @@ WHERE s.schemaname = 'stats_import' AND s.tablename = 'test_dom_ts' ORDER BY s.attname, s.inherited; +WARNING: could not determine element type of column "v" +DETAIL: Cannot set STATISTIC_KIND_MCELEM or STATISTIC_KIND_DECHIST. attname | inherited | r ---------+-----------+--- id | f | t - v | f | t + v | f | f (2 rows) SELECT relname, (stats).* FROM stats_import.pg_statistic_get_difference('test_dom_ts', 'test_dom_ts_clone') \gx -(0 rows) +-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------- +relname | test_dom_ts +attname | v +stainherit | f +stanullfrac | 0 +stawidth | 55 +stadistinct | -1 +stakind1 | 4 +stakind2 | 0 +stakind3 | 0 +stakind4 | 0 +stakind5 | 0 +staop1 | 98 +staop2 | 0 +staop3 | 0 +staop4 | 0 +staop5 | 0 +stacoll1 | 100 +stacoll2 | 0 +stacoll3 | 0 +stacoll4 | 0 +stacoll5 | 0 +stanumbers1 | {0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,0.05,1,1,1,0.05,1} +stanumbers2 | +stanumbers3 | +stanumbers4 | +stanumbers5 | +sv1 | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,fox,brown,quick} +sv2 | +sv3 | +sv4 | +sv5 | +-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------- +relname | test_dom_ts_clone +attname | v +stainherit | f +stanullfrac | 0 +stawidth | 55 +stadistinct | -1 +stakind1 | 0 +stakind2 | 0 +stakind3 | 0 +stakind4 | 0 +stakind5 | 0 +staop1 | 0 +staop2 | 0 +staop3 | 0 +staop4 | 0 +staop5 | 0 +stacoll1 | 0 +stacoll2 | 0 +stacoll3 | 0 +stacoll4 | 0 +stacoll5 | 0 +stanumbers1 | +stanumbers2 | +stanumbers3 | +stanumbers4 | +stanumbers5 | +sv1 | +sv2 | +sv3 | +sv4 | +sv5 | -- -- Test the ability to exactly copy data from one table to an identical table, @@ -2983,9 +3185,10 @@ {"range_length_histogram": "{29,29,109}", "range_empty_frac": "0", "range_bounds_histogram": "{\"{[1,30)}\",\"{[11,30)}\"}"}]'::jsonb); -WARNING: malformed range literal: "{[1,30)}" -DETAIL: Missing left parenthesis or bracket. -HINT: Element "range_bounds_histogram" in expression -2 could not be parsed. +WARNING: could not parse "exprs": invalid data in expression -1 +HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type. +WARNING: could not parse "exprs": invalid data in expression -2 +HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type. pg_restore_extended_stats --------------------------- f @@ -3004,9 +3207,13 @@ {"range_length_histogram": "{29,29,109}", "range_empty_frac": "0", "range_bounds_histogram": "{\"[1,30)\",\"[11,30)\",\"[21,130)\"}"}]'::jsonb); +WARNING: could not parse "exprs": invalid data in expression -1 +HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type. +WARNING: could not parse "exprs": invalid data in expression -2 +HINT: "range_length_histogram", "range_empty_frac", and "range_bounds_histogram" can only be set for a range type. pg_restore_extended_stats --------------------------- - t + f (1 row) SELECT e.expr, e.range_length_histogram, e.range_empty_frac, @@ -3018,14 +3225,14 @@ \gx -[ RECORD 1 ]----------+-------------------------------------------------------------------------------- expr | (range_merge((drange)::int4range, (drange)::int4range))::stats_import.dom_range -range_length_histogram | {2,4,4} -range_empty_frac | 0 -range_bounds_histogram | {"[1,3)","[5,9)","[11,15)"} +range_length_histogram | +range_empty_frac | +range_bounds_histogram | -[ RECORD 2 ]----------+-------------------------------------------------------------------------------- expr | (((dmrange)::int4multirange + '{}'::int4multirange))::stats_import.dom_mrange -range_length_histogram | {29,29,109} -range_empty_frac | 0 -range_bounds_histogram | {"[1,30)","[11,30)","[21,130)"} +range_length_histogram | +range_empty_frac | +range_bounds_histogram | -- Check import of MCELEM stats for an expression whose type is a domain -- over tsvector. @@ -3041,9 +3248,10 @@ 'exprs', '[{"most_common_elems": "{brown,fox,quick}", "most_common_elem_freqs": "{0.3,0.2,0.2,0.3,0.0}", "elem_count_histogram": "{4,4,4,4,4,4,4,4,4,4}"}]'::jsonb); +WARNING: could not parse "exprs": invalid element type in expression -1 pg_restore_extended_stats --------------------------- - t + f (1 row) SELECT e.expr, e.most_common_elems, e.most_common_elem_freqs, @@ -3055,9 +3263,9 @@ \gx -[ RECORD 1 ]----------+-------------------------------------------------- expr | (strip((v)::tsvector))::stats_import.dom_tsvector -most_common_elems | {brown,fox,quick} -most_common_elem_freqs | {0.3,0.2,0.2,0.3,0} -elem_count_histogram | {4,4,4,4,4,4,4,4,4,4} +most_common_elems | +most_common_elem_freqs | +elem_count_histogram | -- Incorrect extended stats kind, exprs not supported SELECT pg_catalog.pg_restore_extended_stats(