Postgres UPDATE with ORDER BY, how to do it?

Question

I need to do a Postgres update on a collection of records & I'm trying to prevent a deadlock which appeared in the stress tests.

The typical resolution to this is to update records in a certain order, by ID for example - but it seems that Postgres doesn't allow ORDER BY for UPDATE.

Assuming I need to do an update, for example:

UPDATE BALANCES WHERE ID IN (SELECT ID FROM some_function() ORDER BY ID); 

results in deadlocks when you run 200 queries concurrently. What to do?

I'm looking for a general solution, not case-specific workarounds like in UPDATE with ORDER BY

It feels that there must be a better solution than writing a cursor function. Also, if there's no better way, how would that cursor function optimally look like? Update record-by-record

Answer 1

As far as I know, there's no way to accomplish this directly through the UPDATE statement; the only way to guarantee lock order is to explicitly acquire locks with a SELECT ... ORDER BY ID FOR UPDATE, e.g.:

UPDATE Balances SET Balance = 0 WHERE ID IN (   SELECT ID FROM Balances   WHERE ID IN (SELECT ID FROM some_function())   ORDER BY ID   FOR UPDATE ) 

This has the downside of repeating the ID index lookup on the Balances table. In your simple example, you can avoid this overhead by fetching the physical row address (represented by the ctid system column) during the locking query, and using that to drive the UPDATE:

UPDATE Balances SET Balance = 0 WHERE ctid = ANY(ARRAY(   SELECT ctid FROM Balances   WHERE ID IN (SELECT ID FROM some_function())   ORDER BY ID   FOR UPDATE )) 

(Be careful when using ctids, as the values are transient. We're safe here, as the locks will block any changes.)

Unfortunately, the planner will only utilise the ctid in a narrow set of cases (you can tell if it's working by looking for a "Tid Scan" node in the EXPLAIN output). To handle more complicated queries within a single UPDATE statement, e.g. if your new balance was being returned by some_function() alongside the ID, you'll need to fall back to the ID-based lookup:

UPDATE Balances SET Balance = Locks.NewBalance FROM (   SELECT Balances.ID, some_function.NewBalance   FROM Balances   JOIN some_function() ON some_function.ID = Balances.ID   ORDER BY Balances.ID   FOR UPDATE ) Locks WHERE Balances.ID = Locks.ID 

If the performance overhead is an issue, you'd need to resort to using a cursor, which would look something like this:

DO $$ DECLARE   c CURSOR FOR     SELECT Balances.ID, some_function.NewBalance     FROM Balances     JOIN some_function() ON some_function.ID = Balances.ID     ORDER BY Balances.ID     FOR UPDATE; BEGIN   FOR row IN c LOOP     UPDATE Balances     SET Balance = row.NewBalance     WHERE CURRENT OF c;   END LOOP; END $$