Re: Statistics Import and Export

From: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
To: Peter Smith <smithpb2250(at)gmail(dot)com>
Cc: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Statistics Import and Export
Date: 2024-02-02 08:33:08
Message-ID: CADkLM=dd6-SamSP6Nkb8SOzwzk0zBzUhK9px_4yWAgGV8oHJPw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Jan 22, 2024 at 1:09 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:

> 2024-01 Commitfest.
>
> Hi, This patch has a CF status of "Needs Review" [1], but it seems
> there were CFbot test failures last time it was run [2]. Please have a
> look and post an updated version if necessary.
>
> ======
> [1] https://commitfest.postgresql.org/46/4538/
> [2]
> https://cirrus-ci.com/github/postgresql-cfbot/postgresql/commitfest/46/4538
>
> Kind Regards,
> Peter Smith.
>

Attached is v4 of the statistics export/import patch.

This version has been refactored to match the design feedback received
previously.

The system views are gone. These were mostly there to serve as a baseline
for what an export query would look like. That role is temporarily
reassigned to pg_export_stats.c, but hopefully they will be integrated into
pg_dump in the next version. The regression test also contains the version
of each query suitable for the current server version.

The export format is far closer to the raw format of pg_statistic and
pg_statistic_ext_data, respectively. This format involves exporting oid
values for types, collations, operators, and attributes - values which are
specific to the server they were created on. To make sense of those values,
a subset of the columns of pg_type, pg_attribute, pg_collation, and
pg_operator are exported as well, which allows pg_import_rel_stats() and
pg_import_ext_stats() to reconstitute the data structure as it existed on
the old server, and adapt it to the modern structure and local schema
objects.

pg_import_rel_stats matches up local columns with the exported stats by
column name, not attnum. This allows for stats to be imported when columns
have been dropped, added, or reordered.

pg_import_ext_stats can also handle column reordering, though it currently
would get confused by changes in expressions that maintain the same result
data type. I'm not yet brave enough to handle importing nodetrees, nor do I
think it's wise to try. I think we'd be better off validating that the
destination extended stats object is identical in structure, and to fail
the import of that one object if it isn't perfect.

Export formats go back to v10.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Corey Huinker 2024-02-02 08:37:10 Re: Statistics Import and Export
Previous Message jian he 2024-02-02 08:25:28 Re: Emitting JSON to file using COPY TO