Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdFOh-0000Ed-N6 for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2020 16:24:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdFOg-0007ZL-HK for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2020 16:24:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kdFOg-0007Xz-BN for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2020 16:24:30 +0000 Received: from n3.nabble.com ([162.255.23.22]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kdFOW-0002bK-KO for pgsql-bugs@postgresql.org; Thu, 12 Nov 2020 16:24:27 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id A8F9622A3A8F3 for ; Thu, 12 Nov 2020 09:24:16 -0700 (MST) Date: Thu, 12 Nov 2020 09:24:16 -0700 (MST) From: chenyanfei To: pgsql-bugs@postgresql.org Message-ID: <1605198256689-0.post@n3.nabble.com> Subject: problem with create function and drop type MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_139099_1912291928.1605198256689" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_139099_1912291928.1605198256689 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =E3=80=90Version=E3=80=91I use the source code compile the database.[postgr= es@bogon ~]$ psql postgrespsql (14devel)Type "help" for help.postgres=3D# select version(); = =20 version--------------------------------------------------------------------= ------------ PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit=E3=80=90Problem=E3=80=91When we test the concurrent commands, we enc= ounter a problem about the the function. The test case like this:drop function if exists test_function;drop type if exists test_type;create type test_type as (a varchar);create or replace function test_function(t1 test_type[])returns integer as $$beginend;$$ LANGUAGE plpgsql;We get the error when we query th= e function definitionpostgres=3D# drop function if exists test_function;DROP FUNCTIONpostgres=3D# drop type if exists test_type;NOTICE: type "test_type= " does not exist, skippingDROP TYPEpostgres=3D# create type test_type as (a varchar);CREATE TYPEpostgres=3D# create or replace function test_function(t= 1 test_type[])postgres-# returns integer as $$postgres$# beginpostgres$# end;postgres$# $$ LANGUAGE plpgsql;CREATE FUNCTIONpostgres=3D# \df2020-11-1= 3 00:18:49.567 CST [11000] ERROR: cache lookup failed for type 163892020-11-13 00:18:49.567 CST [11000] STATEMENT: SELECT n.nspname as "Schema", p.proname as "Name", =20 pg_catalog.pg_get_function_result(p.oid) as "Result data type", =20 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", = =20 CASE p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window'= =20 WHEN 'p' THEN 'proc' ELSE 'func' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.o= id) =20 AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ORDER BY 1, 2, 4;ERROR: cache lookup failed fo= r type 16389=E3=80=90Analyze=E3=80=91To reprocedure the problem, we anlayze o= ur scripts like below. Accounting for the concurrent commands, we use the gdb tool to simulate two sessions.So we set a break point at pl_handler.c:514=09=09/* Test-compile the function */=09=09plpgsql_compile(fake_fcinfo, true);=09=09= /*=09=09 * Disconnect from SPI manager=09=09 */=09=09if ((rc =3D SPI_finish()) !=3D SP= I_OK_FINISH)=09=09 elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc));When the create command execute success afeter plpgsql_compile function, in other session we execute "drop type if exists test_type;" The drop command is successpostgres=3D# drop function if exists test_function;NOTICE: function test_function() does not exist, skippingDROP FUNCTIONpostgres=3D# drop type= if exists test_type;DROP TYPEI think the problem is that the drop type command can not query any depend informations through the pg_depend because the create command doesnot commit now, so it can drop the type. But the create command has done the last check work through plpgsql_compile function and will commit it now. However, because of the depend type has been dropped, s= o the function can not work now. But it also shows create successfully. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html ------=_Part_139099_1912291928.1605198256689 Content-Type: text/html; charset=UTF8 Content-Transfer-Encoding: quoted-printable =E3=80=90Version=E3=80=91 I use the source code compile the database. [postgres@bogon ~]$ psql postgres psql (14devel) Type "help" for help. postgres=3D# select version(); version ---------------------------------------------------------------------------= ----- PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64= -bit =E3=80=90Problem=E3=80=91 When we test the concurrent commands, we encounter a problem about the the = function. The test case=20 like this: drop function if exists test_function; drop type if exists test_type; create type test_type as (a varchar); create or replace function test_function(t1 test_type[]) returns integer as $$ begin end; $$ LANGUAGE plpgsql; We get the error when we query the function definition postgres=3D# drop function if exists test_function; DROP FUNCTION postgres=3D# drop type if exists test_type; NOTICE: type "test_type" does not exist, skipping DROP TYPE postgres=3D# create type test_type as (a varchar); CREATE TYPE postgres=3D# create or replace function test_function(t1 test_type[]) postgres-# returns integer as $$ postgres$# begin postgres$# end; postgres$# $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=3D# \df 2020-11-13 00:18:49.567 CST [11000] ERROR: cache lookup failed for type 16= 389 2020-11-13 00:18:49.567 CST [11000] STATEMENT: SELECT n.nspname as "Schema= ", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data typ= es", CASE p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc' ELSE 'func' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D p.pronamespac= e WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ORDER BY 1, 2, 4; ERROR: cache lookup failed for type 16389 =E3=80=90Analyze=E3=80=91 To reprocedure the problem, we anlayze our scripts like below. Accounting f= or the concurrent commands, we use the gdb tool to simulate two sessions. So we set a break point at pl_handler.c:514 =09=09/* Test-compile the function */ =09=09plpgsql_compile(fake_fcinfo, true); =09=09/* =09=09 * Disconnect from SPI manager =09=09 */ =09=09if ((rc =3D SPI_finish()) !=3D SPI_OK_FINISH) =09=09=09elog(ERROR, "SPI_finish failed: %s", SPI_result_code_string(rc)); When the create command execute success afeter plpgsql_compile function, in= other session we execute "drop type if exists test_type;" The drop command= is success postgres=3D# drop function if exists test_function; NOTICE: function test_function() does not exist, skipping DROP FUNCTION postgres=3D# drop type if exists test_type; DROP TYPE I think the problem is that the drop type command can not query any depend = informations through the pg_depend because the create command doesnot commi= t now, so it can drop the type. But the create command has done the last ch= eck work through plpgsql_compile function and will commit it now. However, = because of the depend type has been dropped, so the function can not work n= ow. But it also shows create successfully. =09 =09 =09

Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. ------=_Part_139099_1912291928.1605198256689--