Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHXJA-0004ey-6g for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 21:46:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHXJ9-000626-1K for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 21:46:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pHXJ8-00061x-Mu for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 21:46:22 +0000 Received: from mail-pj1-x102e.google.com ([2607:f8b0:4864:20::102e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pHXJ4-0003ml-ED for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 21:46:21 +0000 Received: by mail-pj1-x102e.google.com with SMTP id 7-20020a17090a098700b002298931e366so2389832pjo.2 for ; Mon, 16 Jan 2023 13:46:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=h38Cb+3aA6oSJomzTZVKSx4DgyF2iHGY5VTLjigswEU=; b=pEk86nhZxM/H3pmlW6aaAqko2empYJrUvRTcsdnzwtWvEzPL5Gi+bXYloABhUHitLb KHaxTIcxE8DF7w4uKQUdKznLlLBJX3ZxaXbLT44TG1EiLpZQWn2PgsHQtS4k43QsWQBw w1J3NdhZtA9gvRcHmModrc1GAz0zDUQWmjC4sKTAV7EW5+eK0eTMqGhcnN1oYlet0ST4 6WVbtEY9M2L8EuzOlfVdPrk2Zu7eWsr/iWdfeqrjrxo94SZOyihADdvvXeuY+aGUdrpy CO3ZedvOzQgF0eSFhJQntC9gD68WKi8pih4IniGLVKPJYiPy3PdLKyz6Mf9wYqWO1HkB sKYw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=h38Cb+3aA6oSJomzTZVKSx4DgyF2iHGY5VTLjigswEU=; b=FXthIEwgtOGjGZa0vzfATnT6/3rPwvwwCEcuRs9MaGhPe+P4KGjakGW4XkAUL0C48K KNQdcqyDL7g3yLdWkFvDlRp0PahnzTdTab0SPkFMw59E+SKWJUpkghltvPlBCSW5yXwo taTotDNAP+yhQ+qPmpadcP1vzrKVYgdiUdDIFxXFFb+hP6e2qi8dfSyqRytHI8P4i0aP 9oGdhyF26+Ks9XrGSpAmiXu8Wc3I+k0BxpEA19Wqcs0iwvHsPpdssauRmL2tlSLZe14H RL6stQSp2GLQo9vJC7+smcIuMUzbN9dPsT0nGuDjIQg9YCN4Rh2HpMqOumZXjlq6WnEu i9Fg== X-Gm-Message-State: AFqh2kqo2iENDJ2Gyednb2SyGcNvwAznzNiLakOFr0Gjv/6Tem8ppjCu RBAICRlTbPL+H4EAabFsArphYx7D5IE= X-Google-Smtp-Source: AMrXdXupAGtXfNv1TySpIdMbWK89dubD3D7YbeohvuUogAxfEPd/vVAACLOtkdsamlmq+h1T58ikQg== X-Received: by 2002:a17:903:445:b0:189:5f5c:da1e with SMTP id iw5-20020a170903044500b001895f5cda1emr21901265plb.27.1673905577129; Mon, 16 Jan 2023 13:46:17 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:c349:8252:612c:7fef? ([2601:681:5a00:f790:c349:8252:612c:7fef]) by smtp.gmail.com with ESMTPSA id y13-20020a170902b48d00b00192f7f8db8esm6576867plr.297.2023.01.16.13.46.16 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Jan 2023 13:46:16 -0800 (PST) Content-Type: multipart/alternative; boundary="------------0XO1TT007EOv9RRARcUf6pC0" Message-ID: <016ec140-b1eb-a3cb-a1ac-d90ceae0d950@gmail.com> Date: Mon, 16 Jan 2023 14:46:15 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Why is a Read-only Table Gets Autovacuumed "to prevent wraparound" Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <4cd35a2737cc783662f1df14551c3dcb1bf55916.camel@cybertec.at> <5be65cbc-b181-f89d-32e4-8efd58d49f73@gmail.com> From: Rob Sargent In-Reply-To: <5be65cbc-b181-f89d-32e4-8efd58d49f73@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0XO1TT007EOv9RRARcUf6pC0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/16/23 14:18, Ron wrote: > On 1/16/23 07:11, Laurenz Albe wrote: >> On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote: >>> This is a puzzle I have not been able to crack yet. >>> >>> We have a single-page table with 28 rows that is purely read-only. >>> There isn't a way in postgres to make a table RO, but I say this >>> with confidence because pg_stat_user_tables has always showed 0 >>> updates/deletes/inserts. >>> >>> Furthermore, the schema app developers know, for certain, this table >>> does not get changed at all. >>> >>> We installed scripts that run every few minutes that do a 'select *' >>> and over a period of days, we have not seen a change. >>> >>> We disabled autovacuum on this table '{autovacuum_enabled=false}'. >>> But, despite the fact that this table is read-only (by design) and >>> autovac id is disabled, it got autovac'd twice in less than 10 >>> days and on both occasions, pg_stat_activity showed the worker with >>> 'to prevent wraparound'. This explains why autovac did not honor the >>> disabled status. >>> >>> But why is this table autovac'd at all? >> For every table PostgreSQL stores the oldest transaction ID in an >> unfrozen tuple >> in "pg_class.relfrozenxid".  Once that is more than >> "autovacuum_freeze_max_age", >> the table gets autovacuumed.  If the table is already all-frozen, >> that is a short >> operation and will just advance "pg_class.relfrozenxid". > > So OP should VACUUM FREEZE the table. > Hm, did OP say there was an actual problem as is?  Or just a "puzzle" - now explained - and no action is necessary? --------------0XO1TT007EOv9RRARcUf6pC0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 1/16/23 14:18, Ron wrote:
On 1/16/23 07:11, Laurenz Albe wrote:
On Mon, 2023-01-16 at 07:48 -0500, Fred Habash wrote:
This is a puzzle I have not been able to crack yet.

We have a single-page table with 28 rows that is purely read-only. There isn't a way in postgres to make a table RO, but I say this with confidence because pg_stat_user_tables has always showed 0
updates/deletes/inserts.

Furthermore, the schema app developers know, for certain, this table does not get changed at all.

We installed scripts that run every few minutes that do a 'select *' and over a period of days, we have not seen a change.

We disabled autovacuum on this table '{autovacuum_enabled=false}'. But, despite the fact that this table is read-only (by design) and autovac id is disabled, it got autovac'd twice in less than 10
days and on both occasions, pg_stat_activity showed the worker with 'to prevent wraparound'. This explains why autovac did not honor the disabled status.

But why is this table autovac'd at all?
For every table PostgreSQL stores the oldest transaction ID in an unfrozen tuple
in "pg_class.relfrozenxid".  Once that is more than "autovacuum_freeze_max_age",
the table gets autovacuumed.  If the table is already all-frozen, that is a short
operation and will just advance "pg_class.relfrozenxid".

So OP should VACUUM FREEZE the table.

Hm, did OP say there was an actual problem as is?  Or just a "puzzle" - now explained - and no action is necessary?

--------------0XO1TT007EOv9RRARcUf6pC0--