I'll take a look at it. Thanks for the recommendation. On Tue, Aug 7, 2018 at 7:22 PM Pavel Stehule <pavel.steh...@gmail.com> wrote:
> > > 2018-08-08 0:02 GMT+02:00 Marcelo Lacerda <marceloslace...@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 <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 >>> >> >
