| 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. |