[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: plpgsql_check_function issue after upgrade
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2022-11-30 4:34:19
Message-ID: CAFj8pRDZ4z4T0z6iAcpokkD-vz43Xq7Q2qCJUtg-vABGZshUhw () mail ! gmail ! com
[Download RAW message or body]
st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
>
>
> st 30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <
> shashidharreddy001@gmail.com> napsal:
>
>> I have tried updating after upgrade but that wasn't working, so I have
>> dropped and recreated the extension.
>> Now it is crashing every time when we call the function.
>>
>
> what is version od plpgsql_check on Postgres 12, what is version of
> plpgsql_check on Postgres 13 (with version of minor release)?
>
> Can you send backtrace?
> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
>
Do you have installed some other extensions?
>
>
>
>
>
>>
>> On Tue, 29 Nov, 2022, 9:58 pm Pavel Stehule, <pavel.stehule@gmail.com>
>> wrote:
>>
>>>
>>>
>>> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
>>> shashidharreddy001@gmail.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
>>>
>>> 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@gmail.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.stehule@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>>
>>>>>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
>>>>>> shashidharreddy001@gmail.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
>>>>>
>>>>
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <<a \
href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>> \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">st \
30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <<a \
href="mailto:shashidharreddy001@gmail.com" \
target="_blank">shashidharreddy001@gmail.com</a>> napsal:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="auto">I have tried updating after \
upgrade but that wasn't working, so I have dropped and recreated the \
extension.<div dir="auto">Now it is crashing every time when we call the function. \
</div></div></blockquote><div><br></div><div>what is version od plpgsql_check on \
Postgres 12, what is version of plpgsql_check on Postgres 13 (with version of minor \
release)?</div><div><br></div><div>Can you send backtrace? <a \
href="https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD" \
target="_blank">https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_Po \
stgreSQL_backend_on_Linux/BSD</a></div></div></div></blockquote><div><br></div><div>Do \
you have installed some other extensions?</div><div><br></div><div> \
<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
class="gmail_quote"><div><br></div><div><br></div><div><br></div><div> \
<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 29 Nov, 2022, 9:58 pm \
Pavel Stehule, <<a href="mailto:pavel.stehule@gmail.com" \
target="_blank">pavel.stehule@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr"><br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">út 29. 11. 2022 v 16:37 \
odesílatel shashidhar Reddy <<a href="mailto:shashidharreddy001@gmail.com" \
rel="noreferrer" target="_blank">shashidharreddy001@gmail.com</a>> \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
dir="auto">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.</div></blockquote><div><br></div><div>Maybe \
you need to run ALTER EXTENSION plpgsql_check UPDATE before first usage in pg \
13</div><div><br></div><div>If the extension works after re-installation, then the \
problem is not in an extension.</div><div><br></div><div><br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <<a \
href="mailto:shashidharreddy001@gmail.com" rel="noreferrer" \
target="_blank">shashidharreddy001@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hello \
Pavel,<div><br></div><div>This is the function causing the issue on all servers, and \
also i noticed when I use <span style="color:rgb(80,0,80)"><b>plpgsql_check_function \
</b>in any function I am facing the same issue.</span></div><div><span \
style="color:rgb(80,0,80)"><br></span></div></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <<a \
href="mailto:pavel.stehule@gmail.com" rel="noreferrer noreferrer" \
target="_blank">pavel.stehule@gmail.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi<div dir="ltr"><br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">út 29. 11. 2022 v 13:49 \
odesílatel shashidhar Reddy <<a href="mailto:shashidharreddy001@gmail.com" \
rel="noreferrer noreferrer" target="_blank">shashidharreddy001@gmail.com</a>> \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
dir="ltr">Hello,<div><br></div><div>Recently we have upgraded postgres from version \
12 to 13 and upgraded
<span style="font-size:11pt;line-height:107%;font-family:Calibri,sans-serif">plpgsql_check \
to the latest version but after upgrade when calling the below function causing \
postgres restart .</span></div><div><span \
style="font-size:11pt;line-height:107%;font-family:Calibri,sans-serif"><br></span></div><div>CREATE \
OR REPLACE FUNCTION pro.po_check(<br> )<br> RETURNS void<br> LANGUAGE \
'plpgsql'<br> COST 100<br> VOLATILE SECURITY DEFINER PARALLEL \
UNSAFE<br>AS $BODY$<br>DECLARE<br>BEGIN<br><br> PERFORM p.oid, n.nspname, \
p.proname, plpgsql_check_function(p.oid)<br> FROM pg_catalog.pg_namespace n<br> \
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid<br> JOIN \
pg_catalog.pg_language l ON p.prolang = l.oid<br> WHERE l.lanname = \
'plpgsql' AND p.prorettype <> 2279<br> and upper(n.nspname) like \
upper('Pro');<br><br>END;<br>$BODY$;</div><div><br></div><div>and the error \
in syslogs shows</div><div>kernel: [93631.415790] postgres[86383]: segfault at 80 ip \
00007f07f3e3eefd sp 00007fffcf1db500 error 4 in \
plpgsql_check.so[7f07f3e2e000+34000]<br \
clear="all"></div></div></blockquote><div><br></div><div>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)?<br></div><div><br></div><div>Regards</div><div><br></div><div>Pavel<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><br></div>-- <br><div \
dir="ltr">Shashidhar</div></div></div> </blockquote></div></div>
</blockquote></div><br clear="all"><div><br></div>-- <br><div \
dir="ltr">Shashidhar</div> </blockquote></div>
</blockquote></div></div>
</blockquote></div>
</blockquote></div></div>
</blockquote></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic