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 1kuVrj-0008Ad-II for pgsql-bugs@arkaria.postgresql.org; Wed, 30 Dec 2020 07:25:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kuVri-0005tN-Cs for pgsql-bugs@arkaria.postgresql.org; Wed, 30 Dec 2020 07:25:50 +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 1kuVri-0005tF-6J for pgsql-bugs@lists.postgresql.org; Wed, 30 Dec 2020 07:25:50 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kuVrf-0001Rl-UG for pgsql-bugs@lists.postgresql.org; Wed, 30 Dec 2020 07:25:48 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id 325E85C00DF; Wed, 30 Dec 2020 02:25:47 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Wed, 30 Dec 2020 02:25:47 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=7yI18WSfUJL1dFpt6ZegVMgbb0d yblN7c8YwcnneVN4=; b=M2AmxcMhQ62rfqYsxKc2D1cuVKSVssCHPmPNYqjl71n 7AvsVXs7wtde+tKsTG3VXSLHNf1kUsFO5roPi1gPEaNmi0TOVN2IODjrBPlYQAVr O3bf/a4HZdn/AEEcmHFQFOURZOtbhJ/wgUTQTiadkJJffndze9yE1ge1iU5aTXJI 82SX354yNJNwmao1QD5/hl3SKtGeqK7i1g0lSAtlZglVRlcC+nIxGQpV7nHHPtR1 9Y0SRrq9E4qbLuGomIKGT/mKIJKyqUC8urOm5dfTO9TPBsJet2yuCj6N7lGSsPsp Kl3MTvyA+tdpWcYbJcwJrIZ7MdDKFozRsIQZS8VJ4Sw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=7yI18W SfUJL1dFpt6ZegVMgbb0dyblN7c8YwcnneVN4=; b=qx7fgLddIoIyJWQidRm5SA VLKHMXkd8/tfPoxeJPTp4z1pVeRCdJXkw4/8F2augPOy4cDiYJLpcjpDLEHeICgS bpWAJltMvia7uC28t1Ax0bWBHknKibzGOyCE4hNsACeoDE9NrNaqvewHDV22yaQE hgwdUpTFW2JmBhwxlVUpj+/dvU3VbWyQJZzEqyF+duBT5lhzPX/5UaLy7O+dxXs1 17XWG69bWuY8gzcCKFs6SzjPIa3p13vvH77zbSWtQL/vVT6+jNrP8Lqzy8+EU/j4 IL/+2ioU0KtVXMVxKXYsMVATrgw84l3JR8zQW3WnWLaP1ef6dKEK/t2WK7NJVlIQ == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedujedrvddvvddguddutdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enfghrlhcuvffnffculdefhedmnecujfgurhepfffhvffukfhfgggtuggjsehgtderredt tddvnecuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesph grqhhuihgvrhdrgiihiieqnecuggftrfgrthhtvghrnhepffdufeeflefghfekudekkeev vddugfeiteekkeduleefvefhhffftdduffeiuedtnecuffhomhgrihhnpehpohhsthhgrh gvshhqlhdrohhrghenucfkphepudduuddruddtvddrudektddrudekheenucevlhhushht vghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprg hquhhivghrrdighiii X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id 3769B108005F; Wed, 30 Dec 2020 02:25:45 -0500 (EST) Date: Wed, 30 Dec 2020 16:25:42 +0900 From: Michael Paquier To: Max Vikharev Cc: Tom Lane , Jeff Janes , pgsql-bugs Subject: Re: BUG #16691: Autovacuum stops processing certain databases until postgresql rebooted Message-ID: References: <16691-e029ffaaf2e1e10c@postgresql.org> <387272.1604250098@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="yuqtWpy9Olo2OFDj" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --yuqtWpy9Olo2OFDj Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Tue, Dec 29, 2020 at 02:17:51PM +0300, Max Vikharev wrote: > Finally we discovered that the problem is related to > https://www.postgresql.org/message-id/flat/0A3221C70F24FB4583343325556920= 4D1F8A4DC6%40G01JPEXMBYT05 >=20 > Autovacuum hangs on the database which contains orphaned temp tables. When > I deleted the schema with orphaned temporary tables - autovacuum continued > to work without reboot. Just wondering. Are you sure that this instance is based on 12.3? The removal of orphaned temp tables should be more aggressive as we also track down in autovacuum if a specific temp schema OID is in use by a backend, and the initialization of a temp namespace for a backend cleans up temp objects from past connections that were leaving around orphaned objects because of a crash (see around RemoveTempRelations() in InitTempTableNamespace()).=20 -- Michael --yuqtWpy9Olo2OFDj Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl/sK3YACgkQnvQgOdby QH3ViBAAhBwwnTn2pwjXFE951CBOQFqsSImoM6eK74OxC0oiCVLcMwO3bGGrz6mD FCTFwmo+ZiDlLTe7l2900StZx8P6jbQF1GbcFvY9pHZYvKCFBWCmuY1O65ziX6mi Y6W2ADHtFQJCPGg50JVgLarQ/BjkmLM9dLTsKQalF0fwoGImrAPsBB3H73dDoEdD gGAnTxwcysguYELv4cH+hIFazMSXF6HeNLvBz2b4le4YMfG9zzT5D8Vq4a3kYO1r EyfM3R5p+J3RJ3SfYs0fEy+RIW26vAD+w0+05v4zf5EpaiaHZZbvt7IrpatceMNr Odbky46XaDDFHaVLuyd6WclfoUdzQg2Kt2+TnzWVPw7O4O6LMOuCrV2ej71Qrcdd Hn4NqxyuNAKL2LZ3v3GEhcPEzNAVLRE8QTce4nM8AL3IM4Q9IBCOew3XXrtmTSoH PDDdbhDZkn+YmdE6ohI1GiyGo5vDUKRPT8HuPuHcC+P6aDFzybGxHo7L1XDOfntk eYRvMwssQSk47nIRFofJrhwlQpNPljQTg8qHnB+7LDPSgdousynwFwj71PQl6kiA JTsJIsNW7zKfHRT+biRd0QmZjpejSzwK5REwfRKflggh47yCs+OdB8/Mj5pX3eXf fPaIabcfCeA2yrgYSPwwa4R3+BTXbAjeaJve4uctnoH8COElISs= =VEq8 -----END PGP SIGNATURE----- --yuqtWpy9Olo2OFDj--