I've gotten a lot of similar schemas, like: CREATE SCHEMA pg_toast_temp_1 AUTHORIZATION postgres; and CREATE SCHEMA pg_temp_1 AUTHORIZATION postgres; I don't see anything of substance inside those schemas, like tables or sequences, so I THINK they are the legacy of some previous problem (OS crash, ham-fisted tinkering, bug, etc.). I would like to drop them, since there are so many of them, they make it tedious to look through my databases in pgAdmin. Is there a reliable way to distinguish between temp schemas that exist because they are supposed to be there and those that are not? Or even better, a way to tell the database to clean them up itself? Or way better, a way to put a stop to the process that creates them? Thanks! Walter -----Original Message----- From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx] Sent: Tuesday, June 09, 2009 7:34 AM To: Grzegorz Jaśkiewicz Cc: Anirban Pal; pgsql-novice@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx Subject: Re: Why lots of temp schemas are being created 2009/6/8 Grzegorz Jaśkiewicz <gryzman@xxxxxxxxx>: > On Mon, Jun 8, 2009 at 6:57 AM, Anirban Pal<anirban.pal@xxxxxxxxxxxx> wrote: >> Dear all, >> >> Our software use postgres as backend database. It works fine, strange thing >> is that, it creates so many temporary schemas under schema tab, names like >> pg_toast, pg_toast_temp_1 like this upto pg_toast_temp40. This schemas also >> reflected in "pg_namespace" table. Can anybody tell me, is there anything >> wrong in it? Should I get rid of these temporary schemas? Any suggestion >> on this, please. > > you are a purist, bad habit. > Anything beginning with pg_* is an internal schema, don't touch it, > and no - you can't get rid of it. actually, you can get dangling temp schemas/tables if the database crashes or the computer goes through a hard cycle. This can be a major disaster on 8.2 and below if autovacuum is running because it prevents it from doing the database wide vacuum to prevent xid wraparound. This is highly mitigated now because of the way xid works and better warning/notification from the backend. So we should distinguish between temp schemas that exist because they are supposed to be there and those that are not. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
