Thread

Re: Infinite Autovacuum loop caused by failing virtual generated column expression

SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-05-03T18:04:59Z

Hi,

On Tue, Apr 28, 2026 at 2:14 AM Yugo Nagata <nagata@sraoss.co.jp> wrote:

> On Tue, 14 Apr 2026 00:16:42 -0700
> SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> wrote:
>
> > Hi
> >
> > On Mon, Apr 13, 2026 at 11:24 PM Yugo Nagata <nagata@sraoss.co.jp>
> wrote:
> >
> > > On Sat, 11 Apr 2026 17:33:13 +0100
> > > Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> > >
> > > > On Fri, 10 Apr 2026 at 21:19, SATYANARAYANA NARLAPURAM
> > > > <satyanarlapuram@gmail.com> wrote:
> > > > >
> > > > > 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.
> > > > >
> > > >
> > > > True, though this is nothing new. The same thing can happen with
> > > > expression statistics on an expression that raises an error, which
> has
> > > > been possible since PG14.
> > >
> > > Yes, this issue is not new, and I’m not aware of a way to prevent it a
> > > priori.
> > >
> > > >
> > > > > 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?
> > > > >
> > > >
> > > > I'm not sure. The default retry interval is 1 minute, so it won't
> > > > exactly be a flood of messages. Also, if the error only occurs for a
> > > > small subset of rows, it's possible that retrying might succeed.
> > >
> > > I think it would be good to skip ANALYZE for the extended statistics
> that
> > > cause
> > > errors and just emit a warning, rather than aborting ANALYZE for the
> > > entire table.
> > > It seems reasonable to treat this as the user’s responsibility to
> notice
> > > the warning
> > > and address the underlying issue.
> > >
> >
> > Yugo, thanks for the comments. Could you please review the v1 patch when
> you
> > get a chance. It is in the direction you suggested.
>
> I've looked into the patch and have some comments.
>
> The child ResourceOwner is created and released in
> BuildRelationExtStatistics(),
> but I don't think it is necessary if we add other PG_TRY block in
> make_build_data()
> and compute_expr_stats(). For example in make_build_data():
>
> +                       PG_TRY();
> +                       {
> +                               datum = ExecEvalExpr(exprstate,
> +
> GetPerTupleExprContext(estate),
> +
> &isnull);
> +                               }
> +                       PG_CATCH();
> +                       {
> +                               ExecDropSingleTupleTableSlot(slot);
> +                               FreeExecutorState(estate);
> +                               PG_RE_THROW();
> +                       }
> +                       PG_END_TRY();
>

Thanks, for reviewing the patch. Agreed, please find the updated patch.


> Also, we could add tests for extended statistics that do not involve
> virtual generated
> columns, since those are not the cause root of the issue. In addition, it
> might be useful
> to verify that non-skipped extended statistics are still computed
> successfully.
> For example:
>
> +CREATE TABLE expr_err (a int);
> +INSERT INTO expr_err VALUES (1), (2), (3);
> +CREATE STATISTICS expr_err_s1 ON ((a/0)) FROM expr_err;
> +CREATE STATISTICS expr_err_s2 ON (a/0),(a+1) FROM expr_err;
> +CREATE STATISTICS expr_err_s3 ON ((a+1)) FROM expr_err;
> +ANALYZE expr_err;  -- should warn, not fail
> +SELECT statistics_name from pg_stats_ext x
> +    WHERE tablename = 'expr_err' ORDER BY ROW(x.*);
>

Added these tests as well in the v2 patch.

 Thanks,
Satya