Using Firebird Global Temporary Tables for Session-Scoped Data

Quick answer

When you need rows that are visible only to a single connection and are discarded automatically — either at transaction commit or when the connection ends — define a Firebird Global Temporary Table (GTT). The table definition is shared metadata in the database, but every attachment gets its own private row storage, so concurrent sessions never see or block each other's data.

Worked configuration

Create a GTT whose rows survive intermediate commits and are removed only when the connection closes:

CREATE GLOBAL TEMPORARY TABLE session_orders (
    id      INTEGER PRIMARY KEY,
    product VARCHAR(50),
    qty     INTEGER
) ON COMMIT PRESERVE ROWS;

Run this once. The definition is stored in the database metadata, so executing the same CREATE statement again — in any session — fails with a "table already exists" error. Other sessions simply use the existing definition.

Insert some rows, query them, then commit:

INSERT INTO session_orders (id, product, qty) VALUES (1, 'Widget', 10);
INSERT INTO session_orders (id, product, qty) VALUES (2, 'Gadget', 5);
SELECT * FROM session_orders;   -- returns the two rows
COMMIT;
SELECT * FROM session_orders;   -- still returns the two rows

With ON COMMIT PRESERVE ROWS, rows persist across commits for the lifetime of the attachment and are discarded on disconnect. If instead you want rows deleted at every transaction commit, use the other variant:

CREATE GLOBAL TEMPORARY TABLE session_orders (
    id      INTEGER PRIMARY KEY,
    product VARCHAR(50),
    qty     INTEGER
) ON COMMIT DELETE ROWS;

Now each COMMIT ends the transaction and removes that connection's rows. Note that the deletion happens at commit time, not after each statement — within one transaction you can run as many statements as you like against the rows.

How it works

Firebird keeps the GTT definition in the ordinary system tables, so all connections see the same columns, constraints, and indexes. When a connection first touches the GTT, the engine allocates a private data segment for that attachment in its temporary storage area. Because row storage is per-attachment, two sessions can insert rows with identical primary-key values without conflict — each session has its own copy of the data — and there is no row-level lock contention between sessions on the GTT. Indexes declared on a GTT are part of the persistent definition; only the index entries and row data live in per-attachment storage and disappear with the rows.

Limits and common mistakes

• Data never survives the connection. Even with PRESERVE ROWS, disconnecting discards everything. GTTs are unsuitable for staging data that must be handed to another process or session.
• Accidental loss with ON COMMIT DELETE ROWS. Any commit — including one issued by client framework code or an auto-commit setting in your driver or admin tool — wipes the rows. A common failure is a stored procedure or application layer that commits mid-workflow while later steps still expect the GTT data.
• Procedural commits with PRESERVE ROWS. If a procedure commits between calls, PRESERVE ROWS keeps the data, but make sure something eventually cleans up or disconnects; otherwise rows accumulate in temporary storage for the life of a long-lived pooled connection.
• Constraint restrictions. Firebird restricts referential constraints involving GTTs because permanent and temporary data have different lifetimes. The exact rules have varied across releases, so check the release notes for your version (this article assumes Firebird 2.5–4.x behavior) before relying on foreign keys to or from a GTT.
• Temporary storage growth. Large GTT workloads consume space in the server's temporary storage. Monitor the configured temporary directory (TempDirectories in firebird.conf) rather than expecting per-table accounting.
• Backup and restore carry only the definition. GTT rows are never backed up; after a restore the table exists but is empty for every new connection.

Verifying session isolation

1. Open two independent ISQL sessions attached to the same database (each is a separate attachment).
2. Confirm the GTT already exists (create it once beforehand if needed); do not re-run CREATE in the second session.
3. Insert a distinct row in each session:

-- Session A
INSERT INTO session_orders (id, product, qty) VALUES (100, 'Alpha', 7);
-- Session B
INSERT INTO session_orders (id, product, qty) VALUES (200, 'Beta', 3);
COMMIT;

1. In each session run SELECT COUNT(*) FROM session_orders;. Each session should report exactly 1 — its own row — confirming storage is per-attachment.
2. If the table uses ON COMMIT DELETE ROWS, commit in session A and re-run the count there: it should drop to 0, while session B still shows 1. With PRESERVE ROWS, both sessions keep their rows after commit.
3. Disconnect both sessions, reconnect, and query the GTT: it should be empty, confirming rows never survive the attachment.

These checks exercise the behavior directly through SQL, with no external tooling required.