Re: Statement_timeout in procedure block

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