Debian Codegraph @

doc/sql.extensions/README.global_temporary_tables

raw
1 SQL Language Extension: global temporary tables
2
3 Author:
4 Vlad Khorsun <hvlad at users.sourceforge.net>
5
6
7 Function:
8 Global temporary tables (GTTs) are tables with permanent metadata, stored
9 in the system catalogue, but with temporary data. GTT's may be of two kinds -
10 with data, persistent within lifetime of connection in which the given GTT
11 was referenced, and with data, persistent within only during lifetime of
12 referencing transaction. The data from different connections (transactions) are
13 isolated from each other, but metadata of the global temporary table are shared
14 between all connections and transactions.
15
16
17 Syntax and rules :
18
19 CREATE GLOBAL TEMPORARY TABLE
20 ...
21 [ON COMMIT <DELETE | PRESERVE> ROWS]
22
23 Creates metadata of the temporary table in the system catalogue.
24 Clause ON COMMIT sets the kind of temporary table:
25
26 ON COMMIT PRESERVE ROWS : data of the given table after end of transaction
27 remain in database until end of connection
28
29 ON COMMIT DELETE ROWS : data of the given table are deleted from database
30 immediately after end of transaction
31
32 If optional clause ON COMMIT is not specified ON COMMIT DELETE ROWS is
33 used by default.
34
35 CREATE GLOBAL TEMPORARY TABLE - usual DDL statement and processed by the
36 engine the same way as operator CREATE TABLE. Therefore it's impossible to create or
37 drop GTT within stored procedure or trigger.
38
39 GTT differs from permanent tables by value of RDB$RELATIONS.RDB$RELATION_TYPE :
40 GTT with ON COMMIT PRESERVE ROWS option has value 4 in RDB$RELATION_TYPE field
41 whereas GTT with ON COMMIT DELETE ROWS option has value of 5. See full list of
42 values in RDB$TYPES
43
44 GTT may have indexes, triggers, field level and table level constraints - as
45 well as usual tables.
46
47 All kinds of constraints between temporary and persistent tables follow
48 the rules below:
49
50 a) references between persistent and temporary tables are forbidden
51 b) GTT with ON COMMIT PRESERVE ROWS can't have reference on GTT with
52 ON COMMIT DELETE ROWS
53 c) Domain constraints can't have reference on GTT.
54
55
56 Implementation details:
57
58 GTT instance (set of data rows created by and visible within given connection
59 or transaction) is created when referenced for the first time, usually at statement prepare
60 time. Each instance has its own private set of pages on which data and indexes
61 are stored. Data rows and indexes have the same physical storage layout as
62 permanent tables.
63
64 When connection or transaction ends all pages of a GTT instance are released
65 immediately (this is similar as when you do DROP TABLE but metatada remains in
66 database of course). This is much quicker than traditional row by row delete +
67 garbage collection of deleted record versions. DELETE triggers are not fired in
68 this case.
69
70 Note, COMMIT\ROLLBACK RETAINING preserves data of GTT with ON COMMIT DELETE ROWS.
71 Due to bug in Firebird 2.x that data was not visible to the user application.
72 It is fixed in Firebird 3. See also description of ClearGTTAtRetaining setting at
73 firebird.conf.
74
75 Data and index pages of all of the GTTs instances are placed in separate temporary
76 files. Each connection has its own temporary file created when this connection
77 first referenced some GTT. Also these temporary files are always opened with "Forced
78 Writes = OFF" setting despite of database setting.
79
80 There's no limit on number of GTT instances. If you have N transactions
81 active simultaneously and each transaction has referenced some GTT then you'll
82 have N GTTs instances.