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

14 votes

2 answers

24385 views

postgresql transaction functions query-timeout

                          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.
                        

Asked by Sadeq Dousti (243 rep)

Nov 18, 2014, 08:14 PM
Last activity: Mar 16, 2023, 11:37 AM

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

Related Questions