Hi, On Tue, Apr 28, 2026 at 2:14 AM Yugo Nagata <[email protected]> wrote:
> On Tue, 14 Apr 2026 00:16:42 -0700 > SATYANARAYANA NARLAPURAM <[email protected]> wrote: > > > Hi > > > > On Mon, Apr 13, 2026 at 11:24 PM Yugo Nagata <[email protected]> > wrote: > > > > > On Sat, 11 Apr 2026 17:33:13 +0100 > > > Dean Rasheed <[email protected]> wrote: > > > > > > > On Fri, 10 Apr 2026 at 21:19, SATYANARAYANA NARLAPURAM > > > > <[email protected]> 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
v2-0001-Fix-ANALYZE-crash-on-extended-stats-with-virtual-gen.patch
Description: Binary data
