21 likes
·
4.5K reads
6 comments
This section produces "invalid SQL query", I can't seem to figure out why? I have been unable to get this process to work
CREATE OR REPLACE FUNCTION schedule_jobs()
RETURNS VOID
AS $$
BEGIN
-- Schedule retry job
SELECT cron.schedule(
'retry_failed_jobs',
'*/10 * * * *',
$$ SELECT retry_failed_jobs(); $$
);
-- Schedule first job
PERFORM cron.schedule(
'process_current_jobs_if_unlocked_job_1',
'* * * * *',
$$ SELECT process_current_jobs_if_unlocked(); $$
);
-- Schedule second job with a 20-second delay
PERFORM pg_sleep(20);
PERFORM cron.schedule(
'process_current_jobs_if_unlocked_job_2',
'* * * * *',
$$ SELECT process_current_jobs_if_unlocked(); $$
);
-- Schedule third job with another 20-second delay
PERFORM pg_sleep(20);
PERFORM cron.schedule(
'process_current_jobs_if_unlocked_job_3',
'* * * * *',
$$ SELECT process_current_jobs_if_unlocked(); $$
);
END;
$$ LANGUAGE plpgsql;
Sorry, a little oversight from me. If you change the $$ in the function or in each command with a different context it will work:
RETURNS VOID
AS $function$
BEGIN
(...)
$function$ LANGUAGE plpgsql;
Or on each command:
PERFORM cron.schedule(
'process_current_jobs_if_unlocked_job_3',
'* * * * *',
$command$ SELECT process_current_jobs_if_unlocked(); $command$
);
I'll update the article to fix the example.
Thank you for sharing your knowledge. Please do not forget to apply RLS on each tables.
Thanks Bert. I can make this more clear moving forward, but you can also follow this previous article to have them set automatically for you:
Hi! Thanks for this awesome tuto. I followed all steps but the status stays as "Processing". When I log the response_result in the process_current_jobs function, I have: response_result: (SUCCESS,404,"Function not found"). I replaced your supabase functiond urls by mine in the process_job and retry_failed_jobs functions.
Any idea?
Thanks. Best regards, C.