我们如何让“statement_timeout"在函数内部工作? [英] How we can make “statement_timeout” work inside a function?

问题描述

我需要在 postgresql 中在指定的时间限制内执行查询操作.如果查询未在该时间限制内执行，我们需要捕获异常/操作并需要终止所有其他阻止此查询操作发生的操作，并执行我们的升级操作.为此，创建了一个原型，如下所示:

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;

这里的 statement_timeout 在从服务器端调用函数时不会被执行，但在从客户端调用时会被执行.有没有其他方法可以从服务器端调用使 statement_timeout 工作，或者有没有其他方法可以在给定的时间限制内执行我的升级操作?

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 ?

推荐答案

确实不可能从服务器函数内部有效地更改 statement_timeout，必须在客户端之前完成 -发送级别查询.

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.

在 DBA.SE 上看到一个类似的问题:为什么SET LOCAL"statement_timeout"在 PostgreSQL 函数中没有按预期工作?

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

或者 postgres 邮件列表中的这个帖子，可以追溯到 2007 年，但否定的答案仍然适用于当前版本:

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

statement_timeout 按照设计在 plpgsql 中不起作用?

这篇关于我们如何让“statement_timeout"在函数内部工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！