From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 125atishprak@gmail.com
Subject: BUG #19845: pg_dump extended stats break when column numbers change on restore
Date: Sat, 07 Nov 2026 14:19:57 +0000
Message-ID: <19845-a2a2ac0ba28b3605@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 125atishprak@gmail.com
Subject: Re: BUG #19845: pg_dump extended stats break when column numbers change on restore
In-Reply-To: <19845-a2a2ac0ba28b3605@postgresql.org>
References: <19845-a2a2ac0ba28b3605@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox