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/