Re: temp schemas

> On Fri, Aug 29, 2008 at 1:40 PM, Roberts, Jon
<Jon(dot)Roberts(at)asurion(dot)com>
> wrote:
>
> >> Why have you got thousands of them? If you are running with
thousands
> >> of active backends, may I suggest a connection pooler?
> >>
> >
> > I don't know. It looks like a bug to me where a temp table is
created
> > and dropped on commit but the next time the function executes and
> > creates a new temp table, it does this in another temp schema. It
does
> > this over and over until I have thousands of temp schemas that
aren't
> > used.
>
> Is this vanilla PG, or your Greenplum install?

Good memory. Both. GP is based on 8.2.6 while our PostgreSQL installs
are 8.3.0 and 8.3.1.

Here is an example from my local PG database:

C:\>psql elt0n elt0n
Welcome to psql 8.3.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.

elt0n=# select nspname from pg_namespace where nspname like 'pg_temp%';
nspname
---------
(0 rows)

elt0n=# create temporary table t1 on commit drop as select 'jon'::text
as col1;
SELECT
elt0n=# select nspname from pg_namespace where nspname like 'pg_temp%';
nspname
-----------
pg_temp_1
(1 row)

elt0n=# \c elt0n elt0n
You are now connected to database "elt0n".
elt0n=# create temporary table t1 on commit drop as select 'jon'::text
as col1;
SELECT
elt0n=# select nspname from pg_namespace where nspname like 'pg_temp%';
nspname
-----------
pg_temp_1
pg_temp_2
(2 rows)

elt0n=# select version();
version
-----------------------------------------------------
PostgreSQL 8.3.0, compiled by Visual C++ build 1400
(1 row)

elt0n=#

So now I have two pg_temp_% schemas that don't seem to get reused ever
and if I have lots of functions using temp tables, this really slows
down a connection to PG with pgAdmin.

Jon