Dmitri Kouzmenko

kdv@ib-aid.com

In InterBase 7.5, a new capability of working with temporary tables was added. Unlike system temporary tables (tmp$), these tables may be created and used during applications’ work. To this very day, developers had to store temporary data in ordinary tables, and that required constant table content tracking, as well as specific organization of work with data.

Surely, most often temporary tables were necessary to those developers, who had been working with MS SQL before they started to use InterBase/Firebird.

Let’s consider what temporary tables in InterBase 7.5 really are.

Metadata

On the low system level temporary tables are implemented as permanent tables. That is, when you create these tables, information about them is stored in the RDB$RELATIONS system table; pointer page and other system pages are distributed for them as for regular tables. Moreover, these tables not only will be stored in the database constantly, but also will “outlive” backup/restore (as distinct from any other attempts to extend or change the structure of the rdb$ system tables).

Syntax of creation of temporary tables is as following:

CREATE GLOBAL TEMPORARY TABLE  (
table-element-comma-list )
[ON COMMIT { PRESERVE | DELETE } ROWS]

As you see, temporary tables differ from standard ones by the global temporary phrase. Besides, on commit is added. In IB 7.5, in the RDB$RELATIONS system table, there is RDB$RELATION_TYPE column. It contains one of the following values:

RDB$RELATION_TYPE	Description
PERSISTENT	Standard tables (custom or system), in which records are deleted only by delete+commit.
GLOBAL TEMPORARY	Temporary system tables, which display server status, connection to databases, executed queries, and so on. (TMP$DATABASE, etc)
RDB$RELATION_TYPE	Description
GLOBAL TEMPORARY DELETE	Temporary tables, for which ON COMMIT DELETE ROWS is specified, i.e. the records, which will be unconditionally deleted on the commit.
GLOBAL TEMPORARY PRESERVE	Temporary tables, for which ON COMMIT PRESERVE ROWS is specified, i.e. the records, which will be unconditionally deleted on disconnection.

It is not recommended to modify this column manually; this will not result in anything good. That is to say, it is impossible to turn a regular table into a temporary and vice versa.

For a time of transaction

GLOBAL TEMPORARY DELETE stores records only until any commit is performed (not only in the transaction, which created them, but also of any other transaction within this connect). Such behavior resembles a bug, since committing of competitive transactions is not supposed to flush record view. The temporary system tables work in exactly the same way, i.e. they display updated information as soon as any concurrent transaction executes a commit. At the same time, the records created in the table are invisible to all but the current transaction. A rollback in this case is equivalent to a commit, although it is clear that rollback would also cancel all changes made in the regular tables. In the case of commit, the transaction changes will be committed, while the records in temporary tables would “disappear”.

Let’s create such a table, and try to work with it.

CREATE GLOBAL TEMPORARY table TMPTRANS (
ID int not null,
NAME varchar(20),
constraint PK_TMPTRANS primary key (id) )
ON COMMIT DELETE ROWS

Now you may create a procedure, which would fill in the table with some data

CREATE PROCEDURE XTRANS
AS
DECLARE variable I INT;
BEGIN
I = 0;
WHILE (:I < 10000) DO
BEGIN
INSERT INTO detail VALUES (:I, 'asdfasdfasdfasdfasdf');
I = :I+1;
END
END

You may insert as many records as you need: if all you want is to check the work, 100-10E would be enough. If you want to test speed, it is recommended to begin either from 100E records or million records (for example, on my computer this procedure loads 1 million records to a database with 4K within approximately 47 seconds).

Be careful, do not commit after inserting records, otherwise the records will be lost. Performing select * from tmptran allows you to view the records. After commit is performed, query iteration will return an empty table.

TIP: In case you perform these operations using a tool with automatic transaction control (such as IBExpert), you would not see any temporary records, since IBExpert executing any operator in SQLEditor, performs start/commit of 3-4 another (hidden) transactions, commit of which causes loss of record view in the on commit delete table.

At this moment, one may ask a question: where actually are these records? The answer is: despite of “temporariness” of the records, the temporary table records are stored in the same way as in regular tables, i.e. on a disk. At that, after the records are inserted and commit is executed, if one gathers statistics (for example, with the help of IBAnalyst) it would look almost like the following:

Table	Records	RecLength	VerLen	Versions	Max	Vers	Data	Pages	Slots	Avg	fill%
TMPTRANS	1000000	31.00	0.00	0	0	51725			51725	68

For a time of connection

GLOBAL TEMPORARY PRESERVE tables store records until current connection (during which they were added) is released. At that, they can be displayed only within the period of this connection.

