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 unknown symbol
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
Show quoted text
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
Show quoted text
regards, tom lane