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

Hi,

On Thu, Jul 30, 2026 at 8:30 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> I reviewed the patches and the discussion upthread. Here's my take on this.
>
> When autovacuum has N tables to vacuum and any one of them errors out, the remaining ones still get vacuumed (see the PG try-catch block around autovacuum_do_vac_analyze() in do_autovacuum()). That is unlike a database-wide vacuum or a vacuum with a table list. Those error out and do not continue with the others in the list. By vacuum, I mean vacuum analyze here.
>
> That said, given this is expected behaviour, why do we need to fix it just for generated columns?
>
> There are basically three options, and the patch as-is is the weakest one:
>
> 1/ Accept it as expected behaviour. It's not new, it's not a flood, it may succeed on a later retry, and the user can drop or fix the offending stats object in this specific case.
>
> 2/ Fix it in general, in autovacuum, so that no error during analyze loops forever, whatever the source is (extended stats, index stats, per-column expression, or any other error). This is more work but it fixes the real cause.
>
> 3/ Fix only extended-stats expressions, as the patch proposed upthread does so far. This is the narrow one; it is not simple to reason about, and it makes the behaviour inconsistent with the existing autovacuum behaviour.
>
> I would like to understand why we are going with (3) and not (1) or (2). My preference is (1).
>
> [1]
> CREATE FUNCTION f(v int) RETURNS int LANGUAGE plpgsql
> VOLATILE AS $$ DECLARE n int; BEGIN SELECT 1 INTO n; RETURN n / 0; END $$;
> CREATE TABLE t (a int);
> INSERT INTO t VALUES (1), (2), (3);
> CREATE STATISTICS t_s ON (f(a)) FROM t;
> ANALYZE t;

After thinking about this more, I still prefer leaving the behaviour
as-is (option 1). Unless anyone objects, I will withdraw this patch
from the CF. Thanks.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com