0

In my database I have various temporary tables, I do not want to drop, which is default behaviour of temp tables, these tables after each transaction.

To reduce such drop overhead, I have written following definition of the temp tables

CREATE TEMPORARY TABLE TEMPTABLEINPOSTGRESDB (LIKE PERMTABLE INCLUDING INDEXES) ON COMMIT DELETE;

Could you tell me the behaviour of such table? Will DB clear the table instead dropping it every time?

Is there any alternate solution to achieve functionality mentioned below?

Application is adding few records in each session, and application must delete the data once the session completes. The data added in the middle of the session, must not be visible to any other thread of the application.

Edit: Read session instead transaction.

Vishal
  • 3,189
  • 1
  • 15
  • 18

1 Answers1

1

The default behaviour is to drop the temporary table at the end of the session not the transaction. You don't need to do anything to keep it at the end of the transaction. Just don't use on commit:

CREATE TEMPORARY TABLE TEMPTABLEINPOSTGRESDB (LIKE PERMTABLE INCLUDING INDEXES)

Or use on commit preserve rows which is the default.

If you use on commit delete the table will be truncated and not dropped at the end of the transaction but will still be dropped at the end of the session.

If you need to keep the table between sessions you need a permanent table. If the data is specific to a session use a session id. Or KISS and just create and drop a temp table at each session.

http://www.postgresql.org/docs/current/static/sql-createtable.html

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • My problem is to keep the data for the entire session, and truncate the data as session finishes. I do not want to drop the tables to reduce the overhead of creation and drop in each session. So could you tell me what could be the performance impact of create and drop of temporary tables? – Vishal Jul 24 '13 at 02:02