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

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

Walter Coole <WCoole@aperiogroup.com> writes:
> I've gotten a lot of similar schemas, like:
> CREATE SCHEMA pg_toast_temp_1
>   AUTHORIZATION postgres;

These schemas are automatically created to hold temporary tables.  There
is not a lot of point in removing them, because they'll just reappear
next time you have a lot of sessions creating temp tables.

You might want to pester the pgAdmin folk to add an option to ignore
them in pgAdmin's displays.

            regards, tom lane

Thanks for the pointer!

In case anyone else has the same problem, here's what I did:

I used

SELECT MAX(backendid) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

to get the highest backend ID that is running.  I deleted all the pg*_temp_ schemas numbers higher than that.

This didn't seem quite thorough enough, as I found that when a process would end (MAX(backendid) went down), the
correspondingpg*_temp_ schema would not go away.  I think these were schemas created by a previous backend, so would
notbe cleaned up by a backend that hadn't created it.
 

I restarted the database; forcing it to have just one backend.  Then I repeated the above procedure.  I'm fairly sure
thatpg_toast_temp_1 and pg_temp_1 are not actually in use, but I decided to quit while I'm ahead.
 

I guess these schemas are fairly harmless, but it seems kind of messy to have them sloshing around.  It seems like when
anew backend starts up, it would be better to clear out the temp schemas to avoid accidentally using stale data, but
thisdoesn't seem to be happening.  One could also imagine hooking a cleanup in the database startup, but I don't see
thateither.
 

Walter


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com] 
Sent: Wednesday, February 03, 2010 3:36 PM
To: Walter Coole
Cc: Merlin Moncure; Grzegorz Jaśkiewicz; Anirban Pal; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why lots of temp schemas are being created

Walter Coole escribió:

> 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?

Run pg_get_backend_idset() (or something like that, maybe there's "stat"
in the name), which returns a list of backend IDs that are running.
Then see which temp schemas have numbers beyond what's listed there;
those shouldn't be there and could cause problems if the numbers are too
high.

> Or even better, a way to tell the database to clean them up itself?

It does, unless one of them gets a very high backend ID that's not
reused.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Temp schemas are not destroyed on session shutdown; they are rather
> destroyed the next time the backend ID is reused.  Normally that's not a
> problem, because a backend ID is reused pretty soon.  It's only a
> problem when you use so high a backend ID due to high load, that a very
> long time passes before it's reused.  Those temp tables linger and can
> cause Xid wraparound problems.

Not correct --- ordinarily temp tables are removed at backend shutdown.
The only time that wouldn't happen is in event of a backend crash.  In
which case cleanup would happen at next use, as you describe.

The schemas are indeed left around, but they're empty in the normal case.

            regards, tom lane