Problem Statement

Currently, Infinity does not support temporary tables. There are many use cases where users need temporary tables that behave exactly like regular tables but without disk I/O, logging, or persistence — for example, in ETL pipelines, session-scoped temporary data, testing, or caching intermediate results.

Proposed Feature

Introduce temporary tables in Infinity with the following characteristics:

1. Syntax

Create a table using a new TEMPORARY keyword:

CREATE TEMPORARY TABLE table_name (...);

2. Same Operations as Physical Tables

Temporary tables support all standard SQL operations (SELECT, INSERT, UPDATE, DELETE, JOIN, etc.) just like regular persistent tables.

3. No Write-Ahead Log (WAL)

Operations on temporary tables are not logged to disk.

4. No Persistence

Data is lost when the table is dropped or the database restarts. No background flushing or checkpointing.

5. Full Transaction Support

Temporary tables should fully participate in transactions:

Support BEGIN, COMMIT, ROLLBACK

Transaction isolation (e.g., snapshot or serializable) applies to temporary tables as well

Cross-table transactions involving both temporary and physical tables should be allowed (though the temporary table portion won't be durable)

6. No Disk I/O for Operations

All DML/DDL on temporary tables happen purely in RAM, making them extremely fast.

Limitations

• Temporary tables should respect temporary limits (e.g., max_temporary configuration) to prevent OOM
• No recovery on restart — that's by design
• Consider adding TEMPORARY_TABLE_MAX_ROWS or TEMPORARY_TABLE_MAX_SIZE session variable for safety