Message-ID: <cc629559-d52a-621a-720e-d5b3ad2c1aae@enterprisedb.com>
Date: Wed, 1 Feb 2023 14:30:01 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.0
Subject: Re: Support for dumping extended statistics
Content-Language: en-US
To: Bruce Momjian <bruce@momjian.us>,
 Hari krishna Maddileti <hmaddileti@vmware.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <MN2PR05MB68795FCDB5B560D350084753B6FA9@MN2PR05MB6879.namprd05.prod.outlook.com>
 <Y7jbf43jpAC37mpC@momjian.us>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <Y7jbf43jpAC37mpC@momjian.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/cc629559-d52a-621a-720e-d5b3ad2c1aae%40enterprisedb.com>
Precedence: bulk


On 1/7/23 03:39, Bruce Momjian wrote:
> On Thu, Jan  5, 2023 at 06:29:03PM +0000, Hari krishna Maddileti wrote:
>> Hi Team,
>> In order to restore dumped extended statistics (stxdndistinct,
>> stxddependencies, stxdmcv) we need to provide input functions to parse
>> pg_distinct/pg_dependency/pg_mcv_list strings.
>>
>> Today we get the ERROR "cannot accept a value of type pg_ndistinct/
>> pg_dependencies/pg_mcv_list" when we try to do an insert of any type.
>>
>> Approch tried:
>>
>> - Using yacc grammar file (statistics_gram.y) to parse the input string to its
>> internal format for the types pg_distinct and pg_dependencies
>>
>> - We are just calling byteain() for serialized input text of type pg_mcv_list.
>>
>> Currently the changes are working locally,  I would like to push the commit
>> changes to upstream if there any usecase for postgres.   Would like to know if
>> there any interest from postgres side.
> 
> There is certainly interest in allowing the optimizer statistics to be
> dumped and reloaded.  This could be used by pg_restore and pg_upgrade.
> 

Indeed, although I think it'd be better to deal with regular statistics
(which is what 99% of systems use). Furthermore, we should probably
think about differences between major versions - until now we could
change on-disk format of the statistics, because we have reset them.
It'd be silly to do dump on version X, and then fail to restore it on
(X+1) just because the statistics changed a bit.

So we need to be able to determine is the statistics has the correct
format/version, or what. And we need to do that for pg_upgrade.

At the very least we need an option to skip restoring statistics, or
something like that.

regards
-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company