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 1pauQz-0007Hj-Kb for pgsql-hackers@arkaria.postgresql.org; Sat, 11 Mar 2023 08:18:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pauQw-00025s-Ky for pgsql-hackers@arkaria.postgresql.org; Sat, 11 Mar 2023 08:18: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 1pauQw-00025j-5Z for pgsql-hackers@lists.postgresql.org; Sat, 11 Mar 2023 08:18:30 +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 1pauQt-0002YQ-1j for pgsql-hackers@lists.postgresql.org; Sat, 11 Mar 2023 08:18:29 +0000 Received: by mail-qt1-x831.google.com with SMTP id r5so8337169qtp.4 for ; Sat, 11 Mar 2023 00:18:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pcorp-us.20210112.gappssmtp.com; s=20210112; t=1678522704; 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=qwrzUrJpdAXMSSEc4UFe3UoGtOIfXsecCFpIb3pdPZk=; b=nrlJwFlYlNebWqUrO5jTGBEptR1EJ9/m6uCiOU93994lzZVSCOhLf5Wvpkk7OFxlJq JbxUOv2h1zC9WwPvNC9yQFrWIZdTAYOd+Z7Ja+UJLpjop+/jOOwVEdqd1BqA4+2IPwzf Ar984JCrczkxVUlwMAq0eArDkbUuXzg+HGkQIEoB1B3E2AwjVfaS3VWI9KYTQ69qpA8c FyvJ3rgCt57UWn9jwEIDmm/3RSXx/URfWeBrTX/WKMCmb9kG9Fl/O3z6JGgPdbBwjaBS Q+rXKuw48+5dpeA3PM6xX+nDukZTuhQ0gdF7Voclosq9rEqgy6PNZsygnaB0wmICVsKG BPiA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678522704; 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=qwrzUrJpdAXMSSEc4UFe3UoGtOIfXsecCFpIb3pdPZk=; b=lAZ5+Ftk3m7cGeiXMBM2+lFqYNOr3gkw9zzIGzxZi407OakBbhmZTkvQyHlkx30N2f Qgg/Na+v/UD2wzjP4ykmUr8sEKqVSFgXi+wfY9DKFuMBRngsRO9daFDdWugxo2WaSNBd NtwqMBPzFDDSB3/2TI2jkSB3Jo6M27SxHAXOpUoeL0qg1RQJ83xGIZ5jSVgM2fL4DeU8 Q5OOH6rr6ypg2j9xsD2+hEZIaibnzO3ILsjxxZBBouN99r35TMwNtuw/z+xkU/AE81Nu RPB04UghHMOExumvRKXaH11cGTxbaief68XGPw7DY5eXFn9qhQq7pXF/hhQTCsrXaaUA wZhQ== X-Gm-Message-State: AO0yUKWTTNJvLR5EWwn9OFtY0oTAeK2prZnJsNKyc9NkRAP5KNdnBBrA 2nqhKBKyy3Y0EREnR6DeJX3xUuaU3skSzPiWfeLZMw== X-Google-Smtp-Source: AK7set+OdARtLkMGAk99WAQTxIjwbg0OHkKBbHMEVl+K/CI+fvsDf3pDBpgo+OGmtyB6FnNHHKs4Ug== X-Received: by 2002:a05:622a:108:b0:3b9:bca3:d93d with SMTP id u8-20020a05622a010800b003b9bca3d93dmr46695504qtw.22.1678522704489; Sat, 11 Mar 2023 00:18:24 -0800 (PST) Received: from F (50-78-240-110-static.hfc.comcastbusiness.net. [50.78.240.110]) by smtp.gmail.com with ESMTPSA id q14-20020a05620a2a4e00b0073d873df3fesm1511537qkp.30.2023.03.11.00.18.23 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 11 Mar 2023 00:18:24 -0800 (PST) From: "Regina Obe" To: "'Tom Lane'" Cc: "'Gregory Stark \(as CFM\)'" , "'Sandro Santilli'" , , "'Regina Obe'" References: <003001d8f4ae$402282c0$c0678840$@pcorp.us> <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> In-Reply-To: <1019515.1678488437@sss.pgh.pa.us> Subject: RE: Ability to reference other extensions by schema in extension scripts Date: Sat, 11 Mar 2023 03:18:18 -0500 Message-ID: <001d01d953f2$099b6430$1cd22c90$@pcorp.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_001E_01D953C8.20C58340" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQFgXZxpr0yOB+9uS1Xwc1lFBKthIAFb3UkpAlqGOBEBdWkvegLXfdbzAh2KTDUCqQCSKwHAG++5AmsEVhwCGfZasa9Or4Gw 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_001E_01D953C8.20C58340 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit > Subject: Re: Ability to reference other extensions by schema in extension > scripts > > "Regina Obe" writes: > >> requires = 'extfoo, extbar' > >> no_relocate = 'extfoo' > > > So when no_relocate is specified, where would that live? > > In the control file. > > > Would I mark the extfoo as not relocatable on CREATE / ALTER of said > > extension? > > Or add an extra field to pg_extension > > We don't record dependent extensions in pg_extension now, so that doesn't > seem like it would fit well. I was envisioning that ALTER EXTENSION SET > SCHEMA would do something along the lines of > > (1) scrape the list of dependent extensions out of pg_depend > (2) open and parse each of their control files > (3) fail if any of their control files mentions the target one in > no_relocate. > > Admittedly, this'd be a bit slow, but I doubt that ALTER EXTENSION SET > SCHEMA is a performance bottleneck for anybody. > > > I had tried to do that originally, e.g. instead of even bothering with > > such an extra arg, just mark it as not relocatable if the extension's > > script contains references to the required extension's schema. > > I don't think that's a great approach, because those references might appear > in places that can track a rename (ie, in an object name that's resolved to a > stored OID). Short of fully parsing the script file you aren't going to get a > reliable answer. I'm content to lay that problem off on the extension authors. > > > But then what if extfoo is upgraded? > > We already have mechanisms for version-dependent control files, so I don't > see where there's a problem. > > regards, tom lane Attached is a revised patch with these changes in place. ------=_NextPart_000_001E_01D953C8.20C58340 Content-Type: application/octet-stream; name="0006-Allow-use-of-extschema-reqextname-to-reference.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="0006-Allow-use-of-extschema-reqextname-to-reference.patch" From 32430fcec8794a0c10305bde45637d13828f0559 Mon Sep 17 00:00:00 2001=0A= From: Regina Obe =0A= Date: Sun, 5 Feb 2023 01:35:29 -0500=0A= Subject: [PATCH 6/6] 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..da54c7feca 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_001E_01D953C8.20C58340--