Workaround to return a value when an update failed

I am trying to find a way to return the current the value of cell used as a condition for an update when that very same update happens to fail:

UPDATE public.streams
SET version = 1
WHERE id = '0171c6f4-7c44-43dd-ae85-530a77608d8e'::UUID
AND version = 0
RETURNING version

The problem of the code above is that RETURNING only works if the UPDATE succeeds, and I would like to get that regardless of the outcome (actually I’d like to get the value of version along with the outcome (as TRUE or FALSE), that is if the update failed or not).

Answer

In optimistic row locking, if you try to update a row already changed by another thread the update will fail, of course.

And now the current version of the row is actually another row, not the same one (at least for the database).

As a general rule you have two options:

• Consider your changes stale, and lose them by rolling back the whole transaction.

• Refresh the data and try to recover the new changes by merging both new versions of it, somehow.

It seems to me that you are trying to implement the latter. If this is the case, you’ll need to refresh the data, and that necessarily mean to execute a brand new SELECT… separate from the failed UPDATE you tried. I don’t think you can combine both into a single query.