Thread: temp tables remain after server restart

Hi,

one of our programs went haywire and created around 200,000 temp
tables. In the end, I restarted the db, but the temporary tables are
still around

the query

SELECT n.nspname, c.relname, c.relkind, c.relpages, c.reltuples FROM
pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE
(c.relkind = 'r'::"char" OR c.relkind = 'i'::"char") order by relpages

still shows all 200,000 of them.
What should I be doing to clean it up?

They are all under pg_temp_xxxxx namespaces

Any help is appreciated.
--
Hari

Hari Bhaskaran <hbhaskaran@gmail.com> writes:
> one of our programs went haywire and created around 200,000 temp
> tables. In the end, I restarted the db, but the temporary tables are
> still around

What did you do, the old "kill -9 some random process" approach to
database management?  The recommended ways of cancelling a session
wouldn't have caused this.

> What should I be doing to clean it up?

There is code to make them go away the first time a backend wants to use
the relevant pg_temp_xxxxx namespace.  So you could start a backend,
do "create temp table ...", start another backend while the first
remains running, do another "create temp table ...", repeat until they
go away.

It would probably work to do "drop schema pg_temp_xxxxx cascade" too,
but you'd have to be really careful not to clobber the temp schema of
an active backend this way.

            regards, tom lane

> What did you do, the old "kill -9 some random process" approach to
> database management?  The recommended ways of cancelling a session
> wouldn't have caused this.

I never said I kill -9 .  I do pg_ctl stop
BTW, drop cascade on the namespace seems to be working.

create temp queries failed with an error asking to increase
max_locks_per_transaction variable. Now that you mention about
the clean up code, it does make sense - it was probably trying to
cleanup and it couldn't.
I increased max_locks_per_transaction and now delete cascade seems to work
(still running, so I can't say)

> There is code to make them go away the first time a backend wants to use
> the relevant pg_temp_xxxxx namespace.  So you could start a backend,
> do "create temp table ...", start another backend while the first
> remains running, do another "create temp table ...", repeat until they
> go away.

Didn't know that - thanks for the info.