From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com>, pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Statement_timeout in procedure block |
Date: | 2024-06-19 07:24:32 |
Message-ID: | 46a33904c2cd86718cb4d7a0291906a08b8e246a.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2024-06-19 07:27:27 | Re: Materialized views & dead tuples |
Previous Message | jian he | 2024-06-19 06:47:17 | Re: Statement_timeout in procedure block |