Re: Why lots of temp schemas are being created

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(at)gmail(dot)com]
Sent: Tuesday, June 09, 2009 7:34 AM
To: Grzegorz Jaśkiewicz
Cc: Anirban Pal; pgsql-novice(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
Subject: Re: Why lots of temp schemas are being created

2009/6/8 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
> On Mon, Jun 8, 2009 at 6:57 AM, Anirban Pal<anirban(dot)pal(at)newgen(dot)co(dot)in> 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