From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Teja Jakkidi <teja.jakkidi05@gmail.com>,
        pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Statement_timeout in procedure block
In-reply-to: <46a33904c2cd86718cb4d7a0291906a08b8e246a.camel@cybertec.at>
References: <E0175C30-9A8F-4648-A2D5-36908FF6F695@gmail.com>
 <46a33904c2cd86718cb4d7a0291906a08b8e246a.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Wed, 19 Jun 2024 09:24:32 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <114104.1718809113.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 19 Jun 2024 10:58:33 -0400
Message-ID: <114105.1718809113@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/114105.1718809113%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> What works is setting the parameter on the procedure:

> test=3D> CREATE OR REPLACE PROCEDURE sit() LANGUAGE plpgsql SET statemen=
t_timeout =3D 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