From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Teja Jakkidi <teja.jakkidi05@gmail.com>
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Statement_timeout in procedure block
Date: Wed, 19 Jun 2024 09:24:32 +0200
Message-ID: <46a33904c2cd86718cb4d7a0291906a08b8e246a.camel@cybertec.at> (raw)
In-Reply-To: <E0175C30-9A8F-4648-A2D5-36908FF6F695@gmail.com>
References: <E0175C30-9A8F-4648-A2D5-36908FF6F695@gmail.com>
On Tue, 2024-06-18 at 15:11 -0700, Teja Jakkidi wrote:
> 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 can confirm that - it surprises me as well. This is what I tried:
test=> CREATE PROCEDURE sit() LANGUAGE plpgsql AS 'BEGIN SET LOCAL statement_timeout = 2000; PERFORM pg_sleep(5); END;';
CREATE PROCEDURE
test=> CALL sit();
CALL
test=> CREATE OR REPLACE PROCEDURE sit() LANGUAGE plpgsql AS 'BEGIN SET statement_timeout = 2000; PERFORM pg_sleep(5); END;';
CREATE PROCEDURE
test=> CALL sit();
CALL
The statement didn't get interrupted.
What works is setting the parameter on the procedure:
test=> CREATE OR REPLACE PROCEDURE sit() LANGUAGE plpgsql SET statement_timeout = 2000 AS 'BEGIN PERFORM pg_sleep(5); END;';
CREATE PROCEDURE
test=> CALL sit();
ERROR: canceling statement due to statement timeout
CONTEXT: SQL statement "SELECT pg_sleep(5)"
PL/pgSQL function sit() line 1 at PERFORM
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, teja.jakkidi05@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Statement_timeout in procedure block
In-Reply-To: <46a33904c2cd86718cb4d7a0291906a08b8e246a.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox