How we can make “statement_timeout” work inside a function?

Question

I have a requirement to perform a query operation in a specified time limit in postgresql. If the query is not performed in that time limit, we need to catch a exception/a action and need to kill all other operations which are blocking this query operation to take place, and perform our upgrade operation. For this a prototype as been created as follows:

CREATE OR REPLACE FUNCTION execute_upgrade_statement(upgrade_stat text) RETURNS int as $$
BEGIN
EXECUTE upgrade_stat;
return 1;
END;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION upgrade_function(upgrade_statement text,upgrading_table text) RETURNS void as $$
DECLARE
   pid INTEGER;
   return_value INTEGER;
   pid_values INTEGER[];
BEGIN
SET statement_timeout TO 6000;
LOOP
BEGIN
select execute_upgrade_statement(upgrade_statement) into return_value; 
if return_value = 1 THEN
raise notice 'Upgrade query performed';
return;
else
continue;
end if;
END;
END LOOP;
exception when query_canceled then
 select into pid_values array_agg(p1.pid) from pg_locks p1 LEFT JOIN pg_stat_activity psa on p1.pid=psa.pid where p1.relation=(select relid from pg_stat_all_tables where relname=upgrading_table) and p1.pid <> pg_backend_pid();
 FOREACH pid IN ARRAY pid_values
 LOOP
 raise notice 'pid value : %',pid;
 perform pg_terminate_backend(pid);
 END LOOP;
END;
$$
LANGUAGE plpgsql; 


begin;
select upgrade_function('statement','table_name');
commit;

Here statement_timeout is not getting executed when function is called from server side, but it is getting executed when it is called from client side. Is there any other way to make statement_timeout work from server side call or is there any other way to perform my upgrade operation in given time limit ?

Community · Accepted Answer · 2017-04-13 12:42:36Z

3

It is indeed not possible to change effectively statement_timeout from within a server function, it must be done client-side before the top-level query is sent.

See a similar question on DBA.SE: Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?

or this thread in postgres mailing-list, which dates back from 2007, but the negative answer still applies with the current version:

statement_timeout doesnt work within plpgsql by design?

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jul 21, 2016 at 16:00

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How we can make “statement_timeout” work inside a function?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related