Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bxbaS-0001FD-C1 for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Oct 2016 15:18:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bxbaR-0002uA-V2 for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Oct 2016 15:18:23 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bxbYw-00010d-0J for pgsql-hackers@postgresql.org; Fri, 21 Oct 2016 15:16:50 +0000 Received: from mail-dm3nam03on0041.outbound.protection.outlook.com ([104.47.41.41] helo=NAM03-DM3-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bxbYs-0000uU-UG for pgsql-hackers@postgresql.org; Fri, 21 Oct 2016 15:16:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=I0VKuKx2j4NFGw/jCK5o8dJ+TGJtiWNOWfDyajcaL4A=; b=jneqlQdNByoVXGXOkazOGuPtppBt0t7QdaX+iRlHVmE80exDTcguRxsCh8BddJGxkiFrnmQz1p6EmafevtTBwD7PRguaoQ8WrIfHuP8r+xsII+7w2o8jSjxrKgIdrfa/wgB24JvYt6oeQ8uug5PZecDRE64mvQ6JXbK3uVdUgos= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (108.232.179.70) by CY4PR11MB1640.namprd11.prod.outlook.com (10.172.71.16) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.669.12; Fri, 21 Oct 2016 15:16:43 +0000 Subject: Re: Fun fact about autovacuum and orphan temp tables To: Tom Lane , Michael Paquier References: <7b7f3e98-9753-3fa9-aaa4-b6f8a766c251@postgrespro.ru> <20161020153024.19cbc758@ppg> <22658.1477057638@sss.pgh.pa.us> CC: "Constantin S. Pan" , Grigory Smolkin , PostgreSQL mailing lists From: Jim Nasby Message-ID: Date: Fri, 21 Oct 2016 10:15:21 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0) Gecko/20100101 Thunderbird/45.3.0 MIME-Version: 1.0 In-Reply-To: <22658.1477057638@sss.pgh.pa.us> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [108.232.179.70] X-ClientProxiedBy: CY1PR15CA0031.namprd15.prod.outlook.com (10.163.14.41) To CY4PR11MB1640.namprd11.prod.outlook.com (10.172.71.16) X-MS-Office365-Filtering-Correlation-Id: 98d5363c-225b-400e-cde2-08d3f9c5443f X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;2:7DZV8qKv2kFVS10LvAXLBaIiIimZhFyL35LJtQcLovimTJ9BonTJ384m0OJWgg7NIwNix2CaT2DxhHzi9k63iehoBRLGgekWgAecWfbMa0WuHp2jvvocj/I3ub3EP3LoQI7zS5nJdOiPRDmWHv3Hsxed0bko4rqByCnY2NNjh38R3oEeE2kNFounYFOm9QCWvZUWfocmYW0bRxNGQ+m5iA==;3:1b4mTANk37b/eeKxd3mbzi7ymGq8ke2iOxT0S2Ov42ZbxRSPb01b/NN/JLQyw8LXAfxnJ6yiuHZb8sSlZwf2vEOh7gzRmbDY44BvTEHYPQETMa3CyIcEGUoLtWiMnzxDXbYEdHGuOuukVssqSPFuug== X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1640; X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;25:sTnMTFpKwSBfJCVBrOnBuJo3a81sdWJIY1P4LIJtygACejdeFRY/oqO/lXM/iKcSZhYFxprMYBRaAVkVprlWeMiU/nqKjaqB3rp1vMX2J8mOM8IJibS04tKtXwA6PlFkJjqGHbp3bZ6ILmr61dkrMM8E17ekPJjuB7WnMk839SCQBHsHO7VmoWC+1bcCRtN0uy7PAu4jeeuZ2HDkYV6knSnz4lmPpowfMYSEoX8L+XRBIAWoBLtzpjYUmrK99aUZ/jcWuemO+fAFaGERRDKUqLrL0612RKAgnNSC8/fVAB9JNlnNEf+ErfM311TdfUHXpfeYdjBl4ZdpjZ/TzJXUbXwNKQCKDKoEFJcp57Sa5UnS703RTXNsULxVnJQRD/iEWuKBXR5Rwr3nA4LmTw4MOlKf3MrievdlwEwvRE1oTe76dt/h6yLLf0v+ogTYMygg/tvCcv3e+yq06BFSUCpRpr0diFXuu2cvVxecxh/RUR3NXrM558qrDkETaejxxPM0ZJua6mQoc+61WhGFnFVmHNtc0gp0Y5ygSAdSheotOj0ozjFMn1IN+KVV1Dqf9T6FoK9FERKtmzq//sxXIAyokGC33/8QZU16S+VLNSmc9ZVcWOBJUBVzJWeE2LQ9reTQBmtz6T7Hd2Oha9hJQ4fLgix9cMpn8p4QHJplpYqPO27JjksLho67h/J0hKTsO1naw30geTMDOsMIkmSKPnxRA5/hn8CIyeXUvo+irq81fx+d90wqgfgih5N/gILaIx3We02tVkY5tsE2Hw3jb6CDiD8RdB/XU3paZev5cJf0eX5YeeQgyg139XhRoiWDMbMc X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;31:dW4w+iutU9JJ1nJj6pxXwYHxHaOAzGJnzO6BEz4eAyZSxSmqYletFvlQBMuJQ7ye1FN/EkeFIFPcWWJbAaVJ2gOacFNgv7ibb1ZW4Aa5YDfR1hdJU0KIG0mIMiVNjxaFE+yHNzwH06kAwS86ixqj68FkhU08MS1Q7G9W+DoWzoekwKooOTE9Q2z6YKMzKtKpsEd/yM8qirpnKAFhC4DvlyK0PstPa826P7zO5wqiJakb0iMQAiMMAISdnSWEuimuva5v7k5rzsWJbQhLFskIdg==;4:u6p3cgoJsKZE5ddkXScrHP+s8N5soXtIi76Iy4CEVXXBEkzVeUuXKhrM04YPvyOY1XWDb+2qK++5STKqAK90A/ytWnU8eaQzRPittAEorGF/RhLRow24wiSrctWBwMFGPcCEnO3l0zqJJy+i5w1kA55baHUJaiJsNHvu+nU8ufIaEEsZYeSrpXO4VJdbaomoLQLq9gBnLSyAPBl3i75ED6ej7Of3W1nFKy0nST+nkHjPUMP0I4Lmfco4+NTkC+9qLje0740zl7RRM65T/z5p1zi5rUoSTJT1tLoUvjf5DBDjxC+JvWNi8gVzCXhQyu+vlRCKdwz+mL6TUDStR1fB+TO2l54Z6CpcTFTufDe+ZjGPL0HjxmLoILnQsClTdz8DBUzgDEi4RPrAkZx/UhvvUI/WOPbBe2/sXGX9rylFw1MYvT6UxNhq5BPOVIOFy8vN X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(5005006)(8121501046)(10201501046)(3002001)(6043046)(6042046);SRVR:CY4PR11MB1640;BCL:0;PCL:0;RULEID:;SRVR:CY4PR11MB1640; X-Forefront-PRVS: 01026E1310 X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(7916002)(377454003)(24454002)(189002)(199003)(305945005)(42186005)(3846002)(65826007)(33646002)(53416004)(31686004)(76176999)(31696002)(101416001)(68736007)(54356999)(4001350100001)(6116002)(36756003)(7846002)(77096005)(5001770100001)(5660300001)(7736002)(586003)(81166006)(15975445007)(50986999)(64126003)(92566002)(93886004)(2950100002)(105586002)(19580395003)(4326007)(50466002)(8676002)(6666003)(83506001)(97736004)(65956001)(23746002)(106356001)(65806001)(66066001)(230700001)(16601075003)(2906002)(69596002)(81156014)(189998001)(86362001)(47776003)(85282002);DIR:OUT;SFP:1101;SCL:1;SRVR:CY4PR11MB1640;H:decina.attlocal.net;FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1;LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?Windows-1252?Q?1;CY4PR11MB1640;23:CYVWOlFTMsP2dli+BdalyoURyqViBB7zwDEGR?= =?Windows-1252?Q?41zja6DQ60wDlSYIj+CehceA+TbZs1N35shitzLUuOD2ajBYLIBXqx1X?= =?Windows-1252?Q?uodmeJ0n7Bz903V+rHvT5b4pimitRV9Boxm45R/iuAIDo6RgahFG88x9?= =?Windows-1252?Q?JioGB8RTSXHkjq68L68rGacohPMhKukSBYccg2l6Jci35LINQDt2MPcN?= =?Windows-1252?Q?yFpuh8PqDSTTez2caYouoxgtiRMBM8toGWzw+G4zwpgscbdPTWgs0mUd?= =?Windows-1252?Q?NDoAg6WV16qebRHbv5UHCu5eRaTp61b5tz+nhyWOxB/YShBIL6Nn2yHP?= =?Windows-1252?Q?sSOpKC8MDQNPH0cE/+qMCzw5Y8tQBFh7MF9eJZluwOhiGVV3Hc2Dcv7+?= =?Windows-1252?Q?28SXKyHqX1gv3u24DqH8iZGRff6v4XMDY+5tH4jEOhRm2vL+BcWLC6hi?= =?Windows-1252?Q?+eaJ2sL/1Y2OYd+MGzangNU8YAS2ky6kNMlqJCljtcP2D78e7J4Yfn8h?= =?Windows-1252?Q?hyKZjEEOdd2AN39DT1/Cg/Gr65ZZt+qn/cV/qukxcO4zAOotaVZouWSf?= =?Windows-1252?Q?tSjRgkxxMZ2Fupq9Fgrtb9lHU3LY859NrWieqakaTNAASmxaKIYWFbqC?= =?Windows-1252?Q?dt+s/HX2feWT+2zko43QQBAYNwee814d/YmwVOYSYomKm3E0cjZf6cc/?= =?Windows-1252?Q?5c8D/t27JmrUdvw/S3Zl7nm0Zr5lsb3Sos9ojQkSNHq+ZOesTNUCOLEM?= =?Windows-1252?Q?ceE2BOftjwRugQHb0TT0F+T+IX2MMN2SXjag53roMoNddcDdfekZH7rM?= =?Windows-1252?Q?KkuvueZEJfVH23H6wPegAJXS8vdOEa/K0c/8yP0ouymc9zLwnsi7uw0e?= =?Windows-1252?Q?q1pQ0JhtK0zAeL3Aos6ZG189kFDsbY5xjuFhHu4z3OxZIisvUuNuw1Ci?= =?Windows-1252?Q?v3kMb1kTJhs9Pj0QlgpLGWCLRQMaIjpBvXzHprIhyxk4MylUPOc4r+kA?= =?Windows-1252?Q?eRqTWfXlBsyc8RIzBLlY0vHb3V5qRrg+MYz/08G+/o0gioe9XhU+rKUt?= =?Windows-1252?Q?Hm2iGJ0MjO4r751Zy19O47nZT5ikqvoQqTSI+P61zK0spF3/AmsaSg6W?= =?Windows-1252?Q?4JI2kLh4xW3Vdu6g9SYE6Ezh2VX60aHSmwjN07tlEeDXLI3yua4NKZo4?= =?Windows-1252?Q?AlkShuks8U41gYNd1S/9DH3ADub2ds6FqawPI6i3u/vfYy5LtaYtLE9b?= =?Windows-1252?Q?m5VF8CfYWT1SAXabLBZOL74eHyC2IYsxGymPzhuFdvL6Yl/PdpipJ80L?= =?Windows-1252?Q?m1YV5hSNS7qAbem/IVdGRsUKscrlIc6rH+9xV1oxqEKN7rTpZvRHxX/r?= =?Windows-1252?Q?GEhQbLP+IJRYrIJf1FVyulb9tg32XfflHItysq9KMYaUnGlws0qgJHBR?= =?Windows-1252?Q?NIZf5RJltD1gGArnjefJ/cUdCbi5HHFG6qXdR38XpooEt6s94+EY6r0D?= =?Windows-1252?Q?4MGwoCMzGN5Rc0mZ4K7UktYlTO791fla3truA75HtbWUvlzgQ=3D=3D?= X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;6:bYKxzokGWKUriqY4aw7QX6ofN7G1/a2/wLS6F+IDTvjuyDPc0uvOpGaQeeVPLLjUo1upVNpsXTAoQhfCOhdxJoNGMMjTtKhST64lrAhOyQsi/x6Jca0Rr/9C51rakV05TDc+djzfo5wqgfa1HD7ujmaICaSvW4eoVrQhJP3b7/0pfzbLOFfqFbHRO9oj3ypRZb6GGS0tYThSLms/M9Ish1/tD/6aj0xtMzZBE2w7EP5CXGr9kFa1tYv58lJTMtQzi04q8N5urLwzt3QO2v2pPe9Y9ZNgNijDw6LhOZWh1L42bU5QgpzsTao67Don1n6Dat+mGupBHE33UuCaTf1yLA==;5:sRML00Q6bQAK3ZCTLTEOrsqACcFV7Nto/rL7fhwYkSh3xoxAkKGsRmpXW0PbToYdWlNwRpN5fFe/btH0KUwWKPQwCcU/kiv9t6+STU0K2gjCtsyFg84aaySy6CwSo+YUVLlIJgW30oRJh10yW3Br6DHCo7Hnun9z07bJsJUyJ+g=;24:SYdFUU3PN4VZrDwVHm1XQm2+UfiHIdH2w4ZDf9S6u91WhMzzBH3+z3b74K++OqnQvVES+BbMQ1qlnfHq7YswOBpULMeSIR2ROBez0Mta76Q= SpamDiagnosticOutput: 1:99 SpamDiagnosticMetadata: NSPM X-Microsoft-Exchange-Diagnostics: 1;CY4PR11MB1640;7:QudYvenn/txx/J7LjOqNkGXQyXZWISNWoGKpVxzWWbqaWynhZUVKJV36+sXsWI+RrQoHNPeA5ixdvuVV9Q9qyFC0SRrt9Zu5O5ISccigPGpnmB4TZBcDigiNE6D/5R4bXRW5dZt5ax1QGfdXx526JFf7crchgNDeejXBDv2mk2Y/IpVnLkWbaAzFuFm+F/uAAw+mZbxRZiomp/0aaxfzoC2GsybYunV3LeZOtKVEmwz4X1v4AhJ4Mr5AdrpNxuxYKQK+td0RCpBn8pzP9oI9+LvDl/8h2PdZtsICLtBgp0lkID3xPfFce5fuONI0pA68xuAMRsrdhK6W0TMtsK75fbumgIE1aLoike7rP0PkKC0= X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 21 Oct 2016 15:16:43.3573 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1640 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On 10/21/16 8:47 AM, Tom Lane wrote: >> It seems to me that you'd even want to make the drop of orphaned >> > tables mandatory once it is detected even it is not a wraparound >> > autovacuum. > If we are willing to do that then we don't really have to solve the > problem on the backend side. One could expect that autovacuum would > clean things up within a few minutes after a backend failure. Unless all the autovac workers are busy working on huge tables... maybe a delay of several hours/days is OK in this case, but it's not wise to assume autovac will always get to something within minutes. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers