Thread: Why lots of temp schemas are being created

Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information. If you are not the origin al intended recipient and have erroneously received this message, you are prohibited from using, copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for los s or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no bin ding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of NSTL.

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.


--
GJ

Thank you for your response.




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


--
GJ

Disclaimer :- This e-mail and any attachment may contain confidential, proprietary or legally privileged information.
Ifyou are not the original intended recipient and have erroneously received this message, you are prohibited from
using,copying, altering or disclosing the content of this message. Please delete it immediately and notify the sender.
NewgenSoftware Technologies Ltd (NSTL)  accepts no responsibilities for loss or damage arising from the use of the
informationtransmitted by this email including damages from virus and further acknowledges that no binding nature of
themessage shall be implied or assumed unless the sender does so expressly with due authority of NSTL.  

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

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

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

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

Walter Coole escribió:

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

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.

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

IIRC the time when the previous temp schema is destroyed is when the
first temp table is created in the new backend.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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