pg_cron
pg_cron copied to clipboard
citusdata
Add a way to run a job every x seconds
It would be useful to be able to run a job every few seconds. Pg_cron currently relies on battle-tested cron code, which works at a per-minute level. I'm a bit nervous about simply adding another column to the schedule without battle-tested logic for dealing with clock jumps etc. It doesn't necessarily make sense to schedule a job at a particular second, given that a process can easily skip past that second. However, it may be sufficient to just have the ability to schedule a job every x seconds from the start of the process without following a particular cron schedule.
A possible interface could be:
SELECT cron.schedule(interval '1 second', 'SELECT do_job()');
There are workarounds, but they are rather ugly, e.g.:
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE OR REPLACE FUNCTION do_every_second(run_time interval default '60 seconds')
RETURNS bool AS $do_every_second$
DECLARE
end_time timestamptz := now() + run_time;
BEGIN
WHILE now() < end_time LOOP
PERFORM FROM dblink('host=localhost port=5432 dbname=postgres',
'SELECT do_job()') AS (num_rows bigint);
PERFORM pg_sleep(1);
END LOOP;
RETURN true;
END;
$do_every_second$ LANGUAGE plpgsql;
SELECT cron.schedule('* * * * *', 'SELECT do_every_second()');
+1
Having sub-minute scheduling will give a major feature to the lib, as a lot of jobs are sub-second queries that make no sense to run every minute or it won't harm (or the opposite, would benefit) of running each x seconds.
+1
Would be great to have this feature out of the box since this is the only gap stopping us from using this extension at this point.
Another use case: tests. Without this, tests verifying that the extension is correctly set up need to sleep for at least a minute.
it would be nice to make an analog of the setTimeout function from javascript, with a single run in a few seconds
I believe one second intervals may be too fine grained for pg_cron to do well. If you want/need something like that then perhaps you could schedule a stored proc that runs each minute from pg_cron that sleeps for a second between each quick little thing it does.
On Sun, Jan 31, 2021 at 3:37 PM Vladimir Volek [email protected] wrote:
+1
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/citusdata/pg_cron/issues/6#issuecomment-770446565, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMWOHSTOWJ4R6LHAFXSXW3S4W5QHANCNFSM4CQNMIYA .
The suggested workaround needs a small tweak: now() is the start of the transaction so will not increment. The loop condition would need to be clock_timestamp() < end_time instead.
lightdb enterprise postgres is support second granularity and TZ, 100% comp open PG. and with no license limited. any user interested can considered. https://www.hs.net/lightdb/docs/html/pgcron.html
https://github.com/hslightdb/pg_cron，我们已经发布了功能，pg_cron--1.4-1--1.5.sql
Test your plug-in locally, and the result is not quite the same as expected
For example, insert every 30 seconds
CREATE TABLE public. test_ cron( id serial, pg12 timestamp );
select cron. schedule('*/30 * * * * *','insert into test_cron (pg12) values (now())');
[https://github.com/hslightdb/pg_cron，我们已经发布了功能，pg_cron--1.4-1--1.5.sql](https://github.com/hslightdb/pg_cron%EF%BC%8C%E6%88%91%E4%BB%AC%E5%B7%B2%E7%BB%8F%E5%8F%91%E5%B8%83%E4%BA%86%E5%8A%9F%E8%83%BD%EF%BC%8C%5Bpg_cron--1.4-1--1.5.sql%5D(https://github.com/hslightdb/pg_cron/blob/main/pg_cron--1.4-1--1.5.sql))
Test your plug-in locally, and the result is not quite the same as expected
For example, insert every 30 seconds
CREATE TABLE public. test_ cron( id serial, pg12 timestamp );
select cron. schedule('*/30 * * * * *','insert into test_cron (pg12) values (now())');
sorry, the developer forgot to push the latest code. fixed just now.
as above.
Any chance we'll see these two branches merged? Can hslightdb merge with the main pg_cron? Did citusdata reject the hslightdb branch?
