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

Поиск

Список

Период

Сортировка

От	Patrick FICHE
Тема	RE: How can I set a timeout for a locked table in Function ?
Дата	3 января 2020 г. 16:04:07
Msg-id	VI1PR0501MB266933A9E57F7FDD74E619F9EF230@VI1PR0501MB2669.eurprd05.prod.outlook.com обсуждение исходный текст
Ответ на	How can I set a timeout for a locked table in Function ? (İlyas Derse <ilyasderse@gmail.com>)
Ответы	Re: How can I set a timeout for a locked table in Function ?
Список	pgsql-general

Дерево обсуждения

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@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$;