public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL Hackers <[email protected]>
To: [email protected]
Subject: Infinite Autovacuum loop caused by failing virtual generated column expression
Date: Fri, 10 Apr 2026 13:18:36 -0700
Message-ID: <CAHg+QDcdkGQ4Q683Uq7ZJ0P6NcbB=F3Sh8thMSiFY9wwnSEoQQ@mail.gmail.com> (raw)
Hi Hackers,
PG19 added support for stats on virtual generated columns [1]. Creating
extended statistics on a virtual generated column whose expression can
raise an error leads to ANALYZE failing repeatedly, and autovacuum retrying
indefinitely. This floods the server logs and also wastes resources. Vacuum
analyze on that column (without extended stats) succeeds.
In order to avoid retry storms, I think we have two options. (1)
skipping the offending row from the sample, (2) skipping the extended stats
computation for that table with a warning message. At least this avoid
autovacuum infinite retry. Attached a draft patch for the option (2).
Thoughts?
Repro:
CREATE TABLE t (
id int PRIMARY KEY,
a int,
gen int GENERATED ALWAYS AS (100 / a) VIRTUAL
);
INSERT INTO t VALUES (1, 10), (2, 5), (3, 0);
-- This succeeds (per-column stats don't evaluate the expression for
every row)
ANALYZE t;
-- Add extended statistics referencing the virtual gen col
CREATE STATISTICS t_stat ON a, gen FROM t;
-- This fails
ANALYZE t;
-- ERROR: division by zero
-- this succeeds
ANALYZE t(gen)
[1]:
https://www.postgresql.org/message-id/flat/20250422181006.dd6f9d1d81299f5b2ad55e1a%40sraoss.co.jp
Thanks,
Satya
Attachments:
[application/octet-stream] v1-0001-fix-analyze-extended-stats-virtual-gen-col.patch (5.1K, 3-v1-0001-fix-analyze-extended-stats-virtual-gen-col.patch)
download | inline diff:
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index 2b83355d..ba25dd62 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -33,6 +33,7 @@
#include "pgstat.h"
#include "postmaster/autovacuum.h"
#include "rewrite/rewriteHandler.h"
+#include "utils/resowner.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
#include "utils/acl.h"
@@ -194,42 +195,112 @@ BuildRelationExtStatistics(Relation onerel, bool inh, double totalrows,
if (stattarget == 0)
continue;
- /* evaluate expressions (if the statistics object has any) */
- data = make_build_data(onerel, stat, numrows, rows, stats, stattarget);
-
- /* compute statistic of each requested type */
- foreach(lc2, stat->types)
+ /*
+ * Wrap expression evaluation and stats computation in PG_TRY so
+ * that errors from evaluating virtual generated column expressions
+ * (e.g. division by zero) don't cause ANALYZE to fail entirely.
+ * Skip the statistics object and issue a WARNING instead.
+ *
+ * Used child ResourceOwner so that any resources allocated
+ * during expression evaluation are properly released on
+ * error without leaking.
+ */
{
- char t = (char) lfirst_int(lc2);
-
- if (t == STATS_EXT_NDISTINCT)
- ndistinct = statext_ndistinct_build(totalrows, data);
- else if (t == STATS_EXT_DEPENDENCIES)
- dependencies = statext_dependencies_build(data);
- else if (t == STATS_EXT_MCV)
- mcv = statext_mcv_build(data, totalrows, stattarget);
- else if (t == STATS_EXT_EXPRESSIONS)
+ ResourceOwner oldowner = CurrentResourceOwner;
+ ResourceOwner child;
+
+ child = ResourceOwnerCreate(oldowner, "BuildExtStatistics");
+ CurrentResourceOwner = child;
+
+ PG_TRY();
{
- AnlExprData *exprdata;
- int nexprs;
+ /* evaluate expressions (if the statistics has any) */
+ data = make_build_data(onerel, stat, numrows, rows,
+ stats, stattarget);
- /* should not happen, thanks to checks when defining stats */
- if (!stat->exprs)
- elog(ERROR, "requested expression stats, but there are no expressions");
+ /* compute statistic of each requested type */
+ foreach(lc2, stat->types)
+ {
+ char t = (char) lfirst_int(lc2);
+
+ if (t == STATS_EXT_NDISTINCT)
+ ndistinct = statext_ndistinct_build(totalrows, data);
+ else if (t == STATS_EXT_DEPENDENCIES)
+ dependencies = statext_dependencies_build(data);
+ else if (t == STATS_EXT_MCV)
+ mcv = statext_mcv_build(data, totalrows, stattarget);
+ else if (t == STATS_EXT_EXPRESSIONS)
+ {
+ AnlExprData *exprdata;
+ int nexprs;
+
+ /* should not happen, thanks to checks */
+ if (!stat->exprs)
+ elog(ERROR, "requested expression stats, but there are no expressions");
+
+ exprdata = build_expr_data(stat->exprs, stattarget);
+ nexprs = list_length(stat->exprs);
+
+ compute_expr_stats(onerel, exprdata, nexprs,
+ rows, numrows);
+
+ exprstats = serialize_expr_stats(exprdata, nexprs);
+ }
+ }
- exprdata = build_expr_data(stat->exprs, stattarget);
- nexprs = list_length(stat->exprs);
+ /* store the statistics in the catalog */
+ statext_store(stat->statOid, inh,
+ ndistinct, dependencies, mcv, exprstats, stats);
+
+ /* Success: release child ResourceOwner normally */
+ CurrentResourceOwner = oldowner;
+ ResourceOwnerRelease(child,
+ RESOURCE_RELEASE_BEFORE_LOCKS,
+ false, false);
+ ResourceOwnerRelease(child,
+ RESOURCE_RELEASE_LOCKS,
+ false, false);
+ ResourceOwnerRelease(child,
+ RESOURCE_RELEASE_AFTER_LOCKS,
+ false, false);
+ ResourceOwnerDelete(child);
+ }
+ PG_CATCH();
+ {
+ ErrorData *edata;
+
+ /* Release leaked resources from the child ResourceOwner */
+ CurrentResourceOwner = child;
+ ResourceOwnerRelease(child,
+ RESOURCE_RELEASE_BEFORE_LOCKS,
+ false, false);
+ ResourceOwnerRelease(child,
+ RESOURCE_RELEASE_LOCKS,
+ false, false);
+ ResourceOwnerRelease(child,
+ RESOURCE_RELEASE_AFTER_LOCKS,
+ false, false);
+ CurrentResourceOwner = oldowner;
+ ResourceOwnerDelete(child);
+
+ /* Save the error, issue a WARNING and continue */
+ MemoryContextSwitchTo(cxt);
+ edata = CopyErrorData();
+ FlushErrorState();
- compute_expr_stats(onerel, exprdata, nexprs, rows, numrows);
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING),
+ errmsg("skipping statistics object \"%s.%s\" for relation \"%s.%s\"",
+ stat->schema, stat->name,
+ get_namespace_name(onerel->rd_rel->relnamespace),
+ RelationGetRelationName(onerel)),
+ errdetail("%s", edata->message)));
- exprstats = serialize_expr_stats(exprdata, nexprs);
+ FreeErrorData(edata);
}
+ PG_END_TRY();
}
- /* store the statistics in the catalog */
- statext_store(stat->statOid, inh,
- ndistinct, dependencies, mcv, exprstats, stats);
-
/* for reporting progress */
pgstat_progress_update_param(PROGRESS_ANALYZE_EXT_STATS_COMPUTED,
++ext_cnt);
[application/octet-stream] v1-0001-test-analyze-extended-stats-virtual-gen-col.patch (1.9K, 4-v1-0001-test-analyze-extended-stats-virtual-gen-col.patch)
download | inline diff:
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 37070c1a..7234d6b6 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -3207,6 +3207,14 @@ SELECT * FROM check_estimated_rows('SELECT * FROM virtual_gen_stats WHERE w = 0'
(1 row)
DROP TABLE virtual_gen_stats;
+-- extended statistics on virtual generated columns whose expressions can error
+CREATE TABLE virtual_gen_err (a int, b int GENERATED ALWAYS AS (a / 0) VIRTUAL);
+INSERT INTO virtual_gen_err VALUES (1), (2), (3);
+CREATE STATISTICS virtual_gen_err_s ON a, b FROM virtual_gen_err;
+ANALYZE virtual_gen_err; -- should warn, not fail
+WARNING: skipping statistics object "public.virtual_gen_err_s" for relation "public.virtual_gen_err"
+DETAIL: division by zero
+DROP TABLE virtual_gen_err;
-- Permission tests. Users should not be able to see specific data values in
-- the extended statistics, if they lack permission to see those values in
-- the underlying table.
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 3cc6012b..10918ffb 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -1608,6 +1608,13 @@ SELECT * FROM check_estimated_rows('SELECT * FROM virtual_gen_stats WHERE w = 0'
DROP TABLE virtual_gen_stats;
+-- extended statistics on virtual generated columns whose expressions can error
+CREATE TABLE virtual_gen_err (a int, b int GENERATED ALWAYS AS (a / 0) VIRTUAL);
+INSERT INTO virtual_gen_err VALUES (1), (2), (3);
+CREATE STATISTICS virtual_gen_err_s ON a, b FROM virtual_gen_err;
+ANALYZE virtual_gen_err; -- should warn, not fail
+DROP TABLE virtual_gen_err;
+
-- Permission tests. Users should not be able to see specific data values in
-- the extended statistics, if they lack permission to see those values in
-- the underlying table.
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Infinite Autovacuum loop caused by failing virtual generated column expression
In-Reply-To: <CAHg+QDcdkGQ4Q683Uq7ZJ0P6NcbB=F3Sh8thMSiFY9wwnSEoQQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox