agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedStatement_timeout in procedure block
6+ messages / 5 participants
[nested] [flat]
* Statement_timeout in procedure block
@ 2024-06-18 22:11 Teja Jakkidi <teja.jakkidi05@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Teja Jakkidi @ 2024-06-18 22:11 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
Thanks in advance,
J. Teja.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Statement_timeout in procedure block
@ 2024-06-19 04:14 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Teja Jakkidi <teja.jakkidi05@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Achilleas Mantzios @ 2024-06-19 04:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Στις 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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Statement_timeout in procedure block
@ 2024-06-19 06:47 jian he <jian.universality@gmail.com>
parent: Teja Jakkidi <teja.jakkidi05@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: jian he @ 2024-06-19 06:47 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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 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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Statement_timeout in procedure block
@ 2024-06-19 07:24 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Teja Jakkidi <teja.jakkidi05@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2024-06-19 07:24 UTC (permalink / raw)
To: Teja Jakkidi <teja.jakkidi05@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Statement_timeout in procedure block
@ 2024-06-19 14:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-06-19 14:58 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Teja Jakkidi <teja.jakkidi05@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Statement_timeout in procedure block
@ 2024-06-19 15:06 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2024-06-19 15:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Teja Jakkidi <teja.jakkidi05@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-19 15:06 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-18 22:11 Statement_timeout in procedure block Teja Jakkidi <teja.jakkidi05@gmail.com>
2024-06-19 04:14 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2024-06-19 06:47 ` jian he <jian.universality@gmail.com>
2024-06-19 07:24 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-06-19 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-19 15:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox