From: | Patrick FICHE <Patrick(dot)Fiche(at)aqsacom(dot)com> |
---|---|
To: | İlyas Derse <ilyasderse(at)gmail(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | RE: How can I set a timeout for a locked table in Function ? |
Date: | 2020-01-03 13:04:07 |
Message-ID: | VI1PR0501MB266933A9E57F7FDD74E619F9EF230@VI1PR0501MB2669.eurprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
[01-03_AQSA_Main_Corporate_Logo_JPEG_White_Low.jpg]<http://www.aqsacom.com/>
From: İlyas Derse <ilyasderse(at)gmail(dot)com>
Sent: Friday, January 3, 2020 10:54 AM
To: pgsql-general(at)lists(dot)postgresql(dot)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 ?
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2020-01-03 17:00:40 | Re: How can I set a timeout for a locked table in Function ? |
Previous Message | İlyas Derse | 2020-01-03 09:53:50 | How can I set a timeout for a locked table in Function ? |