Issue installing `pg_cron` locally

[osaxma]

Bug report

Describe the bug

Trying to install pg_cron locally but I'm facing the following issue:

zsh:~$ supabase init
zsh:~$ echo "create extension if not exists pg_cron;" > supabase/migrations/000_add_pg_cron.sql
zsh:~$ supabase start 
Error: Error starting database: ERROR:  can only create extension in database postgres
DETAIL:  Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database.
HINT:  Add cron.database_name = 'main' in postgresql.conf to use the current database.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at RAISE

Expected behavior

I'm not sure what's an appropriate way to update cron.database_name based on the branch name.

For instance, I tried adding this to the migration file:

select set_config('cron.database_name', 'main', false); -- or true

But then I get the following error:

Error: Error starting database: ERROR:  parameter "cron.database_name" cannot be changed without restarting the server

System information

• OS: macOS
• cli: 0.17.0

Additional context

By the way, the error above prevents other containers from starting. If you run docker ps, only the db container would be running.

[soedirgo]

Interesting - I think this is a similar problem to #137. It works when running CREATE EXTENSION manually after supabase start.

[osaxma]

I actually ended up doing that but for some reason the jobs are not executed. I couldn't figure out why and I'm not sure if it's related to branching or something else.

To Reproduce:

• In a fresh supabase instance using the cli, run the following:

create extension if not exists pg_cron;

-- I don't think this is necessary for superuser 
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;

create table if not exists public.events (
  id integer generated always as identity,
  name text,
  created_at timestamp with time zone DEFAULT now() NOT NULL
);

insert into events (name) values 
('one'),
('two'),
('three'),
('four'),
('five');

create or replace function public.clean_events_table() 
RETURNS void 
as $$
begin
  delete from public.events;   
end;
$$ language plpgsql security definer;

-- every minute
SELECT cron.schedule('clear events table', '* * * * *', 'select clean_events_table()'); 

• confirm the job exists: select * from cron.job;:

jobid	schedule	command	nodename	nodeport	database	username	active	jobname
1	* * * * *	select clean_events_table()	localhost	5432	postgres	postgres	true	clear events table

• After one minute, the public.events table will not be cleared and running select * from cron.job_run_details; does not show any executed jobs.

[soedirgo]

Ah yeah, it doesn't exactly "work" - as with #137, the background worker for pg_cron fails to start so the jobs don't get executed.

[osaxma]

@soedirgo can confirm -- restarting the database container manually made it work as suggested in #137

[mitchjacksontech]

Have the same issue attempting to enable pg_cron via migrations. #263 mentioned an undocumentated /supabase/extensions.sql file, but I haven't managed to enable pg_cron there, from from a normal migration file such as /supabase/migrations/pg_cron.sql.

drop extension if exists pg_cron;
create extension if not exists pg_cron;
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;

The error:

% supabase start Error: Error starting database: ERROR: can only create extension in database postgres DETAIL: Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database. HINT: Add cron.database_name = 'main' in postgresql.conf to use the current database.

[sweatybridge]

This is fixed since v1.4.7 as we restart the db container after initialisation. The underlying issue was that we rely on pg_terminate_backend when switching/restoring databases and that closes all background worker connections. You may refer to https://github.com/supabase/cli/pull/465 for more details.

[413n]

@sweatybridge I don't know if this should be closed, as the previously mentioned error is still there when adding the pg_cron extensions in a migration. I put the create extensions in the migration since the extensions.sql should be deprecated. I tried with the latest supabase version (1.6.0)

Migration:

create extension if not exists pg_cron with schema extensions;

Error after supabase start goes through the first migration with the create extension:

Error: Error starting database: ERROR:  can only create extension in database postgres
DETAIL:  Jobs must be scheduled from the database configured in cron.database_name, since the pg_cron background worker reads job descriptions from this database.
HINT:  Add cron.database_name = 'main' in postgresql.conf to use the current database.

I tried also select set_config('cron.database_name', 'main', false); but seems that parameter "cron.database_name" cannot be changed without restarting the server.

Is that something wrong on my side or is this problem still there?

[sweatybridge]

@413n you are right. There are 2 separate errors here, one resolved by restarting background worker and the other one is still outstanding.

Essentially we need to create pg_cron extension in the postgres database. I will keep this ticket open as we work on it.

[github-actions[bot]]

:tada: This issue has been resolved in version 1.7.5 :tada:

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot :package::rocket:

[sweatybridge]

Thank you for your patience in this issue. I've tested it be working locally with our latest release. Please let me know if things are still broken.