Hi all,
I’d like to propose a declarative syntax addition for UPDATE and DELETE that supports optimistic locking directly in SQL: an ON NO ROWS clause that triggers an error if no rows are affected by the DML statement.
Problem
In many applications, particularly those using optimistic concurrency control, it’s essential to detect when an UPDATE or DELETE statement fails to affect any rows. Currently, this must be handled imperatively in PL/pgSQL:
UPDATE my_table SET ... WHERE id = $1 AND version = $2;
IF NOT FOUND THEN
RAISE EXCEPTION 'Row not found or version mismatch';
END IF;
This logic cannot be encapsulated declaratively inside a single SQL statement, making reuse in views, CTE chains, or SQL wrapper functions awkward.
Proposal
Introduce an optional clause:
UPDATE my_table SET ... WHERE ... ON NO ROWS RAISE;
DELETE FROM my_table WHERE ... ON NO ROWS RAISE;
which raises the standard NO_DATA_FOUND (P0002) error if no rows are affected by the DML statement.
This would complement INSERT ... ON CONFLICT by allowing UPDATE and DELETE to fail-fast declaratively when no matching rows are found — an extremely common pattern in modern applications.
Workaround Today
My current workaround relies on composing a RIGHT JOIN against a PG/PLSQL function that explicitly checks for the affected row count and throws an error:
WITH affected AS (
UPDATE my_table
SET ...
WHERE id = $1 AND version = $2
RETURNING 1 AS ok
)
SELECT *
FROM affected
RIGHT JOIN (SELECT check_row_count(count(ok)) FROM affected) checker ON true;
-- Where check_row_count(n int) is a PG/PLSQL function like:
CREATE OR REPLACE FUNCTION check_row_count(n int) RETURNS void AS $$
BEGIN
IF n = 0 THEN
RAISE EXCEPTION 'Row not found or version mismatch';
END IF;
END;
$$ LANGUAGE plpgsql;
This workaround has two key downsides:
- It defers the error until all statements are complete, which obscures causality and complicates error handling.
- It introduces syntactic and architectural overhead, masking the intent compared to a declarative ON NO ROWS clause.
Benefits
- Clean declarative syntax
- Encourages safer DML operations
- Makes optimistic locking idioms easier to write, read, and maintain
- Better tooling support for error handling in SQL-only use cases
- Aligns naturally with INSERT ... ON CONFLICT semantics
About Me
Since this is my first post, a little background .I spent 20 years as a product manager in the Oracle Database group, primarily working on XML and JSON technologies. These days I work almost exclusively with PostgreSQL. This idea grew out of real-world experience building a JSON-centric, SQL-wrapped application where declarative control flow is critical. My current stack uses a React front end and a lightweight Node/Express mid-tier that communicates with SQL functions managed in PostgreSQL 17. JSON is the primary interface between the frontend and the database, with business logic encapsulated in SQL functions — often using CTEs for clarity and atomicity. This architecture promotes separation of concerns and avoids tight coupling between the database and backend application logic.
Happy to discuss further or refine the idea. Thanks for considering it.
Best regards,
Mark Drake
