Module 9 — The RETURNING Clause Evolved: OLD/NEW Aliases and Eliminating Race Conditions

The Problem: Atomic Read-Modify-Write

A large fraction of application-database interactions follow this pattern:

1. Read the current value of a row
2. Compute a new value based on the old value
3. Write the new value back

The canonical example: decrementing an account balance.

sql
-- Application code (naive, broken under concurrency):
SELECT balance FROM accounts WHERE id = $1;
-- application computes: new_balance = balance - 100
UPDATE accounts SET balance = $2 WHERE id = $1;

This is a classic read-modify-write race condition. Between the SELECT and the UPDATE, another transaction can modify the balance. Two concurrent withdrawals of $100 from an account with $150 can both read $150, both compute $50 as the new balance, and both write $50 — leaving the account with $50 instead of the correct $-50 (or a rejected second withdrawal).

The correct SQL-level solution is an atomic update:

sql
UPDATE accounts SET balance = balance - 100 WHERE id = $1;

This executes atomically. No race condition. But it discards information: what was the balance before the update?

In many real-world cases, you need the previous value to make a decision in the application:

• Was the balance sufficient? (if old balance < 100, reject the transaction)
• What changed? (for audit logs, event sourcing, change feeds)
• What was the previous state? (for optimistic concurrency control)
• Did the row actually exist before? (to distinguish "updated" from "not found")

Pre-PG18 Workarounds

Workaround 1: Two Round Trips with Locking

sql
BEGIN;

-- Lock the row so no one else can modify it between SELECT and UPDATE
SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;

-- Application checks: if balance < 100, ROLLBACK and return error
-- Otherwise:
UPDATE accounts SET balance = balance - 100 WHERE id = $1;

COMMIT;

The cost:

• Two database round trips (SELECT + UPDATE) instead of one
• FOR UPDATE acquires a row-level exclusive lock — concurrent reads are not blocked, but concurrent writes on the same row queue up
• Lock held for the full duration of application processing between the SELECT and UPDATE
• Deadlock risk if multiple rows are locked in different orders by concurrent transactions

Workaround 2: CTE with Data-Modifying CTE

sql
WITH updated AS (
  UPDATE accounts
  SET balance = balance - 100
  WHERE id = $1
  RETURNING id, balance
)
SELECT
  balance AS new_balance,
  balance + 100 AS old_balance  -- reverse-engineer the old value
FROM updated;

This is one round trip, but you're computing the old value from the new value. For additive changes (balance - 100), this is trivial. For complex transformations, it becomes error-prone. And for non-invertible changes (setting a value to a computed result from another table), it is impossible.

Workaround 3: BEFORE Trigger to Capture Old Value

sql
CREATE TABLE balance_changes (
  account_id BIGINT,
  old_balance NUMERIC,
  new_balance NUMERIC,
  changed_at TIMESTAMPTZ DEFAULT now()
);

