[PG19] pg_dump --statistics restores extended statistics by attribute number
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254172psql -h localhost -U postgresBuilt from patchset v3 (message #3), October 14, 2026 at 05:10 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254172_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254172_3 && git checkout t254172_3Patchset v3 (message #3) is on t254172_3
Hi,
This is Medium 5 from my list in [1]/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com.
Since c32fb29e979, pg_dump --statistics dumps extended statistics.
n_distinct and dependencies are written with the attribute numbers of
the source table. The restored table can number its columns in another
way.
create table d (junk int, a int, b int, c int);
alter table d drop column junk;
insert into d select g % 10, g % 10, g % 100
from generate_series(1, 2000) g;
create statistics d_s (ndistinct) on a, b, c from d;
analyze d;
explain select a, b from d group by a, b;
This estimates 10 rows. Restoring the dump into an empty database
prints
WARNING: could not validate "pg_ndistinct" object:
invalid attribute number 4 found
and the same explain estimates 100 rows there.
It is silent when the numbers are still valid but mean other columns.
That happens for an inheritance child whose columns are in another
order than the parent, because pg_dump recreates it with the order of
the parent. The statistics then land on other columns.
0001 adds two optional arguments to pg_restore_extended_stats, the
source column numbers and the column names of the statistics object,
and maps the numbers by name. 0002 makes pg_dump pass them. 0003 adds
tests. The MCV list is stored by column position, so 0001 skips it
with a warning when the order differs.
The function takes name and value pairs, so this needs no catalog
change, and dumps without the new arguments still load. I put the
mapping in the restore function because pg_dump would have to guess
the column numbers of the restored table. The patches apply to master
and REL_19_STABLE.
[1]: /messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
Thanks,
Shihao
Attachments:
t254172_1v1-0003-Test-pg_restore_extended_stats-with-shifted-attri.patchapplication/octet-stream; name=v1-0003-Test-pg_restore_extended_stats-with-shifted-attri.patchDownload+111-2
v1-0002-pg_dump-pass-source-key-numbers-with-extended-sta.patchapplication/octet-stream; name=v1-0002-pg_dump-pass-source-key-numbers-with-extended-sta.patchDownload+40-1
v1-0001-Let-pg_restore_extended_stats-translate-attribute.patchapplication/octet-stream; name=v1-0001-Let-pg_restore_extended_stats-translate-attribute.patchDownload+167-3
On Tue, Oct 13, 2026 at 12:06:06AM -0400, shihao zhong wrote:
It is silent when the numbers are still valid but mean other columns.
That happens for an inheritance child whose columns are in another
order than the parent, because pg_dump recreates it with the order of
the parent. The statistics then land on other columns.
Yeah, logical vs physical column ordering has been an old problem in
Postgres, because the way attnums are logically ordered may not
reflect with the way pg_dump tries to restore these relations. See
for example this thread, explaining partially why it is a complicated
problem:
/messages/by-id/20141209174146.GP1768@alvh.no-ip.org
The point here is that extended statistics have been always relying on
these numbers internally, strictly positive for attributes, and
strictly negative for expressions.
0001 adds two optional arguments to pg_restore_extended_stats, the
source column numbers and the column names of the statistics object,
and maps the numbers by name. 0002 makes pg_dump pass them. 0003 adds
tests. The MCV list is stored by column position, so 0001 skips it
with a warning when the order differs.
Note that what you are introducing here has also its own set of
problems. Renaming of a column to have the same name as a previous
column would be one. A second potential problem is I suspect that
pg_dump may always restore the tables based on the attribute order you
are trying to force based on the inputs of your new stxkeys and
stxkeynames arguments given to pg_restore_extended_stats(). Rejection
is probably the best thing we can do here, because the extended stats
we are trying to insert do not match with what pg_dump has been
restoring. In short, while I kind of agree that it is a problem based
on the attnum pattern, there is more to it than what you are assuming,
and it's probably not worth spending dozens of hours on at the end.
--
Michael
Hi Michael,
Thanks for the quick reply and the pointer.
I agree 19 is not the place for a big change, so rejection works for me.
The dropped column case is rejected today, that is the warning in my
first mail. The child with another column order is not. Its numbers are
valid for the restored table, so the data lands on other columns.
v2 drops the mapping and only rejects that case. pg_dump passes the
column names of the statistics object, and the restore function skips
the data when they are not the names of its own columns in the same
order. The child then gets
WARNING: could not restore extended statistics object
"public.ch_s": its columns do not match "attnames"
Thanks,
Shihao
Attachments:
t254172_3v2-0003-Test-attnames-in-pg_restore_extended_stats.patchapplication/octet-stream; name=v2-0003-Test-attnames-in-pg_restore_extended_stats.patchDownload+101-2
v2-0002-pg_dump-pass-column-names-with-extended-statistic.patchapplication/octet-stream; name=v2-0002-pg_dump-pass-column-names-with-extended-statistic.patchDownload+25-1
v2-0001-Let-pg_restore_extended_stats-check-the-column-na.patchapplication/octet-stream; name=v2-0001-Let-pg_restore_extended_stats-check-the-column-na.patchDownload+54-1
On Tue, Oct 13, 2026 at 12:56 AM shihao zhong <zhong950419@gmail.com> wrote:
Hi Michael,
Thanks for the quick reply and the pointer.
I agree 19 is not the place for a big change, so rejection works for me.
The dropped column case is rejected today, that is the warning in my
first mail. The child with another column order is not. Its numbers are
valid for the restored table, so the data lands on other columns.v2 drops the mapping and only rejects that case. pg_dump passes the
column names of the statistics object, and the restore function skips
the data when they are not the names of its own columns in the same
order. The child then getsWARNING: could not restore extended statistics object
"public.ch_s": its columns do not match "attnames"Thanks,
Shihao
I agree with Michael that this seems like a lot of work to restore some
statistics that will be overwritten on the next autovacuum, the table
itself would be caught by vacuumdb --missing-stats-only if the stats were
rejected, and in the case where the stats applied then we can generate a
list of suspect tables from the old database based on which tables had a
dropped column in them.
While it would be theoretically possible to teach pg_dump to remap
attribute numbers to account for dropped columns, thus mapping the attnums
in pg_ndistinct and pg_dependencies to their destination numbers, but that
would complicate pg_dump and require the user to specify whether they
wanted these stats to apply to the existing table structure or the rebuilt
one. In that case, the better thing would be to capture the JSON for the
suspect extended stats objects, programmatically replace the affected
attnums, and then re-apply those stats manually, but even that seems like a
lot of work.
Additionally, the ongoing work in join statistics is also considering a
refactor of how statistic columns and expressions are stored [1]/messages/by-id/CAK98qZ3H9HBJaUHD-Y8Y==3bzoP4kP+p5oqySWqHwqAipqUTBg@mail.gmail.com, which may
or may not make this problem more tractable. Either way it will be
different and we will have to account for that.
[1]: /messages/by-id/CAK98qZ3H9HBJaUHD-Y8Y==3bzoP4kP+p5oqySWqHwqAipqUTBg@mail.gmail.com
/messages/by-id/CAK98qZ3H9HBJaUHD-Y8Y==3bzoP4kP+p5oqySWqHwqAipqUTBg@mail.gmail.com