Created Local Temporary Tables

[Adriano dos Santos Fernandes]

# Created Local Temporary Tables (FB 6.0)

Firebird 6.0 introduces support for SQL Created Local Temporary Tables
(LTT). Unlike Global Temporary Tables (GTT),
which have permanent metadata stored in the system catalogue, Local
Temporary Tables exist only within the connection
that created them. The table definition is private to the creating
connection and is automatically discarded when the
connection ends. The data lifecycle depends on the `ON COMMIT` clause:
with `ON COMMIT DELETE ROWS` (the default), data
is private to each transaction and deleted when the transaction ends;
with `ON COMMIT PRESERVE ROWS`, data is shared
across all transactions in the connection and persists until the
connection ends.

## Why Local Temporary Tables?

Local Temporary Tables are useful in scenarios where you need temporary
storage without affecting the database metadata:

### Temporary storage in read-only databases

Since LTT definitions are not stored in the database, they can be
created and used in read-only databases. This is not
possible with Global Temporary Tables, which require metadata modifications.

### Session-private definitions

Each connection can create its own temporary tables with the same names
without conflicts. The table definitions are
completely isolated between connections.

### Ad-hoc temporary storage

LTTs provide a quick way to create temporary storage during a session
for intermediate results, data transformations,
or other temporary processing needs without leaving any trace in the
database after disconnection.

## Comparison with Global Temporary Tables

Metadata Storage
• GTT: Stored in system tables (e.g. RDB$RELATIONS, etc.)
• LTT: Stored only in connection memory

Visibility of Definition
• GTT: Visible to all connections
• LTT: Visible only to the creating connection

Persistence of Definition
• GTT: Permanent (until explicitly dropped)
• LTT: Exists only until the connection ends

Read-only Database Support
• GTT: Not supported
• LTT: Supported

Schema Support
• GTT: Supported
• LTT: Supported

Indexes
• GTT: Full support
• LTT: Basic support (no expression or partial indexes)

DDL Triggers
• GTT: Fire on CREATE / DROP / ALTER
• LTT: Do not fire

DML Triggers
• GTT: Supported
• LTT: Not supported

Constraints (PK, FK, CHECK)
• GTT: Supported
• LTT: Not supported

Explicit Privileges
• GTT: Supported
• LTT: Not supported

## Syntax

### CREATE LOCAL TEMPORARY TABLE

```sql
{CREATE | RECREATE} LOCAL TEMPORARY TABLE [IF NOT EXISTS] [<schema
name>.]<table name>
(<column definitions>)
[ON COMMIT {DELETE | PRESERVE} ROWS]
```

The `ON COMMIT` clause determines the data lifecycle:

- `ON COMMIT DELETE ROWS` (default): Data is private to each transaction
and deleted when the transaction ends.
- `ON COMMIT PRESERVE ROWS`: Data persists until the connection ends.

Example:

```sql
-- Create a simple LTT with default ON COMMIT DELETE ROWS
create local temporary table temp_results (
id integer not null,
val varchar(100)
);

-- Create an LTT that preserves data across transactions
create local temporary table session_cache (
key varchar(50) not null,
data blob
) on commit preserve rows;

-- Create an LTT in a specific schema
create local temporary table my_schema.work_table (
x integer,
y integer
);
```

### ALTER TABLE

Local Temporary Tables support the following `ALTER TABLE` operations:

```sql
-- Add a column
ALTER TABLE <ltt name> ADD <column name> <data type> [NOT NULL];

-- Drop a column
ALTER TABLE <ltt name> DROP <column name>;

-- Rename a column
ALTER TABLE <ltt name> ALTER COLUMN <old name> TO <new name>;

-- Change column position
ALTER TABLE <ltt name> ALTER COLUMN <column name> POSITION <new position>;

-- Change nullability
ALTER TABLE <ltt name> ALTER COLUMN <column name> {DROP | SET} NOT NULL;

-- Change column type
ALTER TABLE <ltt name> ALTER COLUMN <column name> TYPE <new data type>;
```

Example:

