pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19845: pg_dump extended stats break when column numbers change on restore
3+ messages / 3 participants
[nested] [flat]

* BUG #19845: pg_dump extended stats break when column numbers change on restore
@ 2026-11-07 14:19  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 3+ messages in thread

From: PG Bug reporting form @ 2026-11-07 14:19 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 125atishprak@gmail.com

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.







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19845: pg_dump extended stats break when column numbers change on restore
@ 2026-11-07 14:20  Atisss hh <125atishprak@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Atisss hh @ 2026-11-07 14:20 UTC (permalink / raw)
  To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org

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
> <https://www.google.com/maps/search/2,+i+%25+10?entry=gmail&source=g;
> 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
> <https://www.google.com/maps/search/i+%25+10,+i+%25+10?entry=gmail&source=g;
> 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.
>
>
>

Attachments:

  pg-dump-extstats-repro.sql (application/sql, 464B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19845: pg_dump extended stats break when column numbers change on restore
@ 2026-11-08 05:30  Michael Paquier <michael@paquier.xyz>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Michael Paquier @ 2026-11-08 05:30 UTC (permalink / raw)
  To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org

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

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-11-08 05:30 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-07 14:19 BUG #19845: pg_dump extended stats break when column numbers change on restore PG Bug reporting form <noreply@postgresql.org>
2026-11-07 14:20 ` Atisss hh <125atishprak@gmail.com>
2026-11-08 05:30 ` Michael Paquier <michael@paquier.xyz>

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