BUG #19845: pg_dump extended stats break when column numbers change on restore
BUG #19845: pg_dump extended stats break when column numbers change on restore
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19845
Logged by: Atish Prakash
Email address: 125atishprak@gmail.com
PostgreSQL version: 19rc1
Operating system: Linux x86_64
Description:
pg_dump now emits pg_restore_extended_stats() calls, but pg_ndistinct and
pg_dependencies values, and the column order of MCV lists, are tied to
attribute
numbers. A plain dump does not preserve attribute numbers, so the restored
stats
can be lost or, worse, silently applied to the wrong columns.
Reproducer (see pg-dump-extstats-repro.sql for the same script):
-- Case 1: child created first, then attached to a parent.
-- The dump recreates it with the inherited column first.
CREATE TABLE par(x int);
CREATE TABLE chd(y int, x int);
ALTER TABLE chd INHERIT par;
INSERT INTO chd SELECT i % 2, i % 10 FROM generate_series(1, 10000) i;
CREATE STATISTICS st_chd ON x, y FROM chd;
-- Case 2: a dropped column before the statistics keys.
CREATE TABLE dr(gone int, a int, b int);
ALTER TABLE dr DROP COLUMN gone;
INSERT INTO dr SELECT i % 10, i % 10 FROM generate_series(1, 10000) i;
CREATE STATISTICS st_dr ON a, b FROM dr;
ANALYZE;
Then dump with "pg_dump --statistics", restore into an empty database, and
look
at the statistics before the next ANALYZE (autovacuum off makes this
reliable).
Case 1, no warning at all. Column numbers swap (y=1, x=2 becomes x=1, y=2)
and
the statistics data is restored unchanged, so it now describes the wrong
columns:
query actual est. before est.
after
SELECT * FROM chd WHERE x = 3 AND y = 1 1000 1000
1
Case 2, with a warning. ndistinct and dependencies are lost, and only MCV
survives:
WARNING: could not validate "pg_ndistinct" object: invalid attribute
number 3 found
WARNING: could not validate "pg_dependencies" object: invalid attribute
number 3 found
query actual est. before est.
after
SELECT a, b FROM dr GROUP BY a, b 10 10
100
If the swapped columns in case 1 have different types, the MCV list is
rejected
with a WARNING instead ("incorrect value").
Expected: restored statistics describe the same columns as the originals, or
pg_dump leaves out what it cannot restore correctly.
Also seen when dumping an 18 server with pg_dump 19. Custom format behaves
the
same way. pg_upgrade from 18 to 19 is not affected in my test, since it
keeps
attribute numbers.
Impact: with autovacuum running, its next ANALYZE replaces the bad
statistics. In
my test that happened within 100 seconds. The bad estimates last until then,
or
until a manual ANALYZE when autovacuum is off or does not analyze the table.
Possible direction: attribute statistics already restore by column name, so
extended statistics could translate attribute numbers to names on dump and
back
on restore. I have not written a patch.
Found with semantic differential tests. I reproduced and checked the result
myself.
Re: BUG #19845: pg_dump extended stats break when column numbers change on restore
On Sat, Nov 07, 2026 at 02:19:57PM +0000, PG Bug reporting form wrote: > pg_dump now emits pg_restore_extended_stats() calls, but pg_ndistinct and > pg_dependencies values, and the column order of MCV lists, are tied to > attribute > numbers. A plain dump does not preserve attribute numbers, so the restored > stats > can be lost or, worse, silently applied to the wrong columns. That's a known thing. Being able to preserve logical order of attributes across dump-restore is something that has been proposed in the past, and not something that should be fixed here. This has been mentioned previously, and this answer is the same one as I gave previously. -- Michael