Cannot create indexes on cron.job_run_details

If you are seeing a permission error when attempting to create an index on cron.job_run_details:

1
ERROR: must be owner of table job_run_details
2
SQLSTATE 42501

It indicates that you cannot directly modify the managed pg_cron extension’s schema objects.

Why Does This Happen?

The pg_cron extension is a managed Supabase extension. The cron.job_run_details table is owned and maintained by the extension itself, not by your database user role.

Managed extensions protect their schema objects to ensure:

Consistency across platform upgrades
Predictable behavior of the extension
Safe removal and re-installation during maintenance

This is by design: allowing users to modify managed extension objects can cause the extension to malfunction or complicate database upgrades.

How to Resolve This Issue

The cron.job_run_details table will grow indefinitely because records are never cleaned automatically. For most projects, implement a retention policy to delete old records. For projects that must retain complete history, archive old records to a customer-owned table where you can create custom indexes.

Solution 1: Implement a retention policy (recommended)

Create a cron job that periodically deletes records older than your retention window:

1
SELECT cron.schedule(
2
  'cleanup-cron-history',
3
  '0 2 * * *',
4
  $$DELETE FROM cron.job_run_details 
5
    WHERE end_time < now() - interval '7 days'$$
6
);

This schedule deletes records older than 7 days at 2:00 AM UTC daily. Adjust the interval ('7 days') and time ('0 2 * * *') to match your needs.

Verify the job is scheduled:

1
SELECT jobid, jobname, schedule 
2
FROM cron.job 
3
WHERE jobname = 'cleanup-cron-history';

Solution 2: Archive to a customer-owned table

If you must retain all historical run data, archive old records to your own table before deleting them from cron.job_run_details. On your table, you can create custom indexes.

Create an archive table:

1
CREATE TABLE public.cron_run_history_archive (
2
  runid bigint,
3
  jobid bigint,
4
  job_pid integer,
5
  database text,
6
  username text,
7
  command text,
8
  status text,
9
  return_message text,
10
  start_time timestamp with time zone,
11
  end_time timestamp with time zone,
12
  PRIMARY KEY (runid)
13
);

Create indexes on your archive table:

1
CREATE INDEX idx_archive_jobid ON public.cron_run_history_archive(jobid);
2
CREATE INDEX idx_archive_end_time ON public.cron_run_history_archive(end_time);

Schedule the archive job:

1
SELECT cron.schedule(
2
  'archive-cron-history',
3
  '0 3 * * *',
4
  $$INSERT INTO public.cron_run_history_archive 
5
    SELECT * FROM cron.job_run_details 
6
    WHERE end_time < now() - interval '30 days';
7
    DELETE FROM cron.job_run_details 
8
    WHERE end_time < now() - interval '30 days';$$
9
);

Verify the job is scheduled:

1
SELECT jobid, jobname, schedule 
2
FROM cron.job 
3
WHERE jobname = 'archive-cron-history';

Important: Upgrade impact

During a Postgres upgrade, the pg_cron extension is dropped and recreated, and cron.job_run_details is duplicated to preserve records. A large table (millions of old records) significantly increases upgrade duration and disk space requirements.

Before scheduling a Postgres upgrade, ensure your retention policy has run for at least 7 days. This reduces the size of the table that needs to be duplicated.

AI tools

Connect your AI agentAsk ChatGPT Ask Claude