Re: Statistics Import and Export

On Thu, 2025-05-22 at 15:41 -0400, Tom Lane wrote: > There is no additional filter in text mode, so I think pg_restore's > default behavior should also be "no additional filter". Attached. Only the defaults for pg_dump and pg_dumpall are changed, and pg_upgrade explicitly specifies --with-statisti

Re: Statistics Import and Export

Greg Sabino Mullane writes: > On Thu, May 22, 2025 at 2:52 PM Jeff Davis wrote: >> * The default for pg_restore is --no-statistics. That could cause a minor >> surprise if the user specifies --with-statistics for pg_dump and >> not for pg_restore. > Hm...somewhat to my own surprise, I don't like

Re: Statistics Import and Export

On Thu, May 22, 2025 at 3:36 PM Nathan Bossart wrote: > +1, I think defaulting to restoring everything in the dump file is much > less surprising than the alternative. +1. -- Robert Haas EDB: http://www.enterprisedb.com

Re: Statistics Import and Export

On Thu, May 22, 2025 at 03:29:38PM -0400, Greg Sabino Mullane wrote: > On Thu, May 22, 2025 at 2:52 PM Jeff Davis wrote: >> * The default for pg_restore is --no-statistics. That could cause a minor >> surprise if the user specifies --with-statistics for pg_dump and >> not for pg_restore. An argum

Re: Statistics Import and Export

On Thu, May 22, 2025 at 2:52 PM Jeff Davis wrote: > * The default for pg_restore is --no-statistics. That could cause a minor > surprise if the user specifies --with-statistics for pg_dump and > not for pg_restore. An argument could be made that "if the stats are > there, restore them", and I do

Re: Statistics Import and Export

Thanks for the help. This has unblocked us! On Thu, May 22, 2025 at 8:25 AM Nathan Bossart wrote: > On Wed, May 21, 2025 at 04:53:17PM -0700, Jeff Davis wrote: > > On Wed, 2025-05-21 at 16:29 -0500, Nathan Bossart wrote: > >> I don't know precisely where that line might be, but in this case, > >

Re: Statistics Import and Export

On Thu, 2025-05-22 at 10:20 -0400, Robert Haas wrote: > Yeah. This could use comments from a few more people, but I really > hope we don't ship the final release this way. We do have a "Enable > statistics in pg_dump by default" item in the open items list under > "Decisions to Recheck Mid-Beta", b

Re: Statistics Import and Export

On Wed, May 21, 2025 at 04:53:17PM -0700, Jeff Davis wrote: > On Wed, 2025-05-21 at 16:29 -0500, Nathan Bossart wrote: >> I don't know precisely where that line might be, but in this case, >> the >> dumped stats have no hope of restoring into anything older than >> v18... But I see no particular be

Re: Statistics Import and Export

Nathan Bossart writes: > On Thu, May 22, 2025 at 10:20:16AM -0400, Robert Haas wrote: >> It also sort >> of looks like we might have a consensus anyway: Jeff said "I lean >> towards making it opt-in for pg_dump and opt-out for pg_upgrade" and I >> agree with that and it seems you do, too. So perha

Re: Statistics Import and Export

On Thu, May 22, 2025 at 10:20:16AM -0400, Robert Haas wrote: > It also sort > of looks like we might have a consensus anyway: Jeff said "I lean > towards making it opt-in for pg_dump and opt-out for pg_upgrade" and I > agree with that and it seems you do, too. So perhaps Jeff should make > it so?

Re: Statistics Import and Export

On Sat, May 10, 2025 at 3:51 PM Greg Sabino Mullane wrote: > I may have missed something (we seem to have a lot of threads for this > subject), but we are in beta and both pg_dump and pg_upgrade seem to be > opt-out? I still object strongly to this; pg_dump is meant to be a canonical > represe

Re: Statistics Import and Export

On Wed, 2025-05-21 at 16:29 -0500, Nathan Bossart wrote: > I don't know precisely where that line might be, but in this case, > the > dumped stats have no hope of restoring into anything older than > v18... But I see no particular benefit from moving the complexity > to the > import side here. Tha

Re: Statistics Import and Export

