On Wed, Jun 19, 2024 at 6:12 AM Teja Jakkidi <teja.jakkidi05@xxxxxxxxx> wrote: > > Hello PgAdmins, > > We have a Postgres instance where we had set statement_timeout to 1hour at instance level. > However, today we noticed that one of our cron jobs which calls a stored procedure failed with timeout error as it was running for more than an hour. > I tried setting “Set local statement_timeout=‘2 h’” within the stored procedure expecting that the statement timeout will be 2hours for the SP execution. However it did not work as expected. > Can anyone please suggest what can be done here. > i am not sure why “Set local statement_timeout=‘2 h’” does not work. i found related post: https://stackoverflow.com/questions/35706060/how-to-get-execution-time-in-postgres/35706614#35706614 i think you can do something like this: do $$ declare t timestamptz := clock_timestamp(); begin --do the work if (clock_timestamp() - t > interval '1 hour ') then raise exception 'time out'; end if; raise notice 'time spent=%', clock_timestamp() - t; end $$ language plpgsql;