CODEWITHSUNDEEP
postgresql
Jeremy S.
Jeremy S.

Reputation: 3171

Dropping of temp tables in PostgreSQL?

Curious as to whether or not one should be dropping temp tables that are used strictly in regards to the function they are declared within? New to postgreSQL and haven't found much information on the topic. I'm aware of the fact that in SQL this is taken care of automatically but certainly MS SQL and postgreSQL have their differences. What do you think is best practice in terms of the dropping of temp tables declared in functions if at all necesarry?

Upvotes: 2

Views: 4413

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

they are somewhat different for MS and Pg. Ms treats local temp tables created in SP specially - drops on the completion of the procedure Postgres does not currently support GLOBAL temp tables (specifying it in create statement is ignored)

Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated;

The best practice is not very much applicable here I would say. Leaving temp tables for duration of the session is ok (they will be dropped at the end). But often you would prefer using ON COMMIT DROP to drop table after transaction ends, not session... If endless session is comparably ok for postgres, endless transaction can be so-so for MVCC and locking and so on... Again you might want to look into ways to fight it...

To summarise: It is often practice to leave temp tables persist till the end of session, more "normal" to leave them persist till end of transaction. Postgres does not treat temp tables created in fn() specially. Postgres does not have GLOBAL temp tables. Depending on the code you write and env you have you might want to drop or leave temp table to be dropped automatically. Mind session/transaction pooling particularities here a s well.

Upvotes: 3

Related Questions