Jobs continue to run after unscheduled and killed

[ianthrive]

Jobs continue to run even after calling cron.unschedule() and pg_cancel_backed().

This system was previously running v1.3 and did not have this problem. Recently v1.4 was installed and started noticing this problem.

There is an additional problem that started at the same time for which I will make a separate issue after I'm able to accurately reproduce and document it:

It seems that jobs next in queue will not start until all current jobs are done. Not just jobs with the same jobname, but any job regardless of jobname. For example, job_a is scheduled to run every minute and normally takes 10 seconds to complete. And job_b runs every 15 minutes and sometimes takes less, sometimes more, time than that. But job_a will not start until job_b has completed.

\dx pg_cron
                List of installed extensions
┌─────────┬─────────┬────────┬──────────────────────────────┐
│  Name   │ Version │ Schema │         Description          │
├─────────┼─────────┼────────┼──────────────────────────────┤
│ pg_cron │ 1.4     │ ian    │ Job scheduler for PostgreSQL │
└─────────┴─────────┴────────┴──────────────────────────────┘
(1 row)


select now();
┌───────────────────────────────┐
│              now              │
├───────────────────────────────┤
│ 2022-01-27 09:38:40.540122+00 │
└───────────────────────────────┘


select * from cron.job where jobid=2;
┌───────┬──────────┬─────────┬──────────┬──────────┬──────────┬──────────┬────────┬─────────┐
│ jobid │ schedule │ command │ nodename │ nodeport │ database │ username │ active │ jobname │
├───────┼──────────┼─────────┼──────────┼──────────┼──────────┼──────────┼────────┼─────────┤
└───────┴──────────┴─────────┴──────────┴──────────┴──────────┴──────────┴────────┴─────────┘
(0 rows)


select * from cron.job_run_details where jobid=2 and status='running';
┌───────┬─────────┬─────────┬──────────┬──────────┬─────────────────────────────────────────────────────────────┬─────────┬────────────────┬──────────────────────────────┬──────────┐
│ jobid │  runid  │ job_pid │ database │ username │                           command                           │ status  │ return_message │          start_time          │ end_time │
├───────┼─────────┼─────────┼──────────┼──────────┼─────────────────────────────────────────────────────────────┼─────────┼────────────────┼──────────────────────────────┼──────────┤
│     2 │ 1475063 │   31591 │ .....    │ app      │ call public.triggers_queue_ ...redacted...                  │ running │ (null)         │ 2022-01-27 09:35:50.58056+00 │ (null)   │
└───────┴─────────┴─────────┴──────────┴──────────┴─────────────────────────────────────────────────────────────┴─────────┴────────────────┴──────────────────────────────┴──────────┘
(1 row)


select cron.unschedule(2);
ERROR:  could not find valid entry for job 2


select jobid, command, pg_cancel_backend(job_pid) from cron.job_run_details where status='running' and jobid=2;
┌───────┬─────────────────────────────────────────────────────────────┬───────────────────┐
│ jobid │                           command                           │ pg_cancel_backend │
├───────┼─────────────────────────────────────────────────────────────┼───────────────────┤
│     2 │ call public.triggers_queue_ ...redacted...                  │ t                 │
└───────┴─────────────────────────────────────────────────────────────┴───────────────────┘
(1 row)


select * from cron.job_run_details where jobid=2 and status='running';
┌───────┬───────┬─────────┬──────────┬──────────┬─────────┬────────┬────────────────┬────────────┬──────────┐
│ jobid │ runid │ job_pid │ database │ username │ command │ status │ return_message │ start_time │ end_time │
├───────┼───────┼─────────┼──────────┼──────────┼─────────┼────────┼────────────────┼────────────┼──────────┤
└───────┴───────┴─────────┴──────────┴──────────┴─────────┴────────┴────────────────┴────────────┴──────────┘
(0 rows)


select * from cron.job_run_details where jobid=2 and status='running';
┌───────┬─────────┬─────────┬──────────┬──────────┬─────────────────────────────────────────────────────────────┬─────────┬────────────────┬───────────────────────────────┬──────────┐
│ jobid │  runid  │ job_pid │ database │ username │                           command                           │ status  │ return_message │          start_time           │ end_time │
├───────┼─────────┼─────────┼──────────┼──────────┼─────────────────────────────────────────────────────────────┼─────────┼────────────────┼───────────────────────────────┼──────────┤
│     2 │ 1475073 │    2430 │ .....    │ app      │ call public.triggers_queue_ ...redacted...                  │ running │ (null)         │ 2022-01-27 09:41:46.403165+00 │ (null)   │
└───────┴─────────┴─────────┴──────────┴──────────┴─────────────────────────────────────────────────────────────┴─────────┴────────────────┴───────────────────────────────┴──────────┘
(1 row)

[ianthrive]

I've downgraded to v1.3.1 and the I no longer have this issue.

[ardabasoglu]

I have the same problem. My Postgres is on Supabase.

I have also noticed that a cron job is being executed multiple times, even though the "SELECT * FROM cron.job" command only shows one instance of it. Specifically, it appears to be running twice according to its schedule.

[ardabasoglu]

I have identified the issue: there was another Postgres instance running on my development machine which was an exact copy that was executing the same scheduled job concurrently. The job was doing a net.http_post call to an API. That's how I saw that it ran twice.