Re: plpgsql_check_function issue after upgrade

út 29. 11. 2022 v 17:28 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
> shashidharreddy001(at)gmail(dot)com> napsal:
>
>> Plogsql check version is 2.2 and one more finding is before calling the
>> function if we drop and recreate the plpgsql_check extension there is no
>> issue, but each time we can't drop and create.
>>
>
> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before first
> usage in pg 13
>

after checking source code, I was partially wrong. plpgsql_check doesn't
support update scripts. So if there are not same versions of plpgsql_check,
you need to reinstall plpgsql_check always

Regards

Pavel

>
> If the extension works after re-installation, then the problem is not in
> an extension.
>
>
>
>> On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
>> shashidharreddy001(at)gmail(dot)com> wrote:
>>
>>> Hello Pavel,
>>>
>>> This is the function causing the issue on all servers, and also i
>>> noticed when I use *plpgsql_check_function *in any function I am facing
>>> the same issue.
>>>
>>>
>>> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>> wrote:
>>>
>>>> Hi
>>>>
>>>>
>>>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
>>>> shashidharreddy001(at)gmail(dot)com> napsal:
>>>>
>>>>> Hello,
>>>>>
>>>>> Recently we have upgraded postgres from version 12 to 13 and upgraded plpgsql_check
>>>>> to the latest version but after upgrade when calling the below function
>>>>> causing postgres restart .
>>>>>
>>>>> CREATE OR REPLACE FUNCTION pro.po_check(
>>>>> )
>>>>> RETURNS void
>>>>> LANGUAGE 'plpgsql'
>>>>> COST 100
>>>>> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
>>>>> AS $BODY$
>>>>> DECLARE
>>>>> BEGIN
>>>>>
>>>>> PERFORM p.oid, n.nspname, p.proname, plpgsql_check_function(p.oid)
>>>>> FROM pg_catalog.pg_namespace n
>>>>> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
>>>>> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
>>>>> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
>>>>> and upper(n.nspname) like upper('Pro');
>>>>>
>>>>> END;
>>>>> $BODY$;
>>>>>
>>>>> and the error in syslogs shows
>>>>> kernel: [93631.415790] postgres[86383]: segfault at 80 ip
>>>>> 00007f07f3e3eefd sp 00007fffcf1db500 error 4 in
>>>>> plpgsql_check.so[7f07f3e2e000+34000]
>>>>>
>>>>
>>>> it can be a bug in plpgsql_check. But I am not able to fix it without
>>>> some information. Can you send the reproducer (minimal example of your
>>>> code, that reproduce this error)?
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>>>
>>>>> --
>>>>> Shashidhar
>>>>>
>>>>
>>>
>>> --
>>> Shashidhar
>>>
>>