How to delay by N seconds

[mozeryansky]

I am invoking a webhook every minute, but it needs to be done on the :55s mark. Below is how I'm currently achieving this with pg_sleep. However, the waiting time appears in my logs as slow queries.

Can delay be integrated into pg_cron?

select
  cron.schedule(
    'invoke-function-every-minute',
    '* * * * *',
    $$
    select pg_sleep(55);
    select
      net.http_post(
          url:='https://test.requestcatcher.com/api/cron',
          headers:='{"Content-Type": "application/json"}'::jsonb,
          body:=concat('{"time": "', clock_timestamp(), '"}')::jsonb
      ) as request_id;
    $$
  );

[TheOtherBrian1]

I am invoking a webhook every minute

It just so happens that I author the pg_net docs (Supabase webhooks), but for future reference, not everyone who lurks around the pg_cron is familiar with the extension

pg_cron now supports second level timing. You can just specify the job to run every 55 seconds:

select
  cron.schedule(
    'invoke-function-every-55-seconds',
    '55 seconds',
    $$
    select
      net.http_post(
          url:='https://project-ref.supabase.co/functions/v1/function-name',
          headers:=jsonb_build_object('Content-Type','application/json', 'Authorization', 'Bearer ' || 'YOUR_ANON_KEY'),
          body:=jsonb_build_object('time', now() ),
          timeout_milliseconds:=5000
      ) as request_id;
    $$
  );

This is a relatively new feature in pg_cron. If your version of Supabase Postgres is below v15.6.1.122, you'll need to upgrade your database's software in the Infrastructure Settings

[mozeryansky]

@TheOtherBrian1 I need the request to happen on the :55 second mark, not every 55 seconds. Such as https://upstash.com/docs/qstash/features/delay

Thanks for pg_net! I actually got this example from the supabase docs, planned on using it for my cron.

[TheOtherBrian1]

@mozeryansky, unfortunately, pg_cron doesn't support that feature. You may want to make a new issue, marked as enhancement, requesting the option.

Your approach of using pg_sleep() works, but keep in mind that it has a tradeoff. pg_cron connects to your database as an external client, holding a connection for the entire query. In your case, a connection is idle for 55s instead of servicing other apps.

If your database isn't near its connection capacity, this doesn't really matter, but it's something to be mindful of.

If you can shift the timing - for example, running on the 5th second of each minute instead of the 55th - that would be better.

Thanks for pg_net!

No prob 👍

[mozeryansky]

@TheOtherBrian1 I know pg_cron doesn't support it, this is the ticket I filed on pg_cron for the feature.

The point of delaying 55s in pgsql is to use CPU time from supabase instead of wasting cpu time in my serverless function.