Cronjob timeout
Hi,
I am currently running into timeout issues when doing REFRESH MATERIALIZED VIEW CONCURRENTLY. I have 15 MVs which are called after each other. The cronjob looks like this:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv2;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv3;
...
The first one takes a bit over 2 min which then times out.
I have looked a bit into https://supabase.com/docs/guides/database/postgres/timeouts and https://supabase.com/docs/guides/troubleshooting/pgcron-debugging-guide-n1KTaz but no mention of this.
Is there a nice way to handle cronjobs which runs over 2 min without extending the whole postgres user timeout?
I have had a similar cronjob which refreshed older MVs that did not take more than 2 min each which is why I have just recently stumble upon it.
Best regards
Rasmus
Timeouts | Supabase Docs
Extend database timeouts to execute longer transactions
Supabase Docs | Troubleshooting | pg_cron debugging guide
Supabase is the Postgres development platform providing all the backend features you need to build a product.
5 Replies
Could it be as simple as setting the statement_timeout on session level? I think it works but it would be nice to get it confirmed 🙌
There are a couple of ways to extend the default overall role timeout of 2 minutes. That is what your first link covers. I personally would use a function cron calls and set the timeout in that.
OK, so that is basically the same as setting a sql snippet with a set statement_timeout at the top I guess?
I've only ever changed the authenticator REST roles and never had a query(s) run for 2 minutes so have not done this in Postgres myself.
It is all Postgres though, with the only thing Supabase specific being the 2 minute default timeout, so any info on the web or from an AI for Postgres and timeouts should apply. You are not limited to the Supabase docs.
Otherwise have to wait to see if another user comes along if you want more back up on your you say working fix.
I just find cron easier to maintain and code calling a function from cron versus inline code.
Alright thanks for your inputs. Highly appreciate it 🙌