Ability to reference other extensions by schema in extension scripts

From 52d7f06a6663d2f6c8577bd5b60ba937b0565ac0 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Fri, 11 Nov 2022 00:58:46 -0500
Subject: [PATCH] Allow use of @extschema:reqextname@ to reference the schema
 of a required extension called reqextname.

This patch includes
1. Changes to extension execute logic
2. Extension tests both Makefile and meson.build
3. Documentation of the new feature
---
 doc/src/sgml/extend.sgml                      | 17 ++++++++++
 src/backend/commands/extension.c              | 33 +++++++++++++++++++
 src/test/modules/test_extensions/Makefile     |  9 +++--
 .../expected/test_extensions.out              | 29 ++++++++++++++++
 src/test/modules/test_extensions/meson.build  |  7 ++++
 .../test_extensions/sql/test_extensions.sql   |  9 +++++
 .../test_ext_req_schema1--1.0.sql             |  6 ++++
 .../test_ext_req_schema1.control              |  3 ++
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 ++++
 .../test_ext_req_schema2--1.0.sql             | 10 ++++++
 .../test_ext_req_schema2.control              |  4 +++
 .../test_ext_req_schema3--1.0.sql             | 14 ++++++++
 .../test_ext_req_schema3.control              |  4 +++
 13 files changed, 150 insertions(+), 2 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 46e873a166..62fdd9b919 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -908,6 +908,23 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal> 
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 806d6056ab..15945fd432 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1028,6 +1028,39 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		 * If an extension requires other extensions
+		 * Replace each occurence of @extschema:extname@ 
+		 * where extname is the name of the required extension
+		 * with the schema name that extname is located in.
+		 */
+		if (control->requires)
+		{
+			foreach(lc, control->requires)
+			{
+				char	   *curreq = (char *) lfirst(lc);
+				Oid			reqext;
+				Oid			reqschema;
+				reqext = get_required_extension(curreq,
+												control->name,
+												schemaName,
+												false,
+												NIL,
+												false);
+				reqschema = get_extension_schema(reqext);
+				char *reqname;
+				reqname = get_namespace_name(reqschema);
+				StringInfoData rToken;
+				initStringInfo(&rToken);
+				appendStringInfo(&rToken, "%s%s%s", "@extschema:", curreq, "@");
+
+				t_sql = DirectFunctionCall3Coll(replace_text,
+										C_COLLATION_OID,
+										t_sql,
+										CStringGetTextDatum(rToken.data),
+										CStringGetTextDatum(quote_identifier(reqname)));
+			}
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..022504f1ec 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,32 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index e95a9f2e7e..20bee210b8 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -29,6 +29,13 @@ install_data(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
   kwargs: contrib_data_args,
 )
 
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..ba087c26a1 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,12 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+SELECT dep_req();
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..05b0eb24e1
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,10 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..f28be6c82d
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,14 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
+
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..b052fad785
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
-- 
2.21.0.windows.1

From 92019e9015d7c6a536a580b92569d67fee2127e7 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 5 Feb 2023 01:35:29 -0500
Subject: [PATCH 2/2] 1. Changes to extension execute logic to allow
 referencing required extension schemas by variable 2. Extension tests both
 Makefile and meson.build 3. Documentation of the new feature 4. Add a
 DEPENDENCY_AUTO dependency whenever currently created extension references a
 required schema by variable name in its scripts 5. Prevent an extension from
 being relocated if there is a DEPENDENCY_AUTO depend to it

---
 doc/src/sgml/extend.sgml                      | 17 +++++
 src/backend/commands/extension.c              | 71 ++++++++++++++++++-
 src/test/modules/test_extensions/Makefile     |  9 ++-
 .../expected/test_extensions.out              | 66 +++++++++++++++++
 src/test/modules/test_extensions/meson.build  |  7 ++
 .../test_extensions/sql/test_extensions.sql   | 25 +++++++
 .../test_ext_req_schema1--1.0.sql             |  6 ++
 .../test_ext_req_schema1.control              |  3 +
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 ++
 .../test_ext_req_schema2--1.0.sql             |  9 +++
 .../test_ext_req_schema2.control              |  4 ++
 .../test_ext_req_schema3--1.0.sql             | 13 ++++
 .../test_ext_req_schema3.control              |  4 ++
 13 files changed, 238 insertions(+), 3 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..6c93bddb36 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -908,6 +908,23 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal>
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index b1509cc505..0fc87c96d3 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1030,6 +1030,70 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		 * If this extension requires other extensions
+		 * Check each required extension to see if it's schema
+		 * is referenced by @extschema:reqextname@ syntax
+		 */
+		if (control->requires)
+		{
+			ObjectAddresses *addrs;
+			addrs = new_object_addresses();
+			ObjectAddress depender;
+			ObjectAddressSet(depender, ExtensionRelationId, CurrentExtensionObject);
+			foreach(lc, control->requires)
+			{
+				char	   *curreq = (char *) lfirst(lc);
+				Oid			reqext;
+				Oid			reqschema;
+				reqext = get_required_extension(curreq,
+												control->name,
+												schemaName,
+												false,
+												NIL,
+												false);
+				reqschema = get_extension_schema(reqext);
+				char *reqname;
+				reqname = get_namespace_name(reqschema);
+				StringInfoData rToken;
+				initStringInfo(&rToken);
+				appendStringInfo(&rToken, "%s%s%s", "@extschema:", curreq, "@");
+				if (strstr(c_sql,rToken.data) != NULL){
+					/*
+					* If the required extension's schema is referenced by variable name, 
+					* add a dependency that the current extension 
+					* references the schema of the required extension 
+					**/
+					
+					ObjectAddress referenced;
+					ObjectAddressSet(referenced, ExtensionRelationId, reqext);
+
+					/*
+					* Deleting the dependency if it exists, because it will be added again
+					* and no easy check to determine if the dependency already exists
+					*/
+					deleteDependencyRecordsForSpecific(depender.classId, depender.objectId,
+										   DEPENDENCY_AUTO,
+										   referenced.classId, referenced.objectId);
+
+					add_exact_object_address(&referenced, addrs);
+					/*
+					* Replace each occurence of @extschema:reqextname@
+					* with the required extension's schema
+					*/ 
+					t_sql = DirectFunctionCall3Coll(replace_text,
+										C_COLLATION_OID,
+										t_sql,
+										CStringGetTextDatum(rToken.data),
+										CStringGetTextDatum(quote_identifier(reqname)));
+				}
+			}
+			/* Record all required schemas that have their schema referenced in script by this one
+			 * with a DEPENDENCY_AUTO in pg_depend 
+			 */ 
+			record_object_address_dependencies(&depender, addrs, DEPENDENCY_AUTO);
+			free_object_addresses(addrs); 
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
@@ -2816,12 +2880,17 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
 		ObjectAddress dep;
 		Oid			dep_oldNspOid;
 
+		/* If a dependent object scripts 
+		 * reference the variable schema name of this extension
+		 * do not allow relocation */
+		if (pg_depend->deptype == DEPENDENCY_AUTO && pg_depend->classid == ExtensionRelationId )
+			elog(ERROR, "Extension can not be relocated because dependent extension references it's location");
 		/*
 		 * Ignore non-membership dependencies.  (Currently, the only other
 		 * case we could see here is a normal dependency from another
 		 * extension.)
 		 */
-		if (pg_depend->deptype != DEPENDENCY_EXTENSION)
+		if (pg_depend->deptype != DEPENDENCY_EXTENSION )
 			continue;
 
 		dep.classId = pg_depend->classid;
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..fc1f69c098 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,69 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+ count 
+-------
+     3
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+ERROR:  Extension can not be relocated because dependent extension references it's location
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+ count 
+-------
+     3
+(1 row)
+
+DROP EXTENSION test_ext_req_schema3;
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+ count 
+-------
+     1
+(1 row)
+
+DROP EXTENSION test_ext_req_schema2;
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+ count 
+-------
+     0
+(1 row)
+
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index 45597ddc23..7519f6e28e 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -31,6 +31,13 @@ install_data(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
   kwargs: contrib_data_args,
 )
 
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..41f8e6739d 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,28 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+SELECT dep_req();
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+DROP EXTENSION test_ext_req_schema3;
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
+DROP EXTENSION test_ext_req_schema2;
+SELECT count(*) 
+  FROM pg_depend AS d  
+WHERE d.deptype='a' AND d.refclassid::regclass::text = 'pg_extension' AND d.classid::regclass::text = 'pg_extension';
\ No newline at end of file
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..9fe25d48a1
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..bd05669097
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..b052fad785
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
-- 
2.21.0.windows.1

From d25ae7c6795922c82921fb9abfd29719fcc8bdc0 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 5 Feb 2023 01:35:29 -0500
Subject: [PATCH 3/3] Allow @extschema:<extname>@ in extension scripts

Changes to extension execute logic to allow referencing required
extension schemas by variable

This feature is needed in cases such as
when another extension packages a function
which references an object from one of required extensions
and this function is used to back
indexes, constraints, and materialized views

Without this feature, because pg_restore removes the paths,
these indexes, constraints, materialized views are not restored
because when the function is called, the function call fails
because the dependent item is not schema qualified.

 - Extension tests both Makefile and meson.build
 - Documentation of the new feature
 - Prevent an extension from being relocated if
   another extension requires it

 Discussion: https://postgr.es/m/000801d94959%2463a9f520%242afddf60%24%40pcorp.us
---
 doc/src/sgml/extend.sgml                      | 17 ++++++
 src/backend/commands/extension.c              | 55 +++++++++++++++++++
 src/test/modules/test_extensions/Makefile     |  9 ++-
 .../expected/test_extensions.out              | 40 ++++++++++++++
 src/test/modules/test_extensions/meson.build  |  7 +++
 .../test_extensions/sql/test_extensions.sql   | 14 +++++
 .../test_ext_req_schema1--1.0.sql             |  6 ++
 .../test_ext_req_schema1.control              |  3 +
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 +++
 .../test_ext_req_schema2--1.0.sql             |  9 +++
 .../test_ext_req_schema2.control              |  4 ++
 .../test_ext_req_schema3--1.0.sql             | 13 +++++
 .../test_ext_req_schema3.control              |  4 ++
 13 files changed, 186 insertions(+), 2 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..6c93bddb36 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -908,6 +908,23 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal>
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index b1509cc505..57a21ba41e 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -854,6 +854,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 						 const char *schemaName, Oid schemaOid)
 {
 	bool		switch_to_superuser = false;
+	char	   *origSchemaName = (char *) schemaName;
 	char	   *filename;
 	Oid			save_userid = 0;
 	int			save_sec_context = 0;
@@ -1030,6 +1031,46 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		 * If this extension requires other extensions
+		 * Check each required extension to see if it's schema
+		 * is referenced by @extschema:reqextname@ syntax
+		 */
+		if (control->requires)
+		{
+			foreach(lc, control->requires)
+			{
+				char	   *curreq;
+				curreq = (char *) lfirst(lc);
+				Oid			reqext;
+				Oid			reqschema;
+				reqext = get_required_extension(curreq,
+												control->name,
+												origSchemaName,
+												false,
+												NIL,
+												false);
+				reqschema = get_extension_schema(reqext);
+				char *reqname;
+				reqname = get_namespace_name(reqschema);
+				StringInfoData rToken;
+				initStringInfo(&rToken);
+				appendStringInfo(&rToken, "%s%s%s", "@extschema:", curreq, "@");
+				if (strstr(c_sql,rToken.data) != NULL){
+					/*
+					* If the required extension's schema is referenced
+					* by variable name,
+					* Replace each occurence of @extschema:reqextname@
+					* with the required extension's schema
+					*/
+					t_sql = DirectFunctionCall3Coll(replace_text,
+										C_COLLATION_OID,
+										t_sql,
+										CStringGetTextDatum(rToken.data),
+										CStringGetTextDatum(quote_identifier(reqname)));
+				}
+			}
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
@@ -2816,6 +2857,20 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
 		ObjectAddress dep;
 		Oid			dep_oldNspOid;
 
+		/* If an extension requires this extension
+		 * do not allow relocation */
+		if (pg_depend->deptype == DEPENDENCY_NORMAL && pg_depend->classid == ExtensionRelationId){
+			dep.classId = pg_depend->classid;
+			dep.objectId = pg_depend->objid;
+			dep.objectSubId = pg_depend->objsubid;
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot SET SCHEMA of extension %s because other extensions require it",
+							NameStr(extForm->extname)),
+					 errdetail("%s requires extension %s",
+							   getObjectDescription(&dep, false), NameStr(extForm->extname))));
+
+		}
 		/*
 		 * Ignore non-membership dependencies.  (Currently, the only other
 		 * case we could see here is a normal dependency from another
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..21dfd05982 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,43 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+ERROR:  cannot SET SCHEMA of extension test_ext_req_schema1 because other extensions require it
+DETAIL:  extension test_ext_req_schema3 requires extension test_ext_req_schema1
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
+DROP EXTENSION test_ext_req_schema1;
+ERROR:  cannot drop extension test_ext_req_schema1 because other objects depend on it
+DETAIL:  extension test_ext_req_schema2 depends on extension test_ext_req_schema1
+extension test_ext_req_schema3 depends on extension test_ext_req_schema1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index 45597ddc23..7519f6e28e 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -31,6 +31,13 @@ install_data(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
   kwargs: contrib_data_args,
 )
 
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..81ecd13736 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,17 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+SELECT dep_req();
+DROP EXTENSION test_ext_req_schema1;
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
\ No newline at end of file
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..9fe25d48a1
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..bd05669097
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..b052fad785
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
-- 
2.21.0.windows.1

From 5279686f1338f31d974f7bb749a201e0d0b280fc Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 5 Feb 2023 01:35:29 -0500
Subject: [PATCH 4/4] Allow @extschema:<extname>@ in extension scripts

This feature allows an extension author to reference
extension schemas of extensions in the requires variable
of the control file using variable syntax
@extschema:<extname>@  where <extname> is the
name of any of the listed required extensions.

This feature is needed particularly in cases
when another extension packages a function
which references an object from one of its
required extensions and this function is used to back
indexes, constraints, and materialized views.

Without this feature, because pg_restore removes the paths,
these indexes, constraints, materialized views are not restored
because when the function is called, the function call fails
because items it depends on cannot be schema qualified.

This feature will allow extension authors to schema qualify
references to required extension objects to prevent this issue.
Aside from this issue, schema qualifying all these references will
improve security by preventing a rogue version of a function from being
used.

 - Extension tests both Makefile and meson.build
 - Documentation of the new feature
 - Prevent an extension from being relocated if
   another extension requires it

 Discussion: https://postgr.es/m/000801d94959%2463a9f520%242afddf60%24%40pcorp.us
---
 doc/src/sgml/extend.sgml                      | 17 +++++++
 src/backend/commands/extension.c              | 49 +++++++++++++++++++
 src/test/modules/test_extensions/Makefile     |  9 +++-
 .../expected/test_extensions.out              | 40 +++++++++++++++
 src/test/modules/test_extensions/meson.build  |  7 +++
 .../test_extensions/sql/test_extensions.sql   | 14 ++++++
 .../test_ext_req_schema1--1.0.sql             |  6 +++
 .../test_ext_req_schema1.control              |  3 ++
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 +++
 .../test_ext_req_schema2--1.0.sql             |  9 ++++
 .../test_ext_req_schema2.control              |  4 ++
 .../test_ext_req_schema3--1.0.sql             | 13 +++++
 .../test_ext_req_schema3.control              |  4 ++
 13 files changed, 180 insertions(+), 2 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..6c93bddb36 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -908,6 +908,23 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal>
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index b1509cc505..17ebb1a4fc 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1030,6 +1030,41 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		* If this extension requires other extensions
+		* Check each required extension to see if its schema
+		* is referenced by @extschema:reqextname@ syntax
+		*/
+		foreach(lc, control->requires)
+		{
+			char		*curreq = (char *) lfirst(lc);
+			Oid			reqext;
+			Oid			reqschema;
+			StringInfoData rToken;
+			char		*reqname;
+			reqext = get_required_extension(curreq,
+											control->name,
+											(char *) schemaName,
+											false,
+											NIL,
+											false);
+			reqschema = get_extension_schema(reqext);
+			reqname = get_namespace_name(reqschema);
+			initStringInfo(&rToken);
+			appendStringInfo(&rToken, "%s%s%s", "@extschema:", curreq, "@");
+
+			/*
+			* If the required extension's schema is referenced
+			* by variable name,
+			* Replace each occurence of @extschema:<reqextname>@
+			* with the required extension's schema
+			*/
+			t_sql = DirectFunctionCall3Coll(replace_text,
+								C_COLLATION_OID,
+								t_sql,
+								CStringGetTextDatum(rToken.data),
+								CStringGetTextDatum(quote_identifier(reqname)));
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
@@ -2816,6 +2851,20 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
 		ObjectAddress dep;
 		Oid			dep_oldNspOid;
 
+		/* If an extension requires this extension
+		 * do not allow relocation */
+		if (pg_depend->deptype == DEPENDENCY_NORMAL && pg_depend->classid == ExtensionRelationId){
+			dep.classId = pg_depend->classid;
+			dep.objectId = pg_depend->objid;
+			dep.objectSubId = pg_depend->objsubid;
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot SET SCHEMA of extension %s because other extensions require it",
+							NameStr(extForm->extname)),
+					 errdetail("%s requires extension %s",
+							   getObjectDescription(&dep, false), NameStr(extForm->extname))));
+
+		}
 		/*
 		 * Ignore non-membership dependencies.  (Currently, the only other
 		 * case we could see here is a normal dependency from another
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..21dfd05982 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,43 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+ERROR:  cannot SET SCHEMA of extension test_ext_req_schema1 because other extensions require it
+DETAIL:  extension test_ext_req_schema3 requires extension test_ext_req_schema1
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
+DROP EXTENSION test_ext_req_schema1;
+ERROR:  cannot drop extension test_ext_req_schema1 because other objects depend on it
+DETAIL:  extension test_ext_req_schema2 depends on extension test_ext_req_schema1
+extension test_ext_req_schema3 depends on extension test_ext_req_schema1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index 45597ddc23..7519f6e28e 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -31,6 +31,13 @@ install_data(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
   kwargs: contrib_data_args,
 )
 
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..81ecd13736 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,17 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+SELECT dep_req();
+DROP EXTENSION test_ext_req_schema1;
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
\ No newline at end of file
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..9fe25d48a1
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..bd05669097
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..b052fad785
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
-- 
2.21.0.windows.1

From dce6168357e9ed410f94deb66b552129884195ba Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 5 Feb 2023 01:35:29 -0500
Subject: [PATCH 5/5] Allow @extschema:<extname>@ in extension scripts

This feature allows an extension author to reference
extension schemas of extensions in the requires variable
of the control file using variable syntax
@extschema:<extname>@  where <extname> is the
name of any of the listed required extensions.

This feature is needed particularly in cases
when another extension packages a function
which references an object from one of its
required extensions and this function is used to back
indexes, constraints, and materialized views.

Without this feature, because pg_restore removes the paths,
these indexes, constraints, materialized views are not restored
because when the function is called, the function call fails
because items it depends on cannot be schema qualified.

This feature will allow extension authors to schema qualify
references to required extension objects to prevent this issue.
Aside from this issue, schema qualifying all these references will
improve security by preventing a rogue version of a function from being
used.

 - Extension tests both Makefile and meson.build
 - Documentation of the new feature
 - Prevent an extension from being relocated if
   another extension requires it

 Discussion: https://postgr.es/m/000801d94959%2463a9f520%242afddf60%24%40pcorp.us
---
 doc/src/sgml/extend.sgml                      | 17 +++++++
 src/backend/commands/extension.c              | 49 +++++++++++++++++++
 src/test/modules/test_extensions/Makefile     |  9 +++-
 .../expected/test_extensions.out              | 40 +++++++++++++++
 src/test/modules/test_extensions/meson.build  |  7 +++
 .../test_extensions/sql/test_extensions.sql   | 14 ++++++
 .../test_ext_req_schema1--1.0.sql             |  6 +++
 .../test_ext_req_schema1.control              |  3 ++
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 +++
 .../test_ext_req_schema2--1.0.sql             |  9 ++++
 .../test_ext_req_schema2.control              |  4 ++
 .../test_ext_req_schema3--1.0.sql             | 13 +++++
 .../test_ext_req_schema3.control              |  4 ++
 13 files changed, 180 insertions(+), 2 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..6c93bddb36 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -908,6 +908,23 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal>
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index b1509cc505..17ebb1a4fc 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -1030,6 +1030,41 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		* If this extension requires other extensions
+		* Check each required extension to see if its schema
+		* is referenced by @extschema:reqextname@ syntax
+		*/
+		foreach(lc, control->requires)
+		{
+			char		*curreq = (char *) lfirst(lc);
+			Oid			reqext;
+			Oid			reqschema;
+			StringInfoData rToken;
+			char		*reqname;
+			reqext = get_required_extension(curreq,
+											control->name,
+											(char *) schemaName,
+											false,
+											NIL,
+											false);
+			reqschema = get_extension_schema(reqext);
+			reqname = get_namespace_name(reqschema);
+			initStringInfo(&rToken);
+			appendStringInfo(&rToken, "%s%s%s", "@extschema:", curreq, "@");
+
+			/*
+			* If the required extension's schema is referenced
+			* by variable name,
+			* Replace each occurence of @extschema:<reqextname>@
+			* with the required extension's schema
+			*/
+			t_sql = DirectFunctionCall3Coll(replace_text,
+								C_COLLATION_OID,
+								t_sql,
+								CStringGetTextDatum(rToken.data),
+								CStringGetTextDatum(quote_identifier(reqname)));
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
@@ -2816,6 +2851,20 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
 		ObjectAddress dep;
 		Oid			dep_oldNspOid;
 
+		/* If an extension requires this extension
+		 * do not allow relocation */
+		if (pg_depend->deptype == DEPENDENCY_NORMAL && pg_depend->classid == ExtensionRelationId){
+			dep.classId = pg_depend->classid;
+			dep.objectId = pg_depend->objid;
+			dep.objectSubId = pg_depend->objsubid;
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot SET SCHEMA of extension %s because other extensions require it",
+							NameStr(extForm->extname)),
+					 errdetail("%s requires extension %s",
+							   getObjectDescription(&dep, false), NameStr(extForm->extname))));
+
+		}
 		/*
 		 * Ignore non-membership dependencies.  (Currently, the only other
 		 * case we could see here is a normal dependency from another
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..21dfd05982 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,43 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+ERROR:  cannot SET SCHEMA of extension test_ext_req_schema1 because other extensions require it
+DETAIL:  extension test_ext_req_schema3 requires extension test_ext_req_schema1
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
+DROP EXTENSION test_ext_req_schema1;
+ERROR:  cannot drop extension test_ext_req_schema1 because other objects depend on it
+DETAIL:  extension test_ext_req_schema2 depends on extension test_ext_req_schema1
+extension test_ext_req_schema3 depends on extension test_ext_req_schema1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index c3af3e1721..7f47a6fc23 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -30,6 +30,13 @@ test_install_data += files(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
 )
 
 tests += {
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..81ecd13736 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,17 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+SELECT dep_req();
+DROP EXTENSION test_ext_req_schema1;
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
\ No newline at end of file
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..9fe25d48a1
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..bd05669097
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..b052fad785
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
-- 
2.21.0.windows.1

From 32430fcec8794a0c10305bde45637d13828f0559 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 5 Feb 2023 01:35:29 -0500
Subject: [PATCH 6/6] Allow @extschema:<extname>@ in extension scripts

This feature allows an extension author to reference
extension schemas of extensions in the requires variable
of the control file using variable syntax
@extschema:<extname>@  where <extname> is the
name of any of the listed required extensions.

It also defines another optional config parameter
in the control file called no_relocate
which consists of a subset of the requires extensions
that should have ALTER EXTENSION SET SCHEMA prevented.

This feature is needed particularly in cases
when another extension packages a function
which references an object from one of its
required extensions and this function is used to back
indexes, constraints, and materialized views.

Without this feature, because pg_restore removes the paths,
these indexes, constraints, materialized views are not restored
because when the function is called, the function call fails
because items it depends on cannot be schema qualified.

This feature will allow extension authors to schema qualify
references to required extension objects to prevent this issue.
Aside from this issue, schema qualifying all these references will
improve security by preventing a rogue version of a function from being
used.

 - Extension tests both Makefile and meson.build
 - Documentation of the new feature
 - Define a new extension control parameter no_relocate
   Which is a comma separated list of required extensions
   that should be prevented from ALTER .. SET SCHEMA
 - Prevent an extension from being relocated if
   another extension requires it and has it
   listed in it's no_relocate list
 - Change execute_extension_script to take as input
   required extension oids,
   since calling functions already build this list

 Discussion: https://postgr.es/m/000801d94959%2463a9f520%242afddf60%24%40pcorp.us
---
 doc/src/sgml/extend.sgml                      | 35 +++++++
 src/backend/commands/extension.c              | 98 ++++++++++++++++++-
 src/test/modules/test_extensions/Makefile     |  9 +-
 .../expected/test_extensions.out              | 41 ++++++++
 src/test/modules/test_extensions/meson.build  |  7 ++
 .../test_extensions/sql/test_extensions.sql   | 15 +++
 .../test_ext_req_schema1--1.0.sql             |  6 ++
 .../test_ext_req_schema1.control              |  3 +
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 ++
 .../test_ext_req_schema2--1.0.sql             |  9 ++
 .../test_ext_req_schema2.control              |  4 +
 .../test_ext_req_schema3--1.0.sql             | 13 +++
 .../test_ext_req_schema3.control              |  5 +
 13 files changed, 245 insertions(+), 7 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..da54c7feca 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -739,6 +739,22 @@ RETURNS anycompatible AS ...
       </listitem>
      </varlistentry>
 
+     <varlistentry id="extend-extensions-files-no-relocate">
+      <term><varname>no_relocate</varname> (<type>string</type>)</term>
+      <listitem>
+       <para>
+        A list of names of extensions that this extension depends on,
+        and that should be barred from SET SCHEMA.
+        This is needed if an extension script references
+        a required extensions schema by variable name
+        and the object references are in places that can't track renames.
+        for example <literal>no_relocate = 'foo, bar'</literal>.
+        This setting is checked at ALTER EXTENSION .. SET SCHEMA 
+        time of a required extension.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="extend-extensions-files-superuser">
       <term><varname>superuser</varname> (<type>boolean</type>)</term>
       <listitem>
@@ -908,6 +924,25 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal>
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+      <para>For extensions that you reference by this syntax, make sure to add them to the
+       <literal>no_relocate</literal> list as well.</para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 02ff4a9a7f..71683c547c 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -90,6 +90,8 @@ typedef struct ExtensionControlFile
 	bool		trusted;		/* allow becoming superuser on the fly? */
 	int			encoding;		/* encoding of the script file, or -1 */
 	List	   *requires;		/* names of prerequisite extensions */
