Sign in
Log inSign up
Rodrigo Mansueli

22 likes

·

6.9K reads

11 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
·
·3 replies
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
·
Abhay Pansuriya
Abhay Pansuriya
Jun 12, 2024

hi there

3
·
Sandy Mildred
Sandy Mildred
Jun 13, 2024

CONSULT A LICENSE PATECH RECOVERY HACKER FOR CRYPTO RECOVERY I would like to express my gratitude to PATECH RECOVERY HACKER for helping me through a difficult period. I was duped into making an online investment in which I was promised a 25% weekly profit, but it turned out to be a scam. I was very angry until I read an article about PATECH Recovery about how they had assisted others in recovering funds. But because Patech Recovery helped me and made things simple for me, I was able to get my cryptocurrency back. If you've been a victim of something similar, get in touch with them Email: patechrecovery @ proton dot me whatsapp---913////730///0531

Show less

·
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.

·
Ceero4
Ceero4
May 16, 2024

nice post

·
Geoffrey Garrett
Geoffrey Garrett
May 28, 2024

Really awesome post. The only real solution to queues with PG and Supabase, unless you want to add an additional vendor. The code is great though it's not the most robust. I have race condition issues with it + apparently there's sub-minute cron supported now with pg_cron 1.50+ though I haven't had success.

Thanks a lot though, I'll try get my code across to you in a minute.

·
Jonathan Hsu
Jonathan Hsu
Aug 11, 2024

Thank you for this tutorial, it was very helpful to get me started. One thing I'm running into is my edge function running into concurrency limits... Do you have any recommendations for throttling how frequently an edge function can be called?

·