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 1kH7mY-0004qT-9B for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Sep 2020 15:49:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kH7mW-0003ao-RQ for pgsql-bugs@arkaria.postgresql.org; Sat, 12 Sep 2020 15:49:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kH7mW-0003ah-LO for pgsql-bugs@lists.postgresql.org; Sat, 12 Sep 2020 15:49:40 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kH7mU-0007CE-B0 for pgsql-bugs@lists.postgresql.org; Sat, 12 Sep 2020 15:49:40 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 08CFnYCC553830; Sat, 12 Sep 2020 11:49:34 -0400 From: Tom Lane To: Giorgio Saviane cc: Francisco Olarte , Michael Paquier , PostgreSQL mailing lists Subject: Re: BUG #16614: Stale temporary objects makes vacuum ineffective when 1 million transactions remain In-reply-to: References: <16614-23e5c4c4567d1576@postgresql.org> <182231.1599749034@sss.pgh.pa.us> <20200911063036.GH2743@paquier.xyz> <371035.1599834081@sss.pgh.pa.us> Comments: In-reply-to Giorgio Saviane message dated "Sat, 12 Sep 2020 13:57:23 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <553828.1599925774.1@sss.pgh.pa.us> Date: Sat, 12 Sep 2020 11:49:34 -0400 Message-ID: <553829.1599925774@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Giorgio Saviane writes: > Let me clarify. > The sequence of events that led our databases to the wraparound issue is > the following: > 1. The application creates temporary tables that should be by default > cleaned at the end of the session. As a matter of fact the pooler doesn't > actually close the session, neither - apparently - issues a DISCARD ALL, > creating leaks among sessions. > 2. Since sessions are kept alive with stale temporary tables the > autovacuum process cannot do maintenance on transaction ids. > 3. Since we have no evidence on what is going on, the database reaches > the 1 million stop limit to avoid transaction wraparound. > 4. The application gets then out of order. We stop the Postgres server > gracefully and we enter single user mode to run the vacuum > 5. Any attempt to vacuum the database ends up with the usual "WARNING: > database "mydb" must be vacuumed within XXX transactions" message, > revealing that the vacuum could not fix up the transaction ids. So, actually, that was the wrong recovery method. All you really needed to do was terminate that long-running session(s) and wait for a minute or two. There is code to delete old orphaned temp tables, even as far back as 9.5. But *it's part of autovacuum*. Dropping down to single-user mode prevents it from running. I thought briefly about whether VACUUM in single-user mode should also run that code; but I'm not really convinced it'd be a good thing to be auto-dropping tables in single-user mode. And anyway we shouldn't be encouraging use of single-user mode, and it'd be much better to not be in this situation to start with. What seems like the most helpful thing we could do is teach autovacuum to issue log warnings when it sees old temp tables holding back datfrozenxid. The trick here is to not spam the log too much. After a few moment's thought, maybe warn if: * a table is temp but not orphaned, and * its relfrozenxid or relminmxid is equal to datfrozenxid or datminmxid, proving that some previous autovac run saw it as holding back those horizons, and * those xids are older than the relevant freeze limit. For extra credit maybe the message could mention the PID of the session owning the table? regards, tom lane