Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
İlyas Derse
Date:
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 ?

From
Patrick FICHE
Date:

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 ?
Attachment

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

From
Michael Lewis
Date:
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.

Attachment

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

From
Thomas Kellerer
Date:
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 ?

From
Jeff Janes
Date:


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