+	List	   *no_relocate;	/* names of extensions that should be marked as not relocatable
+									these should be a subset of the requires */
 } ExtensionControlFile;
 
 /*
@@ -606,6 +608,21 @@ parse_extension_control_file(ExtensionControlFile *control,
 								item->name)));
 			}
 		}
+		else if (strcmp(item->name, "no_relocate") == 0)
+		{
+			/* Need a modifiable copy of string */
+			char	   *rawnames = pstrdup(item->value);
+
+			/* Parse string into list of identifiers */
+			if (!SplitIdentifierString(rawnames, ',', &control->no_relocate))
+			{
+				/* syntax error in name list */
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("parameter \"%s\" must be a list of extension names",
+								item->name)));
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -851,7 +868,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 						 const char *from_version,
 						 const char *version,
 						 List *requiredSchemas,
-						 const char *schemaName, Oid schemaOid)
+						 const char *schemaName, Oid schemaOid, List *requiredExtensions)
 {
 	bool		switch_to_superuser = false;
 	char	   *filename;
@@ -1030,6 +1047,34 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		* If this extension requires other extensions
+		* Check each required extension to see if its schema
+		* is referenced by @extschema:reqextname@ syntax
+		*/
+		foreach(lc, requiredExtensions)
+		{
+			Oid				reqext = lfirst_oid(lc);
+			Oid				reqschema;
+			StringInfoData	rToken;
+			char			*reqname;
+			reqschema = get_extension_schema(reqext);
+			reqname = get_namespace_name(reqschema);
+			initStringInfo(&rToken);
+			appendStringInfo(&rToken, "%s%s%s", "@extschema:", get_extension_name(reqext), "@");
+
+			/*
+			* If the required extension's schema is referenced
+			* by variable name,
+			* Replace each occurence of @extschema:<reqextname>@
+			* with the required extension's schema
+			*/
+			t_sql = DirectFunctionCall3Coll(replace_text,
+								C_COLLATION_OID,
+								t_sql,
+								CStringGetTextDatum(rToken.data),
+								CStringGetTextDatum(quote_identifier(reqname)));
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
@@ -1613,7 +1658,7 @@ CreateExtensionInternal(char *extensionName,
 	execute_extension_script(extensionOid, control,
 							 NULL, versionName,
 							 requiredSchemas,
-							 schemaName, schemaOid);
+							 schemaName, schemaOid, requiredExtensions);
 
 	/*
 	 * If additional update scripts have to be executed, apply the updates as
@@ -2094,8 +2139,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2120,6 +2165,7 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		values[3] = BoolGetDatum(control->trusted);
 		/* relocatable */
 		values[4] = BoolGetDatum(control->relocatable);
+
 		/* schema */
 		if (control->schema == NULL)
 			nulls[5] = true;
@@ -2131,12 +2177,19 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 			nulls[6] = true;
 		else
 			values[6] = convert_requires_to_datum(control->requires);
+
 		/* comment */
 		if (control->comment == NULL)
 			nulls[7] = true;
 		else
 			values[7] = CStringGetTextDatum(control->comment);
 
+		/* no_relocate */
+		if (control->no_relocate == NIL)
+			nulls[8] = true;
+		else
+			values[8] = convert_requires_to_datum(control->no_relocate);
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
 		/*
@@ -2177,6 +2230,14 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 					nulls[6] = false;
 				}
 				/* comment stays the same */
+				/* no_relocate */
+				if (control->no_relocate == NIL)
+					nulls[8] = true;
+				else
+				{
+					values[8] = convert_requires_to_datum(control->no_relocate);
+					nulls[8] = false;
+				}
 
 				tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 			}
@@ -2816,6 +2877,33 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
 		ObjectAddress dep;
 		Oid			dep_oldNspOid;
 
+		/* If an extension requires this extension
+		 * and the extension has this extension marked as no_relocate,
+		   then prevent set schema */
+		if (pg_depend->deptype == DEPENDENCY_NORMAL && pg_depend->classid == ExtensionRelationId){
+			ExtensionControlFile *dcontrol;
+			dep.classId = pg_depend->classid;
+			dep.objectId = pg_depend->objid;
+			dep.objectSubId = pg_depend->objsubid;
+			/** Check if dependent extension has a no_relocate request for this extension **/
+			dcontrol = read_extension_control_file(get_extension_name(dep.objectId));
+			if (dcontrol->no_relocate) {
+				ListCell   *lc;
+				foreach(lc, dcontrol->no_relocate)
+				{
+					char	*curreq = (char *) lfirst(lc);
+					if (strcmp(curreq, NameStr(extForm->extname)) == 0 ){
+						/** This dependent extension, has a no_relocate hold for this extension **/
+						ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("cannot SET SCHEMA of extension %s because other extensions prevent it",
+									NameStr(extForm->extname)),
+							errdetail("%s prevents relocation of extension %s",
+									getObjectDescription(&dep, false), NameStr(extForm->extname))));
+					}
+				}
+			}
+		}
 		/*
 		 * Ignore non-membership dependencies.  (Currently, the only other
 		 * case we could see here is a normal dependency from another
@@ -3172,7 +3260,7 @@ ApplyExtensionUpdates(Oid extensionOid,
 		execute_extension_script(extensionOid, control,
 								 oldVersionName, versionName,
 								 requiredSchemas,
-								 schemaName, schemaOid);
+								 schemaName, schemaOid, requiredExtensions);
 
 		/*
 		 * Update prior-version name and loop around.  Since
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..7582d2d2aa 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,44 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+ERROR:  cannot SET SCHEMA of extension test_ext_req_schema1 because other extensions prevent it
+DETAIL:  extension test_ext_req_schema3 prevents relocation of extension test_ext_req_schema1
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;
+DROP EXTENSION test_ext_req_schema1;
+ERROR:  cannot drop extension test_ext_req_schema1 because other objects depend on it
+DETAIL:  extension test_ext_req_schema2 depends on extension test_ext_req_schema1
+extension test_ext_req_schema3 depends on extension test_ext_req_schema1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index c3af3e1721..7f47a6fc23 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -30,6 +30,13 @@ test_install_data += files(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
 )
 
 tests += {
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..0f554c856b 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,18 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+SELECT dep_req();
+ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;
+DROP EXTENSION test_ext_req_schema1;
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
\ No newline at end of file
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..b17fb82535
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..bd05669097
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..66c8de5e05
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,5 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
+no_relocate = 'test_ext_req_schema1'
-- 
2.21.0.windows.1

From d7b3ee51af0fbdc7e58fc43473114cd633e09c74 Mon Sep 17 00:00:00 2001
From: Regina Obe <lr@pcorp.us>
Date: Sun, 5 Feb 2023 01:35:29 -0500
Subject: [PATCH 7/7] Allow @extschema:<extname>@ in extension scripts

This feature allows an extension author to reference
extension schemas of extensions in the requires variable
of the control file using variable syntax
@extschema:<extname>@  where <extname> is the
name of any of the listed required extensions.

It also defines another optional config parameter
in the control file called no_relocate
which consists of a subset of the requires extensions
that should have ALTER EXTENSION SET SCHEMA prevented.

This feature is needed particularly in cases
when another extension packages a function
which references an object from one of its
required extensions and this function is used to back
indexes, constraints, and materialized views.

Without this feature, because pg_restore removes the paths,
these indexes, constraints, materialized views are not restored
because when the function is called, the function call fails
because items it depends on cannot be schema qualified.

This feature will allow extension authors to schema qualify
references to required extension objects to prevent this issue.
Aside from this issue, schema qualifying all these references will
improve security by preventing a rogue version of a function from being
used.

 - Extension tests both Makefile and meson.build
 - Documentation of the new feature
 - Define a new extension control parameter no_relocate
   Which is a comma separated list of required extensions
   that should be prevented from ALTER .. SET SCHEMA
 - Prevent an extension from being relocated if
   another extension requires it and has it
   listed in it's no_relocate list
 - Change execute_extension_script to take as input
   required extension oids,
   since calling functions already build this list

 Discussion: https://postgr.es/m/000801d94959%2463a9f520%242afddf60%24%40pcorp.us
---
 doc/src/sgml/extend.sgml                      | 35 +++++++
 src/backend/commands/extension.c              | 98 ++++++++++++++++++-
 src/test/modules/test_extensions/Makefile     |  9 +-
 .../expected/test_extensions.out              | 41 ++++++++
 src/test/modules/test_extensions/meson.build  |  7 ++
 .../test_extensions/sql/test_extensions.sql   | 15 +++
 .../test_ext_req_schema1--1.0.sql             |  6 ++
 .../test_ext_req_schema1.control              |  3 +
 .../test_ext_req_schema2--1.0--2.0.sql        |  7 ++
 .../test_ext_req_schema2--1.0.sql             |  9 ++
 .../test_ext_req_schema2.control              |  4 +
 .../test_ext_req_schema3--1.0.sql             | 13 +++
 .../test_ext_req_schema3.control              |  5 +
 13 files changed, 245 insertions(+), 7 deletions(-)
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema1.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema2.control
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
 create mode 100644 src/test/modules/test_extensions/test_ext_req_schema3.control

diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b70cbe83ae..554876672b 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -739,6 +739,22 @@ RETURNS anycompatible AS ...
       </listitem>
      </varlistentry>
 
+     <varlistentry id="extend-extensions-files-no-relocate">
+      <term><varname>no_relocate</varname> (<type>string</type>)</term>
+      <listitem>
+       <para>
+        A list of names of extensions that this extension depends on,
+        and that should be barred from SET SCHEMA.
+        This is needed if an extension script references
+        a required extensions schema by variable name
+        and the object references are in places that can't track renames.
+        for example <literal>no_relocate = 'foo, bar'</literal>.
+        This setting is checked at ALTER EXTENSION .. SET SCHEMA
+        time of a required extension.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="extend-extensions-files-superuser">
       <term><varname>superuser</varname> (<type>boolean</type>)</term>
       <listitem>
@@ -908,6 +924,25 @@ RETURNS anycompatible AS ...
       </para>
      </listitem>
 
+     <listitem>
+      <para>
+       An extension might depend on other extensions.
+       It is useful to schema qualify calls to dependent extension to minimize reliance on search_path.
+       This is critical for cases such as functions used in indexes, materialized views, or check constraints.
+       To reference a required extension's schema, you must first have <literal>requires</literal>
+       variable specifying the list of extensions your extension requires.
+       In your extension sql scripts,
+       you can reference a required extension's schema with syntax
+       <literal>@extschema:reqextname@</literal> where <literal>reqextname</literal>
+       is the name of an extension in your <literal>requires</literal> list.
+       All occurrences of this string will be
+       replaced by the schema the required extension is installed in before the script is
+       executed.
+      </para>
+      <para>For extensions that you reference by this syntax, make sure to add them to the
+       <literal>no_relocate</literal> list as well.</para>
+     </listitem>
+
      <listitem>
       <para>
        If the extension does not support relocation at all, set
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 02ff4a9a7f..71683c547c 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -90,6 +90,8 @@ typedef struct ExtensionControlFile
 	bool		trusted;		/* allow becoming superuser on the fly? */
 	int			encoding;		/* encoding of the script file, or -1 */
 	List	   *requires;		/* names of prerequisite extensions */
