Обсуждение: How can I set a timeout for a locked table in Function ?

Поиск
Список
Период
Сортировка

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

От
İlyas Derse
Дата:
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 ?

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

 

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$;
select * from public."testlock"(); 
How can I do ?
Вложения

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

От
Michael Lewis
Дата:
I would not think that behavior would extend to lock_timeout based on the explanation on stackexchange. I would assume that the potentially long runtime in this function is mostly in acquiring the lock and not doing the update given the implied primary key in the where clause, so perhaps lock_timeout would fit the need.

Or perhaps this is a much-simplified example and the real problem is not apparent. Why take an exclusive lock on an entire table to update a single row? What is this locks table for? Would advisory locks be the proper solution to the root problem perhaps? Just throwing things out there since context was lacking in the original question.

Вложения

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

От
Thomas Kellerer
Дата:
Michael Lewis schrieb am 03.01.2020 um 18:00:

> Why take an exclusive lock on an entire table to update a single row?

That's what I was asking myself as well.

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

От
Jeff Janes
Дата:


On Fri, Jan 3, 2020 at 1:05 PM Thomas Kellerer <spam_eater@gmx.net> wrote:
Michael Lewis schrieb am 03.01.2020 um 18:00:

> Why take an exclusive lock on an entire table to update a single row?

That's what I was asking myself as well.


Note that the code takes "row exclusive", not "exclusive".  It is several notches less restrictive, and is the mode an UPDATE statement is going to take anyway.  It still isn't clear why he is doing it, however.

Cheers,

Jeff