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

От:
Atisss hh <125atishprak@gmail.com>
Дата:
Вложения:


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.


FAQ