Thread: Dependency isn't created between extension and schema
Hi all, While analyzing the issue James reported to us, I realized that if the schema option in the control file is specified and the schema doesn’t exist we create the schema on CREATE EXTENSION but the created schema doesn’t refer to the extension. Due to this behavior, the schema remains even on DROP EXTENSION. You can see this behavior by using the test_ext6 extension in src/test/module/test_extensions. In the control file, it has the schema option: $ cat src/test/modules/test_extensions/test_ext6.control comment = 'test_ext6' default_version = '1.0' relocatable = false superuser = true schema = 'test_ext6' On CREATE EXTENSION, the schema test_ext6 is created if not exist: postgres(1:692)=# create extension test_ext6 ; CREATE EXTENSION postgres(1:692)=# \dn List of schemas Name | Owner -----------+---------- public | masahiko test_ext6 | masahiko (2 rows) But it isn't dropped on DROP EXTENSION: postgres(1:692)=# drop extension test_ext6 ; DROP EXTENSION postgres(1:692)=# \dn List of schemas Name | Owner -----------+---------- public | masahiko test_ext6 | masahiko (2 rows) Is it a bug? Since the created schema obviously depends on the extension when we created the schema specified in the schema option, I think we might want to create the dependency so that DROP EXTENSION drops the schema as well. I’ve attached the draft patch so that CREATE EXTENSION creates the dependency if it newly creates the schema. Regards, -- Masahiko Sawada EnterpriseDB: https://www.enterprisedb.com/
Attachment
On Mon, Dec 21, 2020 at 04:02:29PM +0900, Masahiko Sawada wrote:
> Is it a bug? Since the created schema obviously depends on the
> extension when we created the schema specified in the schema option, I
> think we might want to create the dependency so that DROP EXTENSION
> drops the schema as well. I’ve attached the draft patch so that CREATE
> EXTENSION creates the dependency if it newly creates the schema.
FWIW, I recall that the "soft" behavior that exists now is wanted, as
it is more flexible for DROP EXTENSION: what you are suggesting here
has the disadvantage to make DROP EXTENSION fail if any non-extension
object has been created on this schema, so this could be disruptive
when it comes to some upgrade scenarios.
<term><replaceable class="parameter">schema_name</replaceable></term>
<listitem>
<para>
The name of the schema in which to install the extension's
objects, given that the extension allows its contents to be
relocated. The named schema must already exist.
While on it.. The docs are incorrect here. As you say,
CreateExtensionInternal() may internally create a schema.
--
Michael
Attachment
On Mon, Dec 21, 2020 at 2:59 AM Michael Paquier <michael@paquier.xyz> wrote: > > On Mon, Dec 21, 2020 at 04:02:29PM +0900, Masahiko Sawada wrote: > > Is it a bug? Since the created schema obviously depends on the > > extension when we created the schema specified in the schema option, I > > think we might want to create the dependency so that DROP EXTENSION > > drops the schema as well. I’ve attached the draft patch so that CREATE > > EXTENSION creates the dependency if it newly creates the schema. > > FWIW, I recall that the "soft" behavior that exists now is wanted, as > it is more flexible for DROP EXTENSION: what you are suggesting here > has the disadvantage to make DROP EXTENSION fail if any non-extension > object has been created on this schema, so this could be disruptive > when it comes to some upgrade scenarios. That's potentially an issue even for a schema created explicitly by the extension's install script, since anyone can create an object within that schema at any time. It seems that the only consistent behavior choice would be to mark the dependency when Postgres is creating the extension automatically but not when the schema already exists. > <term><replaceable class="parameter">schema_name</replaceable></term> > <listitem> > <para> > The name of the schema in which to install the extension's > objects, given that the extension allows its contents to be > relocated. The named schema must already exist. > While on it.. The docs are incorrect here. As you say, > CreateExtensionInternal() may internally create a schema. Alternatively the behavior could be updated to match the docs, since that seems like reasonable intent. James
James Coleman <jtc331@gmail.com> writes:
> On Mon, Dec 21, 2020 at 2:59 AM Michael Paquier <michael@paquier.xyz> wrote:
>> On Mon, Dec 21, 2020 at 04:02:29PM +0900, Masahiko Sawada wrote:
>>> Is it a bug? Since the created schema obviously depends on the
>>> extension when we created the schema specified in the schema option, I
>>> think we might want to create the dependency so that DROP EXTENSION
>>> drops the schema as well.
>> FWIW, I recall that the "soft" behavior that exists now is wanted, as
>> it is more flexible for DROP EXTENSION: what you are suggesting here
>> has the disadvantage to make DROP EXTENSION fail if any non-extension
>> object has been created on this schema, so this could be disruptive
>> when it comes to some upgrade scenarios.
I think it absolutely is intentional. For example, if several extensions
all list "schema1" in their control files, and you install them all, you
would not want dropping the first-created one to force dropping the rest.
I do not really see any problem here that's worth creating such hazards
to fix.
(At least in current usage, I think that control files probably always
list common schemas not per-extension schemas, so that this scenario
would be the norm not the exception.)
> Alternatively the behavior could be updated to match the docs, since
> that seems like reasonable intent.
That documentation is talking about the SCHEMA option in CREATE EXTENSION,
which is an entirely different matter from the control-file option.
A control-file entry is not going to know anything about the specific
installation it's being installed in, while the user issuing CREATE
EXTENSION presumably has local knowledge; so I don't see any strong
argument that the two cases must be treated alike.
regards, tom lane
On Tue, Dec 22, 2020 at 1:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > James Coleman <jtc331@gmail.com> writes: > > On Mon, Dec 21, 2020 at 2:59 AM Michael Paquier <michael@paquier.xyz> wrote: > >> On Mon, Dec 21, 2020 at 04:02:29PM +0900, Masahiko Sawada wrote: > >>> Is it a bug? Since the created schema obviously depends on the > >>> extension when we created the schema specified in the schema option, I > >>> think we might want to create the dependency so that DROP EXTENSION > >>> drops the schema as well. > > >> FWIW, I recall that the "soft" behavior that exists now is wanted, as > >> it is more flexible for DROP EXTENSION: what you are suggesting here > >> has the disadvantage to make DROP EXTENSION fail if any non-extension > >> object has been created on this schema, so this could be disruptive > >> when it comes to some upgrade scenarios. > > I think it absolutely is intentional. For example, if several extensions > all list "schema1" in their control files, and you install them all, you > would not want dropping the first-created one to force dropping the rest. > I do not really see any problem here that's worth creating such hazards > to fix. Thank you for the comments! I understand that it is intentional behavior and the downside of my idea. But what is the difference between the schema created by specifying the schema option in the control file and by CREATE SCHEMA in the install script? Extensions might create the same schema "schema1" in their install script. In this case, dropping the first one force dropping the rest. Looking at some extensions in the world. some extensions use the schema option whereas some use the install script. I think it’s reasonable there are two ways to create the extension’s schema with different dependencies but I think it’s better to be documented. It looked like a non-intuitive behavior when I saw it for the first time. Regards, -- Masahiko Sawada EnterpriseDB: https://www.enterprisedb.com/