Declared Local Temporary Tables

[Adriano dos Santos Fernandes]

Hi!

In SQL standard there is Created LTTs and Declared LTTs.

We have Created LTT already implemented.

I would like to check others understanding of Declared LTTs.

Standard modules is IMO very different from Firebird (and others DBMS)
stored routines.

So my understand in regard to Declared LTT in the adaptation for
Firebird would be:

- A same-name declared LTT can be created in different routines and will
be completely different LTTs

- The declared LTT is bound to the routine statement. For example, a
declared LTT ON-COMMIT-PRESERVE-ROWS can be invoked recursively and
still see the data of the previously invoked routine. Not only that,
when the first called routine exits and is re-invoked, it still see the
old data

- The same point above, when used with DSQL EXECUTE BLOCK will mean that
a prepared statement re-executed will still see data of previous
execution. This should be worked around with statements cache, as the
fact of another prepared statement get one from cache or not should not
change behavior


Adriano

[Adriano dos Santos Fernandes]

- As Declared LTT is bound to statements, ALTERing a routine
automatically makes the new routine instance to start with empty LTTs
instances.


Adriano

[Denis Simonov]

>  - A same-name declared LTT can be created in different routines and will
> be completely different LTTs

Yes

> - The declared LTT is bound to the routine statement. For example, a
> declared LTT ON-COMMIT-PRESERVE-ROWS can be invoked recursively and still see the data of the previously invoked routine. 

> Not only that, when the first called routine exits and is re-invoked, it still see the old data

I'd prefer data visibility to be limited to the current statement level. However, even if these are transaction/connection-level temporary tables, 

I know how to solve this problem. Temporary generators could certainly help, but a GET/SET CONTEXT pair will suffice.

> - The same point above, when used with DSQL EXECUTE BLOCK will mean that a prepared statement re-executed will still see data of previous execution. 

> This should be worked around with statements cache, as the fact of another prepared statement get one from cache or not should not change behavior

There are doubts here, given that the cache of compiled statements can be reset by any DDL.

понедельник, 23 февраля 2026 г. в 14:41:01 UTC+3, Adriano dos Santos Fernandes:

[Dmitry Yemanov]

23.02.2026 14:20, Adriano dos Santos Fernandes wrote:
>
> - A same-name declared LTT can be created in different routines and will
> be completely different LTTs

Yep.

> - The declared LTT is bound to the routine statement.

Routine statement and attachment, I believe -- so that concurrently
executed request clones have unique temporary datasets.

> For example, a
> declared LTT ON-COMMIT-PRESERVE-ROWS can be invoked recursively and
> still see the data of the previously invoked routine. Not only that,
> when the first called routine exits and is re-invoked, it still see the
> old data

Hmmmm. I supposed the most common use case is to start with the empty
DLTTs and fill them during execution. A requirement to truncate just
declared DLTTs (in this use case) looks weird to me.

We start every request with a closed declared cursors and
initialized/nullified declared variables, why declared temporary tables
should behave differently and inherit its data from the prior invocations?

From the other side, if every request resets its DLTTs, then I see no
point in the ON COMMIT clause at all -- but it exists in the SQL spec.
The standard seems to treat GTTs / CLTTs and DLTTs the same way from the
dataset lifetime perspective, only declaration is different. For DLTTs,
however, this is not the practice I've seen in other databases.


Dmitry

[Vlad Khorsun]

23.02.2026 17:38, Dmitry Yemanov:

> 23.02.2026 14:20, Adriano dos Santos Fernandes wrote:
>>
>> - A same-name declared LTT can be created in different routines and will
>> be completely different LTTs
>
> Yep.
>
>> - The declared LTT is bound to the routine statement.
>
> Routine statement and attachment, I believe -- so that concurrently executed request clones have unique temporary datasets.
>
>> For example, a
>> declared LTT ON-COMMIT-PRESERVE-ROWS can be invoked recursively and
>> still see the data of the previously invoked routine. Not only that,
>> when the first called routine exits and is re-invoked, it still see the
>> old data
>
> Hmmmm. I supposed the most common use case is to start with the empty DLTTs and fill them during execution. A requirement to
> truncate just declared DLTTs (in this use case) looks weird to me.

