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@momjian.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                             +