citusdata / citusdata/pg_cron

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

Open
#300 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

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](https://github.com/citusdata/pg_cron/issues/63) and [issue #296](https://github.com/citusdata/pg_cron/issues/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"
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the cron.schedule entry point and reproduce the supplied PostgreSQL procedure and schedules to confirm how overlapping executions queue. Read linked issues #63 and #296 for existing discussion; done would require a decided, documented behavior or setting for queued runs.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
backend, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.