Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?

"FOR UPDATE" is part of "SELECT" not part of "UPDATE".

You can select the rows "for update" which will lock those rows.  You
can then loop over the the results of the 'select' to do the rest of
your logic.

Be careful doing this if other things are also updating these rows.
With SKIP LOCKED you can skip over rows that should have been selected
but were not because another process was updating data that was
unrelated.   Without SKIP LOCKED you risk deadlock if you are
selecting multiple rows.



On Mon, Jul 10, 2017 at 3:22 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> I have tried:
>
>   FOR _gid, _loser, _winner IN
>         UPDATE  words_games
>         SET     finished = CURRENT_TIMESTAMP
>         WHERE   finished IS NULL
>         AND     played1 IS NOT NULL
>         AND     played2 IS NOT NULL
>         AND     (played1 < CURRENT_TIMESTAMP - INTERVAL '24 hours'
>         OR       played2 < CURRENT_TIMESTAMP - INTERVAL '24 hours')
>         RETURNING
>                 gid,
>                 CASE WHEN played1 < played2 THEN player1 ELSE player2 END,
>                 CASE WHEN played1 < played2 THEN player2 ELSE player1 END
>         FOR UPDATE SKIP LOCKED
>   LOOP
>     ...
>   END LOOP;
>
> but this fails with:
>
>     ERROR:  syntax error at or near "FOR"
>
> I have also described my problem at SO:
>
>
> https://stackoverflow.com/questions/45015368/how-to-handle-simultaneous-for-in-update-returning-loops
>
> Thank you
> Alex