Thread: How to run a task continuously in the background

A cron job will only run once a minute, not wake up every second.  But you could write a PHP program that does a one-second sleep before checking if there's something to do, and a batch job that runs periodically to see if the PHP program is running, and if not, launch it. 

That's how I handle a job that opens a tunnel from our PostgreSQL server to a MySQL server running at AWS so we can synchronize data between those two database servers.  It dies periodically for reasons we've never figured out, so every 5 minutes I check to make sure it's running. 

On 12/07/2019 08:08, lilu wrote:
>
>> 在 2019年7月12日，下午1:06，Dirk Mika <Dirk.Mika@mikatiming.de
>> <mailto:Dirk.Mika@mikatiming.de>> 写道：
>>
>> A cron job will only run once a minute, not wake up every second.
>> Right, that’s an issue.
>> But you could write a PHP program that does a one-second sleep before
>> checking if there's something to do, and a batch job that runs
>> periodically to see if the PHP program is running, and if not, launch
>> it.
>> I would like to avoid external programs if possible. In the current
>> Oracle environment, there are potentially multiple schemas on a
>> server in which processing can be active. And processing can be
>> started, monitored and stopped from a client application. And only
>> for the schema with which the application is connected.
>> BR
>> Dirk


But using a specific program that is good at its job is the unix way and
better for it imho. If you have a good scheduler why re-write another
one into every application?


Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852
58031687 | Toronto: +1 647 503 2848
 
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee
youmust not use or disclose such information, instead please report it to
admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The
ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here
https://www.manifest.co.uk/legal/for further information.
 

On Fri, Jul 12, 2019 at 7:06 AM Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>
>
>
> A cron job will only run once a minute, not wake up every second.
>
>
>
> I would like to avoid external programs if possible. In the current Oracle environment, there are potentially
multipleschemas on a server in which processing can be active. And processing can be started, monitored and stopped
froma client application. And only for the schema with which the application is connected. 
>

Creating a background worker that invokes a stored procedure once per
second? <https://www.postgresql.org/docs/11/bgworker.html>
But this is not so simple to put in place.

Otherwise pg_cron with a function that performs a pg_sleep of one
second in a loop.

Anyway, it seems to me you are better refactoring your solution: it
seems you need to process data when _new data_ comes, not once per
second, so it sounds to me like a trigger could solve the problem.

Luca

On Tue, Jul 16, 2019 at 7:32 AM Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
> It's not really important that the job runs once a second, but that it starts immediately when I want it to.
>
> If I start a job with pg_cron, it will not be executed until the next full minute at the earliest.
>
> The processing of the data via a job is deliberately chosen so as to separate the insertion of the data from their
processing.

So, as far as I understand, you want asynchronously processing data
with a process that can be started manually and/or periodically.
I'm probably unable to see what is the goal, but I would go for a
combined solution:
1) a trigger that notifies an external process
<https://www.postgresql.org/docs/current/sql-notify.html>
2) the process runs when notified (by the trigger) or when started
manually or when started by pg_cron (one per minute).

Of course the process is "internal", so something like a stored
procedure (at least as entry point).
The problem with such solution is about race conditions (what if you
manually start something that is already running?), but I guess you
had this problem on the oracle side too.

Hope this helps.
Luca

We used a trigger that called pg_notify (https://www.postgresql.org/docs/9.5/sql-notify.html​) and then had another

process that LISTENed for notifications.

On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
> That is basically still my main question. How do I start a background job (e.g. a function) which waits by polling or
LISTEN/ NOTIFY for records in a table to be processed.
 


You will have a trigger that, once new tuples are created (or older
update and so on) issues a NOTIFY.
Somewhere (within PostgreSQL or outside it) there will be a process
that issued a LISTEN and is locked until a notify comes in. Then it
does process whatever you need to do.
As an example your trigger function will be something like
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter09/Chapter09_Listing05.sql>
and your listening process will be something like
<https://github.com/fluca1978/PostgreSQL-11-Quick-Start-Guide/blob/master/Chapter09/Chapter09_Listing09.perl>.

This makes your processing fully asynchronous, and with some tune
allows you to decide the start/stop/resume policy as you need/wish.

Besides, it is quite hard for me to get to the point where you need to
check for new data every second, and therefore why you are not
satisfied with pg_cron or stuff like that.

Luca

On 17/07/19, Luca Ferrari (fluca1978@gmail.com) wrote:
> On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
> > That is basically still my main question. How do I start a
> > background job (e.g. a function) which waits by polling or LISTEN /
> > NOTIFY for records in a table to be processed.
> 
> You will have a trigger that, once new tuples are created (or older
> update and so on) issues a NOTIFY.
> Somewhere (within PostgreSQL or outside it) there will be a process
> that issued a LISTEN and is locked until a notify comes in. Then it
> does process whatever you need to do.

We make extensive use of postgresql 'contacting' an external process,
but our use case involves many databases in a cluster rather than many
schemas. Also we don't have to deal with cancelling the external
process. We chose this architecture to avoid many postgresql
connections for LISTEN/NOTIFY.

We use a pgmemcache interface trigger to update memcached with a
notification of the database holding items to be flushed. A python
looping process running under supervisord checks memcache for items to
be dealt with, and does so in a serial manner.

For the use case mentioned a per-schema process may be required or a
sub-process/thread created, which could check perhaps for memcache to
signal cancellation of processing for the schema. I guess one might then
have thread locking/cancellation issues to resolve.

Rory

On 2019-07-17 12:34:41 +0100, Rory Campbell-Lange wrote:
> We make extensive use of postgresql 'contacting' an external process,
> but our use case involves many databases in a cluster rather than many
> schemas. Also we don't have to deal with cancelling the external
> process. We chose this architecture to avoid many postgresql
> connections for LISTEN/NOTIFY.
>
> We use a pgmemcache interface trigger to update memcached with a
> notification of the database holding items to be flushed.

Memcached is a cache. It will delete old items if storage is full (or if
they expire). Is this not a problem in your case or did you make sure
that this cannot happen?

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

On 19/07/19, Peter J. Holzer (hjp-pgsql@hjp.at) wrote:
> On 2019-07-17 12:34:41 +0100, Rory Campbell-Lange wrote:
> > We make extensive use of postgresql 'contacting' an external process,
> > but our use case involves many databases in a cluster rather than many
> > schemas. Also we don't have to deal with cancelling the external
> > process. We chose this architecture to avoid many postgresql
> > connections for LISTEN/NOTIFY.
> > 
> > We use a pgmemcache interface trigger to update memcached with a
> > notification of the database holding items to be flushed.
> 
> Memcached is a cache. It will delete old items if storage is full (or if
> they expire). Is this not a problem in your case or did you make sure
> that this cannot happen?

We have a few hundred databases in a cluster, and the notifications are
simply the database name and the value "1" when the database queue
requires processing. With only the database name as key memory use
memcached is bounded. There is a potential race condition between the
queue processor processing the queue before setting the value to "0" and
more queue items landing, but that is not an issue for our use case.

Rory