```sql
create local temporary table temp_data (id integer);

alter table temp_data add name varchar(50) not null;
alter table temp_data alter column name position 1;
alter table temp_data alter column name to full_name;
alter table temp_data alter column id type bigint;
```

### DROP TABLE

```sql
DROP TABLE [IF EXISTS] [<schema name>.]<ltt name>
```

The same `DROP TABLE` statement is used for both regular tables and
Local Temporary Tables. If the table name matches
an LTT in the current connection, the LTT is dropped.

### CREATE INDEX

```sql
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX [IF NOT EXISTS]
[<schema name>.]<index name>
ON [<schema name>.]<ltt name> (<column list>)
```

Example:

```sql
create local temporary table temp_orders (
order_id integer not null,
customer_id integer,
order_date date
);

create unique index idx_temp_orders_pk on temp_orders (order_id);
create descending index idx_temp_orders_date on temp_orders (order_date);
create index idx_temp_orders_cust on temp_orders (customer_id);
```

### ALTER INDEX

```sql
ALTER INDEX <index name> {ACTIVE | INACTIVE}
```

Indexes on LTTs can be deactivated and reactivated, which may be useful
when performing bulk inserts in
`ON COMMIT PRESERVE ROWS` tables.

### DROP INDEX

```sql
DROP INDEX [IF EXISTS] <index name>
```

## Limitations

Local Temporary Tables have the following restrictions:

### Column restrictions

- **DEFAULT values**: Columns cannot have default values.
- **COMPUTED BY columns**: Computed columns are not supported.
- **IDENTITY columns**: Identity (auto-increment) columns are not supported.
- **ARRAY types**: Array type columns are not supported.
- **Domain changes**: Columns can be defined using domains, but changes
to the domain definition are not propagated to
existing LTT columns. The column retains the domain's characteristics
as they were at the time the column was created.

### Constraint restrictions

- **PRIMARY KEY**: Primary key constraints are not supported.
- **FOREIGN KEY**: Foreign key constraints are not supported.
- **CHECK constraints**: Check constraints are not supported.
- **NOT NULL constraints**: Only unnamed NOT NULL constraints are supported.

### Index restrictions

- **Expression-based indexes**: Indexes based on expressions are not
supported.
- **Partial indexes**: Partial (filtered) indexes are not supported.

### Other restrictions

- **EXTERNAL FILE**: LTTs cannot be linked to external files.
- **SQL SECURITY clause**: The SQL SECURITY clause is not applicable to
LTTs.
- **Persistent metadata references**: LTTs cannot be directly referenced
in stored procedures, triggers, views, or
other persistent database objects. Attempting to do so will raise an
error. However, LTTs can be accessed via
`EXECUTE STATEMENT` inside persistent objects, since the SQL text is
parsed at runtime.

## Data Lifecycle

### ON COMMIT DELETE ROWS

When a Local Temporary Table is created with `ON COMMIT DELETE ROWS`
(the default), data is private to each transaction
and automatically deleted when the transaction ends, whether by commit
or rollback. Since Firebird supports multiple
concurrent transactions within the same connection, each transaction has
its own isolated view of the data.

```sql
create local temporary table temp_work (id integer);

insert into temp_work values (1);
insert into temp_work values (2);
select count(*) from temp_work; -- Returns 2

commit;

select count(*) from temp_work; -- Returns 0 (data was deleted)
```

### ON COMMIT PRESERVE ROWS

When created with `ON COMMIT PRESERVE ROWS`, data persists across
transaction boundaries and remains available until
the connection ends.

```sql
create local temporary table session_data (id integer) on commit
preserve rows;

insert into session_data values (1);
commit;

insert into session_data values (2);
commit;

select count(*) from session_data; -- Returns 2 (data preserved across
commits)
```

### COMMIT/ROLLBACK RETAINING

Similar to Global Temporary Tables, `COMMIT RETAINING` and `ROLLBACK
RETAINING` preserve the data in LTTs with
`ON COMMIT DELETE ROWS`.

## Transactional DDL

DDL operations on Local Temporary Tables behave the same as DDL on
persistent tables with respect to transactions.
Changes to the table structure (CREATE, ALTER, DROP) are only made
permanent when the transaction commits. If the
transaction is rolled back, any LTT structural changes made within that
transaction are undone.

Savepoints are also supported. If a savepoint is rolled back, any LTT
changes made after that savepoint are undone.

```sql
set autoddl off; -- ISQL feature

create local temporary table t1 (id integer);

savepoint sp1;

alter table t1 add name varchar(50);

rollback to savepoint sp1;

-- The ALTER TABLE is undone; column 'name' does not exist
```

This applies to all DDL operations on LTTs.

## Schema Integration

Local Temporary Tables are schema-bound objects, just like regular
tables. They follow the same schema resolution
rules:

- When creating an LTT without a schema qualifier, it is created in the
current schema (the first valid schema in the
search path).
- When referencing an LTT without a schema qualifier, the search path is
used to resolve the name.
- Index names for LTTs must be unique within the schema and cannot
conflict with persistent index names.

```sql
set search_path to my_schema;

-- Creates LTT in my_schema
create local temporary table temp_data (id integer);

-- References my_schema.temp_data
select * from temp_data;

-- Explicit schema qualification
create local temporary table other_schema.temp_work (x integer);
```

## Implementation Notes

Local Temporary Tables store their data and indexes in temporary files,
similar to Global Temporary Tables. Each
connection has its own temporary file space for LTT data.

When a connection ends (either normally or due to an error), all Local
Temporary Tables created by that connection
are automatically discarded along with their data. No explicit cleanup
is required.


Adriano

[Denis Simonov]

I have several questions that weren't addressed in the proposed RFC:

1. Is it possible to view which local temporary tables were created in a session, along with descriptions of their fields and indexes? Will new virtual tables be introduced for this purpose?
2. How will statistics on local temporary tables be accumulated in monitoring tables, given that their names within schemas aren't unique? Or is the only way to find out what happened is through tracing?

вторник, 6 января 2026 г. в 14:59:18 UTC+3, Adriano dos Santos Fernandes:

[Adriano dos Santos Fernandes]

On 1/7/26 04:40, Denis Simonov wrote:
> I have several questions that weren't addressed in the proposed RFC:
>
> 1. Is it possible to view which local temporary tables were created in a
> session, along with descriptions of their fields and indexes? Will new
> virtual tables be introduced for this purpose?

I believe a table which can list at least names from LTTs of others
connections would be good.

But I'm not sure if everything is needed. Why would one need to know the
field names of LTTs in others connections if their data cannot be queries?

> 2. How will statistics on local temporary tables be accumulated in
> monitoring tables, given that their names within schemas aren't unique?
> Or is the only way to find out what happened is through tracing?
>

It's expected they go to MON$TABLE_STATS, but not yet sure how to
resolve that problem of non-unique name.


Adriano

[Denis Simonov]

среда, 7 января 2026 г. в 13:55:02 UTC+3, Adriano dos Santos Fernandes:

On 1/7/26 04:40, Denis Simonov wrote:
> I have several questions that weren't addressed in the proposed RFC:
>
> 1. Is it possible to view which local temporary tables were created in a
> session, along with descriptions of their fields and indexes? Will new
> virtual tables be introduced for this purpose?

I believe a table which can list at least names from LTTs of others
connections would be good.

But I'm not sure if everything is needed. Why would one need to know the
field names of LTTs in others connections if their data cannot be queries?

I'm not talking about other users. I'm talking about my connection. Let's say I have an iSQL session open and I've been working with it for two hours. 

Then I decide to look at what LTT have been created and what fields they contain. Theoretically, all of this could be stored at the client level. 

But on the other hand, if we have some kind of SQL query builder, it needs to get metadata from somewhere to be able to build queries correctly.

[Mark Rotteveel]

On 07/01/2026 11:54, Adriano dos Santos Fernandes wrote:
> On 1/7/26 04:40, Denis Simonov wrote:
>> I have several questions that weren't addressed in the proposed RFC:
>>
>> 1. Is it possible to view which local temporary tables were created in a
>> session, along with descriptions of their fields and indexes? Will new
>> virtual tables be introduced for this purpose?
>
> I believe a table which can list at least names from LTTs of others
> connections would be good.
>
> But I'm not sure if everything is needed. Why would one need to know the
> field names of LTTs in others connections if their data cannot be queries?

