# pg_cron debugging guide

This is a general guide for debugging pg\_cron. Below lists issues and how to debug them

## Cannot create/edit/delete cron jobs

Cron jobs can only be modified with the respective SQL functions:

- Schedule: [cron.schedule](https://supabase.com/docs/guides/database/extensions/pg_cron#delete-data-every-week)
- Alter: [cron.alter\_job](https://supabase.com/docs/guides/database/extensions/pg_cron#edit-a-job)
- Delete: [cron.unschedule](https://supabase.com/docs/guides/database/extensions/pg_cron#unschedule-a-job)

If you are trying to make changes, use the cron functions. If the cron functions are inaccessible, contact [Support](https://supabase.com/dashboard/support/new)



***

## Cron Jobs are not running

> You should consider initiating a software upgrade in the [General Settings](https://supabase.com/dashboard/project/_/settings/general) if your Postgres version is below v15.6.1.122. Upgrading will give you access to pg\_cron v1.6.4+, which has many bug fixes and auto-revive capabilities.

### Debugging steps:

#### Check to see if `pg_cron scheduler` is active

pg\_cron operates as the `pg_cron scheduler` process within Postgres. Use the below query to check if the worker is active

```sql
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';
```

If the query does not return a row, the worker has died. To revive it, you must go to the [General Settings](https://supabase.com/dashboard/project/_/settings/general) and initiate a fast reboot:





#### Check the `cron.job_run_details` table for more information

pg\_cron creates logs in its own table `cron.job_run_details`. The below query checks for issues from the past 5 days :

```sql
SELECT *
FROM cron.job_run_details
WHERE
    (status <> 'succeeded' AND status <> 'running')
        AND
     start_time > NOW() - INTERVAL '5 days'
ORDER BY start_time DESC
LIMIT 10;
```

Respond to the errors exposed appropriately.

> Long running jobs may show timeout errors. For jobs that are intended to execute for extended periods, consider wrapping their queries in functions with custom timeouts ([guide](https://supabase.com/docs/guides/database/postgres/timeouts#function-level)).



#### Check if there are too many cron jobs running concurrently

pg\_cron supports up to 32 concurrent jobs, each using a database connection. If too many jobs are running simultaneously, space them out to prevent connection overload and job failure.

The below queries shows all pg\_cron jobs:

```sql
-- All jobs
select schedule, jobname, command from cron.job;

-- Count jobs
select COUNT(*) from cron.job;
```

The below query can be used to find actively querying jobs:

```sql
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';
```

You can view your concurrent peak connection usage throughout the day at the bottom of the [Observability Dashboard](https://supabase.com/dashboard/project/_/observability/database)



#### Check for database strain

Unfortunately, excessive resource strain can slow down or disrupt jobs.

Go to the [reports page](https://supabase.com/dashboard/project/_/observability/database) (or [Supabase Grafana](https://supabase.com/docs/guides/monitoring-and-debugging/metrics/grafana-self-hosted) if you have it setup), and check for signs of resource exhaustion. If it's clear your database is under pressure, consider upgrading your compute add-on or following the advice from one of the optimization guides:

- [Connections](https://github.com/orgs/supabase/discussions/27141)
- [Disk/IO](https://github.com/orgs/supabase/discussions/27003)
- [Memory](https://github.com/orgs/supabase/discussions/27021)
- [CPU](https://github.com/orgs/supabase/discussions/27022)

It is important to make sure you are running the latest release of pg\_cron (1.6.4) if you're noticing strain. It is the most robust.



#### Check the logs for more information

Although `pg_cron` records errors in the `cron.job_run_details` table, in rare cases, more information can be found in the general Postgres logs. You can check the [SQL Editor](https://supabase.com/dashboard/project/_/sql/new?skip=true\&source=logs), with the query source set to **Logs**, for failure events with the following query

```sql
select
  timestamp,
  event_message,
  log_attributes['parsed.error_severity'] as error_severity,
  log_attributes['parsed.user_name'] as user_name,
  log_attributes['parsed.query'] as query,
  log_attributes['parsed.detail'] as detail,
  log_attributes['parsed.hint'] as hint,
  log_attributes['parsed.sql_state_code'] as sql_state_code,
  log_attributes['parsed.backend_type'] as backend_type,
  log_attributes['parsed.application_name'] as application_name
from logs
where
  source = 'postgres_logs'
  and log_attributes['parsed.error_severity'] in ('ERROR', 'FATAL', 'PANIC')
  and match(log_attributes['parsed.application_name'], 'pg_cron')
order by timestamp desc
limit 100;
```

If you're interested in modifying the query, there is an advanced [guide](https://github.com/orgs/supabase/discussions/26224) for navigating the Postgres logs and a general-purpose [one](https://supabase.com/docs/guides/monitoring-and-debugging/advanced-log-filtering) for applying filters.



#### Create custom logs within cron jobs

If it's still not clear what is occurring you may be able to capture more logs by running the pg\_cron query inside a database function:

```sql
create or replace function log_example()
returns void
language plpgsql
as $$
begin

     -- Logging start of function
     raise log 'logging start of cron function call: (%)', (select now());

    -- INSERT LOGIC HERE

     -- Logging end of function
     raise log 'logging end of cron function call: (%)', (select now());

    exception
     -- Handle exceptions here if needed
     when others then
         raise exception 'An error occurred in cron function <insert name here>. ERROR MESSAGE: %', sqlerrm;

end;
$$;
```

You can then search for your custom messages in the [Logs Interface](https://supabase.com/dashboard/project/_/logs/postgres-logs)



#### Upgrading pg\_cron version

The current version of `pg_cron` on Supabase is 1.6.4. It comes with a [few bug fixes](https://github.com/citusdata/pg_cron/releases/tag/v1.6.4). You should consider upgrading to Postgres v15.6.1.122+ in the [General Settings](https://supabase.com/dashboard/project/_/settings/general) to get the latest extension.



#### Contacting support and the maintainers

Although Supabase includes the extension, it is maintained by Citus (a Microsoft subsidiary). You can contact Support for more help, but you should also consider creating an issue in the [pg\_cron repo](https://github.com/citusdata/pg_cron).