Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJmit-006gYj-N7 for pgsql-admin@arkaria.postgresql.org; Wed, 19 Jun 2024 04:15:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sJmiq-008gVb-T4 for pgsql-admin@arkaria.postgresql.org; Wed, 19 Jun 2024 04:15:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJmiq-008gVP-Hl for pgsql-admin@lists.postgresql.org; Wed, 19 Jun 2024 04:15:01 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJmip-002OMX-4G for pgsql-admin@lists.postgresql.org; Wed, 19 Jun 2024 04:15:00 +0000 Content-Type: multipart/alternative; boundary="------------VO5ra7KIAW7nvkv4Yrt8KOEA" Message-ID: <0734fffc-1ee5-4810-8a49-20585fde2d0e@cloud.gatewaynet.com> Date: Wed, 19 Jun 2024 07:14:54 +0300 MIME-Version: 1.0 Subject: Re: Statement_timeout in procedure block To: pgsql-admin@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------VO5ra7KIAW7nvkv4Yrt8KOEA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 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 > > Thanks in advance, > J. Teja. > -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only) --------------VO5ra7KIAW7nvkv4Yrt8KOEA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
Στις 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)
--------------VO5ra7KIAW7nvkv4Yrt8KOEA--