Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wBbH1-001DBF-0Q for pgsql-hackers@arkaria.postgresql.org; Sat, 11 Apr 2026 16:33:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wBbGy-00H4nn-1u for pgsql-hackers@arkaria.postgresql.org; Sat, 11 Apr 2026 16:33:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wBbGy-00H4nf-0l for pgsql-hackers@lists.postgresql.org; Sat, 11 Apr 2026 16:33:29 +0000 Received: from mail-qv1-xf30.google.com ([2607:f8b0:4864:20::f30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wBbGw-00000000XXM-2N2Q for pgsql-hackers@lists.postgresql.org; Sat, 11 Apr 2026 16:33:28 +0000 Received: by mail-qv1-xf30.google.com with SMTP id 6a1803df08f44-89cc797547fso38549046d6.2 for ; Sat, 11 Apr 2026 09:33:26 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1775925204; cv=none; d=google.com; s=arc-20240605; b=kdAOvi9lwWCZsuG+K31EFBGik+UJanzVeMQokyj8A7fq9dOoEUcDXcocmrmO5L6Fcj hUa06P7Gmg+HfDwY2W0zr+6gE+XuYBHWzD7rj5lWaiSl8eeRXFy/kaUZTF56R0zA5Vyz hKsHQOpt2mqqgaPGn+8fwmLMIbRBgyqpTuBOYhK+OH/vtShc1lipyHObR3EStLgbxu2G dT3IlnJPbmlu351yXwxWr/P1Zv1SnCUM8MMPnPMlGFqy4K2uvAyxSqpIUem9I3NBCZLN pY3kEOWio3M8Jvs3/I/0BoeGcZlwqlpZ4ZsNRNacNYzwLcBe7gWrlgKQU/4n7GW9Y2TP LHew== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=gtxWqL0JMNNXNNxPAsErSNQIiNs5FtTXhQivdpSxnwE=; fh=z+N2Vl/ZhBbnTO82mVb72C/Dhz1bCagyUqc9peU935M=; b=Hxn7jhFvPdTYwSP/w9Twa68Sz6rfN9e7ukepVvHZWyCDkr9MaKzjI0s2S1bUJ3Aej+ nBaLkIzxAE7n2X013aESGWVHv6XV45UXJMzak9LtrzMZiYqZX3qNe+wqv8VHBxINq4Xq qcAQmORPviPfWHcaeIZ+XI5jmLMATxhJnticVsMOn1crh85rzZDaiI1DRu+Eobv82503 k3YSu0QRqUYPEFpDypgOCmG8dDGerwa310V5p20W53BqxAzp0Fk6Q84XQZqL6b6qJIWY d3XIjv+aa8NNvd2x9RnVx2hUwr2W0CvhtGWl2SROYXnw70pYHDBC0zR2ldBkZ//JGOhO LMdQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775925204; x=1776530004; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=gtxWqL0JMNNXNNxPAsErSNQIiNs5FtTXhQivdpSxnwE=; b=QSxe5nz8oO23zQc1adEvcZVwnKaKYVdG69Otd2s83duhnmqwV/18Sf7PJ7KSc7XMl9 Y/vYTI1uMdyRNN8lKY0g9/lQQPv8zqtc/mO+U9sA9DZDQsT3drSDlBU6Pn2ZkJLCnpHQ e9Z824A067aOYUp4xrMLm/CtaGVeQ5Tz/Oa2Wl9LKxPbjmvNP7fuYHQh3rVRg0qOX1D5 2Y0hqxMB338jf7thwwuuvQU5dwwBRfHWh0y2QMB49Z66jomYrfL/FhDAWaIfw5bD9/nP ESPVHdzeYwnvsjePiFP2mgSOdDPrwPzqM1ax56f6LBFevXwDR8Li3nrhV+G07c+bejTg FM9g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775925204; x=1776530004; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-gg:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=gtxWqL0JMNNXNNxPAsErSNQIiNs5FtTXhQivdpSxnwE=; b=kkRuwJELtYlTD/HvdhWqGlN95mvefYxWqZrFRnVDgILmDJdD1qSjBaoaj4OHanJHUt Q1WvjC4ALp2GH9OWZDam2sfUJtD4OqqvmKbBkaNRzGO5yJ6Ljh2PlKw89xH4kLR/TxRn PsTmyyG+0hLLlJdKWzwsZmVHHsJk+cOo2X0dgikw0z7o6PT8ebceVJxzisdUAvYZWWco fpIdVPL6ik554RkUrN+XLvNDBO3dxBzUpFyJ06eny52GUy6xv2roHWQU3TlS6B9nFkdX /TY8ympgCMWBA+YuiTaqZXAoq0hEg133w7xnBh6X6SzVCHUgt5Nn1Pw9kD/6R7ytwYAX KLqQ== X-Gm-Message-State: AOJu0YzGmey6RWVgiOvX0bZPqHbdN0whG+WhkSW3QiYwQSt4gQl5zn16 tKFnhaueGclr5K2dKwrJ2F0j14WB0klieUdHZmzVJXzx9DxPyRQNNpes/KJOpbKL8W6JqKr2QaF fzB3Ag0LC84Lw0wm2jbfvDF8La4yfnTY= X-Gm-Gg: AeBDieuSLpwcMNH+rN9+rTk4Zn2PNie8GYjzSysQ6KjRVhhj5wST1rwIlH/8JNDIQhx dl5UX/trLY7cxn28ibH74l3zTC5S2iE2JHaX7GMXC5f9Tzzqyb/zKX7RaiBVKSyU+nVlFKN2wd/ 1e6RG2lG+iRSypKIdil6H9ism7AjxEtEnCSnaAZAeDlafTSrA1LTvAwazLaYCX61I+2Qgsg7eqs XPDTSVTOA0ZuTU031kEbJhJ9Jt+CCAb1glHV2Vmfj9TMzo1gBRZ6SYTZhJpDaFYoXpDzQ/Zc27E vfmQWCCX94L0pVi+v91ftLRsn0XQNZvuZlwZR64= X-Received: by 2002:a0c:e016:0:b0:89e:e841:1f25 with SMTP id 6a1803df08f44-8ac860ba93fmr106144536d6.11.1775925204513; Sat, 11 Apr 2026 09:33:24 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dean Rasheed Date: Sat, 11 Apr 2026 17:33:13 +0100 X-Gm-Features: AQROBzBKwbRhqNZNgc_mEfy2TTTndlllZkLjl2z9MPsQiq-P5dPSKbums3pZ5MM Message-ID: Subject: Re: Infinite Autovacuum loop caused by failing virtual generated column expression To: SATYANARAYANA NARLAPURAM Cc: PostgreSQL Hackers , nagata@sraoss.co.jp Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 10 Apr 2026 at 21:19, SATYANARAYANA NARLAPURAM wrote: > > PG19 added support for stats on virtual generated columns [1]. Creating e= xtended statistics on a virtual generated column whose expression can raise= an error leads to ANALYZE failing repeatedly, and autovacuum retrying inde= finitely. This floods the server logs and also wastes resources. Vacuum ana= lyze 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. > 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 computa= tion 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. Regards, Dean