76

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular.

I want a database query to change the status of a standby - JUST ONE - and return the server IP that is to be used. The pick can be arbitrary: since the status of the server changes with the query, it doesn't matter which standby is selected.

Is it possible to limit my query to just one update?

Here is what I have so far:

UPDATE server_info SET status = 'active' 
WHERE status = 'standby' [[LIMIT 1???]] 
RETURNING server_ip;

Postgres doesn't like this. What could I do differently?

  • Just pick the server in code and add it as a where constrained. This also allows you to have additional conditions (oldest, newest, most recent alive, least loaded, same dc, different rack, least errors) checked first anyway. Most failover protocols require some form of determinism anyway. – eckes Jun 7 at 15:49
  • @eckes That's an interesting idea. In my case "picking the server in code" would have meant first reading a list of available servers from the db, and then updating a record. Because many instances of the application could perform this action, there's a race condition and an atomic operation is needed (or was 5 years ago). The pick did not need to be deterministic. – vastlysuperiorman Jun 7 at 18:03
124

Without concurrent write access

Materialize a selection in a CTE and join to it in the FROM clause of the UPDATE.

WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active' 
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING server_ip;

I originally had a plain subquery here, but that can sidestep the LIMIT for certain query plans as Feike pointed out:

The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT, e.g.:

 Update on buganalysis [...] rows=5
   ->  Nested Loop
         ->  Seq Scan on buganalysis
         ->  Subquery Scan on sub [...] loops=11
               ->  Limit [...] rows=2
                     ->  LockRows
                           ->  Sort
                                 ->  Seq Scan on buganalysis

Reproducing test case

The way to fix the above was to wrap the LIMIT subquery in its own CTE, as the CTE is materialized it will not return different results on different iterations of the nested loop.

Or use a lowly correlated subquery for the simple case with LIMIT 1. Simpler, faster:

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         )
RETURNING server_ip;

With concurrent write access

Assuming default isolation level READ COMMITTED for all of this. Stricter isolation levels (REPEATABLE READ and SERIALIZABLE) may still result in serialization errors. See:

Under concurrent write load, add FOR UPDATE SKIP LOCKED to lock the row to avoid race conditions. SKIP LOCKED was added in Postgres 9.5, for older versions see below. The manual:

With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table.

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING server_ip;

If there is no qualifying, unlocked row left, nothing happens in this query (no row is updated) and you get an empty result. For uncritical operations that means you are done.

However, concurrent transactions may have locked rows, but then don't finish the update (ROLLBACK or other reasons). To be sure run a final check:

SELECT NOT EXISTS (
   SELECT 1
   FROM   server_info
   WHERE  status = 'standby'
   );

SELECT also sees locked rows. Wile that doesn't return true, one or more rows are still being processed and transactions could still be rolled back. (Or new rows have been added meanwhile.) Wait a bit, then loop the two steps: (UPDATE till you get no row back; SELECT ...) until you get true.

Related:

Without SKIP LOCKED in PostgreSQL 9.4 or older

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

Concurrent transactions trying to lock the same row are blocked until the first one releases its lock.

If the first was rolled back, the next transaction takes the lock and proceeds normally; others in the queue keep waiting.

If the first committed, the WHERE condition is re-evaluated and if it's not TRUE any more (status has changed) the CTE (somewhat surprisingly) returns no row. Nothing happens. That's the desired behavior when all transactions want to update the same row.
But not when 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
SET    status = 'active' 
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         AND    pg_try_advisory_xact_lock(id)
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;

This way, the next row 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.

id being any unique bigint column (or any type with an implicit cast like int4 or int2).

If advisory locks are in use for multiple tables in your database concurrently, disambiguate with pg_try_advisory_xact_lock(tableoid::int, id) - id being a unique integer here.
Since tableoid is a bigint quantity, it can theoretically overflow integer. If you are paranoid enough, use (tableoid::bigint % 2147483648)::int instead - leaving a theoretical "hash collision" for the truly paranoid ...

Also, Postgres is free to test WHERE conditions in any order. It could test pg_try_advisory_xact_lock() and acquire a lock before status = 'standby', which could result in additional advisory locks on unrelated rows, where status = 'standby' is not true. Related question on SO:

Typically, you can just ignore this. To guarantee that only qualifying rows are locked, you could nest the predicate(s) in a CTE like above or a subquery with the OFFSET 0 hack (prevents inlining). Example:

Or (cheaper for sequential scans) nest the conditions in a CASE statement like:

WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

However the CASE trick would also keep Postgres from using an index on status. If such an index is available, you don't need extra nesting to begin with: only qualifying rows will be locked in an index scan.

Since you cannot be sure that an index is used in every call, you could just:

WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

The CASE is logically redundant, but it servers the discussed purpose.

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(). The manual:

Once acquired at session level, an advisory lock is held until explicitly released or the session ends.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.