Thread: Statement_timeout in procedure block
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 runningfor more than an hour. I tried setting “Set local statement_timeout=‘2 h’” within the stored procedure expecting that the statement timeout willbe 2hours for the SP execution. However it did not work as expected. Can anyone please suggest what can be done here. Thanks in advance, J. Teja.
Στις 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)
On Wed, Jun 19, 2024 at 6:12 AM Teja Jakkidi <teja.jakkidi05@gmail.com> wrote: > > 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 wasrunning for more than an hour. > I tried setting “Set local statement_timeout=‘2 h’” within the stored procedure expecting that the statement timeout willbe 2hours for the SP execution. However it did not work as expected. > Can anyone please suggest what can be done here. > i am not sure why “Set local statement_timeout=‘2 h’” does not work. i found related post: https://stackoverflow.com/questions/35706060/how-to-get-execution-time-in-postgres/35706614#35706614 i think you can do something like this: do $$ declare t timestamptz := clock_timestamp(); begin --do the work if (clock_timestamp() - t > interval '1 hour ') then raise exception 'time out'; end if; raise notice 'time spent=%', clock_timestamp() - t; end $$ language plpgsql;
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
Laurenz Albe <laurenz.albe@cybertec.at> writes: > 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 That doesn't work either, for me, and I would not expect any of these variants to do so. The reason is that statement_timeout is defined to limit the runtime of a "statement" defined as a single command received from the client --- in this case, the CALL statement --- and the timer starts running (or doesn't) at the time of command receipt. So it's the previously-prevailing value of statement_timeout that determines what happens, and no amount of thrashing within the command will change the already-established overall timeout for it. If you're desperate, transaction_timeout might serve for this, since it looks like changes in that affect the timer immediately. But it has the effect of killing the session altogether, which is probably a bigger hammer than you want. regards, tom lane
On Wed, 2024-06-19 at 10:58 -0400, Tom Lane wrote: > Laurenz Albe <laurenz.albe@cybertec.at> writes: > > 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 > > That doesn't work either, for me, and I would not expect any of these > variants to do so. Ah, you are right. It was the SET statement in the previous incarnation of the procedure that had changed the parameter for my database session. Yours, Laurenz Albe