Formally, they need to be listed in the DEFINITION_SCHEMA.TABLES base
table and the INFORMATION_SCHEMA.TABLES view (and only visible for the
current session, AFAIK) with TABLE_TYPE = 'LOCAL TEMPORARY', but as we
haven't got either, that is a bit of a moot point.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 06/01/2026 12:59, Adriano dos Santos Fernandes wrote:
> Constraints (PK, FK, CHECK)
> • GTT: Supported
> • LTT: Not supported

What is the reason for lack of support for constraints.

None of the rules in (9075-2:2023) 11.6 <table constraint definition>,
11.7 <unique constraint definition>, 11.8 <referential constraint
definition>, and 11.9 <check constraint definition> say they should not
be supported. So, formally, they should be.

The only listed limitations are:

11.8 <referential constraint definition>, syntax rule 6(c):

"""
c) If the referencing table is a created local temporary table, then the
referenced table shall be either a global temporary table or a created
local temporary table.
"""

(So, a local temporary table is only allowed to reference global or
other local temporary tables).

11.9 <check constraint definition>, syntax rule 3(c)

"""
c) If T is a created local temporary table, then no <table reference>
generally contained in the <search condition> shall reference a table
other than either a global temporary table or a created local temporary
table.
"""

(Basically the same as previous, the check constraint may only reference
global or other local temporary tables).

--
Mark Rotteveel

[Adriano dos Santos Fernandes]

On 1/7/26 09:23, 'Mark Rotteveel' via firebird-devel wrote:
> On 06/01/2026 12:59, Adriano dos Santos Fernandes wrote:
>> Constraints (PK, FK, CHECK)
>> • GTT: Supported
>> • LTT: Not supported
>
> What is the reason for lack of support for constraints.
>

Implementation limit.

I'd want to avoid this complexity at least in initial implementation.

In fact, IMO the missing things is more about completeness (and
complexity) than usefulness for LTT. I would later go to "declared LTT"
than go to this.


Adriano

[Mark Rotteveel]

On 08/01/2026 01:40, Adriano dos Santos Fernandes wrote:
> On 1/7/26 09:23, 'Mark Rotteveel' via firebird-devel wrote:
>> On 06/01/2026 12:59, Adriano dos Santos Fernandes wrote:
>>> Constraints (PK, FK, CHECK)
>>> • GTT: Supported
>>> • LTT: Not supported
>>
>> What is the reason for lack of support for constraints.
>>
>
> Implementation limit.
>
> I'd want to avoid this complexity at least in initial implementation.

OK good to know, thanks.

> In fact, IMO the missing things is more about completeness (and
> complexity) than usefulness for LTT. I would later go to "declared LTT"
> than go to this.

Do you mean you think "declared LTT" are more or less important?
(Because you say to inverse of "I would sooner go", but I think you mean
sooner, not later.)

As I understand "declared LTT", that would be something like a table
declaration in a stored procedure or execute block, or something like
that, right?

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

On 08/01/2026 01:40, Adriano dos Santos Fernandes wrote:
> On 1/7/26 09:23, 'Mark Rotteveel' via firebird-devel wrote:
>> On 06/01/2026 12:59, Adriano dos Santos Fernandes wrote:
>>> Constraints (PK, FK, CHECK)
>>> • GTT: Supported
>>> • LTT: Not supported
>>
>> What is the reason for lack of support for constraints.
>>
>
> Implementation limit.
>
> I'd want to avoid this complexity at least in initial implementation.

OK good to know, thanks.

> In fact, IMO the missing things is more about completeness (and
> complexity) than usefulness for LTT. I would later go to "declared LTT"
> than go to this.
Do you mean you think "declared LTT" are more or less important?
(Because you say to inverse of "I would sooner go", but I think you mean
sooner, not later.)

Yes, i meant later as after created.

