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
