Cannot create indexes on cron.job_run_details

[supabase-troubleshoot-docs-app[bot]]

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

ERROR: must be owner of table job_run_details
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:

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

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:

SELECT jobid, jobname, schedule 
FROM cron.job 
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:

CREATE TABLE public.cron_run_history_archive (
  runid bigint,
  jobid bigint,
  job_pid integer,
  database text,
  username text,
  command text,
  status text,
  return_message text,
  start_time timestamp with time zone,
  end_time timestamp with time zone,
  PRIMARY KEY (runid)
);

Create indexes on your archive table:

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

Schedule the archive job:

SELECT cron.schedule(
  'archive-cron-history',
  '0 3 * * *',
  $$INSERT INTO public.cron_run_history_archive 
    SELECT * FROM cron.job_run_details 
    WHERE end_time < now() - interval '30 days';
    DELETE FROM cron.job_run_details 
    WHERE end_time < now() - interval '30 days';$$
);

Verify the job is scheduled:

SELECT jobid, jobname, schedule 
FROM cron.job 
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.

Related resources

• pg_cron documentation
• Inspecting job runs
• Postgres upgrade guide
• pg_cron debugging guide
• pg_cron launcher crashes with duplicate key value violates unique constraint