Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

#

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Jump to bottom

Unable to stop pg_cron scheduler #352

Open
serpent7776 opened this issue Aug 28, 2024 · 5 comments · May be fixed by #381
Open

Unable to stop pg_cron scheduler #352

serpent7776 opened this issue Aug 28, 2024 · 5 comments · May be fixed by #381

Comments

@serpent7776
Copy link

I'm unable to stop pg_cron scheduler, even after I drop the extension. I also tried to restart the cluster and whole postgres service, but the scheduler is still running.

This is an issue, because it maintains connection to the database. In the case I install the extension to database other than postgres, it effectively prevents me from dropping/renaming the database.

It happened both with background workers and libpq connection.

I'd expect that dropping the extension should stop the scheduler.

I'm running postgresql-16-cron 1.6.4-1.pgdg22.04+1

postgres=# create extension pg_cron;

postgres=# SELECT pid,datname,application_name FROM pg_stat_activity WHERE datname = 'postgres';
  pid  | datname  | application_name  
-------+----------+-------------------
 23807 | postgres | pg_cron scheduler
 26168 | postgres | psql

postgres=# drop extension pg_cron;

postgres=# SELECT pid,datname,application_name FROM pg_stat_activity WHERE datname = 'postgres';
  pid  | datname  | application_name  
-------+----------+-------------------
 23807 | postgres | pg_cron scheduler
 26168 | postgres | psql
@TheOtherBrian1
Copy link
Contributor

Can you try killing the process?

Get process id

SELECT
  pid as process_id,
  usename as database_user,
  application_name,
  backend_start as when_process_began,
  wait_event_type,
  state,
  query,
  backend_type
FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';

kill process

SELECT pg_terminate_backend(<pid of the process>)

@serpent7776
Copy link
Author

IIRC I tried to kill the backend postgres process, but it was then auto restarted, but I think I used a different query.
I'm not able to check it now, but I will try to check it in few days.

@serpent7776
Copy link
Author

Sorry, I forgot about this. I can successfully kill the cron process, but then a new one is spawned.

postgres> SELECT
   pid as process_id,
   usename as database_user,
   application_name,
   backend_start as when_process_began,
   wait_event_type,
   state,
   query,
   backend_type
 FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';
+------------+---------------+-------------------+-------------------------------+-----------------+--------+-------+------------------+
| process_id | database_user | application_name  | when_process_began            | wait_event_type | state  | query | backend_type     |
|------------+---------------+-------------------+-------------------------------+-----------------+--------+-------+------------------|
| 104195     | postgres      | pg_cron scheduler | 2024-11-26 16:25:15.464989+00 | Extension       | <null> |       | pg_cron launcher |
+------------+---------------+-------------------+-------------------------------+-----------------+--------+-------+------------------+
SELECT 1
Time: 0.007s

postgres> SELECT pg_terminate_backend(104195)
+----------------------+
| pg_terminate_backend |
|----------------------|
| True                 |
+----------------------+
SELECT 1
Time: 0.005s

postgres> SELECT
   pid as process_id,
   usename as database_user,
   application_name,
   backend_start as when_process_began,
   wait_event_type,
   state,
   query,
   backend_type
 FROM pg_stat_activity where application_name ilike 'pg_cron scheduler';
+------------+---------------+-------------------+------------------------------+-----------------+--------+-------+------------------+
| process_id | database_user | application_name  | when_process_began           | wait_event_type | state  | query | backend_type     |
|------------+---------------+-------------------+------------------------------+-----------------+--------+-------+------------------|
| 104412     | postgres      | pg_cron scheduler | 2024-11-26 16:30:04.98081+00 | Extension       | <null> |       | pg_cron launcher |
+------------+---------------+-------------------+------------------------------+-----------------+--------+-------+------------------+
SELECT 1
Time: 0.005s

@lmugnano4537
Copy link

I've encountered the same issue, is there a workaround for this yet? I was testing this on Greenplum 7.3 where this extension was introduced into the product. Now, I can't shutdown the database without specifying -M fast or immediate because this connection is always present. I tried all the same things mentioned on this ticket. Thanks

@bjorndarri
Copy link

bjorndarri commented Jan 6, 2025 •
edited
Loading

I'm running into the same issue, can't drop a local development database instance (running in docker) due to the pg_cron scheduler session being active, even after dropping the extension and restarting the database.

The session restarts automatically when terminated.

The nuclear option would be to drop the the container and recreate it, but I'm regularly recreating dev-databases, so that's not really a feasible workaround.

I'm running the latest PostgreSQL version:
PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1)

@serpent7776 serpent7776 linked a pull request Jan 23, 2025 that will close this issue
Open
# for free to join this conversation on GitHub. Already have an account? # to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Introduce cron_shutdown function for stopping the main background worker serpent7776/pg_cron
4 participants
@bjorndarri @serpent7776 @TheOtherBrian1 @lmugnano4537