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 1pHYfY-0002t4-Ck for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 23:13:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHYfX-00034v-9Y for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 23:13:35 +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 1pHYfW-00033Q-Pk for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 23:13:34 +0000 Received: from mail-qv1-xf35.google.com ([2607:f8b0:4864:20::f35]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pHYfU-0004T7-D2 for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 23:13:33 +0000 Received: by mail-qv1-xf35.google.com with SMTP id qb7so20523002qvb.5 for ; Mon, 16 Jan 2023 15:13:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=niBF8h4jTX35andTMB5kn2HDYymq5iTonhHGuxDRcvw=; b=Iq690tGNPJ13Bj6p9khkrz0lIc8txoXsaMRQvT2vE1WZNbtVQXfYSy1+cDfdLenst8 mHFhfzqyaRzkNei8eDpV8as0SHY1FOAA4bK98eXvIBV1t6O29wjllHNy8AApMvZ4/fkh ipvoX8FfDm4u1wX0m0MHXug1wx7l1Ywawl4hMwkczVWV0iN9UybP8P6ExyrJDsBnlLtS 8KZ58mQb4dxKpO3Pbl7acMkHyOZsJG4SVyDtI0epliw1+jx78/9DHc1Mz1sX0DDQN3Z+ tYbTjcCWQd4FUBmPfKq7kZ+7oFeapXQAEitZMqjwmJFSl5bCL1BaKqEOxH1CjXFQXxx9 p28A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=niBF8h4jTX35andTMB5kn2HDYymq5iTonhHGuxDRcvw=; b=oAqtLrLvSvr9yxM6k1OJjcd58OkF0T030LMeG5qHd4wXnuNLcV30DA1yNHKxkB+Vkg Adko7Hfdz+NY2XANsItIU1UamknaQ4umFAUtlkYt2tS9ZPQzBO62VzpBGo1ZbvGApEH+ AlFCD1MdCR5b55YZvN7yo572IiQ+hfAt/JrcMeAvOfwAv96Pw2FnP77Binm+7D1dz6fA C09he6BtriHFL2wEqLNz4BZeV9/AYxK9+6I9D14ibd5GSMF/MsOb4mPksZW7TtnXqIc4 faZH7ozklE3OEl7cUhZHokfLY8NSMmeCYlu+7ETcuwU2Pbwv70B9F4J8504rLxFVuQ91 617g== X-Gm-Message-State: AFqh2krIFRqbhXLbE1axMBExPbptwiCLkk8EO0TLdVwQXfAXaytjuf4H fEXs0Z8BSNJGPtPXCCDbC2m87gp8xEI= X-Google-Smtp-Source: AMrXdXtVMvC3b1A1xAy2XZ3gXj8KzSxdOJ1xhDaVc0M/InTsEr0VIfvulzjMHJMTJd/p+c/NV9X0/A== X-Received: by 2002:a05:6214:5f81:b0:535:1aa0:1afe with SMTP id ls1-20020a0562145f8100b005351aa01afemr2202243qvb.8.1673910810770; Mon, 16 Jan 2023 15:13:30 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id r8-20020a05620a298800b006bb29d932e1sm19242356qkp.105.2023.01.16.15.13.30 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 16 Jan 2023 15:13:30 -0800 (PST) Content-Type: multipart/alternative; boundary="------------gYDpgfW8EyyuMEwRpecp65IA" Message-ID: Date: Mon, 16 Jan 2023 17:13:29 -0600 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" To: pgsql-general@lists.postgresql.org References: <4cd35a2737cc783662f1df14551c3dcb1bf55916.camel@cybertec.at> <5be65cbc-b181-f89d-32e4-8efd58d49f73@gmail.com> <016ec140-b1eb-a3cb-a1ac-d90ceae0d950@gmail.com> Content-Language: en-US From: Ron In-Reply-To: <016ec140-b1eb-a3cb-a1ac-d90ceae0d950@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. --------------gYDpgfW8EyyuMEwRpecp65IA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/16/23 15:46, Rob Sargent wrote: > 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? > "Should" as in "it's a good idea", not "it's important but not vital". -- Born in Arizona, moved to Babylonia. --------------gYDpgfW8EyyuMEwRpecp65IA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/16/23 15:46, Rob Sargent wrote:
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?


"Should" as in "it's a good idea", not "it's important but not vital".

--
Born in Arizona, moved to Babylonia.
--------------gYDpgfW8EyyuMEwRpecp65IA--