BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19842
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 19rc1
Operating system: Linux x86_64; platform-independent (reproduced on
Description:
**Description:**
When an extended-statistics object has an expression of a range/multirange
type, and fewer than 2 non-empty range values are sampled by ANALYZE (a
2-row table, or a column where most ranges are empty),
`compute_range_stats()` still produces a statistics slot whose only range
field is `range_empty_frac`. This is legal and intentional:
`pg_stats_ext_exprs` exposes it, and `pg_dump --statistics-only` therefore
emits a `pg_restore_extended_stats()` call whose `exprs` JSON contains only
`range_empty_frac` (no `range_length_histogram`, no
`range_bounds_histogram`).
The restore side rejects exactly this input with a WARNING (and returns
false), so on restore/pg_upgrade the expression statistics are silently
lost. The planner then has no `range_empty_frac` and wrongfully assumes "no
empty ranges", producing wrong selectivity/row estimates.
By the rule established by the statistics-import series (commit 142fd8ff13d
and its series — "the source and the target should hold the same stats data
after restore"), a restore function must accept the statistics that ANALYZE
itself can produce; this is a case that series does not cover.
**Reproduction (self-contained SQL; verified on master):**
```sql
-- source side: 2-row table, range expression with < 2 non-empty values
DROP TABLE IF EXISTS t;
CREATE TABLE t (id int, r int4range);
INSERT INTO t VALUES (1, int4range(1,5)), (2, 'empty'::int4range);
CREATE STATISTICS stx ON (r * int4range(1,10)) FROM t;
ANALYZE t;
-- ANALYZE legitimately produced ONLY range_empty_frac (0.5):
SELECT e.range_length_histogram, e.range_empty_frac,
e.range_bounds_histogram
FROM pg_stats_ext_exprs e
WHERE e.statistics_name='stx' AND NOT e.inherited;
-- (NULL) | 0.5 | (NULL)
-- what pg_dump --statistics-only emits for this object (replayed verbatim):
SELECT
pg_catalog.pg_clear_extended_stats('public','t','public','stx',false);
SELECT * FROM pg_catalog.pg_restore_extended_stats(
'version', '200000'::integer,
'schemaname', 'public',
'relname', 't',
'statistics_schemaname', 'public',
'statistics_name', 'stx',
'inherited', 'f'::boolean,
'exprs', '[{"avg_width": "13","null_frac": "0","n_distinct": "-1",
"range_empty_frac": "0.5"}]'::jsonb
);
-- WARNING: ... (rejected, returns false; restore continues, statistics
lost)
-- after restore: the statistics are gone (expected: range_empty_frac =
0.5):
SELECT e.range_length_histogram, e.range_empty_frac,
e.range_bounds_histogram
FROM pg_stats_ext_exprs e
WHERE e.statistics_name='stx' AND NOT e.inherited;
-- (NULL) | (NULL) | (NULL) <- lost
```
**Actual results:** `pg_restore_extended_stats()` raises a WARNING and
returns false for the exact `exprs` JSON that pg_dump emits from this legal
ANALYZE output; the restored cluster has no `range_empty_frac`.
**Expected behavior:** the restore function should accept a stats slot whose
only range field is `range_empty_frac` (equivalently: treat missing
histogram arrays the same way `compute_range_stats()` does — an empty array
means "no histogram"), restoring `range_empty_frac = 0.5`.
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
On Sat, Nov 07, 2026 at 04:22:06AM +0000, PG Bug reporting form wrote:
> When an extended-statistics object has an expression of a range/multirange
> type, and fewer than 2 non-empty range values are sampled by ANALYZE (a
> 2-row table, or a column where most ranges are empty),
> `compute_range_stats()` still produces a statistics slot whose only range
> field is `range_empty_frac`. This is legal and intentional:
> `pg_stats_ext_exprs` exposes it, and `pg_dump --statistics-only` therefore
> emits a `pg_restore_extended_stats()` call whose `exprs` JSON contains only
> `range_empty_frac` (no `range_length_histogram`, no
> `range_bounds_histogram`).
>
> The restore side rejects exactly this input with a WARNING (and returns
> false), so on restore/pg_upgrade the expression statistics are silently
> lost. The planner then has no `range_empty_frac` and wrongfully assumes "no
> empty ranges", producing wrong selectivity/row estimates.
You are not mentioning the attribute case, that exists down to v18:
CREATE TABLE promo (id int, valid int4range);
INSERT INTO promo
SELECT i, CASE WHEN i = 1 THEN int4range(1, 100) ELSE 'empty' END
FROM generate_series(1, 10000) i;
ANALYZE promo;
SELECT range_length_histogram, range_empty_frac, range_bounds_histogram
FROM pg_stats WHERE tablename = 'promo' AND attname = 'valid';
This generates the following dump data:
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
'version', '200000'::integer,
'schemaname', 'public',
'relname', 'promo',
'attname', 'valid',
'inherited', 'f'::boolean,
'null_frac', '0'::real,
'avg_width', '6'::integer,
'n_distinct', '-1'::real,
'range_length_histogram', '{}'::text,
'range_empty_frac', '0.9999'::real);
And once a restore is attempted:
WARNING: 22023: "range_length_histogram" must be a one-dimensional
array
LOCATION: statatt_build_stavalues, stat_utils.c:627
pg_restore_attribute_stats
----------------------------
f
(1 row)
Hence the extstats case has just been copy-pasting the same rule as
previously, because we have the same 1-dimensional array requirement.
In order to do something about that, we would need to allow a case in
the restore paths where empty arrays are OK on an option-basis. That
means touching array_in_safe() and statatt_build_stavalues(), or
change their interfaces. Invasive, but after seeing 261f89a976bf I
am afraid that we have to fix that especially for the MCEs.
range_bounds_histogram should be mostly esoteric in this case, no?
Still it seems good to make consistent choices across the board,
especially as the range bound histograms require the same fixes.
Corey, what's your take?
--
Michael
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
От:
Corey Huinker <corey.huinker@gmail.com>
Дата:
Hence the extstats case has just been copy-pasting the same rule as
previously, because we have the same 1-dimensional array requirement.
In order to do something about that, we would need to allow a case in
the restore paths where empty arrays are OK on an option-basis. That
means touching array_in_safe() and statatt_build_stavalues(), or
change their interfaces. Invasive, but after seeing 261f89a976bf I
am afraid that we have to fix that especially for the MCEs.
range_bounds_histogram should be mostly esoteric in this case, no?
Still it seems good to make consistent choices across the board,
especially as the range bound histograms require the same fixes.
Corey, what's your take?
Just seeing your reply now, but I also got drawn to 261f89a976bf not making the same (stats->numvalues[k] > 0) to (stats->stavalues[k] != NULL) change for extstats as was made for attrstats.
In addition, we need to complete decouple found/notfound checks for range_bounds_histogram from the other two, as all of those "must be paired" checks are for things that share a stakind.
I'll toy around with some workarounds for the '{}' case and get back to you.
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
On Mon, Nov 09, 2026 at 10:49:38PM -0500, Corey Huinker wrote:
> Just seeing your reply now, but I also got drawn to 261f89a976bf not making
> the same (stats->numvalues[k] > 0) to (stats->stavalues[k] != NULL) change
> for extstats as was made for attrstats.
Yeah, we need to fix that first, I think..
> In addition, we need to complete decouple found/notfound checks for
> range_bounds_histogram from the other two, as all of those "must be paired"
> checks are for things that share a stakind.
>
> I'll toy around with some workarounds for the '{}' case and get back to you.
Okay, thanks. I'll probably tackle some more issues that are assigned
to my head first, which should take a couple of days.
--
Michael
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
Okay, thanks. I'll probably tackle some more issues that are assigned
to my head first, which should take a couple of days.
--
Michael
This bug has the same root cause as bug #19857, but because that one deals exclusively with attribute stats it needs to be backported to 18 as well, so I kept that separate.
0001 - Same bugfix proposed for #19587
0002 - Decoupling range_bounds_histogram from checks against range_length_histogram and range_empty_frac
0003 - The extended stats equivalent of 0001
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
On Tue, Nov 10, 2026 at 08:54:33PM -0500, Corey Huinker wrote: > This bug has the same root cause as bug #19857, but because that one deals > exclusively with attribute stats it needs to be backported to 18 as well, > so I kept that separate. > > 0001 - Same bugfix proposed for #19587 > 0002 - Decoupling range_bounds_histogram from checks against > range_length_histogram and range_empty_frac > 0003 - The extended stats equivalent of 0001 Let's do things in order, and discuss about 0001 first. And I have a few things to say about it.. But let's see that on the other thread. -- Michael
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
On Tue, Nov 10, 2026 at 08:54:33PM -0500, Corey Huinker wrote: > 0003 - The extended stats equivalent of 0001 Corey, could you rebase this one on HEAD, or reshape it according to 2656f664616d? That would help.. -- Michael
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
On Wed, Nov 11, 2026 at 8:34 PM Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Nov 10, 2026 at 08:54:33PM -0500, Corey Huinker wrote:
> 0003 - The extended stats equivalent of 0001
Corey, could you rebase this one on HEAD, or reshape it according to
2656f664616d? That would help..
--
Michael
The v1 0001 patch is now gone as, that was replaced by 2656f664616d.
0001 -
Decoupling range_bounds_histogram. No changes since last patchset where it was number 0002.
0002 -
Fix to pg_dump to handle NULL values in range_length_histogram and most_common_elems when their corresponding stanumbers field is not null.
I was able to test this against 18, however I [1] was not able to synthesize data such that an extended statistics object on 18 that had expression stats with a NULL most_common_elems and NOT NULL most_common_elem_freqs. I asked Claude to try to do it as well, and it too was not able to.
0003 -
This has the same goals as the original 0003, but using the methods laid out in 2656f664616d.
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
On Thu, Nov 12, 2026 at 04:07:54PM -0500, Corey Huinker wrote: > 0001 - > Decoupling range_bounds_histogram. No changes since last patchset where it > was number 0002. Ah, I see what you are getting at here. Without this part a portion of 0003 is actually moot, and the reason where there is no point to check for range_bounds_histogram in parallel of range_length_histogram and range_empty_frac is that they use a different stats slot. Tweaked a few things here, and applied down to v19. > 0002 - > > Fix to pg_dump to handle NULL values in range_length_histogram and > most_common_elems when their corresponding stanumbers field is not null. > > I was able to test this against 18, however I [1] was not able to > synthesize data such that an extended statistics object on 18 that had > expression stats with a NULL most_common_elems and NOT NULL > most_common_elem_freqs. I asked Claude to try to do it as well, and it too > was not able to. I have not been able to process this one today, unfortunately. I had to choose what to do, and picked up the most important matters based on their priority. > 0003 - > This has the same goals as the original 0003, but using the methods laid > out in 2656f664616d. While the code changes were fine, to allow for empty arrays like in 2656f664616d, the tests were bundled at the bottom of stats_import.sql. We have already sections of the tests that check similar patterns, so I have just moved the whole in sections that are more adapted for this goal. Applied also this one, after some edits. Should we also include a test where empty arrays are allowed but we have a multi-dimension array in input? I have refrained from doing so to not complicate more the patch and there were enough tests to check for the basics in terms of v19. Your patch did not do that, either. 0001 and 0003 were by far the most urgent items to handle for v19 (and actually the less risky ones). Without 0002 for the moment, it still means that we could miss some stats at dump/restore due to the NULL/empty change introduced in v19 when upgrading from v17/18, but at least we are clear for any future moves, or when doing a dump from v19. -- Michael
Re: BUG #19842: pg_restore_extended_stats() rejects a faithful pg_dump of ANALYZE-produced range expression statisti
От:
Corey Huinker <corey.huinker@gmail.com>
Дата:
0001 and 0003 were by far the most urgent items to handle for v19 (and
actually the less risky ones). Without 0002 for the moment, it still
means that we could miss some stats at dump/restore due to the
NULL/empty change introduced in v19 when upgrading from v17/18, but at
least we are clear for any future moves, or when doing a dump from
v19.
I agree with your prioritization - 0002 can wait a bit.