Fully agree.

> We start every request with a closed declared cursors and initialized/nullified declared variables, why declared temporary tables
> should behave differently and inherit its data from the prior invocations?
>
> From the other side, if every request resets its DLTTs, then I see no point in the ON COMMIT clause at all -- but it exists in the
> SQL spec. The standard seems to treat GTTs / CLTTs and DLTTs the same way from the dataset lifetime perspective, only declaration is
> different. For DLTTs, however, this is not the practice I've seen in other databases.

What if declared LTT's dataset lifetime will be bound to the object where it is declared:

- if DLTT is declared at PSQL routine, it will be like declared variable
and on end of routine execution DLTT's data will be released. No need to
use ON COMMIT PRESERVE|DELETE ROWS clause in this case. No special handling
for cached statements needed;

- if DLTT is declared at package level, it will work as Adriano wrote above
and in this case clause ON COMMIT have clear application.

Regards,
Vlad

[Dimitry Sibiryakov]

Vlad Khorsun wrote 23.02.2026 17:23:
>
> - if DLTT is declared at PSQL routine, it will be like declared variable
>   and on end of routine execution DLTT's data will be released. No need to
>   use ON COMMIT PRESERVE|DELETE ROWS clause in this case. No special handling
>   for cached statements needed;

It sounds rather like standard collection type than local table...

--
WBR, SD.

[Adriano dos Santos Fernandes]

Hi!

I understand the divergences and even agree with them.

I know no other DBMS that implements DLTT as the standard, and I believe because they are very related to SQL-client modules, which no DBMS implements AFAIK.

Here is DLTT description:

An SQL-client module declared local temporary table is a declared local temporary table defined in an
<SQL-client module definition> or in a <direct SQL statement>. (Note that the <direct SQL statement>s
executed by an SQL-session that is executing direct SQL effectively comprise a distinct SQL-client module.)
An SQL-client module declared local temporary table is effectively materialized the first time any
<externally-invoked procedure> in the <SQL-client module definition> that contains the <temporary
table declaration> is executed, or when the <temporary table declaration> is executed as a <direct SQL
statement>, and it persists for that SQL-session. Every SQL-client module in every SQL-session that ref-
erences an SQL-client module declared local temporary table causes a distinct instance of that declared
local temporary table (i.e., a multiset of rows that is visible only to that SQL-client module during that
SQL-session) to be materialized. That is, the multiset of rows that is referenced by the <table name> of
an SQL-client module declared local temporary table cannot be shared between SQL-sessions, nor between
SQL-client modules that execute during an SQL-session.

And

<SQL-client module definition> ::=
<module name clause> <language clause> <module authorization clause>
[ <module path specification> ]
[ <module transform group specification> ]
[ <module collations> ]
[ <temporary table declaration

...

<module contents> ::=
<declare cursor>
| <dynamic declare cursor>
| <externally-invoked procedure>

...

<externally-invoked procedure> ::=
PROCEDURE <procedure name> <host parameter declaration list> <semicolon>
<SQL procedure statement> <semicolon>

From this POV, SQL-client module may be even a kind of "package for external routines" made in other languages.

But from this POV, DLTT would be a package feature.

So in the end what Vlad said would be the right path.

It may be even considered as two different and independent features: packaged tables and local tables.

And it's funny that packaged tables are more like "scoped GTT" than "created LTT". They should exist in the "catalog" as routines have its metadata separated from the packages.

Adriano

[Dmitry Yemanov]

24.02.2026 04:59, Adriano dos Santos Fernandes wrote:
>
> So in the end what Vlad said would be the right path.

I tend to agree.


Dmitry