Re: Statement_timeout in procedure block

On Wed, Jun 19, 2024 at 6:12 AM Teja Jakkidi <teja.jakkidi05@gmail.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
wasrunning for more than an hour. 
> I tried setting “Set local statement_timeout=‘2 h’” within the stored procedure expecting that the statement timeout
willbe 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;