Re: check_function_bodies not doing much

2018-08-08 0:02 GMT+02:00 Marcelo Lacerda <marceloslacerda(at)gmail(dot)com>:

> That's a whole different nightmare that I'm expecting.
>

>
> "Yep I double-checked all my functions to see if any would break if I
> change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and
> everything is ok."
>
> *1 month later*
>
> "Why is this error log for this application that I wrote one year ago so
> big? I haven't changed anything!"
>
> Error table mytable has no column a
> Error table mytable has no column a
> Error table mytable has no column a
> ...
>
> It's frustrating that the references that a function make to the tables
> and fields it access aren't taken in account for the validation of whether
> a change to the structure of the database breaks the APIs that the database
> exposes.
>

This cannot be done due possible dynamic SQL. And this issue solve
plpgsql_check really well.

Regards

Pavel

>
> On Tue, Aug 7, 2018 at 6:44 PM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> On Tue, Aug 7, 2018 at 2:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> >
>> > Marcelo Lacerda <marceloslacerda(at)gmail(dot)com> writes:
>> > > I was trying to get postgres to warn me that I'm referencing a table
>> that
>> > > it doesn't exists inside a function so I was told on the IRC to check
>> the
>> > > setting "check_function_bodies", however when I use it in a plpgsql
>> > > function it doesn't actually check if the tables in the body exist.
>> Is this
>> > > the correct behavior?
>> >
>> > Yes. It's supposed to be a syntax check, not a check that the function
>> > would work when executed. (Depending on the particular PL you're using,
>> > which you didn't mention, it might be a pretty weak syntax check too.)
>> >
>> > An example of why a thorough check would be inadvisable is that a
>> trigger
>> > function might contain references to OLD and NEW that are in code paths
>> > protected by checks on the trigger event type. That could be perfectly
>> > OK, but a static check couldn't tell.
>> >
>> > I believe there are some external tools floating around that check
>> things
>> > more aggressively, and hence with a higher rate of false positives.
>>
>> The only valid use of this GUC that I can think of is to work around
>> this problem;
>> postgres=# create or replace function f() returns void as
>> $$
>> create temp table x(id int);
>> delete from x;
>> $$ language sql;
>> ERROR: relation "x" does not exist
>>
>> ...I've since given up on writing plain sql functions except for
>> inline cases though so I don't use it anymore. Static resolution of
>> tables is not very useful since the state of the database as the time
>> of function creation is different than what it might be when the
>> function is run (as opposed to compiled languages obviously).
>>
>> merlin
>>
>