Thread: BUG #14964: statement_timeout cann't set in plpgsql

BUG #14964: statement_timeout cann't set in plpgsql

From
digoal@126.com
Date:
The following bug has been logged on the website:

Bug reference:      14964
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 10.1
Operating system:   centos 7.4 x64
Description:

```
create or replace function f1(int) returns setof record as $$
declare
begin
  set local statement_timeout='1ms';
  return query select count(*) as cnt, id from a where id<$1 group by id; 
end;
$$ language plpgsql strict ;
```

but it can execute every time, which when the query run more than 1ms.


Re: BUG #14964: statement_timeout cann't set in plpgsql

From
"David G. Johnston"
Date:
On Mon, Dec 11, 2017 at 7:29 AM, <digoal@126.com> wrote:
The following bug has been logged on the website:

Bug reference:      14964
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 10.1
Operating system:   centos 7.4 x64
Description:

```
create or replace function f1(int) returns setof record as $$
declare
begin
  set local statement_timeout='1ms';
  return query select count(*) as cnt, id from a where id<$1 group by id;
end;
$$ language plpgsql strict ;
```

but it can execute every time, which when the query run more than 1ms.


​A quick Google search turns up the response that this is working as designed.  In short, the timeout applies to statements sent by the client.  Once changed the next statement the client sends to the server will use the new setting.  Since a server function invocation is a single statement as far as the client-server relationship goes a timeout set within a function will not apply until the statement the invoked the function ends and a new statement is sent from the client to the server.

David J.


Re:Re: BUG #14964: statement_timeout cann't set in plpgsql

From
德哥
Date:

Sorry, it's not a bug.
can we use some hook , to deal this function?   



--
公益是一辈子的事,I'm Digoal,Just Do It.

在 2017-12-11 22:44:53,"David G. Johnston" <david.g.johnston@gmail.com> 写道:
On Mon, Dec 11, 2017 at 7:29 AM, <digoal@126.com> wrote:
The following bug has been logged on the website:

Bug reference:      14964
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 10.1
Operating system:   centos 7.4 x64
Description:

```
create or replace function f1(int) returns setof record as $$
declare
begin
  set local statement_timeout='1ms';
  return query select count(*) as cnt, id from a where id<$1 group by id;
end;
$$ language plpgsql strict ;
```

but it can execute every time, which when the query run more than 1ms.


A quick Google search turns up the response that this is working as designed.  In short, the timeout applies to statements sent by the client.  Once changed the next statement the client sends to the server will use the new setting.  Since a server function invocation is a single statement as far as the client-server relationship goes a timeout set within a function will not apply until the statement the invoked the function ends and a new statement is sent from the client to the server.

David J.