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/
create_dependency_schema_and_extension.patch
Description: Binary data