Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?
Date
Msg-id CAKFQuwb4EC8e4DwA0J5qujUUHJ5kNd-6Mp3DESPqVDOFCibGVQ@mail.gmail.com
Whole thread Raw
In response to [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?  (Alexander Farber <alexander.farber@gmail.com>)
Responses Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Tree view
On Mon, Jul 10, 2017 at 7:32 AM, Alexander Farber <alexander.farber@gmail.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.

pgsql-general by date:

Previous
From: Brian Dunavant
Date:
Subject: Re: [GENERAL] How to handle simultaneous FOR-IN UPDATE-RETURNING loops?
Next
From: Tom Lane
Date:
Subject: Re: [GENERAL] Changing collate & ctype for an existing database