Re: merging some features from plpgsql2 project

2017-01-02 20:16 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 1/2/17 12:06 PM, Pavel Stehule wrote:
>
>> SELECT (polymorphiccomposite).* INTO c1, c2; -- take first two columns
>>
>> SELECT xx FROM tab ORDER BY yy INTO target -- more rows not a issue
>>
>> I understand plpgsql_extra_errors as feature that can be enabled on
>> developer, test, or preprod environments and can help to identify some
>> strange places.
>>
>
> Yes, but the two cases you mentioned above are the "strange" cases, and
> you should have to do something extra to allow those, not the other way
> around.

The second example is really strange. But the first example is used in
composite types conversion - when you convert from base to extend type.
This routine is used in plpgsql when you use a assignment statement

composite_var := another_composite_var

>
> I think instead of tying these to extra_*, each GUC should accept a
>> LOG level.
>>
>>
>> Why? Why the none, warning, error are not enough? Why are you think so
>> separate GUC can be better than plpgsql_extra_* ?
>>
>> The fast setting plpgsql.extra_errors = 'all' can switch to some "safe"
>> configuration.
>> The fast setting plpgsql.extra_warnings = 'all' can helps with
>> identification, but doesn't break production (or doesn't breaks other
>> tests)
>>
>
> I see two problems with those settings:
>
> 1) Neither is enabled by default, so 90% of users have no idea they exist.
> Obviously that's an easy enough fix, but...
>

We can strongly talk about it - there can be a chapter in plpgsql doc. Now,
the patterns and antipatterns are not officially documented.

> 2) There's no way to incrementally change those values for a single
> function. If you've set extra_errors = 'all' globally, a single function
> can't say "turn off the too many rows setting for this function".
>

We can enhance the GUC syntax like "all -too_many_rows,-xxx"

>
> BTW, while I can see value in being able to change these settings for an
> entire function, I think the recommended use should be to only change them
> for a specific statement.

What you can do in plain assign statement

target := expression ?

My border is any compatibility break - and I would not to across it. First
issue is probably harder

related to typo "select 1 x into c1,c2" and it can be detected by
plpgsql_check.

Second issue is not a performance issue today (we read only 2 rows
everytime) and it is hard how often it returns wrong result. This issue
cannot be detected by plpgsql_check now.

Regards

Pavel

>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)
>