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:
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)?
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:
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:
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$;
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)?
I checked with the Yugabyte staff developer who opened Issue 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. 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.