Jobs seems to be stacking up of the previous job is still running. How large can this get before thing go bad?

[anderssjoblom-vertex]

One update to the below original writeup. Seems like when using the xx seconds as the schedule format, there is only one extra job being queued up behind. Only when using the std schedule can you get 100 of stacked up jobs waiting t obe executed. if using cron.schedule('test', '59 seconds', $$ call execute_cron() $$) that takes 1 hour, you only get one more stacked job after the hour. If you use cron.schedule('test', '* * * * *', $$ call execute_cron() $$) you will have 60 tasks waiting to be run.

Is this by design, if so, maybe there should be a setting for this.

My plan was to use cron to schedule a long running migration task that can slowly move data to a new set of tables. So how frequent would you schedule the job. Since pg_cron will not start another task until the previous one has finished, then is seemed logical to just use "* * * * *" as the schedule. The assumption is that when a job is scheduled to run and the previous one is still running, it would just be dropped on the floor instead of putting it on the queue.

I have found something related here: issue #63 and issue #296

To verify, I made a simple function to see what happens when a job is already running.

create table cron_test
(
	cron_message text,
	cron_time timestamptz
)

create or replace procedure execute_cron() as $$
declare
  _delay float;
begin

	-- if we don't have an iteration on the log table, waiut to  tje 
  	_delay= case when (select count(*) from cron_test) > 1 then 1 else 610 end;

	insert into public.cron_test values('entered execute_cron, delay: ' || _delay, clock_timestamp());
	commit;
	
	perform pg_sleep(_delay);
	insert into public.cron_test values('leaving execute_cron', clock_timestamp());
	
end; $$ language plpgsql;

select cron.schedule(
    'test-cron-queue',
    '* * * * *', -- Every minute
    $$ call execute_cron() $$
);

Notice how you get 10 queued up processes that fires one at a time after the previous one finish.

Output:

"cron_message"	"cron_time"
"entered execute_cron, delay: 610"	"2023-12-21 17:44:00.040104+00"
"leaving execute_cron"	"2023-12-21 17:54:10.107759+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:10.204902+00"
"leaving execute_cron"	"2023-12-21 17:54:11.212623+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:11.228234+00"
"leaving execute_cron"	"2023-12-21 17:54:12.234996+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:12.252196+00"
"leaving execute_cron"	"2023-12-21 17:54:13.256553+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:13.291467+00"
"leaving execute_cron"	"2023-12-21 17:54:14.300453+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:14.317419+00"
"leaving execute_cron"	"2023-12-21 17:54:15.324196+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:15.340136+00"
"leaving execute_cron"	"2023-12-21 17:54:16.348166+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:16.362762+00"
"leaving execute_cron"	"2023-12-21 17:54:17.369932+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:17.38493+00"
"leaving execute_cron"	"2023-12-21 17:54:18.392344+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:18.407294+00"
"leaving execute_cron"	"2023-12-21 17:54:19.412341+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:54:19.426311+00"
"leaving execute_cron"	"2023-12-21 17:54:20.433101+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:55:00.014174+00"
"leaving execute_cron"	"2023-12-21 17:55:01.020538+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:56:00.032853+00"
"leaving execute_cron"	"2023-12-21 17:56:01.043884+00"
"entered execute_cron, delay: 1"	"2023-12-21 17:57:00.039908+00"

[marcoslot]

How large can this get before thing go bad?

Things go bad when 4 billion runs are pending, which can happen after 7605 years assuming jobs that run every minute.

The behaviour is not currently configurable, but patches are welcome.