0

I have a table my_table in PostgreSQL v 12 which has two fields called id and status. I need to write a stored procedure updateStatus which has three parameters (id, oldStatus and newStatus). It should update the row's status to newStatus only if it is in oldStatus at the moment and (do something else otherwise like throwing an exception). The procedure should work well in concurrent mode. What is the best way to achieve this? I thought of the way suggested here: Atomic UPDATE .. SELECT in Postgres

Is it OK? Or perhaps there is an easier way to achieve the same effect?

LOOP
   UPDATE my_table
   SET    status = _new_status_
   WHERE  id = (SELECT id FROM my_table WHERE id = _id_ status = _old_status
                FOR UPDATE SKIP LOCKED);  -- pg 9.5

   CONTINUE WHEN FOUND; 

   UPDATE my_table
   SET    status = _new_status_
   WHERE  id = (SELECT id FROM my_table WHERE id = _id_ and status = _old_status
                FOR UPDATE);

   EXIT WHEN NOT FOUND;  -- throw some error perhaps etc
END LOOP;
gisly
  • 673
  • 1
  • 8
  • 30
  • Your WHERE clause seems overly complicated. A simple `where id = _id_ and status = _old_status` seems enough - unless `id` isn't a unique column –  Jan 05 '22 at 10:43
  • I've removed the `limit 1` clause but do I need those for update and skip locked clauses? – gisly Jan 05 '22 at 11:33
  • 1
    This is a traditional implementation of Optimistic Locking. There's plenty of documentation about it, and at least three strategies to implement it. What seems to be the problem? – The Impaler Jan 05 '22 at 12:17
  • The problem is to choose the best way. I know what I would have done in the Oracle world but I do not know the best way do it in the PostgreSQL world. If the "select for update" way is OK, I'll stick to it as it's the most readable one, I think – gisly Jan 05 '22 at 12:39
  • 1
    No one can tell you *the best way* because what is best in my environment may not be so in yours. The **only** way for you to know which *is best* is to test them in your environment. Keep in mind however that the best way may change over time and volume. – Belayer Jan 05 '22 at 20:57

0 Answers0