supabase: pg_cron / seems job never executed

I tried to execute a simples CRON job with pg_cron by following the official tutorial https://supabase.io/blog/2021/03/05/postgres-as-a-cron-server and it’s not working for me.

Where I’m now: I have the pg_cron extension installed by supabase UI; I have the simplest CRON job which looks like

SELECT cron.schedule(
'* * * * *',
$$
  DELETE FROM data
  WHERE id=1
$$
);

if I execute SELECT * FROM cron.job; I will get it in the list of scheduled jobs. But at the end of the day, it never will be executed.

What I already try to do:

disable/enable extension
create a brand new project and redo steps following the guide

Any ideas on how I can handle this issue?

_Originally posted by @Miruzz in https://github.com/supabase/supabase/discussions/1628_

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 17 (6 by maintainers)

Most upvoted comments

I managed to schedule jobs but they weren’t running. Ended up restarting the project and project settings and the jobs started running.

xavierchia on Jan 29, 2024

Hey everyone 👋, finally found a quick remedy. Do follow this flow when using the pg_cron extension on the dashboard:

0. [OPTIONAL] Drop all scheduled jobs and the pg_cron extension itself

This is important if you already have pg_cron enabled and have jobs set

-- repeat as much to clear all jobs
select cron.unschedule(<job id>); 

-- drop the extension
drop extension if exists pg_cron;

1. Enable pg_cron

create extension if not exists pg_cron;

2. [IMPORTANT] Provide the following grants to the user `postgres` (and in the future any other user that would be using the cron schema). At the baseline, it seems pg_cron uses the `postgres` user when executing its schedule.

grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;

3. Create sample table `test_table`

CREATE TABLE test_table (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  name text
);

4. Run a CRON job via pg_cron to add a row to this sample table

select
  cron.schedule(
    'whack-every-minute', 
    '* * * * *', 
    $$
    insert into test_table(name) values ('kek');
    $$
  );

5. Observe that the table `test_table` is now being populated every minute

select * from test_table;

dragarcia on Jul 2, 2021

Hey guys! Not much at the moment but we are inching towards finding the root cause. Sorry about that. We’ll update you on any major progress.

dragarcia on Jun 1, 2021

Hi @pyoner & @Miruzz ! Thanks for pointing this out. There does seem to be an issue with pg_cron at the moment on our side and we are now looking into it.

dragarcia on May 25, 2021

Do tell us if these steps work for you guys. This won’t be the long-term solution, however. This is instead a way to hopefully unblock you guys from this bug for the time being. We’re now working on a way for Step 2 to always be triggered automatically upon enabling the extension. Thanks!

These sql queries helped me!
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;
@dragarcia thank you!

Hi all, I ran into this issue today (at the dat of writing this comment) and the solution was to grant usage and privileges as with the quoted reply.

It then worked without having to go through the 5 step process

sduduzog on Jul 11, 2021

Hey @emschwartz! That’s odd 🤔. Could you send over your project ref over to beta@supabase.io? I’ll check out what’s wrong under the hood.

dragarcia on Jul 6, 2021