Thread: ToDo: plpgsql plugin for query and expression verification
Hello I thinking about more restrictive query and expression checking than now. Used parser checking isn't enough - so some possible bugs can be detected in production stage. Other problem is using any expression as SELECT expr. The request on validation can be different and it is probably for more advanced users - so it could be wrapped to some plugin. So users can exactly set an level for checking that is the best for they. postgres=# set check_function_bodies to on;SET postgres=# create or replace function foo(a varchar, b varchar) returns varchar as $$ begin a = current_date; -- result type is different then target type return a || b || c; -- simple expression has unknownsymbol end; $$ language plpgsql; CREATE FUNCTION I think, so these problem have to be identified in compile stage - but it can be too strict for all (and can slow down production) - it is reason for plugin. What do you think about this idea? Regards Pavel Stehule
2010/2/16 Pavel Stehule <pavel.stehule@gmail.com>: > I think, so these problem have to be identified in compile stage - but > it can be too strict for all (and can slow down production) - it is > reason for plugin. > > What do you think about this idea? How do you identify them? Running function body cannot be applied if the function is volatile. Also, I don't see how do you choose function argument values even in immutable cases. Regards, -- Hitoshi Harada
2010/2/16 Hitoshi Harada <umi.tanuki@gmail.com>: > 2010/2/16 Pavel Stehule <pavel.stehule@gmail.com>: >> I think, so these problem have to be identified in compile stage - but >> it can be too strict for all (and can slow down production) - it is >> reason for plugin. >> >> What do you think about this idea? > > How do you identify them? Running function body cannot be applied if > the function is volatile. Also, I don't see how do you choose function > argument values even in immutable cases. It is issue only for dynamic sql and polymorphic functions. But for all others we can do full check in validation stage. I thinking about similar tool to lint - just for plpgsql function. It cannot detect all bugs, but it can diagnose 99% of possible issues. I don't would to execute function - it is useless because you need good UI for execution all path. My idea is different. gram.y has check_sql_expr rutine. This is used for parser checking every static SQL fragment in plpgsql function. With some hook we can do full plan generation instead. Regards Pavel Stehule > > Regards, > > -- > Hitoshi Harada >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I don't would to execute function - it is useless because you need
> good UI for execution all path. My idea is different. gram.y has
> check_sql_expr rutine. This is used for parser checking every static
> SQL fragment in plpgsql function. With some hook we can do full plan
> generation instead.
Previous proposals in this line have foundered on examples like
functions that create a temp table and then manipulate it.
Only DDL-free functions can be statically checked in the way
you suggest.
Between that and the parameter-related limitations that Hitoshi
points out, the use case seems to be rather restricted ...
regards, tom lane
2010/2/16 Pavel Stehule <pavel.stehule@gmail.com>: > 2010/2/16 Hitoshi Harada <umi.tanuki@gmail.com>: >> 2010/2/16 Pavel Stehule <pavel.stehule@gmail.com>: >>> I think, so these problem have to be identified in compile stage - but >>> it can be too strict for all (and can slow down production) - it is >>> reason for plugin. >>> >>> What do you think about this idea? >> >> How do you identify them? Running function body cannot be applied if >> the function is volatile. Also, I don't see how do you choose function >> argument values even in immutable cases. > > It is issue only for dynamic sql and polymorphic functions. But for > all others we can do full check in validation stage. I thinking about > similar tool to lint - just for plpgsql function. It cannot detect all > bugs, but it can diagnose 99% of possible issues. > > I don't would to execute function - it is useless because you need > good UI for execution all path. My idea is different. gram.y has > check_sql_expr rutine. This is used for parser checking every static > SQL fragment in plpgsql function. With some hook we can do full plan > generation instead. Hmm, type mismatching can be checked by your suggestion, but that's it. The true answer to your original post might be "write unit test", isn't it? Regards, -- Hitoshi Harada
2010/2/16 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> I don't would to execute function - it is useless because you need >> good UI for execution all path. My idea is different. gram.y has >> check_sql_expr rutine. This is used for parser checking every static >> SQL fragment in plpgsql function. With some hook we can do full plan >> generation instead. > > Previous proposals in this line have foundered on examples like > functions that create a temp table and then manipulate it. > Only DDL-free functions can be statically checked in the way > you suggest. > No and yes. yes - 100% test are possible only on a) DDL free functions, b) 100% static schema. no - in reality schema is usually stable and we are able to check sql using stable schema. This proposal isn't about ideal checking - it isn't possible. It is about the maximum from what is possible. I would to identify bugs in not often using execution path before production. This case is real. Stored procedures works well and after half of year we finding broken identifiers in some queries. > Between that and the parameter-related limitations that Hitoshi > points out, the use case seems to be rather restricted ... why? why is it better? do you have a way for runtime checking of all possible execution path? regards Pavel > > regards, tom lane >