Re: Why lots of temp schemas are being created

On 08/06/09 13:57, Anirban Pal 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.

Each transaction's temporary tables are isolated from each other by
putting them in a different schema. That's why you can have the same
temp table name in several concurrent transactions. Pg automatically
creates new schema if there aren't enough for all the concurrent
transactions.

The pg_toast_temp schema are for the TOAST tables associated with those
temp tables. Normal tables may have associated TOAST tables containing
out-of-line and/or compressed data. They normally live in the pg_toast
schema. To prevent naming conflicts when two temp tables of the same
name both have TOAST tables, separate pg_toast_temp schema must be
created for them.

Don't delete them or mess with them, and just examine the pg_namespace
system table with something like:

select * from pg_namespace where not nspname like 'pg_%';

--
Craig Ringer