| From: | SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, nagata(at)sraoss(dot)co(dot)jp |
| Subject: | Infinite Autovacuum loop caused by failing virtual generated column expression |
| Date: | 2026-04-10 20:18:36 |
| Message-ID: | CAHg+QDcdkGQ4Q683Uq7ZJ0P6NcbB=F3Sh8thMSiFY9wwnSEoQQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-fix-analyze-extended-stats-virtual-gen-col.patch | application/octet-stream | 5.1 KB |
| v1-0001-test-analyze-extended-stats-virtual-gen-col.patch | application/octet-stream | 1.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Paquier | 2026-04-10 20:34:34 | Re: pg17: XX000: no relation entry for relid 0 |
| Previous Message | Jeff Davis | 2026-04-10 20:03:36 | Re: pg_get__*_ddl consolidation |