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.