# How to automatically drop temporary table upon commit of transaction
**URL:** https://forum.yugabyte.com/t/how-to-automatically-drop-temporary-table-upon-commit-of-transaction/1581
**Category:** General
**Created:** [April 20, 2022, 8:01am UTC](https://forum.yugabyte.com/t/how-to-automatically-drop-temporary-table-upon-commit-of-transaction/1581 "2022-04-20T08:01:17Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@vlst](https://forum.yugabyte.com/u/vlst)
#### Post date: [April 20, 2022, 8:01am UTC](https://forum.yugabyte.com/t/how-to-automatically-drop-temporary-table-upon-commit-of-transaction/1581/1 "2022-04-20T08:01:17Z")
Hello everyone,
I am running YugabyteDB 2.12 single node, and would like to know if it is possible to create a temporary table such that it is automatically dropped upon committing the transaction in which was created.
In “vanilla” PostgreSQL it is possible to specify “ON COMMIT DROP” option when creating a temporary table. In the YugabyteDB documentation for “CREATE TABLE” no such option is mentioned, however when I tried it from ysqlsh it did not complain about the syntax. Here is what I tried from within ysqlsh:
```auto
yugabyte=# begin;
BEGIN
yugabyte=# create temp table foo (x int) on commit drop;
CREATE TABLE
yugabyte=# insert into foo (x) values (1);
INSERT 0 1
yugabyte=# select * from foo;
x
---
1
(1 row)
yugabyte=# commit;
ERROR: Illegal state: Transaction for catalog table write operation 'pg_type' not found
```
The CREATE TABLE documentation for YugabyteDB mentions the following for temporary tables:
“Temporary tables are only visible in the current client session or transaction in which they are created and are automatically dropped at the end of the session or transaction.”
When I create a temporary table (without the “ON COMMIT DROP” option), indeed the table is automatically dropped at the end of the session, but it is not automatically dropped upon commit of the transaction. Is there any way that this can be accomplished (apart from manually dropping the table just before the transaction is committed)?
Your input is greatly appreciated.
Thank you
---
### Author:  [@bryn](https://forum.yugabyte.com/u/bryn)
#### Post date: [April 21, 2022, 12:04am UTC](https://forum.yugabyte.com/t/how-to-automatically-drop-temporary-table-upon-commit-of-transaction/1581/2 "2022-04-21T00:04:43Z")
Thanks for this question
See these two GitHub issues:
> #12221: [The create table doc section doesn’t mention the ON COMMIT clause for a temp table](https://github.com/yugabyte/yugabyte-db/issues/12221)
and
> #7926 [CREATE TEMP … ON COMMIT DROP writes data into catalog table outside the DDL transaction](https://github.com/yugabyte/yugabyte-db/issues/7926)
You cannot (yet, through YB-2.13.0.1) use the `ON COMMIT DROP` feature. But why not use `ON COMMIT DELETE ROWS` and simply let the temp table remain in place until the session ends?
Saying this raises a question: how do you create the temp table in the first place? Your stated goal implies that you’d need to create it before every use. But why? You could, instead, have dedictated initialization code to create the `ON COMMIT DELETE ROWS` temp table that you call from the client for this purpose at (but only at) the start of a session.
If you don’t want to have this, then (back to a variant of your present thinking) you could just do this before every intended use the table:
```auto
drop table if exists t;
create temp table t(k int) on commit delete rows;
```
After all, how else (without dedicated initialization code) would you know whether or not the temp table exists yet?
If you prefer, you could use this logic instead:
```auto
do $body$
begin
if not
(
select exists
(
select 1 from information_schema.tables
where
table_type='LOCAL TEMPORARY' and
table_name='t'
)
)
then
create temp table t(k int) on commit delete rows;
end if;
end;
$body$;
```
Regards, [Bryn Llewellyn](https://www.linkedin.com/in/bryn-llewellyn/)
---
### Author:  [@vlst](https://forum.yugabyte.com/u/vlst)
#### Post date: [April 21, 2022, 3:26pm UTC](https://forum.yugabyte.com/t/how-to-automatically-drop-temporary-table-upon-commit-of-transaction/1581/3 "2022-04-21T15:26:09Z")
Thank you for the reply.
I was not aware that the “ON COMMIT DELETE ROWS” option was supported in YugabyteDB.
Since we are creating our temporary tables from within code that runs in Wildfly, it is possible for a database connection session to remain open for quite a while. This is because a component of Wildfly provides a pool of connection handles, and when a certain thread is finished using such a connection handle from the pool, the underlying database connection is not closed, but rather it will be kept open so that the next thread that requires a database connection handle can use it (unless a timeout period on the order of about half hour expires). Therefore, if these temporary tables are not being dropped at the end of the transaction, it is quite possible for hundreds, even thousands, of such tables to accumulate.
If we were to use the “ON COMMIT DELETE ROWS OPTION”, are the rows deleted as if the “DELETE FROM temp\_table” SQL command were run (thus, still taking up space on the disk), or as if “TRUNCATE temp\_table” was executed (which results in the table rows no longer taking up any disc space)?
Your input is greatly appreciated.
Thank you
---
### Author:  [@bryn](https://forum.yugabyte.com/u/bryn)
#### Post date: [April 21, 2022, 5:59pm UTC](https://forum.yugabyte.com/t/how-to-automatically-drop-temporary-table-upon-commit-of-transaction/1581/4 "2022-04-21T17:59:43Z")
I checked with the Yugabyte staff developer who opened [Issue 7926](https://github.com/yugabyte/yugabyte-db/issues/7926). he tells me that it will be a very long time before `ON COMMIT DROP` can be supported in YSQL. So you do indeed have to work around this.
You asked if `ON COMMIT DELETE ROWS` is implemented like `DELETE` or like `TRUNCATE` . I’m afraid that I don’t know.
You said that you have long-living sessions in a connection pool and the risk of a huge number of temp tables building up during such a session’s lifetime. I’m afraid that you’re not telling me enough about your app’s design to allow me to do more than just speculate. Any such session, at any moment that it’s not idle, will have been checked out for use by a single ultimate next-tier client. Connection pool systems usually provide a hook for initialization code that should run at check-out time (and, I believe) when a checked out connection is returned to the pool. They also provide a hook for initialization code that should run at session creation time.
You could use the _“on-session-create”_ hook to create one or several temp tables as your app logic requires, and to give them fixed known names. You could use the _“on-session-check-out”_ hook to truncate all of these temp tables explicitly. (It would be good to issue `ROLLBACK` here too, as a safety measure.) Or you could use the _“on-session-return-to-pool”_ hook to trunctate the temp tables. Then the real body of the app code could safely access the temp tables using their known names.
Would such an approach work?
Without understanding your scenario at a detailed level (and this isn’t feasible, I’m afraid), I don’t think that I can help you beyond what I’ve already said.
p.s. See YSQL DOC [Issue 12221](https://github.com/yugabyte/yugabyte-db/issues/12221). This mentions that the `ON COMMIT` clause for `CREATE TEMP TABLE` needs to be described. The doc must state what is, and is not, supported.