On Wed, 20 Aug 2025 12:49:14 +0900 Fujii Masao <[email protected]> wrote:
> On Wed, Aug 20, 2025 at 10:42 AM Yugo Nagata <[email protected]> wrote: > > > > Hi, > > > > I found that "vacuumdb --missing-stats-only" always performs ANALYZE > > on tables with a virtual generated column, since such columns currently > > never have statistics. This seems like an obvious waste, so I've attached > > a patch to fix it, ensuring that virtual generated columns are not > > regarded as missing statistics. > > Thanks for the report and patch! This seems to be an oversight from > the commit that added virtual generated columns. > > For the patch, shouldn't we also add a regression test for > --missing-stats-only > with generated columns, to prevent this issue from happening again? Thank you for reviewing the patch and your suggestion. I agree that we should add a test, since the behavior may change in the future when statistics begin to be collected for virtual generated columns, and the test will serve as a reminder when this behavior changes. I've attached a updated patch including the test. Regards, Yugo Nagata -- Yugo Nagata <[email protected]>
>From 8a4bc5cd58c16c6a8950d17ccd63b276cdd2c9b9 Mon Sep 17 00:00:00 2001 From: Yugo Nagata <[email protected]> Date: Wed, 20 Aug 2025 10:41:49 +0900 Subject: [PATCH v2] Don't treat virtual generated columns as missing statistics in vacuumdb --missing-stats-only --- src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++++++ src/bin/scripts/vacuumdb.c | 3 +++ 2 files changed, 15 insertions(+) diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl index ff56a13b46b..c81f9c6b490 100644 --- a/src/bin/scripts/t/100_vacuumdb.pl +++ b/src/bin/scripts/t/100_vacuumdb.pl @@ -340,4 +340,16 @@ $node->issues_sql_unlike( qr/statement:\ ANALYZE/sx, '--missing-stats-only with no missing partition stats'); +$node->safe_psql('postgres', + 'ALTER TABLE regression_vacuumdb_test ADD COLUMN c int GENERATED ALWAYS AS (a+b)' +); +$node->issues_sql_unlike( + [ + 'vacuumdb', '--analyze-only', + '--missing-stats-only', '-t', + 'regression_vacuumdb_parted', 'postgres' + ], + qr/statement:\ ANALYZE/sx, + '--missing-stats-only with virtual generated column'); + done_testing(); diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 79b1096eb08..71a58efff30 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -14,6 +14,7 @@ #include <limits.h> +#include "catalog/pg_attribute_d.h" #include "catalog/pg_class_d.h" #include "common.h" #include "common/connect.h" @@ -957,6 +958,8 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n" " AND NOT a.attisdropped\n" " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n" + " AND a.attgenerated OPERATOR(pg_catalog.<>) " + CppAsString2(ATTRIBUTE_GENERATED_VIRTUAL) "\n" " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n" " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n" " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n" -- 2.43.0
