Add a way to run jobs once at a particular time

[marcocitus]

It would be useful to have a way to schedule a job once at a specific time.

SELECT cron.schedule(now(), 'SELECT do_job()');

[jamessewell]

This would be amazing - this way a trigger could call a job, meaning this extension could be used for scheduled and unscheduled jobs.

Combine this with logging to a table and naming a job (so I could name a trigger executed job with the trigger name, a scheduled job with the schedule name) and I think this becomes a very, very good multipurpose extension.

[Globik]

Is this feature still not implemented?

[whisper-bye]

+1

[exe-dealer]

I think its better to implement just cron.defer('SELECT do_job()') which should exec job in asyncronous fire-and-forget style. Scheduling task at specific time seems to be complicated and useless

[hslightdb]

lightdb enterprise postgresql is support this feature. and without any license limited. any user interested can consider https://www.hs.net/lightdb/docs/html/pgcron.html. it's released. anyone interested can get it. https://github.com/hslightdb/pg_cron

[tbussmann]

@hslightdb: In the link provided, I don't read anything about the ideas listed here. Neither "once at a specific time" nor "immediately". It this is ongoing development on your side, It would be great if you could upstream that set of patches.

[hslightdb]

@hslightdb: In the link provided, I don't read anything about the ideas listed here. Neither "once at a specific time" nor "immediately". It this is ongoing development on your side, It would be great if you could upstream that set of patches.

        -- Change to vacuum only once at the next 10:00:30am (East eight time zone)
        SELECT cron.schedule('nightly-vacuum', '30 0 10 * * *', 'VACUUM', 'single');
         schedule
        ----------
               45

we extended schedule function to add an extra param, 'single' means run once only. we will be push the patch later in this month.

[tbussmann]

cool, thanks for the hint, I missed that! Documentation through samples is sometimes hard to grasp ;)

[hslightdb]

we have update the Documentation and publish the feature, see previous.

[MichaelDBA]

so why not close this issue?

[tbussmann]

AFAIK this feature has only been implemented in a fork of this repository.

[niolap]

Possible option: you schedule a code that unscheduled your job by its name and run the desired function in the same block:

CREATE OR REPLACE FUNCTION run_once_async(command text, schedule text default '* * * * *') 
RETURNS bigint AS $$
DECLARE
  v_job_id bigint;
  v_job_name text;
BEGIN
  select to_char(ROUND(RANDOM()*1000000000),'999999999') into v_job_name;
  SELECT cron.schedule(v_job_name, schedule, CONCAT ('select cron.unschedule(''',v_job_name ,''');', ' ', command))
  INTO v_job_id;
RETURN v_job_id;
END;
$$ LANGUAGE plpgsql;

Then launch a one-time job:

select run_once_async($$select 'I run only once :)';$$);

[marcocitus]

When you only run jobs once you probably want some additional bookkeeping to make sure the job is actually executed (e.g. it is executed exactly/at least once). One approach is to do this outside of pg_cron, but have pg_cron drive the function.

DROP TABLE IF EXISTS once_queue, once_queue_log;

CREATE TABLE once_queue (
  job_id bigserial primary key,
  command text not null,
  schedule_time timestamptz default now(),
  attempts int default 0,
  max_attempts int default 10
);
CREATE INDEX ON once_queue (execution_time); 

CREATE TABLE once_queue_log (
  job_id bigint not null,
  attempt_number int not null,
  command text not null,
  execution_time timestamptz default now(),
  success bool not null,
  error_message text,
  primary key (job_id, attempt_number)
);
CREATE INDEX ON once_queue_log USING brin (execution_time);

CREATE OR REPLACE PROCEDURE run_once_jobs() 
LANGUAGE plpgsql
AS $$
DECLARE
  v_jobid bigint;
  v_command text;
  v_execution_time timestamptz;
  v_attempts int;
  v_success bool := false;
  v_error_message text := NULL;
BEGIN
  LOOP
    BEGIN
      SELECT job_id, command, attempts INTO v_jobid, v_command, v_attempts
      FROM once_queue WHERE schedule_time <= now() AND attempts < max_attempts
      FOR UPDATE SKIP LOCKED LIMIT 1;

      IF NOT FOUND THEN EXIT; END IF;

      EXECUTE v_command;

      DELETE FROM once_queue 
      WHERE job_id = v_jobid;

      v_success = true;
    EXCEPTION WHEN others THEN
      GET STACKED DIAGNOSTICS
        v_error_message = message_text;

      UPDATE once_queue 
      SET attempts = v_attempts + 1, schedule_time = now() + interval '5 seconds'
      WHERE job_id = v_jobid;
    END;

    INSERT INTO once_queue_log (job_id, attempt_number, command, success, error_message) 
    VALUES (v_jobid, v_attempts, v_command, v_success, v_error_message);

    COMMIT;
  END LOOP;
END;
$$;

SELECT cron.schedule('once-runner', '5 seconds', $$CALL run_once_jobs()$$);

Then you can do:

INSERT INTO once_queue VALUES ($$SELECT my_func(124,'hello')$$);

and it will be executed within 5 seconds, exactly once unless it fails 10 times.

Downside of this approach is that it only support transactional commands (not CREATE INDEX CONCURRENTLY).