As I understand "declared LTT", that would be something like a table
declaration in a stored procedure or execute block, or something like
that, right?

Yes.

Adriano

[Adriano dos Santos Fernandes]

Internally, LTTs uses IDs like any other relations.

But IDs are private per attachments. LM locks are not used.

My idea is to not coordinate IDs of LTTs between different attachments
nor with IDs of persistent relations.

Currently we have rel_MAX (last system table) = 57 and user relations
starts with 128.

So it's very safe to use 80-127 as IDs for LTTs, so 48 active LTTs per
attachment without a good chance to shrink it in the future.

Opinions?


Adriano

[Dmitry Yemanov]

13.01.2026 14:03, Adriano dos Santos Fernandes wrote:
>
> But IDs are private per attachments. LM locks are not used.
>
> My idea is to not coordinate IDs of LTTs between different attachments
> nor with IDs of persistent relations.
>
> Currently we have rel_MAX (last system table) = 57 and user relations
> starts with 128.
>> So it's very safe to use 80-127 as IDs for LTTs, so 48 active LTTs per
> attachment without a good chance to shrink it in the future.
>
> Opinions?

In RDB we have rel_MAX > 70 already and system views need some reserve too.

If these LTT IDs are purely internal, did you consider using negative
numbers?


Dmitry

[Adriano dos Santos Fernandes]

rel_id is USHORT and MAX_RELATION_ID is 32767.

So looks better to use greater numbers (a range to 65535), isn't it?


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 13.01.2026 12:34:
> rel_id is USHORT and MAX_RELATION_ID is 32767.
>
> So looks better to use greater numbers (a range to 65535), isn't it?

Why not to expand the internal relation id to integer?
Perhaps, Alex already did it in his metadata cache branch.

--
WBR, SD.

[Vlad Khorsun]

13.01.2026 13:03, Adriano dos Santos Fernandes:

Just a raw idea, not sure it is the best, but try to consider it:
use separate BLR verb for LTT references (blr_rid -> blr_ltt_id) and don't
put LTT's into same container as other relations (att_relations).

Regards,
Vlad

[Adriano dos Santos Fernandes]

I thought on it initially, but didn't see advantages.


Adriano

[Alex Peshkoff]

No I did not, but tried to prepare code for it. All over the code (well,
at least at places which I noticed)

    typedef USHORT MetaId;

is used. Appears it's not too hard to expand that limit when needed.

But in current case use of rel_id between 32768 and 65535 appears enough.

[Alex Peshkoff]

Pay attention that we will anyway need to store LTTs in separate
container. They are per-attachment but regular relations go to DBB.

[Dimitry Sibiryakov]

Alex Peshkoff wrote 13.01.2026 14:17:
> But in current case use of rel_id between 32768 and 65535 appears enough.

Databases with enormous number of tables and views do exist. The last thing
Firebird needs is an another source of mysterious behavior in support list.

--
WBR, SD.

[Alex Peshkoff]

If we agree with this - separate BLR appears the best choice.

[Dimitry Sibiryakov]

Alex Peshkoff wrote 13.01.2026 14:45:
>>   Databases with enormous number of tables and views do exist. The last thing
>> Firebird needs is an another source of mysterious behavior in support list.
>>
>
> If we agree with this - separate BLR appears the best choice.

IMHO, the best choice would be a new set of system tables (in SYSTEM schema)
with expanded ids and RDB$% views (in PUBLIC schema) for backward compatibility
that don't show LTTs and tables from other schemas.
Also I see no need in a new BLR: already existing blr_relation1-3 should be
enough. In this case LTT-specific changes will be localized in
MET_lookup_relation().

--
WBR, SD.

[Jim Starkey]

Maybe it's already in place, but a "table type" field in the system table would avoid slicing and dicing table ids, would be backwards and forwards compatible, and would be easier to handle on the client side, and give a great deal more flexibility.

The fundamental principle here is that in design, falling in love with your first idea is almost always a mistake.

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/cc9ecd82-6963-4390-8ccf-58d7596da319%40gmail.com.

--
Jim Starkey