Обсуждение: check_function_bodies not doing much

On Tue, Aug 7, 2018 at 2:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Marcelo Lacerda <marceloslacerda@gmail.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

2018-08-08 0:02 GMT+02:00 Marcelo Lacerda <marceloslacerda@gmail.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@gmail.com> wrote:

On Tue, Aug 7, 2018 at 2:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Marcelo Lacerda <marceloslacerda@gmail.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