doctrine:schema:drop --full-database drops "public" schema of PostgreSQL

[allan-simon]

Bug Report

Q	A
Version	3.4.0

Summary

Since the upgrade of dbal 3.4.0 , dbal seems to expose the schema "public" to other commands
so when you try to reset your database , the schema "public" is deleted, and you can't run anymore doctrine migrations command

Current behaviour

doctrine:schema:drop --full-database remove public schema

How to reproduce

• run doctrine:schema:drop --full-database
• then console doctrine:migrations:migrate

you will see

 In ExceptionConverter.php line 63:
         
   An exception occurred while executing a query: SQLSTATE[3F000]: Invalid sch
   ema name: 7 ERROR:  no schema has been selected to create in
   LINE 1: CREATE TABLE doctrine_migration_versions (version VARCHAR(19...
                        ^
         
         
 In Exception.php line 28:
         
   SQLSTATE[3F000]: Invalid schema name: 7 ERROR:  no schema has been selected
    to create in
   LINE 1: CREATE TABLE doctrine_migration_versions (version VARCHAR(19...
                        ^
         
         
 In Connection.php line 33:
         
   SQLSTATE[3F000]: Invalid schema name: 7 ERROR:  no schema has been selected
    to create in
   LINE 1: CREATE TABLE doctrine_migration_versions (version VARCHAR(19...
                        ^

Expected behaviour

• public schema being the default schema in postgres, it should not be dropped

or at least we should have a way to recreate it (I tried putting it in my migrations, but as the create table doctrine_migration_versions happens before our first migration is executed I'm stucked)

[allan-simon]

Note: putting public in doctrine.dbal.schema_filter does not seem to help

[morozov]

@allan-simon we addressed some PostgreSQL schema introspection issues in 3.4.1. Could you try updating and see if the issue persists?

[HypeMC]

@morozov I'm having the same problem, after upgrading from version 3.3.8 to 3.4.1 I started getting the following error:

  SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop schema public because other objects depend on it  
  DETAIL:  extension pg_trgm depends on schema public                                                                     
  extension intarray depends on schema public                                                                             
  extension unaccent depends on schema public                                                                             
  HINT:  Use DROP ... CASCADE to drop the dependent objects too.   

[morozov]

Could you provide a reproducer for the problem?

• run doctrine:schema:drop --full-database
• then console doctrine:migrations:migrate

These commands are not part of the DBAL. Does reproducing this issue require defining some schema?

[allan-simon]

@morozov it may be that to get what to delete doctrine:schema:drop --full-database call a method from dbal that now is returning more than before ? (I think console doctrine:migrations:migrate is just the way to "uncover" the change in behaviour )

I will try to dig to see if I can isolate said method in dbal (if any)

[allan-simon]

These commands are not part of the DBAL. Does reproducing this issue require defining some schema?

at least I can tell you , running it on a vanilla postgresql database doctrine:schema:drop --full-database drop the public schema (console doctrine:migrations:migrate is not needed per se )

[HypeMC]

Could you provide a reproducer for the problem?

• run doctrine:schema:drop --full-database
• then console doctrine:migrations:migrate

These commands are not part of the DBAL. Does reproducing this issue require defining some schema?

@morozov I've created a new issue since it only looks remotely related to this one, see #5598.

[allan-simon]

doctrine:schema:drop --full-database call behind the scene and execute the sql query it returns

      public function getDropDatabaseSQL()
      {
          return $this->schemaManager
              ->createSchema()
              ->toDropSql($this->platform);
      }

this behaviour has not changed pre/post upgrade

what seem to have changed between the last version where the bug was not here

https://github.com/doctrine/dbal/blob/3.3.7/src/Schema/Schema.php#L445-L450

and the behaviour changed with this

https://github.com/doctrine/dbal/blob/3.4.0/src/Schema/Schema.php#L445-L451

[allan-simon]

@HypeMC

thanks , actually I think there's different face of the main behaviour change the fact that DropSchemaSqlCollector was not collecting schema/namespace at all.

That explains hence why in your issue at the end it talks about pg_trim etc. which are extensions linked to the namespace , and both DropSchemaSqlCollector and DropSchemaObjectsSQLBuilder don't handle extensions (which itself is an other issue outside of the scope of this ticket)

[allan-simon]

I think

dbal/src/SQL/Builder/DropSchemaObjectsSQLBuilder.php

Line 71 in 94e0164

private function buildNamespaceStatements(array $namespaces): array

should not return a delete statement for public schema as it does not belong to the application scope (the same as you don't expect it to drop system tables )

[allan-simon]

The best solution I can see is

changing https://github.com/doctrine/dbal/blob/3.4.x/src/Schema/PostgreSQLSchemaManager.php#L122

to exclude "public" from here

why ?
Postgresql rely heavily on it existing https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PUBLIC , anywhere you don't prefix a schema, it will be "public" so sooner or later it will be needed , so it does not make sense to delete it

to the extreme that ironically the only test that will be failing if we change this behaviour is
https://github.com/doctrine/dbal/blob/3.4.x/tests/Functional/Schema/PostgreSQLSchemaManagerTest.php#L51-L58
because specificaly it expect public to be a preexisting schema that nobody needs to create (and hence nobody needs to delete)

[morozov]

Closing based on #5600 (comment).

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.