Firebird Global Temporary Tables: A Scoped Workspace Architecture Note

When a private workspace beats a permanent table

Firebird applications frequently need intermediate storage: staging rows for a report, accumulating batch results, or holding recursive output that exceeds PSQL variable capacity. The default choice—a permanent table plus a cleanup job—introduces schema churn, concurrency conflicts, and operational debt. Global Temporary Tables (GTTs) solve a narrower problem: they provide a private workspace whose rows are visible only to the creating transaction or connection, while the table definition remains permanent metadata shared by all sessions.

Requirements that fit a GTT

A GTT is appropriate when all of the following hold:

• The intermediate data must be private to one connection or transaction.
• The data does not need to survive the end of the transaction or session.
• The working set is too large for PSQL variables or client-side memory.
• No other session or external tool needs to read the rows.

If the data must be shared across sessions, outlive the connection, or feed an external process, a GTT is the wrong tool.

The smallest suitable design

Pre-create GTTs during deployment or migration, never at runtime. A minimal transaction-scoped staging table looks like this:

-- Run as SYSDBA or the database owner during deployment
CREATE GLOBAL TEMPORARY TABLE gtt_report_stage (
    line_no     INTEGER NOT NULL,
    account_id  INTEGER NOT NULL,
    amount      NUMERIC(15,2),
    label       VARCHAR(64)
) ON COMMIT DELETE ROWS;

-- Application accounts need only data access
GRANT INSERT, SELECT ON gtt_report_stage TO app_user;

The ON COMMIT DELETE ROWS clause makes the table transaction-scoped: rows disappear when the transaction commits or rolls back. For scratch data that must survive commits within a single session, use ON COMMIT PRESERVE ROWS instead:

CREATE GLOBAL TEMPORARY TABLE gtt_session_cache (
    key   VARCHAR(64) NOT NULL,
    value BLOB SUB_TYPE TEXT
) ON COMMIT PRESERVE ROWS;

GRANT INSERT, SELECT, DELETE ON gtt_session_cache TO app_user;

Granting DELETE is optional but often necessary for session-scoped tables so the application can clear stale rows explicitly.

Why not create GTTs at runtime?

Giving application accounts CREATE TABLE rights introduces two problems. First, DDL in Firebird acquires metadata locks that can block other users. Second, it expands the privilege surface: an account that can create tables can create permanent tables, not just temporary ones. Pre-creating the structures keeps the runtime privilege footprint to INSERT and SELECT.

Trust and data boundaries

The critical distinction is between the table definition and the table data. The definition is global metadata visible to every connection; the rows are invisible to every connection except the one that inserted them. This split creates a clean trust boundary:

Boundary	Scope	Implication
Table definition	Global metadata	Changes require DDL rights and exclusive access
Rows in a DELETE ROWS GTT	Current transaction	Invisible to other transactions; gone after commit/rollback
Rows in a PRESERVE ROWS GTT	Current connection	Invisible to other connections; gone after disconnect

Backup and restore behavior for GTT data differs from permanent tables. Do not assume temporary contents are preserved or discarded by your backup tool; validate the behavior in your recovery procedures.

Operational checks

Before relying on a GTT in production, verify three things in a non-production database.

1. Lifecycle matches intent

Open two concurrent sessions. In session A, insert a row into a DELETE ROWS table and do not commit. Confirm session B sees zero rows. Commit in session A and confirm the row is gone from session A as well. Repeat with a PRESERVE ROWS table: after commit, the row should remain visible in session A but invisible in session B.

2. Temporary storage growth

Firebird stores temporary data in temp space or memory depending on engine configuration. Monitoring table names and available metrics vary across versions; consult the system tables for your specific Firebird release (for example, MON$ tables where supported) to locate temporary usage counters. Set an alert threshold based on the largest expected working set.

3. Connection pool behavior

This is the most common production surprise. If your application uses a connection pool, a PRESERVE ROWS GTT will retain its rows when the connection is returned to the pool, because the database connection remains open. The next borrower of that pooled connection may see stale data. Mitigate this by explicitly deleting rows before returning the connection, or by using DELETE ROWS when the data is only needed within a single transaction.

Failure modes

• Connection loss: Session-scoped (PRESERVE ROWS) data is discarded. If the application expected the data to persist, this is silent data loss.
• Rollback: Transaction-scoped (DELETE ROWS) data is discarded. Any logic that assumed survival across a rollback will fail.
• Metadata contention: Altering a GTT definition requires exclusive access. On a busy system, this can block all sessions that reference the table.
• Temp exhaustion: Unbounded inserts into a GTT can exhaust temp space or memory. The database may raise resource errors that are harder to diagnose than a simple table-full condition on a permanent table.

Conditions that would change the design

Move away from GTTs when any of these become true:

• Data must outlive the connection: Switch to a permanent table with an explicit lifecycle policy.
• Multiple sessions need visibility: Use a permanent table, possibly with a session-key column and a cleanup job.
• External tools must read the data: GTT rows are invisible outside the owning connection; use a permanent or external staging area.
• The working set is small and read-mostly: PSQL variables, derived tables, or a single SELECT with CTEs may be simpler and faster than GTT round-trips.

Verification and limitations

GTT syntax and monitoring facilities vary across Firebird versions. Confirm support and system table availability against the exact deployed server version before rollout. The examples above assume a version with standard CREATE GLOBAL TEMPORARY TABLE support; older or newer releases may differ in clause names or monitoring views.

A practical result check after deployment: run the two-session lifecycle test described above against a staging database, and inspect your monitoring tables for temporary storage counters. If the row visibility and cleanup behavior match the intended ON COMMIT clause, the design is sound for that scope.