Subject: Support for dumping extended statistics

From: Hari krishna Maddileti <[email-masked]>
Date: Thu, 5 Jan 2023 18:29:03 +0000

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.

Regards,
Hari Krishna

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 6 Jan 2023 21:39:59 -0500

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
> 
> 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.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

Embrace your flaws.  They make you human, rather than perfect,
which you will never be.

---

From: Tomas Vondra <[email-masked]>
Date: Wed, 1 Feb 2023 14:30:01 +0100

> 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

---

From: Tom Lane <[email-masked]>
Date: Wed, 01 Feb 2023 09:58:49 -0500

> change on-disk format of the statistics, because we have reset them.

Yeah, it's extremely odd to be proposing dump/reload for extended
stats when we don't yet have it for plain stats.  And yes, the main
stumbling block is that you need to have a plan for stats changing
across versions, or even just environmental issues.  For example,
what if the target DB doesn't use the same collation as the source?
That would affect string sorting and therefore at least partially
invalidate histograms for text columns.

I actually did some work on this, probably close to ten years ago
now, and came up with some hacks that didn't pass community review.
It'd be a good idea to dig up those old discussions if you want to
re-open the topic.

			regards, tom lane