Module A-9·23 min read

PostgreSQL 18 introduces OLD and NEW in RETURNING. This eliminates entire classes of application-level race conditions.

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

Q: A financial application needs to atomically debit an account, but also requires the account's balance *before* and *after* the debit for auditing and to determine if the transaction was successful (i.e., sufficient funds existed). The application must minimize database round trips and avoid deadlocks in a high-concurrency environment. Which approach, leveraging PostgreSQL 18 features, provides the most robust and performant solution?

C. Implement UPDATE accounts SET balance = balance - $2 WHERE id = $1 AND balance >= $2 RETURNING OLD.balance AS balance_before, NEW.balance AS balance_after; — Option C is the correct and most robust solution. PostgreSQL 18's `OLD`/`NEW` in `RETURNING` allows capturing both pre-update and post-update values in a single, atomic statement, eliminating race conditions, minimizing round trips, and avoiding explicit row-level locks beyond what the `UPDATE` itself requires. The `WHERE balance >= $2` clause handles the conditional debit, and `0 rows returned` indicates insufficient funds or row not found. Option A involves two round trips and holds a row-level lock for the duration of application processing, increasing latency and deadlock risk. Option B is one round trip but relies on reverse-engineering the old value, which is error-prone and not always possible for complex transformations. Option D is a common misconception; a subquery in `RETURNING` sees the post-update state of the row within the same transaction, thus `old_balance` would incorrectly return the new balance.

Q: A Senior Software Engineer is designing a critical state machine transition for a `jobs` table, where a job can only move from `pending` to `processing` if it hasn't already been claimed by another worker. They need to ensure atomicity and retrieve the `previous_status` and `current_status` in a single database interaction. They are considering `RETURNING OLD.status, NEW.status` in their `UPDATE` statement. What is the fundamental concurrency guarantee provided by `OLD` values in this PostgreSQL 18 `RETURNING` clause?

C. `OLD` values reflect the committed state of the row at the moment the row lock is acquired for the `UPDATE` or `DELETE` statement, ensuring atomicity of the old-to-new transition. — Option C accurately describes the concurrency guarantee. The `OLD` values in `RETURNING` are captured at the precise moment the row lock is acquired for the `UPDATE` or `DELETE` statement. This ensures that `OLD` represents the state of the row just before the current transaction modified it, and that the transition from `OLD` to `NEW` is atomic from the perspective of other concurrent transactions. Option A is incorrect because `OLD` values are specific to the statement's execution, not the entire transaction's start. Option B is incorrect as `OLD`/`NEW` in `RETURNING` is a distinct feature from triggers, which have their own internal `OLD`/`NEW` records. Option D is incorrect; `OLD` values are directly captured, not reverse-engineered, which is a limitation of some pre-PG18 workarounds like CTEs.

Q: An application uses an `UPSERT` statement (`INSERT ... ON CONFLICT DO UPDATE ...`) to track user login statistics, incrementing `login_count` and updating `last_login`. The application needs to know if a new user record was created or an existing one was updated, and in the latter case, retrieve the `previous_login_count` and `current_login_count`. Which `RETURNING` clause and application logic correctly achieves this, and what would be the expected output for a newly inserted user?

B. `RETURNING OLD.login_count, NEW.login_count;` For a new user, `OLD.login_count` would be `NULL`. — Option B is correct. In an `UPSERT` statement, when the `INSERT` branch executes (i.e., no conflict occurred, and a new row was created), there is no 'old' row, so `OLD` columns will be `NULL`. This provides a clear way for the application to distinguish between an insert and an update (`OLD.login_count IS NULL` for an insert, non-NULL for an update). Option A is incorrect because `OLD.login_count` is `NULL` for an insert, not `0`. Option C is incorrect because `RETURNING OLD.login_count` *does* provide the previous count and allows distinguishing the operation. Option D is incorrect because the primary benefit of `OLD`/`NEW` in `RETURNING` is to get both values in a single atomic statement, avoiding the need for a separate `SELECT`.

What this module covers: PostgreSQL 18 introduces OLD and NEW aliases in the RETURNING clause of UPDATE and DELETE statements. This sounds like a minor syntactic addition. It is not. It eliminates entire classes of application-level race conditions that previously required either two round trips, advisory locks, or explicit table locks to handle safely. This module explains the problem, the pre-18 workarounds and their costs, and the precise semantics of OLD/NEW in RETURNING.

The Problem: Atomic Read-Modify-Write

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

Read the current value of a row
Compute a new value based on the old value
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

Scenario	Before PG18	After PG18
Get old value after UPDATE	SELECT FOR UPDATE + UPDATE (2 RTT)	`RETURNING OLD.column` (1 RTT)
Conditional update + decision	2 RTT + lock	1 RTT, 0 rows = condition failed
Detect concurrent modification	Advisory locks or version check loop	`RETURNING OLD.version` in one statement
Audit log without trigger	Trigger (prevents HOT)	CTE with RETURNING OLD into audit table
State machine transition	SELECT FOR UPDATE + check + UPDATE	`WHERE status = 'pending' RETURNING OLD.status`
Soft delete with profile	SELECT + UPDATE (2 RTT)	`RETURNING OLD.*` (1 RTT)
UPSERT: insert vs update	Check affected rows + re-query	`RETURNING OLD.column IS NULL` → was insert

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 →

Knowledge Check

A financial application needs to atomically debit an account, but also requires the account's balance *before* and *after* the debit for auditing and to determine if the transaction was successful (i.e., sufficient funds existed). The application must minimize database round trips and avoid deadlocks in a high-concurrency environment. Which approach, leveraging PostgreSQL 18 features, provides the most robust and performant solution?

A Senior Software Engineer is designing a critical state machine transition for a jobs table, where a job can only move from pending to processing if it hasn't already been claimed by another worker. They need to ensure atomicity and retrieve the previous_status and current_status in a single database interaction. They are considering RETURNING OLD.status, NEW.status in their UPDATE statement. What is the fundamental concurrency guarantee provided by OLD values in this PostgreSQL 18 RETURNING clause?

An application uses an UPSERT statement (INSERT ... ON CONFLICT DO UPDATE ...) to track user login statistics, incrementing login_count and updating last_login. The application needs to know if a new user record was created or an existing one was updated, and in the latter case, retrieve the previous_login_count and current_login_count. Which RETURNING clause and application logic correctly achieves this, and what would be the expected output for a newly inserted user?

Test your knowledge with more question sets

PreviousModule A-8: PostgreSQL 18: Asynchronous I/O and What It Changes Next Module A-10: Replication, High Availability, and the CAP Trade-offs

Discussion

Join the discussion

Loading comments...