Firebird Global Temporary Tables: Scratch Space Without the Cleanup Chore

If you've ever written a reporting procedure in Firebird that needs to filter a big set of rows, join them to detail tables, and aggregate the result, you've probably faced the staging-table question: where do the intermediate rows live? A permanent table works, but then you own the problems — concurrent sessions trampling each other's rows, DELETE cleanup steps, and leftover junk when something fails halfway. Global temporary tables (GTTs) exist precisely to remove that bookkeeping, and they're one of Firebird's more underused features.

The short version: a GTT has a persistent, shared definition but private data. Every session sees the same table structure, yet the rows you insert are visible only to you. That single property eliminates most of the pain of shared staging tables.

Two flavors, one decision that matters

Firebird offers two GTT variants, and the difference is the lifetime of the data:

• ON COMMIT DELETE ROWS — transaction-scoped. Rows vanish when you commit. This is the right default for single-unit-of-work scratch space.
• ON COMMIT PRESERVE ROWS — connection-scoped. Rows survive commits and disappear only when the connection ends (or you delete them yourself).

The preserve-rows variant looks convenient, but it carries a real trap: connection pooling. If your application uses pooled connections, a "connection" can live for hours or days and be reused by many logical requests. Rows preserved from request A are still sitting there when request B grabs the same physical connection. Unless you have a disciplined habit of explicitly deleting GTT rows at the start or end of each request, prefer the delete-rows variant. Test this under your real pool configuration, not just in isql.

A worked example: filtered aggregation

Here's the pattern in practice. Run this DDL once, as a user with DDL rights on the database (e.g., via isql or your migration tooling):

CREATE GLOBAL TEMPORARY TABLE gtt_order_scratch (
    order_id    BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    order_total NUMERIC(15,2)
) ON COMMIT DELETE ROWS;

Then a stored procedure can use it as private scratch space:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE report_regional_totals (
    region_code VARCHAR(10),
    date_from   DATE,
    date_to     DATE
) RETURNS (customer_id BIGINT, total NUMERIC(15,2))
AS
BEGIN
    -- Stage the filtered orders; private to this transaction
    INSERT INTO gtt_order_scratch (order_id, customer_id, order_total)
    SELECT o.order_id, o.customer_id, o.order_total
    FROM orders o
    WHERE o.region = :region_code
      AND o.order_date BETWEEN :date_from AND :date_to;

    -- Join scratch rows to detail data and aggregate
    FOR
        SELECT s.customer_id, SUM(s.order_total)
        FROM gtt_order_scratch s
        JOIN customers c ON c.customer_id = s.customer_id
        GROUP BY s.customer_id
        INTO :customer_id, :total
    DO
        SUSPEND;
END^
SET TERM ; ^

When the calling transaction commits, the staged rows disappear automatically. There is no DELETE FROM gtt_order_scratch cleanup step, and — more importantly — two sessions running this procedure simultaneously never see or block each other's rows. With a permanent staging table you'd need a session-ID column, extra indexes on it, and cleanup logic that still leaks rows on abnormal termination.

To verify the behavior yourself: create both GTT variants in a test database, insert a row in each, commit, and re-query from the same connection and from a second isql session. You'll see the delete-rows table empty after commit, the preserve-rows table still populated in the original connection, and both empty from the other session's point of view.

Trade-offs and limitations worth knowing

GTTs are not a free lunch:

• No cross-session sharing. Data privacy is the point, but it means a GTT can't pass intermediate state between sessions or application tiers. If two processes need the same staged data, you need a permanent table.
• Constraint restrictions. Foreign keys involving GTTs are limited compared to permanent tables — don't design a schema that leans on GTT referential integrity.
• Optimizer behavior. Indexes on GTTs are allowed, but statistics and plan selection can differ from permanent tables. After writing a procedure like the one above, check the plan (SET PLAN in isql) with realistic data volumes to confirm the indexes you expect are actually used.
• Schema permanence. The definition is a real schema object. Altering or dropping a GTT requires the same migration discipline as any table — it's "temporary" data, not a temporary object.

Exact syntax and optimizer treatment are version-sensitive; the behavior described here reflects Firebird 2.5 through 4.x, so confirm against your target release's release notes before relying on edge-case semantics.

The practical takeaway

Reach for an ON COMMIT DELETE ROWS GTT whenever a procedure or report needs intermediate rows that belong to one unit of work. You'll delete your cleanup code, your session-ID columns, and an entire class of concurrency bugs in one move. Reserve ON COMMIT PRESERVE ROWS for cases where you genuinely need scratch data across multiple commits — and if you use it behind a connection pool, make explicit deletion part of the request lifecycle, then test that it actually happens.