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.Mika@mikatiming.de> Sent: Tuesday, July 16, 2019 1:32 AM To: pgsql-general@lists.postgresql.org Subject: Re: How to run a task continuously in the background
Creating a background worker that invokes a stored procedure once per
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.