Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 2B5E71337DAE for ; Sat, 18 Sep 2010 15:50:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 68365-04 for ; Sat, 18 Sep 2010 18:50:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 0FB5E1337B8B for ; Sat, 18 Sep 2010 15:50:07 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o8IIo7v3016388; Sat, 18 Sep 2010 14:50:07 -0400 (EDT) To: Mark Rostron cc: "pgsql-admin@postgresql.org" Subject: Re: management of pg-schemas: pg_temp_n and pg_toast_n ? In-reply-to: References: Comments: In-reply-to Mark Rostron message dated "Sat, 18 Sep 2010 11:41:13 -0700" Date: Sat, 18 Sep 2010 14:50:07 -0400 Message-ID: <16387.1284835807@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201009/118 X-Sequence-Number: 35662 Mark Rostron writes: > We do a lot of work with temp tables here, and over time a lot of pg_temp_[n] schemas have appeared. > Similarly, we notice build-up of pg_toast_[n] schemas. This is expected. There'll be one for each backend slot that's ever used any temp tables. > Is it ok to drop these if they contain no tables? It's risky: you have the race condition that some backend might be just starting to use a temp schema when you decide to blow away the schema. It's also pretty pointless, since the schema will just come back again next time it's needed. I'd say just ignore the temp schemas. regards, tom lane