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

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/&gt;

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 ?

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.

https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

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.

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