Why lots of temp schemas are being created

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. If you 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. Newgen Software Technologies Ltd (NSTL) accepts no responsibilities for loss or damage arising from the use of the information transmitted by this email including damages from virus and further acknowledges that no binding nature of the message 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 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@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

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

I restarted the database; forcing it to have just one backend. Then I repeated the above procedure. I'm fairly sure that pg_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 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.

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

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.

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