CREATE FUNCTION capture_balance_change() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO balance_changes VALUES (OLD.id, OLD.balance, NEW.balance, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_balance_update
BEFORE UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION capture_balance_change();

The cost:

• Extra table, extra trigger, extra INSERT on every update
• The old value is in a separate table — application must join to retrieve it
• Trigger maintenance overhead (documented in Module 4 — triggers prevent HOT updates)
• Not usable for ad-hoc queries; requires schema setup in advance

Workaround 4: Subquery in RETURNING

sql
UPDATE accounts
SET balance = balance - 100
WHERE id = $1
RETURNING
  balance AS new_balance,
  (SELECT balance FROM accounts WHERE id = $1) AS old_balance;

This does not work correctly. The subquery in RETURNING sees the post-update state of the row (in the same transaction). balance in the subquery returns the updated value, not the original. This is a commonly attempted workaround that silently produces wrong results.

PostgreSQL 18: OLD and NEW in RETURNING

PG18 solves this cleanly. UPDATE and DELETE statements can now use OLD.column and NEW.column aliases in the RETURNING clause to access both the pre-update and post-update values of any column in a single statement.

Basic Syntax

sql
-- Return both old and new balance in one atomic UPDATE
UPDATE accounts
SET balance = balance - 100
WHERE id = $1
RETURNING
  OLD.balance AS balance_before,
  NEW.balance AS balance_after,
  NEW.id;

-- Result:
-- balance_before | balance_after | id
-- ---------------+---------------+----
--           1500 |          1400 | 42

One round trip. Atomic. No locks beyond what the UPDATE itself requires. No reverse-engineering the old value. No triggers.

DELETE with OLD

DELETE only has OLD — there is no NEW row after deletion.

sql
DELETE FROM sessions
WHERE user_id = $1
  AND expires_at < now()
RETURNING
  OLD.id AS deleted_session_id,
  OLD.created_at AS session_created,
  OLD.expires_at AS session_expired;

Returns every deleted row's pre-deletion state. Useful for:

• Audit logging of what was deleted
• Triggering downstream cleanup based on the deleted row's values
• Confirming which specific rows were deleted (when the WHERE clause could match multiple)

Unqualified Columns in RETURNING

For backward compatibility, unqualified column names in RETURNING continue to refer to NEW for UPDATE and OLD for DELETE:

sql
-- These are equivalent for UPDATE:
RETURNING balance          -- refers to NEW.balance (post-update)
RETURNING NEW.balance      -- explicit

-- For DELETE:
RETURNING balance          -- refers to OLD.balance (pre-delete)
RETURNING OLD.balance      -- explicit

Prefer explicit OLD. / NEW. in new code — it makes intent unambiguous and is self-documenting.

Patterns Enabled by OLD/NEW RETURNING

Pattern 1: Atomic Conditional Update with Decision

The most common use case: update a row only if a condition is met, and know in the application whether the condition was satisfied.

sql
-- Debit an account only if balance is sufficient
-- Return both old and new balance so the application can decide what happened
UPDATE accounts
SET balance = balance - $2
WHERE id = $1
  AND balance >= $2
RETURNING
  OLD.balance AS balance_before,
  NEW.balance AS balance_after;

-- If the WHERE clause fails (balance < amount):
-- → 0 rows returned → application knows: insufficient funds, no change made

-- If it succeeds:
-- → 1 row with balance_before=1500, balance_after=1400

Before PG18, distinguishing "row not found" from "condition failed" required either a separate existence check or careful result parsing. Now: zero rows means the condition failed (or the row doesn't exist), one row means success — and you have full before/after context.

Pattern 2: Optimistic Concurrency Control

Optimistic concurrency uses a version counter or timestamp to detect concurrent modifications without holding locks.

sql
-- Schema: version column incremented on every update
ALTER TABLE documents ADD COLUMN version BIGINT NOT NULL DEFAULT 1;

-- Application reads: document with version = 7
-- Application modifies content
-- Application writes: only update if version is still 7

UPDATE documents
SET
  content = $1,
  version = version + 1
WHERE id = $2
  AND version = $3  -- $3 = 7, the version the application read
RETURNING
  OLD.version AS expected_version,
  NEW.version AS current_version,
  NEW.content;

-- 0 rows returned → concurrent modification detected
--   application must re-read and retry

-- 1 row returned with NEW.version = 8 → success

The application gets confirmation of both the version it expected and the version that was written — useful for logging and debugging concurrent modification conflicts.

Pattern 3: State Machine Transitions

Many business objects have a state machine — pending → processing → completed. A common requirement: only transition if the current state is the expected state, and know what state it was in.

sql
-- Claim a job for processing (worker picks up a pending job)
UPDATE jobs
SET
  status = 'processing',
  worker_id = $1,
  started_at = now()
WHERE id = $2
  AND status = 'pending'
RETURNING
  OLD.status AS previous_status,
  NEW.status AS current_status,
  NEW.worker_id,
  NEW.started_at;

-- 0 rows → job was already claimed by another worker, or doesn't exist
-- 1 row with previous_status='pending' → this worker successfully claimed it

Before PG18, this pattern required either a SELECT FOR UPDATE to check the current status before updating, or inferring success from update count alone (which doesn't tell you if the condition failed or the row was missing).

Pattern 4: Audit Log from the Update Itself

Instead of a trigger (which prevents HOT updates and adds schema complexity), generate an audit log entry from the RETURNING clause directly in the application:

sql
WITH balance_update AS (
  UPDATE accounts
  SET balance = balance - $2
  WHERE id = $1
    AND balance >= $2
  RETURNING
    OLD.id,
    OLD.balance AS old_balance,
    NEW.balance AS new_balance,
    now() AS changed_at
)
INSERT INTO audit_log (account_id, old_balance, new_balance, changed_at, operation)
SELECT id, old_balance, new_balance, changed_at, 'debit'
FROM balance_update;

The entire operation — update the account, log the change — in a single transaction, with no trigger, no extra round trip, and no HOT update penalty. If the update fails (insufficient balance), no audit row is inserted. Atomicity is guaranteed by the transaction.

Pattern 5: Soft Delete with History

sql
-- Soft delete: mark as deleted, return what was deleted for archiving
UPDATE users
SET
  deleted_at = now(),
  deleted_by = $1
WHERE id = $2
  AND deleted_at IS NULL
RETURNING
  OLD.email AS email_before_delete,
  OLD.plan_tier AS plan_at_deletion,
  OLD.created_at AS account_created,
  NEW.deleted_at AS deleted_at,
  NEW.deleted_by AS deleted_by;

The application receives the pre-deletion profile in one round trip, can write it to a data warehouse or archive table, without any additional reads.

What OLD/NEW Does NOT Cover

INSERT: Only NEW Exists

INSERT ... RETURNING only has NEW (the inserted row). There is no OLD for inserts — the row didn't exist before.

sql
INSERT INTO accounts (user_id, balance)
VALUES ($1, 0)
RETURNING NEW.id, NEW.balance, NEW.created_at;
-- or simply:
RETURNING id, balance, created_at;  -- unqualified = NEW for INSERT

UPSERT (INSERT ON CONFLICT): Full Access

INSERT ... ON CONFLICT DO UPDATE ... RETURNING gains the most from OLD/NEW:

sql
INSERT INTO account_stats (user_id, login_count, last_login)
VALUES ($1, 1, now())
ON CONFLICT (user_id) DO UPDATE
SET
  login_count = account_stats.login_count + 1,
  last_login = now()
RETURNING
  OLD.login_count AS previous_count,  -- NULL if this was an INSERT (no old row)
  NEW.login_count AS current_count,
  OLD.last_login AS previous_login,
  NEW.last_login AS current_login;

When the ON CONFLICT DO UPDATE branch executes, OLD contains the pre-upsert values. When the INSERT branch executes (no conflict), OLD columns are NULL — the row didn't exist before. The application can distinguish insert vs update from OLD.login_count IS NULL.

Triggers Still See OLD/NEW Separately

Row-level triggers have always had OLD and NEW records. PG18's RETURNING OLD/NEW is a separate feature — it gives the SQL layer the same access that trigger functions have always had internally. The two mechanisms are independent.

The Concurrency Guarantee

The OLD values returned by RETURNING OLD are the values that existed in the row at the moment the row lock was acquired for the UPDATE or DELETE, which is the same moment the update itself executes. This means:

• OLD reflects the committed state of the row just before this transaction modified it
• No other transaction can have modified the row between when OLD was captured and when NEW was written — the row lock prevents it
• The old-to-new transition is atomic from every other transaction's perspective

This is a stronger guarantee than SELECT ... FOR UPDATE + UPDATE in two round trips, because with two round trips there is always the question of whether the application correctly used the locked value. With RETURNING OLD, the database captures and returns the old value as part of the single atomic operation — no application logic is in the critical path between read and write.

Summary

The principle: any operation that previously required reading before writing to know what changed can now be expressed as a single atomic statement. The database captures the transition — both the before and after state — and returns it to the application as part of the write operation itself.

Module 10 closes the core architecture loop with replication and high availability — the CAP trade-offs, the consistency models of each replication mode, and the operational decisions that separate a database that survives incidents from one that doesn't.

Next: Module 10 — Replication, High Availability, and the CAP Trade-offs →