+	List	   *no_relocate;	/* names of extensions that should be marked as not relocatable
+									these should be a subset of the requires */
 } ExtensionControlFile;
 
 /*
@@ -606,6 +608,21 @@ parse_extension_control_file(ExtensionControlFile *control,
 								item->name)));
 			}
 		}
+		else if (strcmp(item->name, "no_relocate") == 0)
+		{
+			/* Need a modifiable copy of string */
+			char	   *rawnames = pstrdup(item->value);
+
+			/* Parse string into list of identifiers */
+			if (!SplitIdentifierString(rawnames, ',', &control->no_relocate))
+			{
+				/* syntax error in name list */
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						 errmsg("parameter \"%s\" must be a list of extension names",
+								item->name)));
+			}
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -851,7 +868,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 						 const char *from_version,
 						 const char *version,
 						 List *requiredSchemas,
-						 const char *schemaName, Oid schemaOid)
+						 const char *schemaName, Oid schemaOid, List *requiredExtensions)
 {
 	bool		switch_to_superuser = false;
 	char	   *filename;
@@ -1030,6 +1047,34 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 											CStringGetTextDatum(qSchemaName));
 		}
 
+		/*
+		* If this extension requires other extensions
+		* Check each required extension to see if its schema
+		* is referenced by @extschema:reqextname@ syntax
+		*/
+		foreach(lc, requiredExtensions)
+		{
+			Oid				reqext = lfirst_oid(lc);
+			Oid				reqschema;
+			StringInfoData	rToken;
+			char			*reqname;
+			reqschema = get_extension_schema(reqext);
+			reqname = get_namespace_name(reqschema);
+			initStringInfo(&rToken);
+			appendStringInfo(&rToken, "%s%s%s", "@extschema:", get_extension_name(reqext), "@");
+
+			/*
+			* If the required extension's schema is referenced
+			* by variable name,
+			* Replace each occurence of @extschema:<reqextname>@
+			* with the required extension's schema
+			*/
+			t_sql = DirectFunctionCall3Coll(replace_text,
+								C_COLLATION_OID,
+								t_sql,
+								CStringGetTextDatum(rToken.data),
+								CStringGetTextDatum(quote_identifier(reqname)));
+		}
 		/*
 		 * If module_pathname was set in the control file, substitute its
 		 * value for occurrences of MODULE_PATHNAME.
@@ -1613,7 +1658,7 @@ CreateExtensionInternal(char *extensionName,
 	execute_extension_script(extensionOid, control,
 							 NULL, versionName,
 							 requiredSchemas,
-							 schemaName, schemaOid);
+							 schemaName, schemaOid, requiredExtensions);
 
 	/*
 	 * If additional update scripts have to be executed, apply the updates as
@@ -2094,8 +2139,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 	{
 		ExtensionVersionInfo *evi = (ExtensionVersionInfo *) lfirst(lc);
 		ExtensionControlFile *control;
-		Datum		values[8];
-		bool		nulls[8];
+		Datum		values[9];
+		bool		nulls[9];
 		ListCell   *lc2;
 
 		if (!evi->installable)
@@ -2120,6 +2165,7 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 		values[3] = BoolGetDatum(control->trusted);
 		/* relocatable */
 		values[4] = BoolGetDatum(control->relocatable);
