Обсуждение: statement_timeout doesnt work within plpgsql by design?

pagila=# select version();                          version
-------------------------------------------------------------PostgreSQL 8.2.3 on i386-pc-solaris2.10, compiled by cc
-Xa
(1 row)

pagila=# create or replace function test() returns bool as $$ begin set 
statement_timeout = 3000; perform pg_sleep(4) ; return true; end $$ language 
plpgsql;
CREATE FUNCTION
pagila=# select test();test
------t
(1 row)

pagila=# select test();
ERROR:  canceling statement due to statement timeout
CONTEXT:  SQL statement "SELECT  pg_sleep(4)"
PL/pgSQL function "test" line 1 at perform


is this behavior by design?  if so why would you design it that way? :-) 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat <xzilla@users.sourceforge.net> writes:
> pagila=# create or replace function test() returns bool as $$ begin set 
> statement_timeout = 3000; perform pg_sleep(4) ; return true; end $$ language 
> plpgsql;
> CREATE FUNCTION

statement_timeout is measured across an entire interactive command, not
individual commands within a function; and the timeout that applies to
an interactive command is determined at its beginning.  So the above
doesn't do what you think.
        regards, tom lane

On Tuesday 20 February 2007 12:50, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > pagila=# create or replace function test() returns bool as $$ begin set
> > statement_timeout = 3000; perform pg_sleep(4) ; return true; end $$
> > language plpgsql;
> > CREATE FUNCTION
>
> statement_timeout is measured across an entire interactive command, not
> individual commands within a function; and the timeout that applies to
> an interactive command is determined at its beginning.  So the above
> doesn't do what you think.
>

Well, I'd be happy if it caused the entire function to bail out or if it 
caused individual statements within a function to bail out, but it does 
neither.  I can see how that would be a bit tricky to implement though. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL