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 1pbqBK-0001VS-26 for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Mar 2023 21:58:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pbqBI-0005lx-Hk for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Mar 2023 21:58:12 +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 1pbqBI-0005lo-27 for pgsql-hackers@lists.postgresql.org; Mon, 13 Mar 2023 21:58:12 +0000 Received: from mail-qt1-x831.google.com ([2607:f8b0:4864:20::831]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pbqBC-0003zb-Kw for pgsql-hackers@lists.postgresql.org; Mon, 13 Mar 2023 21:58:11 +0000 Received: by mail-qt1-x831.google.com with SMTP id c18so14876817qte.5 for ; Mon, 13 Mar 2023 14:58:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pcorp-us.20210112.gappssmtp.com; s=20210112; t=1678744685; h=content-language:thread-index:mime-version:message-id:date:subject :in-reply-to:references:cc:to:from:from:to:cc:subject:date :message-id:reply-to; bh=6DPUO3Qs2ZIqMA5TseBmimRKkrRtmLYicc1i3+Y6YLs=; b=U+6X4hH/c0sJ4I+ZTU9aYAySbnWG2Wxq4cMLsM/DxKk/KCw4dfiRQ+yavbnVWpMmMZ KGCvbqey5BX90GtfyRBuL4LmPwKDvTocccHqDrLotVevrbfS9Wk6teK0tQ8Ts9RqUjkv clnoamiPgwAYgYEhMMXfylieViRVpY5Iy/vliIz9AzkTpk1x4qloM+EpGW4MHPjzVDLV 7NrIBqe4vpcFfiD5wWoOAHpCWIDooHC9XgRvMGZytZ+iPVmpRfBjn3KcjltYwQ5owd4H LUG189IXVEaup9aM1ri1nhuwQyLlCxtI7m8Z+X0GEEdI1tsM1j4MufpOz/N4WAK+BZor kN1w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678744685; h=content-language:thread-index:mime-version:message-id:date:subject :in-reply-to:references:cc:to:from:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=6DPUO3Qs2ZIqMA5TseBmimRKkrRtmLYicc1i3+Y6YLs=; b=N60OQbVpX/frPOrUZCDH9/DpPdSXaPuR7rVsYVMb+aSskypAsoMn5RB/N9tnsAE41N WxFQDzs+7UEhPwmq8Cc7V74Gm8NF5HuGZoZ3QHx/xQcEliufsP4Z7VRPRyst2NswwM8u YUraNcDfHMdQle48+Jl5e49gVNYck9Azi0FWBFZhTIav/k/tgHeu2zlN9uXiyQT5zwbh EqtHZGA8R2siRrTsu+sjcvJz2CeemwwOJUapcDzc3vPgN+TWY4qCld3euDL0DjeNeUtw vmASgAveykuwp7UYMwgkM2vE/yHfHLW9Ueyc575WS9ErmS1Uytr83FO9HAzt+cO/x/39 ayNw== X-Gm-Message-State: AO0yUKWok8y1JyaqDUn6HAfaC5429HdXk1xai3cW9aVgumly8nPOj5od Pzsp2O9Fq82SykWhf/Noadn0Ob2OXp1j+jLkGWNqEw== X-Google-Smtp-Source: AK7set8tTFQsoXhHkS1ktFIfrVeE4Kk6tbqzY5iZLjgzAftwhb15Sibe7sSEJH7H/SEfpIAT9VUMFQ== X-Received: by 2002:a05:622a:138f:b0:3bb:855a:9ed7 with SMTP id o15-20020a05622a138f00b003bb855a9ed7mr62885036qtk.42.1678744684853; Mon, 13 Mar 2023 14:58:04 -0700 (PDT) Received: from F (50-78-240-110-static.hfc.comcastbusiness.net. [50.78.240.110]) by smtp.gmail.com with ESMTPSA id 74-20020a370a4d000000b007456b51ee13sm536574qkk.16.2023.03.13.14.58.03 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 13 Mar 2023 14:58:04 -0700 (PDT) From: "Regina Obe" To: Cc: "'Tom Lane'" , "'Gregory Stark \(as CFM\)'" , , "'Regina Obe'" References: <000401d94bc8$48dff700$da9fe500$@pcorp.us> <167762779361.628976.14009177203307564086.pgcf@coridan.postgresql.org> <005401d95083$26ca24a0$745e6de0$@pcorp.us> <995823.1678478825@sss.pgh.pa.us> <004b01d95394$16123ee0$4236bca0$@pcorp.us> <1012975.1678486476@sss.pgh.pa.us> <005501d953a0$906c4df0$b144e9d0$@pcorp.us> <1019515.1678488437@sss.pgh.pa.us> <001d01d953f2$099b6430$1cd22c90$@pcorp.us> <20230313115916.ivvg4tj54szeu5rj@c19> In-Reply-To: <20230313115916.ivvg4tj54szeu5rj@c19> Subject: RE: Ability to reference other extensions by schema in extension scripts Date: Mon, 13 Mar 2023 17:57:57 -0400 Message-ID: <000701d955f6$dfaaf8a0$9f00e9e0$@pcorp.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0008_01D955D5.5899F4E0" X-Mailer: Microsoft Outlook 15.0 thread-index: AQFb3UkpK58iwMTTldDTs6BRw63MSAJahjgRAXVpL3oC133W8wIdikw1AqkAkisBwBvvuQJrBFYcAhn2WrEDIVE3ogJhu78/rzqBmMA= Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0008_01D955D5.5899F4E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit > On Sat, Mar 11, 2023 at 03:18:18AM -0500, Regina Obe wrote: > > Attached is a revised patch with these changes in place. > > I've given a try to this patch. It builds and regresses fine. > > My own tests also worked fine. As long as ext1 was found in the ext2's > no_relocate list it could not be relocated, and proper error message is given > to user trying it. > > Nitpicking, there are a few things that are weird to me: > > 1) I don't get any error/warning if I put an arbitrary string into no_relocate > (there's no check to verify the no_relocate is a subset of the requires). > > 2) An extension can still reference extensions it depends on without putting > them in no_relocate. This may be intentional, as some substitutions may not > require blocking relocation, but felt inconsistent with the normal > @extschema@ which is never replaced unless an extension is marked as non- > relocatable. > > --strk; > > Libre GIS consultant/developer > https://strk.kbt.io/services.html Attached is a slightly revised patch to fix the extra whitespace in the extend.gml document that Sandro noted to me. Thanks, Regina ------=_NextPart_000_0008_01D955D5.5899F4E0 Content-Type: application/octet-stream; name="0007-Allow-use-of-extschema-reqextname-to-reference.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="0007-Allow-use-of-extschema-reqextname-to-reference.patch" From d7b3ee51af0fbdc7e58fc43473114cd633e09c74 Mon Sep 17 00:00:00 2001=0A= From: Regina Obe =0A= Date: Sun, 5 Feb 2023 01:35:29 -0500=0A= Subject: [PATCH 7/7] Allow @extschema:@ in extension scripts=0A= =0A= This feature allows an extension author to reference=0A= extension schemas of extensions in the requires variable=0A= of the control file using variable syntax=0A= @extschema:@ where is the=0A= name of any of the listed required extensions.=0A= =0A= It also defines another optional config parameter=0A= in the control file called no_relocate=0A= which consists of a subset of the requires extensions=0A= that should have ALTER EXTENSION SET SCHEMA prevented.=0A= =0A= This feature is needed particularly in cases=0A= when another extension packages a function=0A= which references an object from one of its=0A= required extensions and this function is used to back=0A= indexes, constraints, and materialized views.=0A= =0A= Without this feature, because pg_restore removes the paths,=0A= these indexes, constraints, materialized views are not restored=0A= because when the function is called, the function call fails=0A= because items it depends on cannot be schema qualified.=0A= =0A= This feature will allow extension authors to schema qualify=0A= references to required extension objects to prevent this issue.=0A= Aside from this issue, schema qualifying all these references will=0A= improve security by preventing a rogue version of a function from being=0A= used.=0A= =0A= - Extension tests both Makefile and meson.build=0A= - Documentation of the new feature=0A= - Define a new extension control parameter no_relocate=0A= Which is a comma separated list of required extensions=0A= that should be prevented from ALTER .. SET SCHEMA=0A= - Prevent an extension from being relocated if=0A= another extension requires it and has it=0A= listed in it's no_relocate list=0A= - Change execute_extension_script to take as input=0A= required extension oids,=0A= since calling functions already build this list=0A= =0A= Discussion: = https://postgr.es/m/000801d94959%2463a9f520%242afddf60%24%40pcorp.us=0A= ---=0A= doc/src/sgml/extend.sgml | 35 +++++++=0A= src/backend/commands/extension.c | 98 ++++++++++++++++++-=0A= src/test/modules/test_extensions/Makefile | 9 +-=0A= .../expected/test_extensions.out | 41 ++++++++=0A= src/test/modules/test_extensions/meson.build | 7 ++=0A= .../test_extensions/sql/test_extensions.sql | 15 +++=0A= .../test_ext_req_schema1--1.0.sql | 6 ++=0A= .../test_ext_req_schema1.control | 3 +=0A= .../test_ext_req_schema2--1.0--2.0.sql | 7 ++=0A= .../test_ext_req_schema2--1.0.sql | 9 ++=0A= .../test_ext_req_schema2.control | 4 +=0A= .../test_ext_req_schema3--1.0.sql | 13 +++=0A= .../test_ext_req_schema3.control | 5 +=0A= 13 files changed, 245 insertions(+), 7 deletions(-)=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema1.control=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema2.control=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql=0A= create mode 100644 = src/test/modules/test_extensions/test_ext_req_schema3.control=0A= =0A= diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml=0A= index b70cbe83ae..554876672b 100644=0A= --- a/doc/src/sgml/extend.sgml=0A= +++ b/doc/src/sgml/extend.sgml=0A= @@ -739,6 +739,22 @@ RETURNS anycompatible AS ...=0A= =0A= =0A= =0A= + =0A= + no_relocate (string)=0A= + =0A= + =0A= + A list of names of extensions that this extension depends on,=0A= + and that should be barred from SET SCHEMA.=0A= + This is needed if an extension script references=0A= + a required extensions schema by variable name=0A= + and the object references are in places that can't track = renames.=0A= + for example no_relocate =3D 'foo, bar'.=0A= + This setting is checked at ALTER EXTENSION .. SET SCHEMA=0A= + time of a required extension.=0A= + =0A= + =0A= + =0A= +=0A= =0A= superuser (boolean)=0A= =0A= @@ -908,6 +924,25 @@ RETURNS anycompatible AS ...=0A= =0A= =0A= =0A= + =0A= + =0A= + An extension might depend on other extensions.=0A= + It is useful to schema qualify calls to dependent extension to = minimize reliance on search_path.=0A= + This is critical for cases such as functions used in indexes, = materialized views, or check constraints.=0A= + To reference a required extension's schema, you must first have = requires=0A= + variable specifying the list of extensions your extension = requires.=0A= + In your extension sql scripts,=0A= + you can reference a required extension's schema with syntax=0A= + @extschema:reqextname@ where = reqextname=0A= + is the name of an extension in your requires = list.=0A= + All occurrences of this string will be=0A= + replaced by the schema the required extension is installed in = before the script is=0A= + executed.=0A= + =0A= + For extensions that you reference by this syntax, make sure = to add them to the=0A= + no_relocate list as well.=0A= + =0A= +=0A= =0A= =0A= If the extension does not support relocation at all, set=0A= diff --git a/src/backend/commands/extension.c = b/src/backend/commands/extension.c=0A= index 02ff4a9a7f..71683c547c 100644=0A= --- a/src/backend/commands/extension.c=0A= +++ b/src/backend/commands/extension.c=0A= @@ -90,6 +90,8 @@ typedef struct ExtensionControlFile=0A= bool trusted; /* allow becoming superuser on the fly? */=0A= int encoding; /* encoding of the script file, or -1 */=0A= List *requires; /* names of prerequisite extensions */=0A= + List *no_relocate; /* names of extensions that should be marked as = not relocatable=0A= + these should be a subset of the requires */=0A= } ExtensionControlFile;=0A= =0A= /*=0A= @@ -606,6 +608,21 @@ parse_extension_control_file(ExtensionControlFile = *control,=0A= item->name)));=0A= }=0A= }=0A= + else if (strcmp(item->name, "no_relocate") =3D=3D 0)=0A= + {=0A= + /* Need a modifiable copy of string */=0A= + char *rawnames =3D pstrdup(item->value);=0A= +=0A= + /* Parse string into list of identifiers */=0A= + if (!SplitIdentifierString(rawnames, ',', &control->no_relocate))=0A= + {=0A= + /* syntax error in name list */=0A= + ereport(ERROR,=0A= + (errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= + errmsg("parameter \"%s\" must be a list of extension names",=0A= + item->name)));=0A= + }=0A= + }=0A= else=0A= ereport(ERROR,=0A= (errcode(ERRCODE_SYNTAX_ERROR),=0A= @@ -851,7 +868,7 @@ execute_extension_script(Oid extensionOid, = ExtensionControlFile *control,=0A= const char *from_version,=0A= const char *version,=0A= List *requiredSchemas,=0A= - const char *schemaName, Oid schemaOid)=0A= + const char *schemaName, Oid schemaOid, List *requiredExtensions)=0A= {=0A= bool switch_to_superuser =3D false;=0A= char *filename;=0A= @@ -1030,6 +1047,34 @@ execute_extension_script(Oid extensionOid, = ExtensionControlFile *control,=0A= CStringGetTextDatum(qSchemaName));=0A= }=0A= =0A= + /*=0A= + * If this extension requires other extensions=0A= + * Check each required extension to see if its schema=0A= + * is referenced by @extschema:reqextname@ syntax=0A= + */=0A= + foreach(lc, requiredExtensions)=0A= + {=0A= + Oid reqext =3D lfirst_oid(lc);=0A= + Oid reqschema;=0A= + StringInfoData rToken;=0A= + char *reqname;=0A= + reqschema =3D get_extension_schema(reqext);=0A= + reqname =3D get_namespace_name(reqschema);=0A= + initStringInfo(&rToken);=0A= + appendStringInfo(&rToken, "%s%s%s", "@extschema:", = get_extension_name(reqext), "@");=0A= +=0A= + /*=0A= + * If the required extension's schema is referenced=0A= + * by variable name,=0A= + * Replace each occurence of @extschema:@=0A= + * with the required extension's schema=0A= + */=0A= + t_sql =3D DirectFunctionCall3Coll(replace_text,=0A= + C_COLLATION_OID,=0A= + t_sql,=0A= + CStringGetTextDatum(rToken.data),=0A= + CStringGetTextDatum(quote_identifier(reqname)));=0A= + }=0A= /*=0A= * If module_pathname was set in the control file, substitute its=0A= * value for occurrences of MODULE_PATHNAME.=0A= @@ -1613,7 +1658,7 @@ CreateExtensionInternal(char *extensionName,=0A= execute_extension_script(extensionOid, control,=0A= NULL, versionName,=0A= requiredSchemas,=0A= - schemaName, schemaOid);=0A= + schemaName, schemaOid, requiredExtensions);=0A= =0A= /*=0A= * If additional update scripts have to be executed, apply the updates = as=0A= @@ -2094,8 +2139,8 @@ = get_available_versions_for_extension(ExtensionControlFile *pcontrol,=0A= {=0A= ExtensionVersionInfo *evi =3D (ExtensionVersionInfo *) lfirst(lc);=0A= ExtensionControlFile *control;=0A= - Datum values[8];=0A= - bool nulls[8];=0A= + Datum values[9];=0A= + bool nulls[9];=0A= ListCell *lc2;=0A= =0A= if (!evi->installable)=0A= @@ -2120,6 +2165,7 @@ = get_available_versions_for_extension(ExtensionControlFile *pcontrol,=0A= values[3] =3D BoolGetDatum(control->trusted);=0A= /* relocatable */=0A= values[4] =3D BoolGetDatum(control->relocatable);=0A= +=0A= /* schema */=0A= if (control->schema =3D=3D NULL)=0A= nulls[5] =3D true;=0A= @@ -2131,12 +2177,19 @@ = get_available_versions_for_extension(ExtensionControlFile *pcontrol,=0A= nulls[6] =3D true;=0A= else=0A= values[6] =3D convert_requires_to_datum(control->requires);=0A= +=0A= /* comment */=0A= if (control->comment =3D=3D NULL)=0A= nulls[7] =3D true;=0A= else=0A= values[7] =3D CStringGetTextDatum(control->comment);=0A= =0A= + /* no_relocate */=0A= + if (control->no_relocate =3D=3D NIL)=0A= + nulls[8] =3D true;=0A= + else=0A= + values[8] =3D convert_requires_to_datum(control->no_relocate);=0A= +=0A= tuplestore_putvalues(tupstore, tupdesc, values, nulls);=0A= =0A= /*=0A= @@ -2177,6 +2230,14 @@ = get_available_versions_for_extension(ExtensionControlFile *pcontrol,=0A= nulls[6] =3D false;=0A= }=0A= /* comment stays the same */=0A= + /* no_relocate */=0A= + if (control->no_relocate =3D=3D NIL)=0A= + nulls[8] =3D true;=0A= + else=0A= + {=0A= + values[8] =3D convert_requires_to_datum(control->no_relocate);=0A= + nulls[8] =3D false;=0A= + }=0A= =0A= tuplestore_putvalues(tupstore, tupdesc, values, nulls);=0A= }=0A= @@ -2816,6 +2877,33 @@ AlterExtensionNamespace(const char = *extensionName, const char *newschema, Oid *o=0A= ObjectAddress dep;=0A= Oid dep_oldNspOid;=0A= =0A= + /* If an extension requires this extension=0A= + * and the extension has this extension marked as no_relocate,=0A= + then prevent set schema */=0A= + if (pg_depend->deptype =3D=3D DEPENDENCY_NORMAL && pg_depend->classid = =3D=3D ExtensionRelationId){=0A= + ExtensionControlFile *dcontrol;=0A= + dep.classId =3D pg_depend->classid;=0A= + dep.objectId =3D pg_depend->objid;=0A= + dep.objectSubId =3D pg_depend->objsubid;=0A= + /** Check if dependent extension has a no_relocate request for this = extension **/=0A= + dcontrol =3D = read_extension_control_file(get_extension_name(dep.objectId));=0A= + if (dcontrol->no_relocate) {=0A= + ListCell *lc;=0A= + foreach(lc, dcontrol->no_relocate)=0A= + {=0A= + char *curreq =3D (char *) lfirst(lc);=0A= + if (strcmp(curreq, NameStr(extForm->extname)) =3D=3D 0 ){=0A= + /** This dependent extension, has a no_relocate hold for this = extension **/=0A= + ereport(ERROR,=0A= + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),=0A= + errmsg("cannot SET SCHEMA of extension %s because other = extensions prevent it",=0A= + NameStr(extForm->extname)),=0A= + errdetail("%s prevents relocation of extension %s",=0A= + getObjectDescription(&dep, false), = NameStr(extForm->extname))));=0A= + }=0A= + }=0A= + }=0A= + }=0A= /*=0A= * Ignore non-membership dependencies. (Currently, the only other=0A= * case we could see here is a normal dependency from another=0A= @@ -3172,7 +3260,7 @@ ApplyExtensionUpdates(Oid extensionOid,=0A= execute_extension_script(extensionOid, control,=0A= oldVersionName, versionName,=0A= requiredSchemas,=0A= - schemaName, schemaOid);=0A= + schemaName, schemaOid, requiredExtensions);=0A= =0A= /*=0A= * Update prior-version name and loop around. Since=0A= diff --git a/src/test/modules/test_extensions/Makefile = b/src/test/modules/test_extensions/Makefile=0A= index c3139ab0fc..c073df963c 100644=0A= --- a/src/test/modules/test_extensions/Makefile=0A= +++ b/src/test/modules/test_extensions/Makefile=0A= @@ -6,14 +6,19 @@ PGFILEDESC =3D "test_extensions - regression testing = for EXTENSION support"=0A= EXTENSION =3D test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 = test_ext6 \=0A= test_ext7 test_ext8 test_ext_cine test_ext_cor \=0A= test_ext_cyclic1 test_ext_cyclic2 \=0A= - test_ext_evttrig=0A= + test_ext_evttrig \=0A= + test_ext_req_schema1 test_ext_req_schema2 = test_ext_req_schema3=0A= +=0A= DATA =3D test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \=0A= test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \=0A= test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \=0A= test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \=0A= test_ext_cor--1.0.sql \=0A= test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \=0A= - test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql=0A= + test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \=0A= + test_ext_req_schema1--1.0.sql \=0A= + test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql = \=0A= + test_ext_req_schema3--1.0.sql=0A= =0A= REGRESS =3D test_extensions test_extdepend=0A= =0A= diff --git = a/src/test/modules/test_extensions/expected/test_extensions.out = b/src/test/modules/test_extensions/expected/test_extensions.out=0A= index 821fed38d1..7582d2d2aa 100644=0A= --- a/src/test/modules/test_extensions/expected/test_extensions.out=0A= +++ b/src/test/modules/test_extensions/expected/test_extensions.out=0A= @@ -312,3 +312,44 @@ Objects in extension "test_ext_cine"=0A= table ext_cine_tab3=0A= (9 rows)=0A= =0A= +CREATE SCHEMA test_s_dep;=0A= +CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;=0A= +CREATE EXTENSION test_ext_req_schema3 CASCADE;=0A= +NOTICE: installing required extension "test_ext_req_schema2"=0A= +SELECT dep_req();=0A= + dep_req =0A= +---------=0A= + 1032w=0A= +(1 row)=0A= +=0A= +SELECT dep_req2();=0A= + dep_req2 =0A= +----------=0A= + 1032w=0A= +(1 row)=0A= +=0A= +SELECT dep_req3();=0A= + dep_req3 =0A= +----------=0A= + 2032w=0A= +(1 row)=0A= +=0A= +ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';=0A= +CREATE SCHEMA test_s_dep2;=0A= +ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;=0A= +ERROR: cannot SET SCHEMA of extension test_ext_req_schema1 because = other extensions prevent it=0A= +DETAIL: extension test_ext_req_schema3 prevents relocation of = extension test_ext_req_schema1=0A= +SELECT dep_req();=0A= + dep_req =0A= +---------=0A= + 1update=0A= +(1 row)=0A= +=0A= +ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;=0A= +DROP EXTENSION test_ext_req_schema1;=0A= +ERROR: cannot drop extension test_ext_req_schema1 because other = objects depend on it=0A= +DETAIL: extension test_ext_req_schema2 depends on extension = test_ext_req_schema1=0A= +extension test_ext_req_schema3 depends on extension test_ext_req_schema1=0A= +HINT: Use DROP ... CASCADE to drop the dependent objects too.=0A= +DROP EXTENSION test_ext_req_schema3;=0A= +DROP EXTENSION test_ext_req_schema2;=0A= diff --git a/src/test/modules/test_extensions/meson.build = b/src/test/modules/test_extensions/meson.build=0A= index c3af3e1721..7f47a6fc23 100644=0A= --- a/src/test/modules/test_extensions/meson.build=0A= +++ b/src/test/modules/test_extensions/meson.build=0A= @@ -30,6 +30,13 @@ test_install_data +=3D files(=0A= 'test_ext_evttrig--1.0--2.0.sql',=0A= 'test_ext_evttrig--1.0.sql',=0A= 'test_ext_evttrig.control',=0A= + 'test_ext_req_schema1--1.0.sql',=0A= + 'test_ext_req_schema1.control',=0A= + 'test_ext_req_schema2--1.0.sql',=0A= + 'test_ext_req_schema2.control',=0A= + 'test_ext_req_schema2--1.0--2.0.sql',=0A= + 'test_ext_req_schema3.control',=0A= + 'test_ext_req_schema3--1.0.sql',=0A= )=0A= =0A= tests +=3D {=0A= diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql = b/src/test/modules/test_extensions/sql/test_extensions.sql=0A= index 41b6cddf0b..0f554c856b 100644=0A= --- a/src/test/modules/test_extensions/sql/test_extensions.sql=0A= +++ b/src/test/modules/test_extensions/sql/test_extensions.sql=0A= @@ -209,3 +209,18 @@ CREATE EXTENSION test_ext_cine;=0A= ALTER EXTENSION test_ext_cine UPDATE TO '1.1';=0A= =0A= \dx+ test_ext_cine=0A= +=0A= +CREATE SCHEMA test_s_dep;=0A= +CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;=0A= +CREATE EXTENSION test_ext_req_schema3 CASCADE;=0A= +SELECT dep_req();=0A= +SELECT dep_req2();=0A= +SELECT dep_req3();=0A= +ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';=0A= +CREATE SCHEMA test_s_dep2;=0A= +ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;=0A= +SELECT dep_req();=0A= +ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;=0A= +DROP EXTENSION test_ext_req_schema1;=0A= +DROP EXTENSION test_ext_req_schema3;=0A= +DROP EXTENSION test_ext_req_schema2;=0A= \ No newline at end of file=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql = b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql=0A= new file mode 100644=0A= index 0000000000..462fb52145=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql=0A= @@ -0,0 +1,6 @@=0A= +/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */=0A= +-- complain if script is sourced in psql, rather than via CREATE = EXTENSION=0A= +\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. = \quit=0A= +=0A= +CREATE DOMAIN req AS text=0A= + CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) = OPERATOR(pg_catalog.=3D) '1');=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema1.control = b/src/test/modules/test_extensions/test_ext_req_schema1.control=0A= new file mode 100644=0A= index 0000000000..9ea4558a90=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema1.control=0A= @@ -0,0 +1,3 @@=0A= +comment =3D 'Create required extension to be referenced'=0A= +default_version =3D '1.0'=0A= +relocatable =3D true=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql = b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql=0A= new file mode 100644=0A= index 0000000000..73a44a25e5=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql=0A= @@ -0,0 +1,7 @@=0A= +/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */=0A= +-- complain if script is sourced in psql, rather than via CREATE = EXTENSION=0A= +\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. = \quit=0A= +=0A= +CREATE OR REPLACE FUNCTION dep_req() RETURNS = @extschema:test_ext_req_schema1@.req=0A= +LANGUAGE SQL IMMUTABLE PARALLEL SAFE=0A= +AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql = b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql=0A= new file mode 100644=0A= index 0000000000..b17fb82535=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql=0A= @@ -0,0 +1,9 @@=0A= +/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */=0A= +-- complain if script is sourced in psql, rather than via CREATE = EXTENSION=0A= +\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. = \quit=0A= +CREATE DOMAIN dreq AS text=0A= + CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) = OPERATOR(pg_catalog.=3D) '2');=0A= +=0A= +CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req=0A= +LANGUAGE SQL IMMUTABLE PARALLEL SAFE=0A= +AS 'SELECT ''1032w''';=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema2.control = b/src/test/modules/test_extensions/test_ext_req_schema2.control=0A= new file mode 100644=0A= index 0000000000..d2ba5add97=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema2.control=0A= @@ -0,0 +1,4 @@=0A= +comment =3D 'Test schema referencing of required extensions'=0A= +default_version =3D '1.0'=0A= +relocatable =3D true=0A= +requires =3D 'test_ext_req_schema1'=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql = b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql=0A= new file mode 100644=0A= index 0000000000..bd05669097=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql=0A= @@ -0,0 +1,13 @@=0A= +/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */=0A= +-- complain if script is sourced in psql, rather than via CREATE = EXTENSION=0A= +\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. = \quit=0A= +CREATE DOMAIN req2 AS text=0A= + CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) = OPERATOR(pg_catalog.=3D) '2');=0A= +=0A= +CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req=0A= +LANGUAGE SQL IMMUTABLE PARALLEL SAFE=0A= +AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';=0A= +=0A= +CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq=0A= +LANGUAGE SQL IMMUTABLE PARALLEL SAFE=0A= +AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';=0A= diff --git = a/src/test/modules/test_extensions/test_ext_req_schema3.control = b/src/test/modules/test_extensions/test_ext_req_schema3.control=0A= new file mode 100644=0A= index 0000000000..66c8de5e05=0A= --- /dev/null=0A= +++ b/src/test/modules/test_extensions/test_ext_req_schema3.control=0A= @@ -0,0 +1,5 @@=0A= +comment =3D 'Test schema referencing of 2 required extensions'=0A= +default_version =3D '1.0'=0A= +relocatable =3D true=0A= +requires =3D 'test_ext_req_schema1,test_ext_req_schema2'=0A= +no_relocate =3D 'test_ext_req_schema1'=0A= -- =0A= 2.21.0.windows.1=0A= =0A= ------=_NextPart_000_0008_01D955D5.5899F4E0--