PostgreSQL: Schema Update recreates foreign key when referenced table is a reserved word and in custom schema

[cristi-contiu]

Bug Report

Q	A
Version	3.9.4

Summary

The orm:schema-tool:update, orm:validate-schema and doctrine-migrations diff commands report a foreign key needing updating right after running the update command.

This happens on PostgreSQL only when the foreign table's name is a reserved keyword that needs quoting (lke user) and if the foreign table is in a different schema, like "other_schema" (not a default search path schema).

I narrowed it down to Schema\Comparator::diffForeignKey, where a difference is identified when comparing the foreign table names: $key1->getUnqualifiedForeignTableName() returns "user" (with qoutes), while $key2->getUnqualifiedForeignTableName() returns user (without qoutes).

This is because the full foreign table name for $key1 (in $fromSchema) is other_schema."user" , while $key2 (in $toSchema) is other_schema.user. When the ForeignKeyConstraint objects are created, they are only trimmed of quotes if the first character is a quote, which is not in these cases.

I suggest trimming the quotes in getUnqualifiedForeignTableName. The same thing is done right above to local and foreign column names (getUnquotedLocalColumns / getUnquotedForeignColumns).

Current behavior

These 2 queries are identified as differences right after updating the schema:

ALTER TABLE other_schema.other_table DROP CONSTRAINT FK_47BC724AA76ED395
ALTER TABLE other_schema.other_table ADD CONSTRAINT FK_47BC724AA76ED395 FOREIGN KEY (user_id) REFERENCES other_schema."user" (id) NOT DEFERRABLE INITIALLY IMMEDIATE

Expected behavior

No differences should be identified right after updating the schema.

How to reproduce

On the first run of this script, the schemas and tables are created. Next runs, it drops and re-created the foreign key.

<?php

require 'vendor/autoload.php';

$dsnParser = new \Doctrine\DBAL\Tools\DsnParser(['postgresql' => 'pdo_pgsql']);
$connectionParams = $dsnParser->parse('postgresql://postgres:password@database:5432/postgres?serverVersion=16&charset=utf8');
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);
$platform = $conn->getDatabasePlatform();
$sm = $conn->createSchemaManager();

$fromSchema = $sm->introspectSchema();

$toSchema = new \Doctrine\DBAL\Schema\Schema();
$userTable = $toSchema->createTable('other_schema.user');
$userTable->addColumn('id', 'integer', ['autoincrement' => true]);
$userTable->setPrimaryKey(["id"]);
$otherTable = $toSchema->createTable('other_schema.other_table');
$otherTable->addColumn('id', 'integer', ['autoincrement' => true]);
$otherTable->addColumn("user_id", "integer");
$otherTable->setPrimaryKey(["id"]);
$otherTable->addForeignKeyConstraint($userTable, ["user_id"], ["id"]);

$schemaDiff = (new \Doctrine\DBAL\Schema\Comparator())->compareSchemas($fromSchema, $toSchema);

foreach ($platform->getAlterSchemaSQL($schemaDiff) as $query) {
    echo $query . PHP_EOL;
    $conn->executeStatement($query);
}