Since version 3.4.0 doctrine:migration:diff generates a migration to drop spatial_ref_sys table used by PostGIS extension

[lmichelin]

Bug Report

Version	Working?
3.3.8	✅ No issue
3.4.0	❌ Issue
3.4.4	❌ Issue

Summary

I'm using PostgreSQL with PostGIS extension. Since version 3.4.0, when I run the doctrine:migration:diff command, a migration is generated to drop the spatial_ref_sys table used by PostGIS.

Current behaviour

Generated migration:

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP TABLE spatial_ref_sys');
    }

Output when I run the migration:

SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop table spatial_ref_sys because extension postgis requires it
HINT:  You can drop extension postgis instead.

How to reproduce

• Connect doctrine to a PostgreSQL database
• Add the PostGIS extension to PostgreSQL (CREATE EXTENSION IF NOT EXISTS "postgis")
• Run the doctrine:migration:diff command

Expected behaviour

The doctrine:migration:diff should not generate any migration to remove the spatial_ref_sys table, like in version 3.3.8.

[morozov]

How can this issue be reproduced without the dependencies like Migrations?

I deployed a PostgreSQL server instance from the postgis/postgis container image. I can confirm that the table in question exists:

DROP TABLE spatial_ref_sys
-- [2BP01] ERROR: cannot drop table spatial_ref_sys because extension postgis requires it
-- Hint: You can drop extension postgis instead.

But I cannot see it listed via AbstractSchemaManager::listTable() on DBAL 3.4.0 (the list is empty on an empty database).

[lmichelin]

Hello, in version 3.3.8, AbstractSchemaManager::listTable() was used to list PostgreSQL tables, but since version 4.4.0, PostgreSQLSchemaManager implements its own listTables method, and so AbstractSchemaManager::doListTables() is used to list the tables. This new method returns a not empty list with one element corresponding to the spatial_ref_sys table. The screenshot below shows this issue.

[morozov]

@lmichelin we need to agree on the environment where this issue can be reproduced. So far, I cannot reproduce it. Here's what I did:

1. Spin up a database container as mentioned earlier:

   docker run -p 5432:5432 -e POSTGRES_PASSWORD=Passw0rd postgis/postgis:14-3.3

2. Run the following script:

   <?php
   
   use Doctrine\DBAL\DriverManager;
   use Doctrine\DBAL\Exception;
   
   require 'vendor/autoload.php';
   
   $connection = DriverManager::getConnection([
       'driver' => 'pdo_pgsql',
       'host' => 'localhost',
       'user' => 'postgres',
       'password' => 'Passw0rd',
   ]);
   
   try {
       $connection->executeStatement('DROP TABLE spatial_ref_sys');
   } catch (Exception $e) {
       echo $e->getMessage() . PHP_EOL;
   }
   
   $schemaManager = $connection->createSchemaManager();
   
   $tableNames = $schemaManager->listTableNames();
   sort($tableNames);
   
   foreach ($tableNames as $tableName) {
       echo $tableName . PHP_EOL;
   }

3. Observe the output:

   An exception occurred while executing a query: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop table spatial_ref_sys because extension postgis requires it
   HINT:  You can drop extension postgis instead.
   tiger.addr
   tiger.addrfeat
   tiger.bg
   tiger.county
   tiger.county_lookup
   tiger.countysub_lookup
   tiger.cousub
   tiger.direction_lookup
   tiger.edges
   tiger.faces
   tiger.featnames
   tiger.geocode_settings
   tiger.geocode_settings_default
   tiger.loader_lookuptables
   tiger.loader_platform
   tiger.loader_variables
   tiger.pagc_gaz
   tiger.pagc_lex
   tiger.pagc_rules
   tiger.place
   tiger.place_lookup
   tiger.secondary_unit_lookup
   tiger.state
   tiger.state_lookup
   tiger.street_type_lookup
   tiger.tabblock
   tiger.tabblock20
   tiger.tract
   tiger.zcta5
   tiger.zip_lookup
   tiger.zip_lookup_all
   tiger.zip_lookup_base
   tiger.zip_state
   tiger.zip_state_loc
   topology.layer
   topology.topology
   

As you can see, the script fails to drop the table in question (meaning that it exists) but it doesn't list it. Although, it lists a bunch of other tables supposedly owned by the extension.

Please try reproducing your issue using this container image and the script.

[lmichelin]

I followed these 3 steps to reproduce your test and I can confirm I have the same output as yours.

Actually there is no issue with the listTableNames method, there is an issue with the listTables method.

To reproduce it you can do the following test:

1. Spin up the same database as above

2. Install doctrine/dbal 3.3.8: composer require doctrine/dbal 3.3.8

3. execute this script:

<?php

use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Exception;

require 'vendor/autoload.php';

$connection = DriverManager::getConnection([
    'driver' => 'pdo_pgsql',
    'host' => 'localhost',
    'user' => 'postgres',
    'password' => 'Passw0rd',
]);

// keep only postgis extension to have only the spatial_ref_sys table
$connection->executeStatement('DROP EXTENSION IF EXISTS postgis_tiger_geocoder');
$connection->executeStatement('DROP EXTENSION IF EXISTS postgis_topology');

try {
    $connection->executeStatement('DROP TABLE spatial_ref_sys');
} catch (Exception $e) {
    echo $e->getMessage().PHP_EOL;
}

$schemaManager = $connection->createSchemaManager();

$tables = $schemaManager->listTables();

echo 'Tables count: '.count($tables).PHP_EOL;

foreach ($tables as $table) {
    echo $table->getName().PHP_EOL;
}

4. Observe the output:

An exception occurred while executing a query: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop table spatial_ref_sys because extension postgis requires it
HINT:  You can drop extension postgis instead.
Tables count: 0

✅ No table is listed.

5. Install doctrine/dbal 3.4.4: composer require doctrine/dbal 3.4.4

6. Execute the same script

7. Observe the output:

An exception occurred while executing a query: SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop table spatial_ref_sys because extension postgis requires it
HINT:  You can drop extension postgis instead.
Tables count: 1
spatial_ref_sys

❌ One table is listed: spatial_ref_sys.

[morozov]

Thanks for the details, @lmichelin. Please check if #5687 works for you.

[lmichelin]

I just tried your fork and it works, thanks! 🎉

[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.