Without concurrency
Use a subquery in the FROM clause of the UPDATE:
UPDATE server_info s
SET status = 'active'
FROM (
SELECT server_ip -- your pk column or any (set of) unique column(s)
FROM server_info
WHERE status = 'standby'
LIMIT 1 -- arbitrary pick (cheapest)
) sub
WHERE s.server_ip = sub.server_ip
RETURNING server_ip;
With concurrency
For concurrent write load, add FOR UPDATE to the subquery to lock and avoid race conditions:
UPDATE server_info s
SET status = 'active'
FROM (
SELECT server_ip
FROM server_info
WHERE status = 'standby'
LIMIT 1
FOR UPDATE
) sub
WHERE s.server_ip = sub.server_ip
RETURNING server_ip;
Concurrent SELECT .. FOR UPDATE block until the first transaction releases the lock.
If the first was rolled back, the next transaction takes the lock, the rest keeps waiting.
If the first committed, the WHERE conditions is not TRUE any more (status has changed) and the subquery (somewhat surprisingly) returns no row after re-testing the condition. Nothing happens. That would be the desired behavior if all transactions want to update the same row.
But not if each transaction wants to update the next row. And since we just want to update an arbitrary (or random) row, there is no point in waiting at all.
We can unblock the situation with the help of advisory locks:
UPDATE server_info s
SET status = 'active'
FROM (
SELECT server_ip
FROM server_info
WHERE status = 'standby'
AND pg_try_advisory_xact_lock(id)
LIMIT 1
FOR UPDATE
) sub
WHERE s.server_ip = sub.server_ip
RETURNING server_ip;
This way, the next row that is not locked yet will be updated. Each transaction gets a fresh row to work with. I had help from Czech Postgres Wiki for this trick.
If the command is part of a long transaction, consider session-level locks that can be (and have to be) released manually. So you can unlock as soon as you are done with the locked row: pg_try_advisory_lock() and pg_advisory_unlock().
Per documentation:
Once acquired at session level, an advisory lock is held until
explicitly released or the session ends.
Related:
