RE: How can I set a timeout for a locked table in Function ?

[Patrick FICHE <Patrick.Fiche@xxxxxxxxxxx>]

Hi,

 

You cannot set the statement_timeout within a function.

You have to set it before you call the function.

For example, you can try :

BEGIN;

  SET LOCAL statement_timeout = 6000;

  SELECT * from public.”testlock”();

COMMIT;

 

The reason why is explained in this post : https://dba.stackexchange.com/questions/82977/why-set-local-statement-timeout-does-not-work-as-expected-with-postgresql-func

 

Regards,

 

Patrick Fiche

Database Engineer, Aqsacom Sas.

c. 33 6 82 80 69 96

 

 

From: İlyas Derse <ilyasderse@xxxxxxxxx>
Sent: Friday, January 3, 2020 10:54 AM
To: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: How can I set a timeout for a locked table in Function ?

 

CREATE OR REPLACE FUNCTION public."testlock"()
    RETURNS TABLE
         (
                  id integer,
                  name character varying, 
                  state integer, 
                  owner character varying
         ) 
    LANGUAGE 'plpgsql'
AS $BODY$        
          BEGIN  
                  SET  "statement_timeout" = 6000; --- It's not changing. !!
                 LOCK TABLE public."lock" IN ROW EXCLUSIVE MODE;   
                                   UPDATE public."lock" as l set name = 'deneme' WHERE l."id" = 4;         
         
                 RETURN QUERY 
                          select l."id",l."name",l."state",l."owner" from public."lock" as l, pg_sleep(10) where l."id" = 4;            
         END; 
$BODY$;

select * from public."testlock"(); 

How can I do ?