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