Re: temp schemas - Mailing list pgsql-general

> -----Original Message-----
>
> "Roberts, Jon" <Jon.Roberts@asurion.com> writes:
> > I am noticing a large number of temp schemas in my database.  We use
> > temp tables but it doesn't appear that the schemas get dropped for
some
> > reason.
>
> That's intentional.  There doesn't seem a lot of value in dropping a
> catalog entry that'll just have to be created again later.
>

This is what I'm trying to understand.  At what point does PostgreSQL
determine it needs to create a new temp schema versus reusing an
existing one?  Maybe we are doing something incorrectly in our code.

> > This greatly slows down how long it takes pgAdmin to connect
> > because it retrieves thousands of pg_temp_% schemas.
>
> 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.

On Wednesday, we had 170,243 temp schemas and today, we have 173,384.

> (It might be a good idea to fix pgAdmin so it ignores other sessions'
> temp schemas, though.)
>

It looks this SQL is executing when connecting with pgAdmin which
doesn't exclude temp schemas.  Even though I don't have it configured to
show temp schemas, the SQL doesn't exclude these records.  This is with
1.8.2 of pgAdmin.

SELECT CASE WHEN nspname LIKE E'pg\\_temp\\_%%' THEN 1
            WHEN (nspname LIKE E'pg\\_%') THEN 0
            ELSE 3 END AS nsptyp,
       nsp.nspname, nsp.oid, pg_get_userbyid(nspowner) AS
namespaceowner, nspacl, description,       has_schema_privilege(nsp.oid,
'CREATE') as cancreate
  FROM pg_namespace nsp
  LEFT OUTER JOIN pg_description des ON des.objoid=nsp.oid
 WHERE NOT ((nspname = 'pg_catalog' and (SELECT count(*) FROM pg_class
WHERE relname = 'pg_class' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'pgagent' and (SELECT count(*) FROM pg_class WHERE relname =
'pga_job' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'information_schema' and (SELECT count(*) FROM pg_class WHERE
relname = 'tables' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'dbo' and (SELECT count(*) FROM pg_class WHERE relname =
'systables' AND relnamespace = nsp.oid) > 0) OR
(nspname = 'sys' and (SELECT count(*) FROM pg_class WHERE relname =
'all_tables' AND relnamespace = nsp.oid) > 0))
ORDER BY 1, nspname


Jon