How to run a task continuously in the background

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to run a task continuously in the background

Dirk Mika

Hi,

 

another oracle -> postgreSQL migration question.

 

I have a database in which a job runs continuously to perform tasks that are inserted into a table in the form of records.

The current Oracle implementation is to use DBMS_JOB to start a job (to be precise, there can be multiple jobs working in parallel) that goes through the following loop:

  • is there anything to do?
    • Yes -> Do Tasks
    • No  -> 1 second sleep
  • Was the EXIT command sent to me?
    • Yes -> Exit loop.

 

The question now is how do I start in PostgreSQL a background task that meets the following requirements:

  1. It must continue to run even if the connection in which the task was started is terminated.
  2. I need to be able to check if a background task exists.
  3. I need to be able to finish the background task without explicitly sending the EXIT command (see above) to the task.

 

I've already looked at the pg_background extension, but I don't think it meets either requirement 2 or requirement 3. But maybe I just didn’t look hard enough. And with regard to requirement 1, I'm not sure that's guaranteed.

 

Any suggestions on how this might be realized. Preferably without an external application.

 

BR

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

RE: How to run a task continuously in the background

Steven Winfield
pg_cron, perhaps?
https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/

I _think_ it ticks all three of your boxes.


Steve.




This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice
Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Michael Nolan
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. 
--
Mike Nolan

On Thu, Jul 11, 2019 at 5:44 AM Steven Winfield <[hidden email]> wrote:
pg_cron, perhaps?
https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/

I _think_ it ticks all three of your boxes.


Steve.




This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice
Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Alan Hodgson-3
On Thu, 2019-07-11 at 11:19 -0500, Michael Nolan wrote:
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. 

If you run such a job under systemd you can tell systemd to automatically restart it if it dies.

Alternate, the application monit is an older means of monitoring and restarting persistent processes, complete with email notifications.

This is quite a common system administration task. No need to roll your own.
Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika
In reply to this post by Steven Winfield

 

pg_cron, perhaps?
https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/

I _think_ it ticks all three of your boxes.

I also checked pg_cron, but this has the disadvantage that it allows starting a job at most once a minute. On the other hand, the job runs as long as there are records to process. And during this time nothing should be queued again.

DIrk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika
In reply to this post by Michael Nolan

 

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

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Brent Wood-3
You could perhaps tweak the code for pg_cron or pg_agent for a custom solution that supports a finer time resolution?

On Friday, July 12, 2019, 5:06:42 PM GMT+12, Dirk Mika <[hidden email]> wrote:


 

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

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

lilu
In reply to this post by Dirk Mika

在 2019年7月12日,下午1:06,Dirk Mika <[hidden email]> 写道:

 
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
-- 
Dirk Mika
Software Developer

<image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png>

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197 
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884 
Geschäftsführer: Harald Mika, Jörg Mika

<CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg>


Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Tim Clarke-3

On 12/07/2019 08:08, lilu wrote:

>
>> 在 2019年7月12日,下午1:06,Dirk Mika <[hidden email]
>> <mailto:[hidden email]>> 写道:
>>
>> 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 5803 1687 | 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 you must not use or disclose such information, instead please report it to [hidden email]<mailto:[hidden email]>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika

 

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?

 

Well, the requirement is basically not job scheduling, but the continuous execution of a task that is to be started and stopped and that can work autonomously.

 

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika
In reply to this post by Brent Wood-3

 

You could perhaps tweak the code for pg_cron or pg_agent for a custom solution that supports a finer time resolution?

 

I might take a look at that. But that would mean that I would have to deal a little with the extension API. ;-)

 

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

dinesh kumar
In reply to this post by Dirk Mika
Is runseven(extended version of pgbucket) is what you are looking for ?


runseven is still in beta, still require sometime to make it GA release.

You can refer more information about runseven here.

--Dinesh

On Fri, Jul 12, 2019 at 5:03 PM Dirk Mika <[hidden email]> wrote:

 

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?

 

Well, the requirement is basically not job scheduling, but the continuous execution of a task that is to be started and stopped and that can work autonomously.

 

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 



--
Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika

Is runseven(extended version of pgbucket) is what you are looking for ?

 

 

runseven is still in beta, still require sometime to make it GA release.

 

You can refer more information about runseven here.

 

Run7 looks very promising. The only drawback is that it is not controllable via SQL commands.

 

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Luca Ferrari-2
In reply to this post by Dirk Mika
On Fri, Jul 12, 2019 at 7:06 AM Dirk Mika <[hidden email]> 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 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.
>

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


Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika

Creating a background worker that invokes a stored procedure once per

But this is not so simple to put in place.

 

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.

 

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.

 

The processing of the data via a job is deliberately chosen so as to separate the insertion of the data from their processing.

If a trigger were to do this, the transaction in which the data is inserted would take longer. This is not intended.

It is common for many records to be inserted in a short time, but processing takes a little time. The application that inserts the data should however not be slowed down.

 

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Luca Ferrari-2
On Tue, Jul 16, 2019 at 7:32 AM Dirk Mika <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Weatherby,Gerard
In reply to this post by Dirk Mika

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.


-- 
Gerard Weatherby| Application Architect
NMRbox | Department of Molecular Biology and Biophysics | UConn Health
263 Farmington Avenue, Farmington, CT 06030-6406
Phone: 860 679 8484
uchc.edu 

From: Dirk Mika <[hidden email]>
Sent: Tuesday, July 16, 2019 1:32 AM
To: [hidden email]
Subject: Re: How to run a task continuously in the background
 

Creating a background worker that invokes a stored procedure once per

But this is not so simple to put in place.

 

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.

 

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.

 

The processing of the data via a job is deliberately chosen so as to separate the insertion of the data from their processing.

If a trigger were to do this, the transaction in which the data is inserted would take longer. This is not intended.

It is common for many records to be inserted in a short time, but processing takes a little time. The application that inserts the data should however not be slowed down.

 

Dirk

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika

 

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.

 

What kind of process is this? I'm assuming that this is an application written in C.

 

The advantage of LISTEN / NOTIFY is only that the process which should process data does not have to do polling, but is notified when there is something to do.

 

Dirk

 

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

Dirk Mika
In reply to this post by Luca Ferrari-2

 

So, as far as I understand, you want asynchronously processing data

with a process that can be started manually and/or periodically.

 

The process should be started manually and then run until it is stopped by a stop_job() function call. In Oracle there is a package which contains the functions engine.start_job() and engine.stop_job(). :-)

I only mentioned “periodic” because the current Oracle implementation polls to see if there is something to process and goes to sleep for a second when there is no data to process.

 

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

2) the process runs when notified (by the trigger) or when started

manually or when started by pg_cron (one per minute).

 

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.

 

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.

 

In the Oracle world, there is a maximum of one job that takes care of processing.

 

Dirk

 

--
Dirk Mika
Software Developer



mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika


 

lup
Reply | Threaded
Open this post in threaded view
|

Re: How to run a task continuously in the background

lup
In reply to this post by Dirk Mika


On Jul 17, 2019, at 1:26 AM, Dirk Mika <[hidden email]> wrote:

 

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.

 

What kind of process is this? I'm assuming that this is an application written in C.

 

The advantage of LISTEN / NOTIFY is only that the process which should process data does not have to do polling, but is notified when there is something to do.

 

Dirk

Also, the NOTIFY wouldn’t significantly extend the lifetime or impact of the trigger.

 

-- 
Dirk Mika
Software Developer

<image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png>

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197 
[hidden email]
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884 
Geschäftsführer: Harald Mika, Jörg Mika

<CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg>


12