> > I don't know precisely where that line might be, but in this case, the > dumped stats have no hope of restoring into anything older than v18 (since > the stats import functions won't exist), which is well past the point where > we started using -1 for reltuples. If we could dump the stats from

Re: Statistics Import and Export

On Wed, May 21, 2025 at 02:14:55PM -0700, Jeff Davis wrote: > Originally, one of the reasons we added a version field during dump is > so that some future version could reinterpret stats in older dump files > during import. > > This patch is using a newer version of pg_dump to interpret stats from

Re: Statistics Import and Export

On Wed, 2025-05-21 at 11:08 -0500, Nathan Bossart wrote: > On Tue, May 20, 2025 at 10:32:39AM -0700, Hari Krishna Sunder wrote: > > Ah ya, forgot that reltuples are not always accurate. This sounds > > reasonable to me. > > Cool.  Here is what I have staged for commit, which I am planning to > do

Re: Statistics Import and Export

Looks good to me. On Wed, May 21, 2025 at 9:08 AM Nathan Bossart wrote: > On Tue, May 20, 2025 at 10:32:39AM -0700, Hari Krishna Sunder wrote: > > Ah ya, forgot that reltuples are not always accurate. This sounds > > reasonable to me. > > Cool. Here is what I have staged for commit, which I am

Re: Statistics Import and Export

On Tue, May 20, 2025 at 10:32:39AM -0700, Hari Krishna Sunder wrote: > Ah ya, forgot that reltuples are not always accurate. This sounds > reasonable to me. Cool. Here is what I have staged for commit, which I am planning to do shortly. -- nathan >From e68770a6089500e6b4d02bcb3009ec12da392d5f M

Re: Statistics Import and Export

Ah ya, forgot that reltuples are not always accurate. This sounds reasonable to me. On Mon, May 19, 2025 at 2:32 PM Nathan Bossart wrote: > On Mon, May 19, 2025 at 02:13:45PM -0700, Hari Krishna Sunder wrote: > > I think it would be better to revert 9879105 since there can be a > > considerable

Re: Statistics Import and Export

On Mon, May 19, 2025 at 02:13:45PM -0700, Hari Krishna Sunder wrote: > I think it would be better to revert 9879105 since there can be a > considerable number of true empty tables that we don´t need to process. I'm not sure that's a use-case we really need to optimize. Even with 100,000 empty tab

Re: Statistics Import and Export

Sorry didn't know about the conference. I think it would be better to revert 9879105 since there can be a considerable number of true empty tables that we don’t need to process. --- Hari Krishna Sunder On Mon, May 19, 2025 at 9:51 AM Nathan Bossart wrote: > On Wed, May 14, 2025 at 01:30:48PM

Re: Statistics Import and Export

On Wed, May 14, 2025 at 01:30:48PM -0700, Hari Krishna Sunder wrote: > Here is the patch with a comment. Thanks. > On Wed, May 14, 2025 at 8:53 AM Nathan Bossart > wrote: >> There was a similar report for vacuumdb's new --missing-stats-only option. >> We fixed that in commit 9879105 by removing

Re: Statistics Import and Export

On Fri, May 16, 2025 at 11:47:12AM -0700, Hari Krishna Sunder wrote: > Gentle ping on this. Most of the major PostgreSQL developers were at pgconf.dev held in Montreal last week, explaining a reduction in the activity of the mailing lists. Your initial report was on Monday the 14th, with this pin

Re: Statistics Import and Export

Gentle ping on this. --- Hari Krishna Sunder On Wed, May 14, 2025 at 1:30 PM Hari Krishna Sunder wrote: > Thanks Nathan. > Here is the patch with a comment. > > On Wed, May 14, 2025 at 8:53 AM Nathan Bossart > wrote: > >> On Tue, May 13, 2025 at 05:01:02PM -0700, Hari Krishna Sunder wrote: >>

Re: Statistics Import and Export

