Postgres Orphaned Temp Tables

LOG: autovacuum: found orphan temp table “pg_temp_23″.”a_temp_table_temp” in database “productiondb”

I recently came across this issue after performing a PITR (Point in Time Restore) on a Postgres Instance. The Log message from autovacuum was throwing multiple times a second, and I don’t know about you but I couldn’t stand to let my log fill up with garbage or leave orphaned objects behind. So of course I hit the Net looking for a solution and found a lot about ignoring the Log message, or re-performing the PITR to another point in time with hopes that there will not be temp objects :-( . I found this function pg_is_other_temp_schema() which will return true if the schema oid passed to it belongs to a temp schema in a session other than your own (System Information Functions), and I wrote a simple query to find these temp tables/objects.

select pgns.nspname as schema_name
, pgc.relname as object_name
from pg_class pgc
join pg_namespace pgns on pgc.relnamespace = pgns.oid
where pg_is_other_temp_schema(pgc.relnamespace);

Next it was time to drop these tables and resolve the issue, and surprisingly enough you can actually run a drop table on a temp table from another session. In my case there were about 15 temp tables which each had overflowed to pg_toast tables and I am kind of lazy so I set off to write a query to generate the code to drop all of these objects. Normally when your table extends to pg_toast if you were to drop the table the pg_toast object also gets cleaned up in the background as part of the dropping process, fortunately it is the same with temp tables.

select case when pgc.relname like '%_index' 
then 'drop index ' || pgns.nspname || '.' || pgc.relname || ';' 
else 'drop table ' || pgns.nspname || '.' || pgc.relname || ';' end as drop_query
from pg_class pgc
join pg_namespace pgns on pgc.relnamespace = pgns.oid
where pg_is_other_temp_schema(pgc.relnamespace) 
and pgc.relname not like '%toast%';

Then all that was left to do was copy, paste, and execute! This ended up working out great and I am happy to announce that the autovacuum log message went away and the havoc it was wreaking on my OCD with it.