Re: plpgsql_check_function issue after upgrade

Hi

po 5. 12. 2022 v 15:22 odesílatel shashidhar Reddy <
shashidharreddy001(at)gmail(dot)com> napsal:

> Hello Pavel,
>
> I have re installed it after upgrade as update option was not working.
>
> I could see the error in syslogs as error 4 in plpgsql_check.so and the
> databases are restarting.
>

1. please, don't send top post messages

2. There can be bugs in plpgsql_check, but without more detailed
information I am not able to fix it. Your example is working fine on my
computer.

can you run this function

CREATE OR REPLACE FUNCTION po_check(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE r record;
BEGIN
FOR r IN SELECT p.oid, n.nspname, p.proname
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');
LOOP
RAISE NOTICE '>>% %<<', r.oid, r.nspname || '.' || r.proname;
PERFORM plpgsql_check_function(r.oid);
RAISE NOTICE 'ok';
END LOOP;
END;
$BODY$;

What is an output?

>
> On Mon, 5 Dec, 2022, 5:51 pm Pavel Stehule, <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> ú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
>>>>>
>>>>