Re: Curing plpgsql's memory leaks for statement-lifespan values
| От | Pavel Stehule |
|---|---|
| Тема | Re: Curing plpgsql's memory leaks for statement-lifespan values |
| Дата | |
| Msg-id | CAFj8pRBJrG_NShceWO9yg8ETQAz6bERPUEY6Csz086bMZK7sXA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Curing plpgsql's memory leaks for statement-lifespan values (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Curing plpgsql's memory leaks for statement-lifespan values
|
| Список | pgsql-hackers |
Hi
2016-07-27 16:49 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Jul 25, 2016 at 6:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I suppose that a fix based on putting PG_TRY blocks into all the affected
>> functions might be simple enough that we'd risk back-patching it, but
>> I don't really want to go that way.
> try/catch blocks aren't completely free, either, and PL/pgsql is not
> suffering from a deplorable excess of execution speed.
BTW, just to annotate that a bit: I did some measurements and found out
that on my Linux box, creating/deleting a memory context
(AllocSetContextCreate + MemoryContextDelete) is somewhere around 10x
more expensive than a PG_TRY block. This means that the PG_TRY approach
would actually be faster for cases involving only a small number of
statements-needing-local-storage within a single plpgsql function
execution. However, the memory context creation cost is amortized across
repeated executions of a statement, whereas of course PG_TRY won't be.
We can roughly estimate that PG_TRY would lose any time we loop through
the statement in question more than circa ten times. So I believe the
way I want to do it will win speed-wise in cases where it matters, but
it's not entirely an open-and-shut conclusion.
Anyway, there are enough other reasons not to go the PG_TRY route.
I did some synthetic benchmarks related to plpgsql speed - bubble sort and loop over handling errors and I don't see any slowdown
handling exceptions is little bit faster with your patch
CREATE OR REPLACE FUNCTION public.loop_test(a integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare x int;
begin
for i in 1..a
loop
declare s text;
begin
s := 'AHOJ';
x := (random()*1000)::int;
raise exception '*****';
exception when others then
x := 0; --raise notice 'handled';
end;
end loop;
end;
$function$
CREATE OR REPLACE FUNCTION public.loop_test(a integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
declare x int;
begin
for i in 1..a
loop
declare s text;
begin
s := 'AHOJ';
x := (random()*1000)::int;
raise exception '*****';
exception when others then
x := 0; --raise notice 'handled';
end;
end loop;
end;
$function$
head - 100K loops 640ms, patched 610ms
Regards
Pavel
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
В списке pgsql-hackers по дате отправления: