Optimistic Lock in UPDATE query

Question

I want to execute a single update query and have the result indicate to me in some way between three possible outcomes; successful, version conflict or entity not found

Example UPDATE query:

update vessel_clearance vc
    set status = "blah",
        last_updated_at = {new_ts},
        expiry = {some_time}
    where vc.id = {vesselclearanceid}
        and vc.last_updated_at = {current_version_ts}

Is this possible? I have experimented with the WITH clause but have been unsuccessful so far.

Answer 1

Assuming vessel_clearance.id is the PK, so below UPDATE and SELECT can only ever find a single row.

Here is a single statement to achieve your objective:

WITH upd AS (
   UPDATE vessel_clearance vc
   SET    status = "blah"
        , last_updated_at = {new_ts}
        , expiry = {some_time}
   WHERE  vc.id = {vesselclearanceid}
   AND    vc.last_updated_at = {current_version_ts}
   RETURNING 1  -- see below
   )
SELECT COALESCE(
     (SELECT 'UPDATE_APPLIED'   FROM upd)
   , (SELECT 'VERSION_CONFLICT' FROM vessel_clearance WHERE id = {vesselclearanceid})
           , 'ENTITY_NOT_FOUND'
   ) AS result;

RETURNING 1 because it only matters that a row is returned. My query does not care about returned values.

The row is only locked if the UPDATE actually happens. SELECT does not lock the row in Postgres. So much for "Optimistic Locking".
The outer SELECT returns 'UPDATE_APPLIED' in this case.

If, and only if, it doesn't happen, another SELECT checks for the existence of the given ID. If found, the only remaining answer 'VERSION_CONFLICT' is returned.

Else we get 'ENTITY_NOT_FOUND'.

Notably, this is a single statement. CTEs and the outer SELECT, all see the same snapshot of the table. So there is no race condition.