Re: BUG #19845: pg_dump extended stats break when column numbers change on restore
Re: BUG #19845: pg_dump extended stats break when column numbers change on restore
On Sat, 7 Nov 2026 at 19:50, PG Bug reporting form <noreply@postgresql.org> wrote:
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.