public inbox for [email protected]
help / color / mirror / Atom feedFrom: Regina Obe <[email protected]>
To: [email protected]
Cc: 'Tom Lane' <[email protected]>
Cc: [email protected]
Subject: RE: Ability to reference other extensions by schema in extension scripts
Date: Sun, 26 Feb 2023 01:39:24 -0500
Message-ID: <[email protected]> (raw)
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<20221215085234.rpjzlm7hmsmbanqv@c19>
<[email protected]>
<20230223183906.6rhtybwdpe37sri7@c19>
> So in conclusion we have 3 possible paths to go with this
>
> 1) Just don't allow any extensions referenced by other extensions to be
> relocatable.
> It will show a message something like
> "SET SCHEMA not allowed because other extensions depend on it"
> Given that if you don't specify relocatable in you .control file, the
assume is
> relocatable = false , this isn't too far off from standard protocol.
>
> 2) Use objsubid=1 to denote that another extension explicitly references
the
> schema of another extension so setting schema of other extension is not
okay.
> So instead of introducing another dependency, we'd update the
> DEPENDENCY_NORMAL one between the two schemas with objsubid=1
> instead of 0.
>
> This has 2 approaches:
>
> a) Update the existing DEPENDENCY_NORMAL between the two extensions
> setting the objsubid=1
>
> or
> b) Create a new DEPEDENCY_NORMAL between the two extensions with
> objsubid=1
>
> I'm not sure if either has implications in backup / restore . I suspect b
would
> be safer since I suspect objsubid might be checked and this dependency
only
> needs checking during SET SCHEMA time.
>
> 3) Create a whole new DEPENDENCY type, perhaps calling it something like
> DEPENDENCY_EXTENSION_SCHEMA
>
> 4) Just don't allow @extschema:<reqextension>@ syntax to be used unless
> the <reqextension> is marked as relocatable=false. This one I don't like
> because it doesn't solve my fundamental issue of
>
> postgis_tiger_geocoder relying on fuzzystrmatch, which is marked as
> relocatable.
>
> The main issue I was trying to solve is my extension references
fuzzystrmatch
> functions in a function used for functional indexes, and this fails
restore of
> table indexes because I can't schema qualify the fuzzystrmatch extension
in
> the backing function.
>
>
> If no one has any opinion, I'll go with option 1 which is the one that
strk had
> actually proposed before and seems least programmatically invasive, but
> perhaps more annoying user facing.
>
> My preferred would be #2
>
> Thanks,
> Regina
Attached is my revision 3 patch, which follows the proposed #1.
Don't allow schema relocation of an extension if another extension requires
it.
Attachments:
[application/octet-stream] 0003-Allow-use-of-extschema-reqextname-to-reference.patch (15.2K, ../[email protected]/2-0003-Allow-use-of-extschema-reqextname-to-reference.patch)
download | inline diff:
From d25ae7c6795922c82921fb9abfd29719fcc8bdc0 Mon Sep 17 00:00:00 2001
From: Regina Obe <[email protected]>
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:[email protected]
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1update''::@extschema:[email protected]';
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:[email protected]
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:[email protected]';
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:[email protected]
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''1032w''::@extschema:[email protected]';
+
+CREATE FUNCTION dep_req3() RETURNS @extschema:[email protected]
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''2032w''::@extschema:[email protected]';
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
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: RE: Ability to reference other extensions by schema in extension scripts
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox