From: | Marcelo Lacerda <marceloslacerda(at)gmail(dot)com> |
---|---|
To: | pavel(dot)stehule(at)gmail(dot)com |
Cc: | mmoncure(at)gmail(dot)com, tgl(at)sss(dot)pgh(dot)pa(dot)us, pgsql-general(at)postgresql(dot)org |
Subject: | Re: check_function_bodies not doing much |
Date: | 2018-08-08 03:32:21 |
Message-ID: | CAPmRTtOw_KueL2eSf4R3dJZU7G33YMcCsF3yqhaGoJc2-O6bgw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'll take a look at it. Thanks for the recommendation.
On Tue, Aug 7, 2018 at 7:22 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:
>
>
> 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
>>>
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | czezz | 2018-08-08 14:09:07 | Postgres - search for value throughout many tables? |
Previous Message | Pavel Stehule | 2018-08-07 22:21:55 | Re: check_function_bodies not doing much |