Hello
I try to play with different implementations of plpgsql deep checking.
The most important task of deep checking is creating plans for all
queries and expressions in function. The prerequisite for this task is
knowledge of data types of all variables. Record and row types is
break, but there is workaround - we are able to derive data types from
plans and we can assign with high success rate valid types to this
kind variables. We are not able to do with result of dynamic SQL and
temporary tables still - just we are not able to detect possible
errors for dynamic queries ever.
There are four possible implementations:
0) special recursive check routine + derivation data types from plans:
+ zero impact on current code, readability, - one other long recursive
routine
a) enhance parser + derivation data types from plans: + no new
recursive routine, - order of check depends on bison processing order,
result needs a final sort
b) enhance executor nodes + take data types from fake execution: +
relative less new code, - decrease readability of executor code, 20%
slowdown of CPU bottle neck code (new code is on critical path)
I tested code (this is a worst situation) - patch is in attachment (it
is WIP - just for test of impact new code to performance)
CREATE OR REPLACE FUNCTION public.test()
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare i int;
declare j int;
begin
i := 1;
while i < 10000 loop
j := 1;
while j < 1000 loop
j := j + 1;
end loop;
i := i + 1;
end loop;
return i;
end;
$function$
c) merge checking and dumping and derivation data from plans: + zero
impact on current code, readability, - some new code
my @0 works well, but was repeatedly rejected by Tom and Heikki, @a
needs final sort - so it needs more complex infrastructure for
creating result tuplestore, @b has mensurable performance impact
(from 9454 to 11274 ms), so there are only @c.
comments, notices?
Regards
Pavel
