Re: [PROPOSAL] Backup and recovery of pg_statistic

On Thu, Dec 24, 2015 at 05:28:11PM +0300, Dmitry Ivanov wrote:
> Suppose you want to upgrade from 9.4 to 9.6. In that case you would use the
> pg_upgrade utility provided by the release 9.6, which means that it's the
> pg_dump who would have to connect to the older instance and to prepare tuples
> to be inserted to the pg_statistic of the newer instance. The pg_dump utility
> would have to convert statistical data to the new format (for example, add
> placeholders for new columns), so generated INSERT statements would be fine
> provided that the pg_dump would be up-to-date.
>
> The documentation states that we should always run the pg_upgrade binary of
> the new server, not the old one [http://www.postgresql.org/docs/9.5/static/
> pgupgrade.html, Usage, #9]. This means that the pg_upgrade will definitely use
> a fresh version of pg_dump utility that is aware of all possible pitfalls.
>
> Furthermore, each INSERT statement consists of textually-serialized columns of
> pg_statistic. Columns of 'anyarray' type are deserialized using the 'array_in'
> procedure which performs various sanity checks, including the element type
> check. Thus it is not possible to insert an anyarray object which will cause
> server death.

My idea was to do the insert via a function, with a version number at
the head:

SELECT pg_stats_insert('1.0', '{row value}');

When the pg_stats format is changed, the version number is bumped. The
backend would know the pg_stats version it supports and either remap or
ignore pg_stats_insert() calls for older versions.

To get more complicated, you could have a version number for data types
too and just invalidate inserts for data type format changes, rather
than requiring the entire pg_stats version to be bumped. I am not sure
how we would consistently record the data type name. pg_upgrade
preserves pg_type.oid, so that would work for it, but pg_type.oid is not
preserved for non-pg_upgrade usage of non-builtin data types. For those
cases, I guess the type name would be sufficient.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +