11

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.

14

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.