Firebird’s UPDATE OR INSERT: One‑Shot Atomic Upsert and How to Verify It

Why Do You Need an Atomic Upsert?

In many applications you want to ensure that a row exists with a particular key, and if it already exists you just want to tweak a value. The classic pattern is a SELECT followed by either an INSERT or an UPDATE. That two‑step round‑trip is prone to race conditions and adds unnecessary network traffic.

Firebird offers a single‑statement solution: UPDATE OR INSERT. It atomically updates a row if it matches a unique key, or inserts a new row otherwise. This blog walks through the mechanics, the MATCHING clause, RETURNING support, concurrency trade‑offs, and a concrete example you can run in isql to verify the behavior in your environment.

How UPDATE OR INSERT Works Under the Hood

The statement has the form:

UPDATE OR INSERT INTO table_name (col1, col2, …) VALUES (?, ?, …)
  MATCHING (unique_column1, unique_column2, …);

Firebird first attempts an UPDATE using the columns listed in MATCHING as the search key. If no row is found, it falls back to an INSERT. The operation is fully transactional: either the UPDATE or the INSERT succeeds, and the surrounding transaction can roll back the whole thing if needed.

Because the statement touches at most one row, the database can guarantee that you never accidentally update multiple rows with a single call.

The MATCHING Clause and Unique Constraints

The MATCHING clause lists the columns that Firebird uses to locate an existing row. These columns must be backed by a primary key or a unique constraint; otherwise Firebird will raise an error at prepare time or runtime.

• Always verify that a unique index exists: SELECT * FROM RDB$RELATION_CONSTRAINTS WHERE RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' OR RDB$CONSTRAINT_TYPE = 'UNIQUE';
• For composite keys, the order of columns in MATCHING must match the index definition.
• If the index is missing, Firebird will refuse to execute the statement and return an error like SQL error code = -104.

RETURNING Support – Get the PK Without a Second Query

Starting with Firebird 2.1, the statement can include a RETURNING clause:

UPDATE OR INSERT INTO settings (user_id, name, value) VALUES (1, 'theme', 'dark')
  MATCHING (user_id, name) RETURNING id;

If your client library forwards the RETURNING clause to the server and can capture a single row result, you get the primary key (or any other column) instantly. This eliminates the need for a separate SELECT after the upsert.

Check your driver’s documentation: some drivers ignore RETURNING or require a special API call to fetch the result.

Concurrency Trade‑Offs

Firebird’s algorithm is simple: try an UPDATE; if it affects zero rows, perform an INSERT. Under high contention, two sessions can both miss the UPDATE and then race on the INSERT. One will succeed; the other will hit a unique‑key violation.

• Catch the error (e.g., SQL error code = -104) and retry the UPDATE OR INSERT in a new transaction.
• Alternatively, serialize the operation by using a higher isolation level (e.g., SERIALIZABLE) or by locking the key row explicitly before attempting the upsert.
• Note that INSERT triggers fire only on the INSERT path, and UPDATE triggers only on the UPDATE path.

Worked Example in isql

Below is a self‑contained example you can paste into Firebird’s isql client. Replace mydb.fdb with your database file.

# Connect to the database
CONNECT 'mydb.fdb' USER 'SYSDBA' PASSWORD 'masterkey';

# Create a test table with a composite unique key
CREATE TABLE settings (
  user_id INTEGER NOT NULL,
  name VARCHAR(50) NOT NULL,
  value VARCHAR(255),
  id INTEGER GENERATED BY DEFAULT AS IDENTITY,
  CONSTRAINT pk_settings PRIMARY KEY (id),
  CONSTRAINT uq_user_name UNIQUE (user_id, name)
);

# First upsert – should perform an INSERT
UPDATE OR INSERT INTO settings (user_id, name, value)
  VALUES (1, 'theme', 'light')
  MATCHING (user_id, name) RETURNING id;

# Second upsert – same key, should perform an UPDATE
UPDATE OR INSERT INTO settings (user_id, name, value)
  VALUES (1, 'theme', 'dark')
  MATCHING (user_id, name) RETURNING id;

# Verify row count stays at 1
SELECT COUNT(*) FROM settings WHERE user_id = 1 AND name = 'theme';

# Show the final row
SELECT * FROM settings WHERE user_id = 1 AND name = 'theme';

COMMIT;
DISCONNECT;

After running the script, the SELECT COUNT(*) should return 1, and the final row should show value = 'dark'. The RETURNING clause will print the id of the affected row on each call.

Limitations to Keep in Mind

• Single‑row only: UPDATE OR INSERT operates on a single row from a VALUES list. It cannot read from another table, update multiple rows in one statement, or delete rows that do not match.
• Not a full MERGE: If you need set‑based logic (e.g., upsert many rows at once or conditionally update different columns), you’ll need an EXECUTE BLOCK or stored procedure.
• Driver support: Some drivers may not expose the RETURNING result or may not support the statement at all. Verify with isql or your client’s API.
• Concurrency risk: As noted, under high contention duplicate‑key errors can still surface. Implement retry logic or explicit locking if your workload requires it.

Actionable Takeaway

Use UPDATE OR INSERT when you need a reliable, single‑statement upsert for a known unique key. Verify that the matching columns are covered by a unique index, test the statement in isql to confirm the expected behavior, and handle the unique‑key error if you anticipate contention. If you need set‑based upserts, fall back to an EXECUTE BLOCK or a multi‑row INSERT … SELECT pattern.

With these checks in place, you can confidently replace the classic SELECT‑then‑INSERT/UPDATE pattern and reduce latency and race conditions in your Firebird applications.