How to setup a cron in pg_cron to run every 90 seconds

Question

I want to a cron in database using pg_cron to update the value every 90 seconds.

i am looking at this solutions Can a cron job run every 'x' seconds but this is not correct way.

also i am looking at Cron job to run a PHP script every 90 seconds between 5AM and 10PM? but runing two crons is not a viable solution if i got with this solutions and then i have to find a way to sleep the database jon for 90 secods.

If anyone has any idea please suggest me.

I want to run a cron that will run every secods in the database and it will update the value in a certain table after querying it.

1) How critical is it that it be exactly 90 seconds? 2) I could see calling a function every minute that looks at a status table for the last update time. If the time to next update is > 1 minute don't do anything and wait to the next call. If <= 1 min then capture the remaining time and use it as value to [pg_sleep](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-DELAY). Call `pg_sleep( — Adrian Klaver, Jan 16 '23 at 17:13

Adrian Klaver · Answer 1 · 2023-01-16T21:27:53.177

An example of a function that I mentioned in comment:

create table update_status(id
insert into update_status values (1, now()); integer, last_update timestamp);

CREATE OR REPLACE FUNCTION public.update_check()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    _last_update timestamp;
    _update_togo interval;
    _update_interval interval := '20 secs ';
    _sleep_interval interval;
    _select_val varchar;
BEGIN
    select into _last_update  last_update from update_status where id = 1;
    select into _update_togo now() - _last_update;
    if _update_togo < _update_interval then
        RAISE NOTICE 'Now start %', clock_timestamp();
        RAISE NOTICE 'Interval %', _update_togo;
        select into _sleep_interval _update_interval - _update_togo;
        RAISE NOTICE 'Sleep interval %', _sleep_interval;
        perform pg_sleep_for(_sleep_interval);
        select into _select_val 'Run at';
        RAISE NOTICE '% %', _select_val,clock_timestamp();
    end if;
END;
$function$
;

update  update_status set last_update = now();

select update_check();
NOTICE:  Now start 2023-01-16 11:53:15.936465-08
NOTICE:  Interval 00:00:07.314329
NOTICE:  Sleep interval 00:00:12.685671
NOTICE:  Run at 2023-01-16 11:53:28.634306-08
 update_check 
--------------

This is a simplistic proof of concept and would need more work to deal with exceptions.

How to setup a cron in pg_cron to run every 90 seconds

1 Answers1