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
