Re: Statement_timeout in procedure block

On Wed, Jun 19, 2024 at 6:12 AM Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com> 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;