Postgresql: schema introspection incorrectly parses sequences

[Grundik]

Bug Report

Q	A
Version	3.6.2

Summary

Schema introspection incorrectly parses sequences from namespace public, if current namespace is other than public.

Current behaviour

Doctrine\DBAL\Schema\PostgreSQLSchemaManager#_getPortableSequenceDefinition just removes schema name if it is public: https://github.com/doctrine/dbal/blob/3.6.x/src/Schema/PostgreSQLSchemaManager.php#L357

    protected function _getPortableSequenceDefinition($sequence)
    {
        if ($sequence['schemaname'] !== 'public') {
            $sequenceName = $sequence['schemaname'] . '.' . $sequence['relname'];
        } else {
            $sequenceName = $sequence['relname'];
        }

but later on, when sequences are added to the schema, if there are no namespace specified, it is filled from default one: https://github.com/doctrine/dbal/blob/3.6.x/src/Schema/Schema.php#L206

    private function normalizeName(AbstractAsset $asset): string
    {
        return $asset->getFullQualifiedName($this->getName());
    }

and deeper into https://github.com/doctrine/dbal/blob/3.6.x/src/Schema/AbstractAsset.php#L130:

        if ($this->_namespace === null) {
            $name = $defaultNamespaceName . '.' . $name;
        }

But! This new schema are coming from search_path: https://github.com/doctrine/dbal/blob/3.6.x/src/Schema/AbstractSchemaManager.php#L1678

        $searchPaths = $this->getSchemaSearchPaths();
        if (isset($searchPaths[0])) {
            $schemaConfig->setName($searchPaths[0]);
        }

It means, that sequence changes its namespace from public to one, which is in search_path.

Due to this behaviour, schema introspection are not getting database schema correctly, and even fails if there are sequences with same name in different namespaces.

How to reproduce

Create database, namespace, and then two sequences:

CREATE SCHEMA test;
CREATE SEQUENCE public.test;
CREATE SEQUENCE test.test;

// Connect to the database
$conn = Doctrine\DBAL\DriverManager::getConnection([...]);
// Change default schema
$conn->executeQuery('SET search_path TO test');
// Try to introspect it
$schema = $conn->getSchemaManager()->introspectSchema();

That would result an error:

PHP Fatal error:  Uncaught Doctrine\DBAL\Schema\Exception\SequenceAlreadyExists: The sequence "test.test" already exists. in .../doctrine/dbal/src/Schema/Exception/SequenceAlreadyExists.php:16

Expected behaviour

Namespace names on sequences should be preserved, introspection of multi-namespaced databases should not fail.

[derrabus]

Do you want to work on a fix?

[Grundik]

There is a simple fix: just remove special handling of 'public' namespace in PostgreSQLSchemaManager->_getPortableSequenceDefinition. But that will probably open can of worms, so I dont know how to reliably fix that without bunch of BC break problems.