My FeedDiscussionsHeadless CMS
Sign in
Log inSign up
Learn more about Hashnode Headless CMSHashnode Headless CMS
Collaborate seamlessly with Hashnode Headless CMS for Enterprise.
Upgrade ✨Learn more
Rodrigo Mansueli

21 likes

·

3.6K reads

6 comments

Jeremy Bauman
Jeremy Bauman
Aug 16, 2023

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;
1
·
·1 reply
Rodrigo Mansueli
Rodrigo Mansueli
Author
·Aug 16, 2023

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.

1
·
Bert Cammayo
Bert Cammayo
Aug 27, 2023

Thank you for sharing your knowledge. Please do not forget to apply RLS on each tables.

1
·
·1 reply
Rodrigo Mansueli
Rodrigo Mansueli
Author
·Aug 27, 2023

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:

blog.mansueli.com/sudo-with-postgres-supabase

1
·
Carlito
Carlito
Dec 6, 2023

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.

·