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
plpgsql_check_implementation.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers