Re: [Firebird-devel] Local tables
On 16/03/2022 00:45, Dmitry Yemanov wrote: > 15.03.2022 21:43, Adriano dos Santos Fernandes wrote: >> >>> In fact, what you priorly define as LT is IMO "declared" LTT. >> >> I had that impression before read the standard, but then I changed my >> opinion. > > "Part 4: Persistent Stored Modules (SQL/PSM)" is about PSQL, AFAIU. > It includes: "12.8 " that refers to "Part > 2: Foundation (SQL/Foundation)" which defines: > > ::= > DECLARE LOCAL TEMPORARY TABLE > [ ON COMMIT ROWS ] > > Given that Part 4 also defines DECLARE CURSOR which is also redirected > to "Part 2: Foundation (SQL/Foundation)" I understand it as declared > LTTs are allowed in PSQL. > I have part 4 only of SQL 2011. LTTs can be declared only in SQL-server modules, in our case, packages. They cannot be declared inside routines. Sure we may extrapolate, if we do things that we believe is not going to be incompatible with future standards. But standard LTTs seems to be very weird in an aspect: it works per requests. Here is some quotes. 5.b does not copy LTTs identities. 5.g.i should be PSQL routines. That means a package with a LTT cannot store data on a LTT and call another routine of the same package that reads the data. It is much more useful if data is shared in this case. - 5) Preserve the current SQL-session context CSC and create a new SQL-session context RSC derived from CSC as follows: b) The values of the current SQL-session identifier, the SQL-session user identifier, the identities of all instances of global temporary tables, the cursor instance descriptor of all open cursors accessible in the SQL-session, the current constraint mode for each integrity constraint, the current transaction access mode, the current transaction isolation level, the current condition area limit, the subject table restriction flag, and the restricted subject table name list are set to their values in CSC. g) Case: i) If R is an SQL routine, then the identities of all instances of created local temporary tables, declared local temporary tables that are defined by s that are contained in s, the cursor instance descriptors of all open cursors that are not global extended dynamic cursors, prepared statements that do not have global extended names, and SQL descriptor areas that do not have global extended names are removed from RSC. ii) Otherwise: 1) Remove from RSC the identities of all instances of created local temporary tables that are referenced in s that are not the of P, declared local temporary tables that are defined by s that are contained in s that are not the of P, and the cursor instance descriptors of all open cursors that are not global extended dynamic cursors and whose SQL-client module is not the SQL- client module of P. 2) It is implementation-defined whether the identities of all instances of created local temporary tables that are referenced in the of P, declared local tem- porary tables that are defined by s that are contained in the of P, the cursor instance descriptors of all open cursors that are not global extended dynamic cursors and whose SQL-client module is the SQL- client module of P, prepared statements that do not have global extended names, and SQL descriptor areas that do not have global extended names are removed from RSC. - Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:14, Adriano dos Santos Fernandes wrote: On 15/03/2022 14:02, Vlad Khorsun wrote: SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be "created" or "declared". The semantics of these standard specs are different and not very easy applicable to Firebird as they are based on standard notion of modules, that is not similar to PSQL routines. Could you specify, in short, what part of standard is hard to apply to the Firebird ? Declared LTTs are defined in "SQL-client module" SQL-client module seems to have a relation to packages. They can group procedures. So as I said, declared LTTs feat well in packages. The standard does not define LTTs inside routines. Yes. But we can think of "standalone" PSQL routine as of member of implicit package, if needed. If they did, they could have defined it with different semantics than what I propose, for example, it could use the routine only as a scope, but with shared data between invocations. So my reluctance in define LTT inside routines. It could be discussed and I see no big problem implementing both scope's. I.e. data of DECLARE'd LTT could shared or not for recursive invocations of PSQL routine with declaration. Also, we should define access rules for sub-routines. Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON COMMIT PRESERVE/DELETE ROWS ]. "ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL routine, Does it means ON COMMIT would be prohibited there inside routines? Prohibited or ignored, to be decided. while could be useful for packaged LTT's. But LT (non-transactional) is also useful in packages. It then means in package not using ON COMMIT would mean a different thing than absence of ON COMMIT in GTT. Not something we would want. It is also should be discussed properly. BTW, why do you want LT to be non-transactional ? Because of implementation difficulties or by another reason ? For me, transactional LT[T] is a must have feature, while non-transactional could be an extension. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:50, Adriano dos Santos Fernandes write: On 15/03/2022 15:20, Vlad Khorsun wrote: So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? At first look I see two way's to go: a) PSQL routines can't see CREATE'd LTT's, or b) PSQL routines can see and use LTT definition that exists at the moment of routine definition\loading into metadata cache. Such LTT's definition should be not changed while dependend PSQL routine reside in metadata cache, or PSQL routine should be invalidated when LTT definition it depends on is changed. Of course, there could be something else, lets continue to think on it. I think (a) is the best option. (b) seems not friendly for Firebird and for this usage GTT seems the way to go. At current state of things I tend to agree with you. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:41, Dmitry Yemanov wrote: Anyway, IMO "declared" LTTs are much more useful than "created" LTTs, so the latter ones may be deferred. Or we may live without them at all ;-) CREATE'd LTT's could be very useful to store\materialize intermediate results of complex evaluations. Think about it as of temp variables used in complex expressions. Obviously, GTT's are much less useful in such scenarios. *Relational* DBMS should be able to operate with *relation*-based expressions, including intermediate steps\results, isn't is ? ;) Yes, we have CTE's which allows to reduce complexity of many queries, but it is not an universal solution. And our optimizer is not smart enough, unfortunately to make CTE's more powerful. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 3/15/22 20:41, Dmitry Yemanov wrote: 15.03.2022 20:31, Alex Peshkoff via Firebird-devel wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Same for almost all objects declared in procedure that are more complex than INT. For example cursor definition may be rather complex and also usable in multiple procedures. True. But I don't remember CREATE CURSOR in the SQL spec ;-) Anyway, IMO "declared" LTTs are much more useful than "created" LTTs, so the latter ones may be deferred. Or we may live without them at all ;-) Taking into an account mentioned in this thread restriction: a) PSQL routines can't see CREATE'd LTT's I fully agree with you here. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 21:43, Adriano dos Santos Fernandes wrote: In fact, what you priorly define as LT is IMO "declared" LTT. I had that impression before read the standard, but then I changed my opinion. "Part 4: Persistent Stored Modules (SQL/PSM)" is about PSQL, AFAIU. It includes: "12.8 " that refers to "Part 2: Foundation (SQL/Foundation)" which defines: ::= DECLARE LOCAL TEMPORARY TABLE [ ON COMMIT ROWS ] Given that Part 4 also defines DECLARE CURSOR which is also redirected to "Part 2: Foundation (SQL/Foundation)" I understand it as declared LTTs are allowed in PSQL. I also did not found any major DBMS that implemented declared LTTs, nor SQL-standard modules. Sybase ASA, as you already mentioned, although maybe not so "major". From their docs: "Declared local temporary tables within compound statements exist within the compound statement." Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 15:20, Vlad Khorsun wrote: >> So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? > > At first look I see two way's to go: > a) PSQL routines can't see CREATE'd LTT's, or > b) PSQL routines can see and use LTT definition that exists at the > moment of routine > definition\loading into metadata cache. Such LTT's definition should > be not changed > while dependend PSQL routine reside in metadata cache, or PSQL > routine should be > invalidated when LTT definition it depends on is changed. > > Of course, there could be something else, lets continue to think on it. > I think (a) is the best option. (b) seems not friendly for Firebird and for this usage GTT seems the way to go. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 15:00, Adriano dos Santos Fernandes wrote:
>> Would it be possible to use some other word to distinguish, instead of
>> just leaving out "local" (which essentially loses the most important
>> aspect of what it really is)?
>>
>> Suggestions for thought:
>>
>> Internal?
>> Volatile?
>> Virtual?
>> Transient?
>>
>
> None of these words seems good for me to distinguish the difference.
>
I liked Sybase syntax:
http://dev.cs.ovgu.de/db/sybase9/help/dbrfen9/0356.htm
declare local temporary table (
...
) [ ON COMMIT { DELETE | PRESERVE } ROWS | NOT TRANSACTIONAL ]
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:07, Dmitry Yemanov wrote: > 15.03.2022 17:14, Adriano dos Santos Fernandes wrote: >> >> As part of "Support multiple rows for DML RETURNING (#6815)" feature, >> BLR verbs for "local table" were created. >> >> Local tables (LT) as defined there works outside transaction control. >> For #6815 this does not matter, but I want to add LT feature that will >> use these verbs. >> >> LTs are defined inside PSQL routines and its data is separated per >> routine invocation. For example a recursive routine will have different >> data in its LTs per each invocation, like local variables. > > What about autonomous transactions? Will procedure's LT data be visible > for them or every autonomous transaction gets its own copy? > Like a standard variable, data will be shared with the autonomous transaction scope. Note that if we say that as the contrary, LT would be very similar to a LTT (with transaction control). And that would not be problematic, but... I also want packaged LT in the future, with would be very useful to work outside transaction control. >> SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be >> "created" or "declared". The semantics of these standard specs are >> different and not very easy applicable to Firebird as they are based on >> standard notion of modules, that is not similar to PSQL routines. > > I'd just adapt their definitions to our realities. For me, "created" LTT > is similar to GTT (i.e. stored in the schema) but with data isolated per > request (per PSQL routine). My understanding is that it is per session / package (SQL-client module), not request / routine. > "Declared" LTTs are defined inside the PSQL routine, Inside a package (SQL-client module) with data shared in the package / session. > In fact, what you priorly define as LT is IMO "declared" LTT. > I had that impression before read the standard, but then I changed my opinion. I also did not found any major DBMS that implemented declared LTTs, nor SQL-standard modules. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 13:00, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 15.03.2022 15:37: >> On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: >>> Very interesting - but other name for them should be used. It's too easy >>> to loose difference between LT & LTT that are absolutely different >>> things. >>> >> Maybe just "DECLARE TABLE" then? > > Oracle uses term "collection". Why not to adapt their syntax? > The only similarity with LT is that data is not under transaction control. Oracle's collection variables are not integrated with SQL update commands. It's mostly a PL/SQL (not SQL) feature. LT, while defined in PSQL, works like a SQL table, except that it does not uses transaction control. Oracle's collection is integrated with standard tables. It's part of their object-relational integration. Collections only as variable feels as an incomplete feature for me. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:56, Adriano dos Santos Fernandes wrote: On 15/03/2022 14:39, Vlad Khorsun wrote: 15.03.2022 19:27, Dmitry Yemanov wrote: 15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). First, CREATE'd LTT's have the visibility scope and lifetime of the attachment. Its definition is not seen by other attachments and every attachment could have LTT with the same name and different definition. Second, attachment could have private part (instance) of metadata cache that will contains definitions of every CREATE'd LTT and every statement could use it without additional penalty. No need to pollute persistent schema with temporary objects and pay runtime cost for storing\erasing such definitions. So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? At first look I see two way's to go: a) PSQL routines can't see CREATE'd LTT's, or b) PSQL routines can see and use LTT definition that exists at the moment of routine definition\loading into metadata cache. Such LTT's definition should be not changed while dependend PSQL routine reside in metadata cache, or PSQL routine should be invalidated when LTT definition it depends on is changed. Of course, there could be something else, lets continue to think on it. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:02, Vlad Khorsun wrote:
>
>> SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be
>> "created" or "declared". The semantics of these standard specs are
>> different and not very easy applicable to Firebird as they are based on
>> standard notion of modules, that is not similar to PSQL routines.
>
> Could you specify, in short, what part of standard is hard to apply to
> the Firebird ?
>
Declared LTTs are defined in "SQL-client module"
SQL-client module seems to have a relation to packages.
They can group procedures.
So as I said, declared LTTs feat well in packages.
The standard does not define LTTs inside routines.
If they did, they could have defined it with different semantics than
what I propose, for example, it could use the routine only as a scope,
but with shared data between invocations. So my reluctance in define LTT
inside routines.
>> Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON
>> COMMIT PRESERVE/DELETE ROWS ].
>
> "ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL
> routine,
Does it means ON COMMIT would be prohibited there inside routines?
> while could be useful for packaged LTT's.
>
But LT (non-transactional) is also useful in packages.
It then means in package not using ON COMMIT would mean a different
thing than absence of ON COMMIT in GTT. Not something we would want.
>> Declared LTTs could be done as part of packages. They would be like
>> GLOBAL TEMPORARY TABLES (GTT) but declared (created, updated) as part of
>> packages headers (public) or body (private). They would work under
>> transaction control and may be represented in metadata.
>
> Mostly agree - just not sure about metadata, it could be discussed later.
>
Sure.
>> LTs could also be added in packages. It's different feature than LTTs.
>
> What is a difference ?
>
In that scope, mostly about it not being transactional.
>> I propose syntax of LT:
>>
>> DECLARE LOCAL TABLE (
>> { }...
>> );
>
> This statement should be used as part of common DECLARE section of
> PSQL routine
Yes.
>> "DELETE FROM " would be optimized to use
>> blr_local_table_truncate.
>
> You mean - if without WHERE clause, correct ?
Sure.
> What about undo ?
> In general - how operations on LT[T] should be handled in case of PSQL
> exceptions ?
>
It will not have undo.
PSQL exceptions during multi-row changes would abort the operation
leaving already done changes.
>> Standard scope rules will be used. A LT may use the same name of a
>> schema-based table and will shadow it inside the routine.
>
> Also, packaged LT should hide "GLOBAL" name when used in routine of
> the same
> package, correct ?
>
Yes. But note that I propose packaged LTs for a later moment. We would
first need more useful and simple feature first implemented there:
packaged variables.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
Vlad Khorsun wrote 15.03.2022 18:39: No need to pollute persistent schema with temporary objects and pay runtime cost for storing\erasing such definitions. That's a good occasion for creating a virtual metadata dictionary that provides records for persistent objects as well as session-local ones. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 12:03, Kjell Rilbe wrote: > Den 2022-03-15 kl. 15:37, skrev Adriano dos Santos Fernandes: >> On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: >>> Very interesting - but other name for them should be used. It's too easy >>> to loose difference between LT & LTT that are absolutely different >>> things. >>> >> Maybe just "DECLARE TABLE" then? >> >> We would refer to them as "declared tables". >> >> In routines they will be local declared tables. >> >> In the future, when they could also be in a package, a packaged declared >> table. > > From a linguistic point of view I find "declared" to be a nonsense > qualification of "table". Aren't all tables declared in a sense? > No, some are CREATEd. > The essence of these tables seems to be that they are local to > "something", but the term "local" seems to be "taken" by the things > called local temporary tables(?). > Yes. > Would it be possible to use some other word to distinguish, instead of > just leaving out "local" (which essentially loses the most important > aspect of what it really is)? > > Suggestions for thought: > > Internal? > Volatile? > Virtual? > Transient? > None of these words seems good for me to distinguish the difference. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 14:39, Vlad Khorsun wrote: > 15.03.2022 19:27, Dmitry Yemanov wrote: >> 15.03.2022 20:17, Vlad Khorsun wrote: >>> For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). >>> >>> I'd consider about CREATE'd LTT as attachment-private object. I see >>> no need to store its definition at the persistent schema. >> >> If multiple procedures process the same layout of temporary data, IMHO >> it's handier to create such LTT definition once (as persistent) rather >> than declare the same LTT in the every procedure (or attachment). > > First, CREATE'd LTT's have the visibility scope and lifetime of the > attachment. > Its definition is not seen by other attachments and every attachment > could have > LTT with the same name and different definition. Second, attachment > could have > private part (instance) of metadata cache that will contains definitions > of every > CREATE'd LTT and every statement could use it without additional penalty. > > No need to pollute persistent schema with temporary objects and pay > runtime > cost for storing\erasing such definitions. > So CREATEd LTTs cannot be used in PSQL routines, only by DSQL? Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:31, Alex Peshkoff via Firebird-devel wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Same for almost all objects declared in procedure that are more complex than INT. For example cursor definition may be rather complex and also usable in multiple procedures. True. But I don't remember CREATE CURSOR in the SQL spec ;-) Anyway, IMO "declared" LTTs are much more useful than "created" LTTs, so the latter ones may be deferred. Or we may live without them at all ;-) Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:27, Dmitry Yemanov wrote: 15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). First, CREATE'd LTT's have the visibility scope and lifetime of the attachment. Its definition is not seen by other attachments and every attachment could have LTT with the same name and different definition. Second, attachment could have private part (instance) of metadata cache that will contains definitions of every CREATE'd LTT and every statement could use it without additional penalty. No need to pollute persistent schema with temporary objects and pay runtime cost for storing\erasing such definitions. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 3/15/22 20:27, Dmitry Yemanov wrote: 15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Same for almost all objects declared in procedure that are more complex than INT. For example cursor definition may be rather complex and also usable in multiple procedures. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 20:17, Vlad Khorsun wrote: For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. If multiple procedures process the same layout of temporary data, IMHO it's handier to create such LTT definition once (as persistent) rather than declare the same LTT in the every procedure (or attachment). Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 19:07, Dmitry Yemanov wrote: SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be "created" or "declared". The semantics of these standard specs are different and not very easy applicable to Firebird as they are based on standard notion of modules, that is not similar to PSQL routines. I'd just adapt their definitions to our realities. +1 For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). I'd consider about CREATE'd LTT as attachment-private object. I see no need to store its definition at the persistent schema. It would be good to have ability to query schema for such objects, though. "Declared" LTTs are defined inside the PSQL routine, with data isolated the same way. In fact, what you priorly define as LT is IMO "declared" LTT. +1. And don't forget about packages. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 17:14, Adriano dos Santos Fernandes wrote: As part of "Support multiple rows for DML RETURNING (#6815)" feature, BLR verbs for "local table" were created. Local tables (LT) as defined there works outside transaction control. For #6815 this does not matter, but I want to add LT feature that will use these verbs. LTs are defined inside PSQL routines and its data is separated per routine invocation. For example a recursive routine will have different data in its LTs per each invocation, like local variables. What about autonomous transactions? Will procedure's LT data be visible for them or every autonomous transaction gets its own copy? SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be "created" or "declared". The semantics of these standard specs are different and not very easy applicable to Firebird as they are based on standard notion of modules, that is not similar to PSQL routines. I'd just adapt their definitions to our realities. For me, "created" LTT is similar to GTT (i.e. stored in the schema) but with data isolated per request (per PSQL routine). "Declared" LTTs are defined inside the PSQL routine, with data isolated the same way. In fact, what you priorly define as LT is IMO "declared" LTT. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 18:00, Dimitry Sibiryakov wrote: Adriano dos Santos Fernandes wrote 15.03.2022 15:37: On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Maybe just "DECLARE TABLE" then? Oracle uses term "collection". Why not to adapt their syntax? Is it standard ? Could "collection" be joined with regular tables ? Isn't it is better to implement standard SET\ARRAY ? If there is strong demand to have Oracle's collection - could it be implemented later based on LT[T] ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 16:14, Adriano dos Santos Fernandes wrote:
Hi!
As part of "Support multiple rows for DML RETURNING (#6815)" feature,
BLR verbs for "local table" were created.
Local tables (LT) as defined there works outside transaction control.
For #6815 this does not matter, but I want to add LT feature that will
use these verbs.
LTs are defined inside PSQL routines and its data is separated per
routine invocation. For example a recursive routine will have different
data in its LTs per each invocation, like local variables.
Ok.
SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be
"created" or "declared". The semantics of these standard specs are
different and not very easy applicable to Firebird as they are based on
standard notion of modules, that is not similar to PSQL routines.
Could you specify, in short, what part of standard is hard to apply to the
Firebird ?
Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON
COMMIT PRESERVE/DELETE ROWS ].
"ON COMMIT" clause seems meaningless for LTT's DECLARED within PSQL routine,
while could be useful for packaged LTT's.
Declared LTTs could be done as part of packages. They would be like
GLOBAL TEMPORARY TABLES (GTT) but declared (created, updated) as part of
packages headers (public) or body (private). They would work under
transaction control and may be represented in metadata.
Mostly agree - just not sure about metadata, it could be discussed later.
LTs could also be added in packages. It's different feature than LTTs.
What is a difference ?
I propose syntax of LT:
DECLARE LOCAL TABLE (
{ }...
);
This statement should be used as part of common DECLARE section of PSQL
routine
or any place of package definition (but before usage), correct ?
It will work with common commands: DELETE, UPDATE, INSERT, MERGE, UPDATE
OR INSERT, SELECT.
"DELETE FROM " would be optimized to use
blr_local_table_truncate.
You mean - if without WHERE clause, correct ? What about undo ?
In general - how operations on LT[T] should be handled in case of PSQL
exceptions ?
Standard scope rules will be used. A LT may use the same name of a
schema-based table and will shadow it inside the routine.
Also, packaged LT should hide "GLOBAL" name when used in routine of the same
package, correct ?
Future enhancements may be done (index usage, faster get of count of
records), but I don't want to go there in initial design/implementation.
I'd say indices (at least one) is a must. Probably we could allow only inplace
index definition (i.e. as part of DECLARE LOCAL TABLE only). But, of course, it
could be postponed for a while.
So far I see no need to introduce non-standard LT and would like to see LTT's.
But I, probably, not see whole picture.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
15.03.2022 18:03, Kjell Rilbe wrote: From a linguistic point of view I find "declared" to be a nonsense qualification of "table". Aren't all tables declared in a sense? Maybe, but this is what SQL spec suggests. In PSQL, we have any locals syntactically "declared" -- DECLARE VARIABLE, DECLARE CURSOR. So it looks consistent to have DECLARE LOCAL TEMPORARY TABLE there too. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
Adriano dos Santos Fernandes wrote 15.03.2022 15:37: On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Maybe just "DECLARE TABLE" then? Oracle uses term "collection". Why not to adapt their syntax? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
Den 2022-03-15 kl. 15:37, skrev Adriano dos Santos Fernandes: On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Maybe just "DECLARE TABLE" then? We would refer to them as "declared tables". In routines they will be local declared tables. In the future, when they could also be in a package, a packaged declared table. From a linguistic point of view I find "declared" to be a nonsense qualification of "table". Aren't all tables declared in a sense? The essence of these tables seems to be that they are local to "something", but the term "local" seems to be "taken" by the things called local temporary tables(?). Would it be possible to use some other word to distinguish, instead of just leaving out "local" (which essentially loses the most important aspect of what it really is)? Suggestions for thought: Internal? Volatile? Virtual? Transient? Regards, Kjell begin:vcard fn:Kjell Rilbe n:Rilbe;Kjell org:Marknadsinformation i Sverige AB;Utveckling & databaser adr;quoted-printable:;;Ulvsundav=C3=A4gen 106C;Bromma;Stockholm;16867;Sverige email;internet:kjell.ri...@marknadsinformation.se title:Utvecklings- & databasansvarig tel;cell:0733-442464 x-mozilla-html:TRUE url:http://www.marknadsinformation.se version:2.1 end:vcard Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 15/03/2022 11:23, Alex Peshkoff via Firebird-devel wrote: > > Very interesting - but other name for them should be used. It's too easy > to loose difference between LT & LTT that are absolutely different things. > Maybe just "DECLARE TABLE" then? We would refer to them as "declared tables". In routines they will be local declared tables. In the future, when they could also be in a package, a packaged declared table. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Local tables
On 3/15/22 17:14, Adriano dos Santos Fernandes wrote: Hi! As part of "Support multiple rows for DML RETURNING (#6815)" feature, BLR verbs for "local table" were created. Local tables (LT) as defined there works outside transaction control. For #6815 this does not matter, but I want to add LT feature that will use these verbs. LTs are defined inside PSQL routines and its data is separated per routine invocation. For example a recursive routine will have different data in its LTs per each invocation, like local variables. Very interesting - but other name for them should be used. It's too easy to loose difference between LT & LTT that are absolutely different things. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Local tables
Hi!
As part of "Support multiple rows for DML RETURNING (#6815)" feature,
BLR verbs for "local table" were created.
Local tables (LT) as defined there works outside transaction control.
For #6815 this does not matter, but I want to add LT feature that will
use these verbs.
LTs are defined inside PSQL routines and its data is separated per
routine invocation. For example a recursive routine will have different
data in its LTs per each invocation, like local variables.
SQL standard defines LOCAL TEMPORARY TABLES (LTT), which may be
"created" or "declared". The semantics of these standard specs are
different and not very easy applicable to Firebird as they are based on
standard notion of modules, that is not similar to PSQL routines.
Declared LTTs uses syntax "DECLARE LOCAL TEMPORARY TABLE ... [ ON
COMMIT PRESERVE/DELETE ROWS ].
Declared LTTs could be done as part of packages. They would be like
GLOBAL TEMPORARY TABLES (GTT) but declared (created, updated) as part of
packages headers (public) or body (private). They would work under
transaction control and may be represented in metadata.
LTs could also be added in packages. It's different feature than LTTs.
I propose syntax of LT:
DECLARE LOCAL TABLE (
{ }...
);
It will work with common commands: DELETE, UPDATE, INSERT, MERGE, UPDATE
OR INSERT, SELECT.
"DELETE FROM " would be optimized to use
blr_local_table_truncate.
Standard scope rules will be used. A LT may use the same name of a
schema-based table and will shadow it inside the routine.
Future enhancements may be done (index usage, faster get of count of
records), but I don't want to go there in initial design/implementation.
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
