From: | Achilleas Mantzios <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Statement_timeout in procedure block |
Date: | 2024-06-19 04:14:54 |
Message-ID: | 0734fffc-1ee5-4810-8a49-20585fde2d0e@cloud.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Στις 19/6/24 01:11, ο/η Teja Jakkidi έγραψε:
> 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.
It could be that the stored procedure does many queries the total time
of which surpass your limit. Try setting before the procedure call :
SET session statement_timeout='2h';
CALL <your procedure>
>
> Thanks in advance,
> J. Teja.
>
--
Achilleas Mantzios
IT DEV - HEAD
IT DEPT
Dynacom Tankers Mgmt (as agents only)
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2024-06-19 04:24:10 | Re: Materialized views & dead tuples |
Previous Message | David G. Johnston | 2024-06-18 23:45:55 | Re: Scripting a ALTER PROCEDURE or FUNCTION to Change OWNER |