Re: check_function_bodies not doing much

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.

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
>