MIME-Version: 1.0
References: 
 <CAHg+QDcdkGQ4Q683Uq7ZJ0P6NcbB=F3Sh8thMSiFY9wwnSEoQQ@mail.gmail.com>
 <CAEZATCXWgYexeVm8yS6G8oFyyF=8ef9QwDt4Eyia-5hxBhjoVw@mail.gmail.com>
 <20260414152425.879493d6202956e23afeae03@sraoss.co.jp>
In-Reply-To: <20260414152425.879493d6202956e23afeae03@sraoss.co.jp>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 14 Apr 2026 00:16:42 -0700
Message-ID: 
 <CAHg+QDfu46gK5mqtG9fA5YnT+rc70nidPYs_TFsZk-z2bdTysQ@mail.gmail.com>
Subject: Re: Infinite Autovacuum loop caused by failing virtual generated
 column expression
To: Yugo Nagata <nagata@sraoss.co.jp>
Cc: Dean Rasheed <dean.a.rasheed@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003faec2064f666203"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDfu46gK5mqtG9fA5YnT%2Brc70nidPYs_TFsZk-z2bdTysQ%40mail.gmail.com>
Precedence: bulk

--0000000000003faec2064f666203
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

On Mon, Apr 13, 2026 at 11:24=E2=80=AFPM 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 expressi=
on
> 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) succeed=
s.
> > >
> >
> > 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=E2=80=99m 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 sta=
ts
> 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=E2=80=99s responsibility to=
 notice
> the warning
> and address the underlying issue.
>

Yugo, thanks for the comments. Could you please review the v1 patch when yo=
u
get a chance. It is in the direction you suggested.

Thanks,
Satya

--0000000000003faec2064f666203
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi</div><br><div class=3D"gmail_quote gma=
il_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Apr 13, 2=
026 at 11:24=E2=80=AFPM Yugo Nagata &lt;<a href=3D"mailto:nagata@sraoss.co.=
jp">nagata@sraoss.co.jp</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex">On Sat, 11 Apr 2026 17:33:13 +0100<br>
Dean Rasheed &lt;<a href=3D"mailto:dean.a.rasheed@gmail.com" target=3D"_bla=
nk">dean.a.rasheed@gmail.com</a>&gt; wrote:<br>
<br>
&gt; On Fri, 10 Apr 2026 at 21:19, SATYANARAYANA NARLAPURAM<br>
&gt; &lt;<a href=3D"mailto:satyanarlapuram@gmail.com" target=3D"_blank">sat=
yanarlapuram@gmail.com</a>&gt; wrote:<br>
&gt; &gt;<br>
&gt; &gt; PG19 added support for stats on virtual generated columns [1]. Cr=
eating extended statistics on a virtual generated column whose expression c=
an raise an error leads to ANALYZE failing repeatedly, and autovacuum retry=
ing indefinitely. This floods the server logs and also wastes resources. Va=
cuum analyze on that column (without extended stats) succeeds.<br>
&gt; &gt;<br>
&gt; <br>
&gt; True, though this is nothing new. The same thing can happen with<br>
&gt; expression statistics on an expression that raises an error, which has=
<br>
&gt; been possible since PG14.<br>
<br>
Yes, this issue is not new, and I=E2=80=99m not aware of a way to prevent i=
t a priori.<br>
<br>
&gt; <br>
&gt; &gt; 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 aut=
ovacuum infinite retry. Attached a draft patch for the option (2). Thoughts=
?<br>
&gt; &gt;<br>
&gt; <br>
&gt; I&#39;m not sure. The default retry interval is 1 minute, so it won=
9;t<br>
&gt; exactly be a flood of messages. Also, if the error only occurs for a<b=
r>
&gt; small subset of rows, it&#39;s possible that retrying might succeed.<b=
r>
<br>
I think it would be good to skip ANALYZE for the extended statistics that c=
ause<br>
errors and just emit a warning, rather than aborting ANALYZE for the entire=
 table.<br>
It seems reasonable to treat this as the user=E2=80=99s responsibility to n=
otice the warning<br>
and address the underlying issue.<br></blockquote><div><br></div><div>Yugo,=
 thanks for the comments. Could you please review the v1 patch when you</di=
v><div>get a chance. It is in the direction you suggested.</div><div><br></=
div><div>Thanks,</div><div>Satya</div><div>=C2=A0</div></div></div>

--0000000000003faec2064f666203--