Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?

Question

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works within a transaction:

BEGIN; 
 SET LOCAL statement_timeout = 100; 
 SELECT pg_sleep(10); 
COMMIT;

where the results are (as expected):

BEGIN
SET
ERROR: canceling statement due to statement timeout
ROLLBACK

However, if I put the same commands within a function body:

CREATE OR REPLACE FUNCTION test() RETURNS void AS '
 SET LOCAL statement_timeout = 100;
 SELECT pg_sleep(10);
' LANGUAGE sql;
SELECT test();

the timeout does not occur, and the function test() takes 10 seconds to execute.

Please advise on why the two cases differ, and how I can correct it to set statement timeouts within a function.

Answer 1

The way statement_timeout works, the time starts counting when the server receives a new command from the client.

Queries launches inside server-side functions are not commands from a client, they don't reset that timer or push a new one onto a stack of timers.

This is why SET LOCAL statement_timeout = 100; has no effect.

And if a function does SET statement_timeout = 100; it will have an effect only starting at the next command from the client.

I don't see any way to control the execution time of individual queries inside a function.

• i000174: Reference: postgresql.org/docs/current/sql-set.html

Answer 2

This should work:

SET LOCAL statement_timeout = 100;
BEGIN; 
 SELECT pg_sleep(10); 
COMMIT;

• Mikko Rantalainen: SET LOCAL cannot be used outside a transaction.