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

[Patrick FICHE]

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

[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]<http://www.aqsacom.com/>

From: İlyas Derse <ilyasde...@gmail.com>
Sent: Friday, January 3, 2020 10:54 AM
To: pgsql-general@lists.postgresql.org
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 ?