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 1kGNns-0001o0-N8 for pgsql-bugs@arkaria.postgresql.org; Thu, 10 Sep 2020 14:44:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kGNnr-0002do-Fa for pgsql-bugs@arkaria.postgresql.org; Thu, 10 Sep 2020 14:43:59 +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 1kGNnr-0002c5-59 for pgsql-bugs@lists.postgresql.org; Thu, 10 Sep 2020 14:43:59 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kGNno-0008TS-EZ for pgsql-bugs@lists.postgresql.org; Thu, 10 Sep 2020 14:43:57 +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 08AEhspO182232; Thu, 10 Sep 2020 10:43:54 -0400 From: Tom Lane To: gsaviane@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16614: Stale temporary objects makes vacuum ineffective when 1 million transactions remain In-reply-to: <16614-23e5c4c4567d1576@postgresql.org> References: <16614-23e5c4c4567d1576@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Thu, 10 Sep 2020 08:45:19 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <182230.1599749034.1@sss.pgh.pa.us> Date: Thu, 10 Sep 2020 10:43:54 -0400 Message-ID: <182231.1599749034@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > We noticed the same issue described below occurring multiple times in some > of our many Postgres 9.5 deployments. > The application makes extensive use of temporary tables. Although there are > scheduled activities for regularly vacuuming the database, after some months > of uptime the database gets into the 1 million transactions limit. Couple of possibilities here ... Autovacuum, and manual vacuum too, cannot do anything with temp tables belonging to other sessions. (It's physically impossible because the live data for a temp table is likely to only exist inside the memory of the owning session.) So, if you have temp tables that survive long enough to need vacuuming because of wraparound considerations, then the owning session has to do that itself. Alternatively, maybe you don't have any temp-table-owning sessions that live that long, but for some reason some temp tables failed to get dropped when their owning session exited. (AFAIK this could only happen as part of a database crash --- is that a common occurrence for you?) They're still there and still holding back the global XID horizon, so the only way to fix things is to forcibly drop them. PG versions later than 9.5 have added increasingly aggressive rules for automatically dropping such orphan temp tables. From memory, 9.5 does have some behavior of that sort; but it's evidently not firing for you. What you'll need to do is find the old tables (look for pg_class entries with large values of age(relfrozenxid)) and manually DROP those tables. I would not recommend the wholesale removal of temp schemas shown in your script, because that carries too much risk of removing tables that are in active use. regards, tom lane