Re: Leftover TEMPORARY tables?

jboes(at)nexcerpt(dot)com (Jeff Boes) writes:
> I just noticed that there are a number of tables in our schema with
> names fo the form pg_temp_*; these correspond to temporary tables
> created by our application, but they shouldn't still exist. Under what
> circumstances would a temporary table become permanent?

In theory, never. Do you have frequent backend crashes? Is it possible
that the backend sessions that created these tables are actually still
running?

If you're quite certain that the sessions that created those tables are
dead, it is safe to issue DROPs against them. I am not sure whether you
have to jump through any hoops to do so though --- the protection
checks against dropping system tables look for "pg_XXX" and so are
likely to mutiny :-(. You might need to run a standalone backend with
the -O command-line switch to get around that protection check.

PG 7.3 handles this scenario a little better, but I take it you haven't
upgraded yet ...

regards, tom lane