Обсуждение: enhancing plpgsql debug api - hooks on statements errors and function errors
Hi
When I implemented profiler and coverage check to plpgsql_check I had to write a lot of hard maintaining code related to corect finishing some operations (counter incrementing) usually executed by stmt_end and func_end hooks. It is based on the fmgr hook and its own statement call stack. Can be nice if I can throw this code and use some nice buildin API.
Can we enhance dbg API with two hooks stmt_end_err func_end_err ?
These hooks can be called from exception handlers before re raising.
Or we can define new hooks like executor hooks - stmt_exec and func_exec. In custom hooks the exception can be catched.
What do you think about this proposal?
regards
Pavel
Re: enhancing plpgsql debug api - hooks on statements errors and function errors
От
Pavel Stehule
Дата:
Hi
út 25. 4. 2023 v 10:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
HiWhen I implemented profiler and coverage check to plpgsql_check I had to write a lot of hard maintaining code related to corect finishing some operations (counter incrementing) usually executed by stmt_end and func_end hooks. It is based on the fmgr hook and its own statement call stack. Can be nice if I can throw this code and use some nice buildin API.Can we enhance dbg API with two hooks stmt_end_err func_end_err ?These hooks can be called from exception handlers before re raising.Or we can define new hooks like executor hooks - stmt_exec and func_exec. In custom hooks the exception can be catched.What do you think about this proposal?
I did quick and ugly benchmark on worst case
CREATE OR REPLACE FUNCTION public.speedtest(i integer)
RETURNS void
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare c int = 0;
begin
while c < i
loop
c := c + 1;
end loop;
raise notice '%', c;
end;
$function$
RETURNS void
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare c int = 0;
begin
while c < i
loop
c := c + 1;
end loop;
raise notice '%', c;
end;
$function$
and is possible to write some code (see ugly patch) without any performance impacts if the hooks are not used. When hooks are active, then there is 7% performance lost. It is not nice - but this is the worst case. The impact on real code should be significantly lower
Regards
Pavel
Вложения
Re: enhancing plpgsql debug api - hooks on statements errors and function errors
От
Kirk Wolak
Дата:
On Tue, Apr 25, 2023 at 11:33 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hiút 25. 4. 2023 v 10:27 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:HiWhen I implemented profiler and coverage check to plpgsql_check I had to write a lot of hard maintaining code related to corect finishing some operations (counter incrementing) usually executed by stmt_end and func_end hooks. It is based on the fmgr hook and its own statement call stack. Can be nice if I can throw this code and use some nice buildin API.Can we enhance dbg API with two hooks stmt_end_err func_end_err ?These hooks can be called from exception handlers before re raising.Or we can define new hooks like executor hooks - stmt_exec and func_exec. In custom hooks the exception can be catched.What do you think about this proposal?
+1. I believe I bumped into a few of these use cases with plpgsql_check (special handling for security definer and exception handling).
My cursory review of the patch file is that despite the movement of the code, it feels pretty straight forward.
The 7% overhead appears in a "tight loop", so it's probably really overstated. I will see if I can apply this and do a more realistic test.
[I have a procedure that takes ~2hrs to process a lot of data, I would be curious to see this impact and report back]
I did quick and ugly benchmark on worst caseCREATE OR REPLACE FUNCTION public.speedtest(i integer)
RETURNS void
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare c int = 0;
begin
while c < i
loop
c := c + 1;
end loop;
raise notice '%', c;
end;
$function$and is possible to write some code (see ugly patch) without any performance impacts if the hooks are not used. When hooks are active, then there is 7% performance lost. It is not nice - but this is the worst case. The impact on real code should be significantly lowerRegardsPavel