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

On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:

>
> However there is a problem: I can not use a "single-instance" cronjob to
> run words_expire_games hourly.
>

​Setup a cron job that invokes the servlet - probably via "curl"

My question is if I should ensure that only 1 servlet runs the custom
> PL/pgSQL function by using "synchronized" in Java as I do it right now:
>

​Probably not. UPDATE takes out a lock that will prevent other updates
from acting on the same records concurrently.​

> Or if maybe there is some condition (maybe "UPDATE SKIP LOCKED"?) I could
> add to my custom function copy-pasted below? -
>

Why are you trying random syntax that isn't documented?

https://www.postgresql.org/docs/9.6/static/sql-update.html​

UPDATE words_games
> SET finished = CURRENT_TIMESTAMP
> WHERE finished IS NULL
>

​That should be sufficient. Do you have any examples that show it is not?

In short, one of the main reasons for "UPDATE RETURNING" is so that one
needn't determine the records to be updated separately from the actual act
of updating. Instead you update first and then capture the results for
subsequent use.

David J.