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