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 <name> ... [ 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 <name> (
{ <column definition> }...
);
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 <local table>" 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
