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]/messages/by-id/CAExHW5vVFtCejh+UYzNxMGSXOfJ_1xwi5aQHQfemqJgFmkyK5Q@mail.gmail.com. However, it is showing a failure on windows only [2]https://cirrus-ci.com/task/5164175841820672.
regress_log has the following difference.
@@ -444546,7 +444546,7 @@
'relname', 'pagg_tab_para_p1',
'relpages', '54'::integer,
'reltuples', '12000'::real,
- 'relallvisible', '0'::integer,
+ 'relallvisible', '54'::integer,
'relallfrozen', '0'::integer
);
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
@@ -444584,7 +444584,7 @@
'relname', 'pagg_tab_para_p2',
'relpages', '45'::integer,
'reltuples', '10000'::real,
- 'relallvisible', '0'::integer,
+ 'relallvisible', '45'::integer,
'relallfrozen', '0'::integer
);
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
@@ -444622,7 +444622,7 @@
'relname', 'pagg_tab_para_p3',
'relpages', '36'::integer,
'reltuples', '8000'::real,
- 'relallvisible', '0'::integer,
+ 'relallvisible', '36'::integer,
'relallfrozen', '0'::integer
);
SELECT * FROM pg_catalog.pg_restore_attribute_stats(
@@ -450930,11 +450930,23 @@
'version', '180000'::integer,
'schemaname', 'public',
'relname', 'test_io_bulkwrite_strategy',
- 'relpages', '0'::integer,
- 'reltuples', '-1'::real,
+ 'relpages', '1'::integer,
+ 'reltuples', '100'::real,
'relallvisible', '0'::integer,
'relallfrozen', '0'::integer
);
+SELECT * FROM pg_catalog.pg_restore_attribute_stats(
+ 'version', '180000'::integer,
+ 'schemaname', 'public',
+ 'relname', 'test_io_bulkwrite_strategy',
+ 'attname', 'i',
+ 'inherited', 'f'::boolean,
+ 'null_frac', '0'::real,
+ 'avg_width', '4'::integer,
+ 'n_distinct', '-1'::real,
+ 'histogram_bounds',
'{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}'::text,
+ 'correlation', '1'::real
+);First 3 and 5th differences are new differences but we have seen
something similar to the 4th one previously.
I am not able to reproduce the failure even after running it 30 times
on my linux laptop.
[1]: /messages/by-id/CAExHW5vVFtCejh+UYzNxMGSXOfJ_1xwi5aQHQfemqJgFmkyK5Q@mail.gmail.com
[2]: https://cirrus-ci.com/task/5164175841820672
--
Best Wishes,
Ashutosh Bapat
Import Notes
Reply to msg id not found: CAExHW5tGWMxdf3XfHb+4H+Ow95qfuGVc0Hkm74ok7QGhvj6Agw@mail.gmail.comReference msg id not found: CAExHW5tGWMxdf3XfHb+4H+Ow95qfuGVc0Hkm74ok7QGhvj6Agw@mail.gmail.com
Hi,
On Tue, Apr 1, 2025 at 12:54 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
[1] /messages/by-id/CAExHW5vVFtCejh+UYzNxMGSXOfJ_1xwi5aQHQfemqJgFmkyK5Q@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 an open item but 1. We have already started tracking open items
2. There's follow on work.
Once we fix this issue, we need to enable statistics dump and
comparison in pg_upgrade/002_pg_upgrade using the attached patch.
--
Best Wishes,
Ashutosh Bapat
Attachments:
0001-Enable-statistics-comparison-in-dump-restor-20250402.patchtext/x-patch; charset=US-ASCII; name=0001-Enable-statistics-comparison-in-dump-restor-20250402.patchDownload
From d144f733a30979040d1b0d18fe02340e17a4c5c6 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Wed, 2 Apr 2025 15:04:16 +0530
Subject: [PATCH] Enable statistics comparison in dump/restore test
Test verifying roundtrip dump/restore of regression database in
pg_upgrade/002_pg_upgrade.pl uses plain dumps from original and restored
database for comparison. The dumps do not contain statistics since at the time
of commit 172259afb563d35001410dc6daad78b250924038, the statistics dump wasn't
stable. So we miss statistics coverage. Enable it once the statistics dump is
stable.
Author: Ashutosh Bapat
---
src/bin/pg_upgrade/t/002_pg_upgrade.pl | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 7494614ee64..be3d6df8b45 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -86,7 +86,7 @@ sub get_dump_for_comparison
# Don't dump statistics, because there are still some bugs.
$node->run_log(
[
- 'pg_dump', '--no-sync', '--no-statistics',
+ 'pg_dump', '--no-sync',
'-d' => $node->connstr($db),
'-f' => $dumpfile
]);
base-commit: b53b88109f94bd81ed0ac580035a936000bc2865
--
2.34.1
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 $oldnode: $src_dump is taken
on line 321, and then $dump_file is taken on line 339. $dump_file is
used for the restore, but then $src_dump is used for the comparison.
If autovacuum were off, then $src_dump and $dump_file should be the
same, but autovacuum is not disabled until line 415. Furthermore,
there's an initdb between lines 321 and 339, allowing autovacuum enough
time to activate. Otherwise we probably wouldn't have noticed.
You could disable autovacuum earlier; though not too early, because
allowing it time to run improves test coverage for stats. Or, you could
use the same $src_dump for both restoration and comparison, but it
looks like you wanted coverage of the --create option. (Aside: why
parallel restore there? Is that just for test coverage or was there a
performance reason?)
Regards,
Jeff Davis
On Wed, Apr 2, 2025 at 10:55 PM Jeff Davis <pgsql@j-davis.com> 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_upgrade.pl introduced in
172259afb5. There are two dumps taken from $oldnode: $src_dump is taken
on line 321, and then $dump_file is taken on line 339. $dump_file is
used for the restore, but then $src_dump is used for the comparison.If autovacuum were off, then $src_dump and $dump_file should be the
same, but autovacuum is not disabled until line 415. Furthermore,
there's an initdb between lines 321 and 339, allowing autovacuum enough
time to activate. Otherwise we probably wouldn't have noticed.
Thanks for the analysis. This is useful. I will post a fix on the
original thread. I have assigned myself and fixed the open item entry.
You could disable autovacuum earlier; though not too early, because
allowing it time to run improves test coverage for stats. Or, you could
use the same $src_dump for both restoration and comparison, but it
looks like you wanted coverage of the --create option. (Aside: why
parallel restore there? Is that just for test coverage or was there a
performance reason?)
For performance.
--
Best Wishes,
Ashutosh Bapat