1

I'm using a large number of TEMP TABLES and want to make sure I'm not leaking storage.

I create and use the table in a transaction with CREATE TEMP TABLE mytable ... ON COMMIT DROP.

I'm having trouble understanding the documentation for CREATE TEMPORARY TABLE. It states:

The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands.

Will ON COMMIT DROP reclaim space or do I need to actually run VACUUM mytable?

If I do need to run VACUUM then I'm not sure how, as the temp table isn't available after the transaction ends.

Joe
  • 1,655
  • 2
  • 11
  • 14

1 Answers1

3

Temporary tables are dropped completely at the end of your session in any case. (Or at the end of the current transaction already with ON COMMIT DROP.) The autovacuum daemon (or manual VACUUM, for that matter) is not involved in this.

Since temp tables are registered in the system catalogs like regular tables, dropping them leaves some (very small!) dead tuples behind, which are cleaned up by autovacuum, eventually. If you want to count that as effect on storage.

Related:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633