Thanks Nathan. Here is the patch with a comment. On Wed, May 14, 2025 at 8:53 AM Nathan Bossart wrote: > On Tue, May 13, 2025 at 05:01:02PM -0700, Hari Krishna Sunder wrote: > > We found a minor issue when testing statistics import with upgrading from > > versions older than v14. (We have VACUUM

Re: Statistics Import and Export

On Tue, May 13, 2025 at 05:01:02PM -0700, Hari Krishna Sunder wrote: > We found a minor issue when testing statistics import with upgrading from > versions older than v14. (We have VACUUM and ANALYZE disabled) > 3d351d916b20534f973eda760cde17d96545d4c4 >

Re: Statistics Import and Export

We found a minor issue when testing statistics import with upgrading from versions older than v14. (We have VACUUM and ANALYZE disabled) 3d351d916b20534f973eda760cde17d96545d4c4 changed the def

Re: Statistics Import and Export

On Tue, Apr 1, 2025 at 10:24 PM Robert Haas wrote: > On Tue, Apr 1, 2025 at 4:24 PM Jeff Davis wrote: > > On Tue, 2025-04-01 at 09:37 -0400, Robert Haas wrote: > > > I don't think I was aware of the open item; I was just catching up on > > > email. > > > > I lean towards making it opt-in for pg_

Re: Can we use Statistics Import and Export feature to perforamance testing?

> > You might be getting confused because the code does look at the > pg_class fields, but that's only to estimate the tuple density. When > pg_class has those estimates, they're used to calculate the estimated > density by doing reltuples / relpages, but that average rows per page > > Thanks for t

Re: Can we use Statistics Import and Export feature to perforamance testing?

On Sat, 12 Apr 2025 at 20:29, Corey Huinker wrote: >> >> at the *actual size* of the relation and takes that into account when >> scaling the statistics (see table_block_relation_estimate_size() in >> tableam.c). If the table sizes don't match between the two servers >> then there's no guarantees

Re: Can we use Statistics Import and Export feature to perforamance testing?

> > * Question > > By using Statistics Import and Export feature, is it possible to achieve > the above request by following procedure? > > > > (1) Export the statistics from production environment by using pg_dump > --statistics-only. > > (2) On the staging env

Re: Can we use Statistics Import and Export feature to perforamance testing?

> > at the *actual size* of the relation and takes that into account when > scaling the statistics (see table_block_relation_estimate_size() in > tableam.c). If the table sizes don't match between the two servers > then there's no guarantees the planner will produce the same plan. > Sorry that I d

RE: Can we use Statistics Import and Export feature to perforamance testing?

Hi, Thank you for your reply. I understand that the access plans are not guaranteed to be the same. Can we add these notes to the pg_dump page in the PostgreSQL Documentation in order to prevent users from asking the same question? Regards, Ryohei Takahashi

Re: Can we use Statistics Import and Export feature to perforamance testing?

On Tue, 8 Apr 2025 at 12:21, Ryohei Takahashi (Fujitsu) wrote: > By using Statistics Import and Export feature, is it possible to achieve the > above request by following procedure? > (1) Export the statistics from production environment by using pg_dump > --statistics-only.

Can we use Statistics Import and Export feature to perforamance testing?

Hi, I have a question about Statistics Import and Export. * Background I'm working for PGEcons[1], which is the PostgreSQL consortium in Japan. Several companies participating in PGEcons have the following request for PostgreSQL. They have two environments, production environment and st

Re: Statistics Import and Export

> > * Changed to use LookupExplicitNamespace() > Seems good. > * Added test for temp tables > +1 > * Doc fixes So this patch swings the pendulum a bit back towards accepting some things as errors. That's understandable, as we're never going to have a situation where we can guarantee that th

Re: Statistics Import and Export

> > > Also, why do we need the clause "WHERE s.tablename = ANY($2)"? Isn't > > that already implied by "JOIN unnest($1, $2) ... s.tablename = > > u.tablename"? > > Good question. Corey, do you recall why this was needed? > In my patch, that SQL statement came with the comment: + /* + * The resul

Re: Statistics Import and Export

> > The first is that i_relallfrozen is undefined in versions earlier than > 18. That's trivial to fix, we just add "0 AS relallfrozen," in the > earlier versions, but still refrain from outputting it. > Ok, so long as we refrain from outputting it, I'm cool with whatever we store internally. >

Re: Statistics Import and Export

On Wed, 2025-03-19 at 15:17 -0700, Jeff Davis wrote: > On Sat, 2025-03-15 at 21:37 -0400, Corey Huinker wrote: > > > 0001 - no changes, but the longer I go the more I'm certain this > > > is > > > something we want to do. > > This replaces regclassin with custom lookups of the namespace and > reln

statistics import and export: another difference in dump/restore

Hi Jeff and Corey, Thanks for fixing the bug related to materialized view statistics. I have now submitted patches so that the test compares statistics as well. [1]. However, it is showing a failure on windows only [2]. regress_log has the following difference. @@ -444546,7 +444546,7 @@ 'relname

Re: Statistics Import and Export

On Fri, Apr 04, 2025 at 07:32:48PM -0400, Corey Huinker wrote: > This patch shrinks the array size to 1 for versions < 9.4, which keeps the > modern code fairly elegant. Committed. -- nathan

Re: Statistics Import and Export

On Fri, Apr 4, 2025 at 6:25 PM Nathan Bossart wrote: > On Fri, Apr 04, 2025 at 03:58:53PM -0500, Nathan Bossart wrote: > > I pushed commit 8ec0aae to fix this. > > And now I'm seeing cross-version test failures due to our use of WITH > ORDINALITY, which wasn't added until v9.4. Looking into it..

Re: Statistics Import and Export

On Fri, Apr 04, 2025 at 03:58:53PM -0500, Nathan Bossart wrote: > I pushed commit 8ec0aae to fix this. And now I'm seeing cross-version test failures due to our use of WITH ORDINALITY, which wasn't added until v9.4. Looking into it... -- nathan

Re: Statistics Import and Export

On Fri, Apr 04, 2025 at 03:06:45PM -0500, Nathan Bossart wrote: > I see the buildfarm failure and am working on a fix. I pushed commit 8ec0aae to fix this. -- nathan

Re: Statistics Import and Export

On Fri, Apr 04, 2025 at 02:56:54PM -0500, Nathan Bossart wrote: > Committed. I see the buildfarm failure and am working on a fix. -- nathan

Re: Statistics Import and Export

On Thu, Apr 03, 2025 at 09:19:51PM -0500, Nathan Bossart wrote: > Great. I'm planning to commit the attached patch set tomorrow morning. Committed. -- nathan

Re: Statistics Import and Export

On Tue, Apr 01, 2025 at 10:44:19PM -0700, Jeff Davis wrote: > On Tue, 2025-04-01 at 22:21 -0500, Nathan Bossart wrote: >> We might be able to improve this by inventing a new callback that fails for >> all formats except for custom with feesko() available.  That would at least >> ensure hard failure

Re: Statistics Import and Export

Thanks for reviewing. On Thu, Apr 03, 2025 at 03:23:40PM -0700, Jeff Davis wrote: > This simplifies commit a0a4601765. I'd break out that simplification as > a separate commit to make it easier to understand what happened. Done. > In patch 0003, there are quite a few static function-scoped vari

Re: Statistics Import and Export

On Wed, 2025-04-02 at 21:26 -0500, Nathan Bossart wrote: > Okay, here is an updated patch set. > * Besides custom format calling WriteToc() twice to update the data >   offsets, tar format ... even if it did, the worst case is that > the >   content of restore.sql (which isn't used by pg_restore

Re: statistics import and export: another difference in dump/restore

On Wed, Apr 2, 2025 at 10:55 PM Jeff Davis wrote: > > On Wed, 2025-04-02 at 15:35 +0530, Ashutosh Bapat wrote: > > Once we fix this issue, we need to enable statistics dump and > > comparison in pg_upgrade/002_pg_upgrade using the attached patch. > > The diff appears to be an issue in 002_pg_upgra

Re: Statistics Import and Export

On Wed, Apr 02, 2025 at 10:34:58PM -0400, Corey Huinker wrote: >> >> > Also, why do we need the clause "WHERE s.tablename = ANY($2)"? Isn't >> > that already implied by "JOIN unnest($1, $2) ... s.tablename = >> > u.tablename"? >> >> Good question. Corey, do you recall why this was needed? >> > >

Re: Statistics Import and Export

On Tue, 2025-04-01 at 22:21 -0500, Nathan Bossart wrote: > It certainly feels risky.  I was able to avoid executing the queries > twice > in all cases by saving the definition length in the TOC entry and > skipping > that many bytes the second time round. Another idea that was under-discussed is w

Re: statistics import and export: another difference in dump/restore

On Wed, 2025-04-02 at 15:35 +0530, Ashutosh Bapat wrote: > Once we fix this issue, we need to enable statistics dump and > comparison in pg_upgrade/002_pg_upgrade using the attached patch. The diff appears to be an issue in 002_pg_upgrade.pl introduced in 172259afb5. There are two dumps taken from

Re: Statistics Import and Export

Hi, https://commitfest.postgresql.org/patch/4538/ is still in "needs review", even though the feature really has been committed. Is that intention, e.g. to track pending changes that we're planning to make? Greetings, Andres

Re: statistics import and export: another difference in dump/restore

Hi, On Tue, Apr 1, 2025 at 12:54 PM Ashutosh Bapat wrote: > [1] > https://www.postgresql.org/message-id/caexhw5vvftcejh+uyznxmgsxofj_1xwi5aqhqfemqjgfmky...@mail.gmail.com > [2] https://cirrus-ci.com/task/5164175841820672 I have added this to PG 18 open items. It might be too early to call this

Re: Statistics Import and Export

On Tue, 2025-04-01 at 22:21 -0500, Nathan Bossart wrote: > It certainly feels risky.  I was able to avoid executing the queries > twice > in all cases by saving the definition length in the TOC entry and > skipping > that many bytes the second time round. That feels like a better approach. >   Th

Re: Statistics Import and Export

On Tue, Apr 01, 2025 at 03:05:59PM -0700, Jeff Davis wrote: > To restate the problem: one of the problems being solved here is that > the existing code for custom-format dumps calls WriteToc twice. That > was not a big problem before this patch, when the contents of the > entries was easily accessi

Re: Statistics Import and Export

On Tue, Apr 1, 2025 at 4:24 PM Jeff Davis wrote: > On Tue, 2025-04-01 at 09:37 -0400, Robert Haas wrote: > > I don't think I was aware of the open item; I was just catching up on > > email. > > I lean towards making it opt-in for pg_dump and opt-out for pg_upgrade. Big +1. > But I think we shoul

Re: Statistics Import and Export

On Tue, 2025-04-01 at 13:44 -0500, Nathan Bossart wrote: > Apologies for the noise.  I noticed one more way to simplify 0002.  > As > before, there should be no functional differences. To restate the problem: one of the problems being solved here is that the existing code for custom-format dumps

Re: Statistics Import and Export

On Tue, 2025-04-01 at 09:37 -0400, Robert Haas wrote: > I don't think I was aware of the open item; I was just catching up on > email. I lean towards making it opt-in for pg_dump and opt-out for pg_upgrade. But I think we should leave open the possibility for changing the default to opt-out for pg

Re: Statistics Import and Export

On Mon, Mar 31, 2025 at 09:33:15PM -0500, Nathan Bossart wrote: > My goal is to commit the attached patches on Friday morning, but of course > that is subject to change based on any feedback or objections that emerge > in the meantime. I spent some more time polishing these patches this morning.

Re: Statistics Import and Export

On Tue, Apr 01, 2025 at 01:20:30PM -0500, Nathan Bossart wrote: > On Mon, Mar 31, 2025 at 09:33:15PM -0500, Nathan Bossart wrote: >> My goal is to commit the attached patches on Friday morning, but of course >> that is subject to change based on any feedback or objections that emerge >> in the mean

Re: Statistics Import and Export

On Mon, Mar 31, 2025 at 6:04 PM Jeff Davis wrote: > On Mon, 2025-03-31 at 13:39 -0400, Robert Haas wrote: > > +1. I think I said this before, but I don't think it's correct to > > regard the statistics as part of the database. It's great for > > pg_upgrade to preserve them, but I think doing so fo

Re: Statistics Import and Export

On Mon, 2025-03-31 at 13:39 -0400, Robert Haas wrote: > +1. I think I said this before, but I don't think it's correct to > regard the statistics as part of the database. It's great for > pg_upgrade to preserve them, but I think doing so for a regular dump > should be opt-in. I'm confused about th

Re: Statistics Import and Export

On Mon, Mar 31, 2025 at 10:33 PM Nathan Bossart wrote: > On Mon, Mar 31, 2025 at 11:11:47AM -0400, Corey Huinker wrote: > Regarding whether pg_dump should dump statistics by default, my current > thinking is that it shouldn't, but I think we _should_ have pg_upgrade > dump/restore statistics by de

Re: Statistics Import and Export

On Mon, Mar 31, 2025 at 11:11:47AM -0400, Corey Huinker wrote: > In light of v11-0001 being committed as 4694aedf63bf, I've rebased the > remaining patches. I spent the day preparing these for commit. A few notes: * I've added a new prerequisite patch that skips the second WriteToc() call for

Re: Statistics Import and Export

On Thu, Feb 27, 2025 at 10:43 PM Greg Sabino Mullane wrote: > I know I'm coming late to this, but I would like us to rethink having > statistics dumped by default. +1. I think I said this before, but I don't think it's correct to regard the statistics as part of the database. It's great for pg_u

Re: Statistics Import and Export

> > The second is that the pg_upgrade test (when run with >> olddump/oldinstall) compares the before and after dumps, and if the >> "before" version is 17, then it will not have the relallfrozen argument >> to pg_restore_relation_stats. We might need a filtering step in >> adjust_new_dumpfile? >> >

Re: Statistics Import and Export

On Fri, 2025-03-28 at 21:11 -0400, Corey Huinker wrote: > A rebase and a reordering of the commits to put the really-really- > must-have relallfrozen ahead of the really-must-have stats batching > and both of them head of the error->warning step-downs. v11-0001 has a couple issues: The first is t

Re: Statistics Import and Export

A rebase and a reordering of the commits to put the really-really-must-have relallfrozen ahead of the really-must-have stats batching and both of them head of the error->warning step-downs. From 96b10b1eb955c5619d23cadf7de8b12d2db638a9 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Sat, 15 Mar

Re: Statistics import and export: difference in statistics of materialized view dumped

On Fri, 2025-03-28 at 14:53 +0530, Ashutosh Bapat wrote: > When I applied v1 it didn't pass. I applied v1 on top of master as of March 15 (771ba90298), and then took your two changes adding the tests, and it passed. Version v2j is just rebased forward, which involved a trivial merge conflict. >

Re: Statistics import and export: difference in statistics of materialized view dumped

On Fri, Mar 28, 2025 at 10:41 AM Jeff Davis wrote: > > On Thu, 2025-03-27 at 17:07 +0530, Ashutosh Bapat wrote: > > Pulled the latest sources but the test is still failing with the same > > differences. > > The attached set of patches (your 0001 and 0002, combined with my patch > v2j-0003) applied

Re: Statistics import and export: difference in statistics of materialized view dumped

On Thu, 2025-03-27 at 17:07 +0530, Ashutosh Bapat wrote: > Pulled the latest sources but the test is still failing with the same > differences. The attached set of patches (your 0001 and 0002, combined with my patch v2j-0003) applied on master (058b5152f0) are passing the pg_upgrade test suite for

Re: Statistics import and export: difference in statistics of materialized view dumped

On Wed, Mar 12, 2025 at 4:29 PM Ashutosh Bapat wrote: > > I ran my test with this patch (we have to remove 0003 patch in my test > which uses --no-statistics option). It failed with following > differences > @@ -452068,8 +452068,8 @@ > SELECT * FROM pg_catalog.pg_restore_relation_stats( > 'version

Re: Statistics Import and Export

On Tue, Mar 25, 2025 at 1:32 AM Jeff Davis wrote: > On Sat, 2025-03-08 at 14:09 -0500, Corey Huinker wrote: > > > > > > except it is perfectly clear that you *asked for* data and > > > statistics, so you get what you asked for. however the user > > > conjures in their heads what they are looking

Re: Statistics Import and Export

> > At this point, I feel I've demonstrated the limit of what can be made into > WARNINGs, giving us a range of options for now and into the beta. I'll > rebase and move the 0002 patch to be in last position so as to tee up > 0003-0004 for consideration. > And here's the rebase (after bde2fb797aae

Re: Statistics Import and Export

> > The original reason we wanted to issue warnings was to allow ourselves > a chance to change the meaning of parameters, add new parameters, or > even remove parameters without causing restore failures. If there are > any ERRORs that might limit our flexibility I think we should downgrade > those

Re: Statistics Import and Export

On Tue, 2025-03-25 at 10:53 -0400, Corey Huinker wrote: > > So this patch swings the pendulum a bit back towards accepting some > things as errors. Not exactly. I see patch 0001 as a change to the function signatures from regclass to schemaname/relname, both for usability as well as control over

Re: Statistics Import and Export

On Sat, 2025-03-08 at 14:09 -0500, Corey Huinker wrote: > > > > except it is perfectly clear that you *asked for* data and > > statistics, so you get what you asked for. however the user > > conjures in their heads what they are looking for, the logic is > > simple, you get what you asked for.  >

Re: Statistics Import and Export

> > This replaces regclassin with custom lookups of the namespace and > relname, but misses some of the complexities that regclassin is > handling. For instance, it calls RangeVarGetRelid(), which calls > LookupExplicitNamespace(), which handles temp tables and > InvokeNamespaceSearchHook(). > > At

Re: Statistics Import and Export

On Sat, 2025-03-15 at 21:37 -0400, Corey Huinker wrote: > > 0001 - no changes, but the longer I go the more I'm certain this is > > something we want to do. This replaces regclassin with custom lookups of the namespace and relname, but misses some of the complexities that regclassin is handling. F

Re: Statistics Import and Export

On Mon, Mar 17, 2025 at 07:24:46PM -0400, Corey Huinker wrote: > On Mon, Mar 17, 2025 at 10:24 AM Nathan Bossart > wrote: >> I'm assuming that writing a completely different TOC on the second pass >> would corrupt the dump file. Perhaps we could teach it to skip stats >> entries on the second pas

Re: Statistics Import and Export

On Mon, Mar 17, 2025 at 10:24 AM Nathan Bossart wrote: > On Sun, Mar 16, 2025 at 05:32:15PM -0400, Corey Huinker wrote: > >> > >> * The custom format actually does two WriteToc() calls, and since these > >> patches move the queries to this part of pg_dump, it means we'll run > all > >> the qu

Re: Statistics Import and Export

On Sun, Mar 16, 2025 at 05:32:15PM -0400, Corey Huinker wrote: >> >> * The custom format actually does two WriteToc() calls, and since these >> patches move the queries to this part of pg_dump, it means we'll run all >> the queries twice. The comments around this code suggest that the second >

Re: Statistics Import and Export

> > * The custom format actually does two WriteToc() calls, and since these > patches move the queries to this part of pg_dump, it means we'll run all > the queries twice. The comments around this code suggest that the second > pass isn't strictly necessary and that it is really only useful

Re: Statistics Import and Export

Thanks for working on this, Corey. On Fri, Mar 14, 2025 at 04:03:16PM -0400, Corey Huinker wrote: > 0003 - > > Storing the restore function calls in the archive entry hogged a lot of > memory and made people nervous. This introduces a new function pointer that > generates those restore SQL calls

Re: Statistics Import and Export

On Thu, Mar 6, 2025 at 3:48 AM Jeff Davis wrote: > On Wed, 2025-03-05 at 23:04 -0500, Corey Huinker wrote: > > > > Anyway, here's a rebased set of the existing up-for-consideration > > patches, plus the optimization of avoiding querying on non-expression > > indexes. > > Comments on 0003: > > * A

Re: Statistics Import and Export

On 2025-03-06 13:47:51 -0500, Corey Huinker wrote: > I'm at the same conclusion. This would mean keeping the one > getAttributeStats query perrelation, Why does it have to mean that? It surely would be easier with separate queries, but I don't think there's anything inherently blocking us from doi

Re: Statistics Import and Export

> > > https://www.postgresql.org/docs/current/ddl-priv.html > > The above text indicates that we should do the check, but also that > it's not terribly important for actual security. > Ok, I'm convinced. > > > If we do, we'll want to change downgrade the following errors to > > warn+return fals

Re: Statistics Import and Export

Hi, On 2025-03-06 12:16:44 -0500, Corey Huinker wrote: > > > > To be honest, I am a bit surprised that we decided to enable this by > > default. It's not obvious to me that statistics should be regarded as > > part of the database in the same way that table definitions or table > > data are. That

Re: Statistics import and export: difference in statistics of materialized view dumped

On Wed, Mar 12, 2025 at 4:08 AM Jeff Davis wrote: > > On Tue, 2025-03-11 at 11:26 -0400, Tom Lane wrote: > > Right, that was what I was thinking, but hadn't had time to look in > > detail. The postDataBound dependency isn't real helpful here, we > > could lose that if we had the data dependency.

Statistics import and export: difference in statistics of materialized view dumped

Hi Jeff, Corey, After fixing the statistics difference in dumps of tables with indexes, I now see difference in statistics of materialized view dump in the test I am developing at [1] (see the latest patches there). I see following difference in the dump from the original regression database and t

Re: Statistics Import and Export

On Thu, Mar 06, 2025 at 01:47:34PM -0500, Tom Lane wrote: > 1. pg_upgrade has made a policy judgement to apply parallelism across > databases not within a database, ie it will launch concurrent dump/ > restore tasks in different DBs but not authorize any one of them to > eat multiple CPUs. That ne

Re: Statistics Import and Export

On Wed, 2025-03-05 at 23:04 -0500, Corey Huinker wrote: > > Anyway, here's a rebased set of the existing up-for-consideration > patches, plus the optimization of avoiding querying on non-expression > indexes. Comments on 0003: * All the argument names for pg_restore_attribute_stats match pg_stat

Re: Statistics import and export: difference in statistics of materialized view dumped

On Tue, 2025-03-11 at 11:26 -0400, Tom Lane wrote: > Right, that was what I was thinking, but hadn't had time to look in > detail.  The postDataBound dependency isn't real helpful here, we > could lose that if we had the data dependency. Attached a patch. It's a bit messier than I expected, so I'

Re: Statistics Import and Export

> > I don't follow. We already have the tablenames, schemanames and oids of the > to-be-dumped tables/indexes collected in pg_dump, all that's needed is to > send > a list of those to the server to filter there? > Do we have something that currently does that? All of the collect functions (collect

Re: Statistics import and export: difference in statistics of materialized view dumped

Jeff Davis writes: > On Tue, 2025-03-11 at 10:17 -0400, Tom Lane wrote: >> Are you doing the restore in parallel by any chance? I had a todo >> item to revisit the dependencies that pg_dump is creating for stats >> items, because they looked wrong to me, ie inadequate to guarantee >> correct rest

Re: Statistics import and export: difference in statistics of materialized view dumped

On Tue, 2025-03-11 at 10:17 -0400, Tom Lane wrote: > Ashutosh Bapat writes: > > After fixing the statistics difference in dumps of tables with > > indexes, I now see difference in statistics of materialized view > > dump > > in the test I am developing at [1] (see the latest patches there). > > A

Re: Statistics import and export: difference in statistics of materialized view dumped

Ashutosh Bapat writes: > After fixing the statistics difference in dumps of tables with > indexes, I now see difference in statistics of materialized view dump > in the test I am developing at [1] (see the latest patches there). Are you doing the restore in parallel by any chance? I had a todo i

Re: Statistics Import and Export: difference in statistics dumped

On Tue, Mar 11, 2025 at 5:23 AM Jeff Davis wrote: > > On Mon, 2025-03-10 at 17:53 -0400, Tom Lane wrote: > > I wrote: > > > I think what is happening is that the patch shut off CREATE > > > INDEX's update of not only the table's stats but also the > > > index's stats. This seems unhelpful: the in

Re: Statistics Import and Export: difference in statistics dumped

On Mon, 2025-03-10 at 17:53 -0400, Tom Lane wrote: > I wrote: > > I think what is happening is that the patch shut off CREATE > > INDEX's update of not only the table's stats but also the > > index's stats.  This seems unhelpful: the index's empty > > stats can never be what's wanted. > > I looked

  1   2   3   4   5   6   >