Обсуждение: 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.

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