Showing results for 
Search instead for 
Did you mean: 

How do orphaned temp tables get removed?


When using temp tables, the tables are created in a temporary schema, with name something like pg_temp_##. These tables and schemas are usually removed at the end of a session (i.e., when you log out of psql). However, in some cases, like a server crash, these temp tables and schemas are left standing in the database. In these situations, autovacuum should identify and drop these tables, in which case a DBA might see several entries in the log with the following message:

LOG:  autovacuum: found orphan temp table "pg_temp_##."<>" in database "<database name>"

The important part of such a message is it’s a LOG-level message, not a FATAL or ERROR-level message, so these are merely informational messages where autovacuum identified an orphan temp table, but will not do anything about it (yet).

Bear in mind that these temp tables will not interfere with ongoing transactions and sessions, but could create uneasiness for any DBA or sysadmin. This issue gets resolved when the database enters vacuum-for-wraparound maintenance, and automatically drops these tables. However, DBAS wishing to avoid the messages in the log, these tables can be safely dropped issuing a drop schema pg_temp_## cascade;


Version history
Revision #:
3 of 3
Last update:
‎07-25-2019 02:35 PM
Updated by:
Labels (1)