From: | "Weatherby,Gerard" <gweatherby(at)uchc(dot)edu> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to run a task continuously in the background |
Date: | 2019-07-16 11:58:33 |
Message-ID: | 1563278313157.67005@uchc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <Dirk(dot)Mika(at)mikatiming(dot)de>
Sent: Tuesday, July 16, 2019 1:32 AM
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: How to run a task continuously in the background
Creating a background worker that invokes a stored procedure once per
second? <https://www.postgresql.org/docs/11/bgworker.html<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_11_bgworker.html&d=DwMGaQ&c=EZxp_D7cDnouwj5YEFHgXuSKoUq2zVQZ_7Fw9yfotck&r=f-X3XMK2s9o3QbCr8_n2sYw801ckLPRVRmiIoBpOKHg&m=--Q0KrfGCflvz0nQ-nVc0aEVt7HWE4zo7tjxzoXIKTg&s=Vem581EECRyjgEXTrLv2sXtILmQGDJaKvWQ3XWy8OBQ&e=>>
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
[cid:image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png]
mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany
fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
[cid:CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg]<https://urldefense.proofpoint.com/v2/url?u=https-3A__youtu.be_qfOFXrpSKLQ&d=DwMGaQ&c=EZxp_D7cDnouwj5YEFHgXuSKoUq2zVQZ_7Fw9yfotck&r=f-X3XMK2s9o3QbCr8_n2sYw801ckLPRVRmiIoBpOKHg&m=--Q0KrfGCflvz0nQ-nVc0aEVt7HWE4zo7tjxzoXIKTg&s=h0u0odE7epQmXCwzTHla7eVNtemb4UUzjkdE1tLALcw&e=>
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Voras | 2019-07-16 16:29:01 | Why no CREATE TEMP MATERIALIZED VIEW ? |
Previous Message | Emanuel Araújo | 2019-07-16 11:41:51 | Re: disable and enable trigger all when a foreign keys |