Created Local Temporary Tables

[Adriano dos Santos Fernandes]

Hi!

I started to design/implement Created LTTs.

These are tables that lives only inside an attachment, without any
metadata stored. Like GTTs, they have data bound to either the
connection or the transaction.

In PSQL, they cannot be used directly, but can with EXECUTE STATEMENT.

In the initial scope, it should have support for indexes. But PKs, FKs
and identity columns are not priorities.

I see there are two paths in relation to IDs and locks.

1. Created LTTs use same relation ID namespace. Create some IPC-based
way so multiple attachments don't reuse relation IDs (Created LTTs and
persistent relations). No need to tweak code that locks relations.

2. Store Created LTTs outside of rel_relations. Created LTTs should not
use the locks as persistent ones.

Do you have some advice here of what would be better?


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 26.05.2025 13:17:
> In the initial scope, it should have support for indexes. But PKs, FKs
> and identity columns are not priorities.
>
> I see there are two paths in relation to IDs and locks.
>
> 1. Created LTTs use same relation ID namespace. Create some IPC-based
> way so multiple attachments don't reuse relation IDs (Created LTTs and
> persistent relations). No need to tweak code that locks relations.
>
> 2. Store Created LTTs outside of rel_relations. Created LTTs should not
> use the locks as persistent ones.
>
> Do you have some advice here of what would be better?

There already are cached record sets. I see no reason why LTT cannot simply
use them.
And because they live only inside of attachment, simple `att_ltt_map` in the
Attachment is enough to find the data by name.
No need for locks and IPC at all.

--
WBR, SD.

[Mark Rotteveel]

On 26/05/2025 13:17, Adriano dos Santos Fernandes wrote:
> I started to design/implement Created LTTs.
>
> These are tables that lives only inside an attachment, without any
> metadata stored. Like GTTs, they have data bound to either the
> connection or the transaction.
>
> In PSQL, they cannot be used directly, but can with EXECUTE STATEMENT.

I know that for PSQL using a GTT would be the better option, but the
only times I've used LTTs in other DBMSes (that I can recall) was in
procedural code. Why this restriction?

Mark
--
Mark Rotteveel

--
Mark Rotteveel

[Dmitry Yemanov]

26.05.2025 14:17, Adriano dos Santos Fernandes wrote:
>
> I started to design/implement Created LTTs.
>
> These are tables that lives only inside an attachment, without any
> metadata stored. Like GTTs, they have data bound to either the
> connection or the transaction.
>
> In PSQL, they cannot be used directly, but can with EXECUTE STATEMENT.

AFAIU, this is mostly requested for PSQL. So I'd instead suggest
*declared* LTTs - with lifetime bounded to the containing PSQL routine.


Dmitry

[Dimitry Sibiryakov]

Dmitry Yemanov wrote 26.05.2025 13:37:
>> In PSQL, they cannot be used directly, but can with EXECUTE STATEMENT.
>
> AFAIU, this is mostly requested for PSQL. So I'd instead suggest *declared* LTTs
> - with lifetime bounded to the containing PSQL routine.

All this will be much easier if BLR stage is removed. Then DDL nodes can be
used freely from PSQL code.

--
WBR, SD.

[Dimitry Sibiryakov]

'Dimitry Sibiryakov' via firebird-devel wrote 26.05.2025 13:42:
>
>   All this will be much easier if BLR stage is removed. Then DDL nodes can be
> used freely from PSQL code.

(at least in EB code, otherwise stored BLR should be removed as well.)

--
WBR, SD.

[Adriano dos Santos Fernandes]

We have discussed declared LTTs already, and when I revisited it I
believe created LTTs would be better to be implemented first. Declared
LTTs are not directly applicable to Firebird as defined in the standard.

When we have GTTs only, people want something different and declared
LTTs would be more like what they prefer over GTTs.

It does not means created LTTs are bad, it's just different. MySQL
people are happy with them and uses a lot with ad-hoc (say, DSQL)
queries. PSQL (say, code created inside a database) is not unanimity.


Adriano

[Adriano dos Santos Fernandes]

On 26/05/2025 08:29, 'Mark Rotteveel' via firebird-devel wrote:
> On 26/05/2025 13:17, Adriano dos Santos Fernandes wrote:
>> I started to design/implement Created LTTs.
>>
>> These are tables that lives only inside an attachment, without any
>> metadata stored. Like GTTs, they have data bound to either the
>> connection or the transaction.
>>
>> In PSQL, they cannot be used directly, but can with EXECUTE STATEMENT.
>
> I know that for PSQL using a GTT would be the better option,

Better for who? When?

I guess it's not better in an environment where developers need to ask,
wait and justify DBAs to update database, when developer just want to
make his work. I hope you understand this is not uncommon.

> but the
> only times I've used LTTs in other DBMSes (that I can recall) was in
> procedural code.


> Why this restriction?
>

We can allow it in EXECUTE BLOCK. It just doesn't fit with persistent
routines, which should be valid in the creation moment and should
prevent dependencies to make them invalid.


Adriano

[Mark Rotteveel]

On 27/05/2025 12:39, Adriano dos Santos Fernandes wrote:
> On 26/05/2025 08:29, 'Mark Rotteveel' via firebird-devel wrote:
>> On 26/05/2025 13:17, Adriano dos Santos Fernandes wrote:
>>> I started to design/implement Created LTTs.
>>>
>>> These are tables that lives only inside an attachment, without any
>>> metadata stored. Like GTTs, they have data bound to either the
>>> connection or the transaction.
>>>
>>> In PSQL, they cannot be used directly, but can with EXECUTE STATEMENT.
>>
>> I know that for PSQL using a GTT would be the better option,
>
> Better for who? When?
>
> I guess it's not better in an environment where developers need to ask,
> wait and justify DBAs to update database, when developer just want to
> make his work. I hope you understand this is not uncommon.

If you can create procedures, I assume you can also create GTTs. But OK,
I didn't consider execute block in this.

>> but the
>> only times I've used LTTs in other DBMSes (that I can recall) was in
>> procedural code.
>
>
>> Why this restriction?
>>
>
> We can allow it in EXECUTE BLOCK. It just doesn't fit with persistent
> routines, which should be valid in the creation moment and should
> prevent dependencies to make them invalid.

Why not? The LTT would be created during execution of the routine, and
be gone when execution ends. There is no (external) dependency, as the
declaration and usage is within that routine.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 27.05.2025 13:11:
>
> Why not? The LTT would be created during execution of the routine, and be gone
> when execution ends. There is no (external) dependency, as the declaration and
> usage is within that routine.

But expected lifetime for LTT is till the end of attachment, no?

--
WBR, SD.

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 27.05.2025 12:39:
> We can allow it in EXECUTE BLOCK. It just doesn't fit with persistent
> routines, which should be valid in the creation moment and should
> prevent dependencies to make them invalid.

If LTT is referred by name instead of ID what's wrong with a reference to a
missing routine? SP just throw error "Table not found" and that's all.

--
WBR, SD.