Thread

1. BUG #1314: STATEMENT_TIMEOUT DOES NOT WORK PROPERLY

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-11T22:43:58Z

   The following bug has been logged online:
   
   Bug reference:      1314
   Logged by:          Adnan DURSUN
   
   Email address:      adursun@hvkk.mil.tr
   
   PostgreSQL version: 8.0 Beta
   
   Operating system:   W2K
   
   Description:        STATEMENT_TIMEOUT DOES NOT WORK PROPERLY
   
   Details: 
   
   Hi, i use PostgreSQL 8 Beta4.I write a sf like that :
   /////////
   CREATE OR REPLACE FUNCTION fn_test()
     RETURNS "varchar" AS
   $BODY$
   DECLARE
      c refcursor;
      r record;
   BEGIN
      SET SESSION STATEMENT_TIMEOUT = 1000;
      OPEN C FOR SELECT * FROM T_KULLANICILAR FOR UPDATE;
      FETCH C INTO R;
      RESET STATEMENT_TIMEOUT;
      RETURN '1';
   EXCEPTION
      WHEN QUERY_CANCELED THEN
             RAISE EXCEPTION 'UNABLE TO OBTAIN LOCK... :%';
   END;
   $BODY$
     LANGUAGE 'plpgsql' VOLATILE;
   /////////
   1.I RUN A THIS "SELECT * FROM T_KULLANICILAR FOR UPDATE" from another 
   session to lock T_KULLANICILAR table rows. 
   2.While T_KULLANICILAR table was locked,i called sf like "SELECT FN_TEST()" 
   from psql command prompt.But STATEMENT_TIMEOUT doesn't effect.  
   3.but i enter "SET SESSION STATEMENT_TIMEOUT = 1000;" command before step 2, 
   STATEMENT_TIMEOUT affects and cancel query. 
   
   
   
   

2. Re: BUG #1314: STATEMENT_TIMEOUT DOES NOT WORK PROPERLY

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-11T22:53:08Z

   "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
   > CREATE OR REPLACE FUNCTION fn_test()
   >   RETURNS "varchar" AS
   > $BODY$
   > DECLARE
   >    c refcursor;
   >    r record;
   > BEGIN
   >    SET SESSION STATEMENT_TIMEOUT = 1000;
   >    OPEN C FOR SELECT * FROM T_KULLANICILAR FOR UPDATE;
   >    FETCH C INTO R;
   >    RESET STATEMENT_TIMEOUT;
   >    RETURN '1';
   > EXCEPTION
   >    WHEN QUERY_CANCELED THEN
   >           RAISE EXCEPTION 'UNABLE TO OBTAIN LOCK... :%';
   > END;
   > $BODY$
   
   statement_timeout limits the time taken for an *interactive* command,
   not the time for an individual command inside a function.  What this
   means is you can't change it around inside a function and expect
   anything to happen --- the timeout, or lack of it, for the current
   interactive command was determined long before your function got called.
   
   			regards, tom lane