Search Postgresql Archives

Re: plpgsql_check_function issue after upgrade

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

po 5. 12. 2022 v 15:22 odesílatel shashidhar Reddy <shashidharreddy001@xxxxxxxxx> 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.stehule@xxxxxxxxx> wrote:


út 29. 11. 2022 v 17:28 odesílatel Pavel Stehule <pavel.stehule@xxxxxxxxx> napsal:


út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <shashidharreddy001@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
Hi


út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <shashidharreddy001@xxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux