How to automatically drop temporary table upon commit of a transaction in YugabyteDB?

Question

[Question posted by a user on YugabyteDB Community Slack]

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 it 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)? Your input is greatly appreciated. Thank you

Answer 1

See these two GitHub issues:

#12221: The create table doc section doesn’t mention the ON COMMIT clause for a temp table

and

#7926 CREATE TEMP … ON COMMIT DROP writes data into catalog table outside the DDL transaction

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 dedicated 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$;