+
 		/* schema */
 		if (control->schema == NULL)
 			nulls[5] = true;
@@ -2131,12 +2177,19 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 			nulls[6] = true;
 		else
 			values[6] = convert_requires_to_datum(control->requires);
+
 		/* comment */
 		if (control->comment == NULL)
 			nulls[7] = true;
 		else
 			values[7] = CStringGetTextDatum(control->comment);
 
+		/* no_relocate */
+		if (control->no_relocate == NIL)
+			nulls[8] = true;
+		else
+			values[8] = convert_requires_to_datum(control->no_relocate);
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 
 		/*
@@ -2177,6 +2230,14 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
 					nulls[6] = false;
 				}
 				/* comment stays the same */
+				/* no_relocate */
+				if (control->no_relocate == NIL)
+					nulls[8] = true;
+				else
+				{
+					values[8] = convert_requires_to_datum(control->no_relocate);
+					nulls[8] = false;
+				}
 
 				tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 			}
@@ -2816,6 +2877,33 @@ AlterExtensionNamespace(const char *extensionName, const char *newschema, Oid *o
 		ObjectAddress dep;
 		Oid			dep_oldNspOid;
 
+		/* If an extension requires this extension
+		 * and the extension has this extension marked as no_relocate,
+		   then prevent set schema */
+		if (pg_depend->deptype == DEPENDENCY_NORMAL && pg_depend->classid == ExtensionRelationId){
+			ExtensionControlFile *dcontrol;
+			dep.classId = pg_depend->classid;
+			dep.objectId = pg_depend->objid;
+			dep.objectSubId = pg_depend->objsubid;
+			/** Check if dependent extension has a no_relocate request for this extension **/
+			dcontrol = read_extension_control_file(get_extension_name(dep.objectId));
+			if (dcontrol->no_relocate) {
+				ListCell   *lc;
+				foreach(lc, dcontrol->no_relocate)
+				{
+					char	*curreq = (char *) lfirst(lc);
+					if (strcmp(curreq, NameStr(extForm->extname)) == 0 ){
+						/** This dependent extension, has a no_relocate hold for this extension **/
+						ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							errmsg("cannot SET SCHEMA of extension %s because other extensions prevent it",
+									NameStr(extForm->extname)),
+							errdetail("%s prevents relocation of extension %s",
+									getObjectDescription(&dep, false), NameStr(extForm->extname))));
+					}
+				}
+			}
+		}
 		/*
 		 * Ignore non-membership dependencies.  (Currently, the only other
 		 * case we could see here is a normal dependency from another
@@ -3172,7 +3260,7 @@ ApplyExtensionUpdates(Oid extensionOid,
 		execute_extension_script(extensionOid, control,
 								 oldVersionName, versionName,
 								 requiredSchemas,
-								 schemaName, schemaOid);
+								 schemaName, schemaOid, requiredExtensions);
 
 		/*
 		 * Update prior-version name and loop around.  Since
diff --git a/src/test/modules/test_extensions/Makefile b/src/test/modules/test_extensions/Makefile
index c3139ab0fc..c073df963c 100644
--- a/src/test/modules/test_extensions/Makefile
+++ b/src/test/modules/test_extensions/Makefile
@@ -6,14 +6,19 @@ PGFILEDESC = "test_extensions - regression testing for EXTENSION support"
 EXTENSION = test_ext1 test_ext2 test_ext3 test_ext4 test_ext5 test_ext6 \
             test_ext7 test_ext8 test_ext_cine test_ext_cor \
             test_ext_cyclic1 test_ext_cyclic2 \
-            test_ext_evttrig
+            test_ext_evttrig \
+            test_ext_req_schema1 test_ext_req_schema2 test_ext_req_schema3
+
 DATA = test_ext1--1.0.sql test_ext2--1.0.sql test_ext3--1.0.sql \
        test_ext4--1.0.sql test_ext5--1.0.sql test_ext6--1.0.sql \
        test_ext7--1.0.sql test_ext7--1.0--2.0.sql test_ext8--1.0.sql \
        test_ext_cine--1.0.sql test_ext_cine--1.0--1.1.sql \
        test_ext_cor--1.0.sql \
        test_ext_cyclic1--1.0.sql test_ext_cyclic2--1.0.sql \
-       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql
+       test_ext_evttrig--1.0.sql test_ext_evttrig--1.0--2.0.sql \
+       test_ext_req_schema1--1.0.sql \
+       test_ext_req_schema2--1.0.sql test_ext_req_schema2--1.0--2.0.sql \
+       test_ext_req_schema3--1.0.sql
 
 REGRESS = test_extensions test_extdepend
 
diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out
index 821fed38d1..7582d2d2aa 100644
--- a/src/test/modules/test_extensions/expected/test_extensions.out
+++ b/src/test/modules/test_extensions/expected/test_extensions.out
@@ -312,3 +312,44 @@ Objects in extension "test_ext_cine"
  table ext_cine_tab3
 (9 rows)
 
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+NOTICE:  installing required extension "test_ext_req_schema2"
+SELECT dep_req();
+ dep_req 
+---------
+ 1032w
+(1 row)
+
+SELECT dep_req2();
+ dep_req2 
+----------
+ 1032w
+(1 row)
+
+SELECT dep_req3();
+ dep_req3 
+----------
+ 2032w
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+ERROR:  cannot SET SCHEMA of extension test_ext_req_schema1 because other extensions prevent it
+DETAIL:  extension test_ext_req_schema3 prevents relocation of extension test_ext_req_schema1
+SELECT dep_req();
+ dep_req 
+---------
+ 1update
+(1 row)
+
+ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;
+DROP EXTENSION test_ext_req_schema1;
+ERROR:  cannot drop extension test_ext_req_schema1 because other objects depend on it
+DETAIL:  extension test_ext_req_schema2 depends on extension test_ext_req_schema1
+extension test_ext_req_schema3 depends on extension test_ext_req_schema1
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
diff --git a/src/test/modules/test_extensions/meson.build b/src/test/modules/test_extensions/meson.build
index c3af3e1721..7f47a6fc23 100644
--- a/src/test/modules/test_extensions/meson.build
+++ b/src/test/modules/test_extensions/meson.build
@@ -30,6 +30,13 @@ test_install_data += files(
   'test_ext_evttrig--1.0--2.0.sql',
   'test_ext_evttrig--1.0.sql',
   'test_ext_evttrig.control',
+  'test_ext_req_schema1--1.0.sql',
+  'test_ext_req_schema1.control',
+  'test_ext_req_schema2--1.0.sql',
+  'test_ext_req_schema2.control',
+  'test_ext_req_schema2--1.0--2.0.sql',
+  'test_ext_req_schema3.control',
+  'test_ext_req_schema3--1.0.sql',
 )
 
 tests += {
diff --git a/src/test/modules/test_extensions/sql/test_extensions.sql b/src/test/modules/test_extensions/sql/test_extensions.sql
index 41b6cddf0b..0f554c856b 100644
--- a/src/test/modules/test_extensions/sql/test_extensions.sql
+++ b/src/test/modules/test_extensions/sql/test_extensions.sql
@@ -209,3 +209,18 @@ CREATE EXTENSION test_ext_cine;
 ALTER EXTENSION test_ext_cine UPDATE TO '1.1';
 
 \dx+ test_ext_cine
+
+CREATE SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema1 SCHEMA test_s_dep;
+CREATE EXTENSION test_ext_req_schema3 CASCADE;
+SELECT dep_req();
+SELECT dep_req2();
+SELECT dep_req3();
+ALTER EXTENSION test_ext_req_schema2 UPDATE TO '2.0';
+CREATE SCHEMA test_s_dep2;
+ALTER EXTENSION test_ext_req_schema1 SET SCHEMA test_s_dep2;
+SELECT dep_req();
+ALTER EXTENSION test_ext_req_schema2 SET SCHEMA test_s_dep;
+DROP EXTENSION test_ext_req_schema1;
+DROP EXTENSION test_ext_req_schema3;
+DROP EXTENSION test_ext_req_schema2;
\ No newline at end of file
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
new file mode 100644
index 0000000000..462fb52145
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql
@@ -0,0 +1,6 @@
+/* src/test/modules/test_extensions/test_ext_req_schema1--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema1" to load this file. \quit
+
+CREATE DOMAIN req AS text
+  CONSTRAINT starts_with_1 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '1');
diff --git a/src/test/modules/test_extensions/test_ext_req_schema1.control b/src/test/modules/test_extensions/test_ext_req_schema1.control
new file mode 100644
index 0000000000..9ea4558a90
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema1.control
@@ -0,0 +1,3 @@
+comment = 'Create required extension to be referenced'
+default_version = '1.0'
+relocatable = true
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
new file mode 100644
index 0000000000..73a44a25e5
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0--2.0.sql
@@ -0,0 +1,7 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:test_ext_req_schema1@.req';
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
new file mode 100644
index 0000000000..b17fb82535
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql
@@ -0,0 +1,9 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN dreq AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema2.control b/src/test/modules/test_extensions/test_ext_req_schema2.control
new file mode 100644
index 0000000000..d2ba5add97
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema2.control
@@ -0,0 +1,4 @@
+comment = 'Test schema referencing of required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1'
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
new file mode 100644
index 0000000000..bd05669097
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3--1.0.sql
@@ -0,0 +1,13 @@
+/* src/test/modules/test_extensions/test_ext_req_schema2--1.0.sql */
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION test_ext_req_schema2" to load this file. \quit
+CREATE DOMAIN req2 AS text
+  CONSTRAINT starts_with_2 check(pg_catalog.left(value,1) OPERATOR(pg_catalog.=) '2');
+
+CREATE FUNCTION dep_req2() RETURNS @extschema:test_ext_req_schema1@.req
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:test_ext_req_schema1@.req';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:test_ext_req_schema2@.dreq
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:test_ext_req_schema2@.dreq';
diff --git a/src/test/modules/test_extensions/test_ext_req_schema3.control b/src/test/modules/test_extensions/test_ext_req_schema3.control
new file mode 100644
index 0000000000..66c8de5e05
--- /dev/null
+++ b/src/test/modules/test_extensions/test_ext_req_schema3.control
@@ -0,0 +1,5 @@
+comment = 'Test schema referencing of 2 required extensions'
+default_version = '1.0'
+relocatable = true
+requires = 'test_ext_req_schema1,test_ext_req_schema2'
+no_relocate = 'test_ext_req_schema1'
-- 
2.21.0.windows.1