Let’s create a table

CREATE GLOBAL TEMPORARY table TMPCONN (
ID int not null,
NAME varchar(20),
constraint PK_TMPTRANS primary key (id) )
ON COMMIT PRESERVE ROWS

Create a record in this table (it also can be done in IBExpert)

INSERT INTO TMPCONN VALUES (1, 'a')

Perform commit. Now, within this connect, the record will be visible from different transactions. If another instance of IBExpert (or any other tool) runs, and you execute the same insert operator, it would be executed with no PK or UNUQIE key violation error.

As soon as you close the current connection and open a new one, the entered data will be lost.

Connections between temporary tables

It is quite interesting that you can create Foreign Keys between temporary tables, but this cannot be done between a temporary table and a constant one. However, when creating FK one should take into consideration the record view area in both tables. For example, you create two tables:

MASTER, on commit delete
DETAIL, on commit preserve

and create FK from DETAIL to MASTER. As a result, (actually, InterBase would not allow to create such FK) after creation of records in master and detail, the first commit would delete all records in master, and that causes presence of records with missing connections in DETAIL (in fact, such type of connection as commit preserve -< on commit delete is not permitted, though you can perform the opposite).

To do that (and to change “temporariness” type of the records), the ALTER TABLE operator has the following extension:

ALTER TABLE <table> ON COMMIT {PRESERVE | DELETE} ROWS
{RESTRICT| CASCADE}

This operator changes table’s type (preserve/delete) and can also perform cascading correction of type of the tables bound by FK, in order to prevent the situations with mismatch of records’ lifetime in master and detail. The RESTRICT directive will inform about error, if other temporary tables refer to this table.

Temporary tables of all types cannot use FK pointing to constant tables.

Garbage collecting

As already mentioned, in spite of “temporariness” of table content, the on commit delete and on commit preserve records, are nevertheless stored on the disk, as in ordinary tables. Therefore, the server sometime must remove them (as garbage).

This happens when the following events occur:

Table type	When garbage is collected
ON COMMIT DELETE	At first “exclusive” connection to the database
ON COMMIT PRESERVE	When canceling the connection created the record

An example of the procedure, which automatically fills a temporary table with records, is given on purpose. Tests were held using 1 million records. For the tests, 2 IBExpert instances were launched and one IB_SQL was used. Without going into details of the test, we will list its results and conclusions

• For ON COMMIT DELETE tables, garbage is collected during first exclusive connection to the database. Assume we have 10 working applications, which fill in temporary tables. To delete records in all temporary tables, all 10 applications should disconnect, and at least one should connect. Right at that moment, garbage collecting in the ON COMMIT DELETE tables begins. All connections, which attempt to connect to the server before garbage collecting is finished, will “hung”.
• Working with ON COMMIT DELETE temporary folders may lead to fast grow of the database during a day, since it is very seldom that during this period of time all users disconnect from the database
• The more garbage is collected in the ON COMMIT DELETE temporary tables, the longer will be the delay between the first connection and the working. It takes approximately 25 seconds for server to delete 1 million temporary records, and ~120 seconds to delete 3 million temporary records.

• For the ON COMMIT PRESERVE tables, garbage is collected when disconnecting the connection, which creat- ed these records.

  Resume

• The more records a connection creates in temporary tables, the longer the application would “hang” when disconnection is performed. Deletion of 1 million temporary records, as well as in the previous case, takes ~25-35 sec- onds.

Summary

Temporary tables InterBase 7.5 - are very useful for applications, which form complex reports and execute intermediate calculations on the server. However, due to strange behavior of ON COMMIT DELETE, it becomes possible to use transaction context temporary tables only in the applications, which works with only one transaction at a time. Or they can work with several transactions, on condition that a commit of competitive transaction is forbidden until the transaction (which works with the temporary table) performs a commit.

Furthermore, use of ON COMMIT DELETE tables causes collecting of garbage records during multiuser work (since database’s size increases), and collects garbage on first connection to the database. This can cause an undesirable delay in the beginning of the users’ work.

ON COMMIT PRESERVE is a more favorable way, though the process of disconnection of applications would be more time-consuming (of course, unless these applications created records in temporary tables). In order to avoid users’ complaints, you will probably need to specially handle application disconnection, and to display a message asking to wait some time. p.s. during the temporary tables test, a spontaneous processor loading by the IB7.5.0.28 server was observed (though the applications were inactive). At that, the loading appeared in certain order of transactions’ starting and finishing, while they did not contain the executed operator. The reason of this effect is currently being ascertained (with InterBase 7.5 SP1 also).