postgresql: Schema's Comparator does not compare default schema as equal when used implicitly/explicitly

[allan-simon]

Bug Report

DBAL's Comparator is not able for postgresql to handle schema being in the search_path

In postgresql the default schema can be either used explicitly CREATE TABLE public.dummy or implicitly CREATE TABLE dummy
(more to come)

Q	A
Version	all version

Summary

Current behaviour

How to reproduce

Expected behaviour

[morozov]

@allan-simon in the future, please use some other place for your personal notes and reminders.

[morozov]

@allan-simon how much time do you need to provide the details?

[allan-simon]

sorry I do that tonight

Edit: on it

Unordered notes:

so it seems that on a high level
doctrine/migrations#441
doctrine/migrations#1196

happens for the same root i.e because

the DiffGenerator use a fromSchema and a toSchema , one from the metadata , one from the actual database and they are not able to handle the fact that the schema in the search_path can be omitted

so for example in 441 the issue is that

1. PostgreSQLSchemaManager.php::listSchemaNames return public so the Schema coming from purely data present in the database has the information that the public schema exists
2. the ORM annotation don't precise any Schema, so the schema coming from metadata things there's no schema

=> when Comparator the two it thinks a schema needs to be drop in the up migration (but dbal does not generate any SQL for drop of SQL schema , so nothing is generated ) , and do a create in the down migration

for 1196 I still need to dig , but it's certainly that the Schema getting information from the database get the information without schema (as it's the one in the default search_path) while the Schema getting information metadata get the table with the full name

[allan-simon]

at least compared to #5609 , I now agree that it's not about public itself as a constant string, but rather indeed that either doctrine's dbal's Comparator or higher in the stack the piece of code feeding the fromSchema and toSchema that don't take in account the behaviour of postgresql related to the use of search_path

[morozov]

@allan-simon I don't know how to use the information you provided to proceed. Please express your problem in a format like "When I do X, I expect Y to happen but instead Z happens". X, Y and Z should be expressed in terms of the DBAL API.

[allan-simon]

so basically if we go a level higher we have the following issue (which is related to doctrine/migrations#1196 )

admitting you have

#[ORM\Entity]
#[ORM\Table(schema)]
class Foobar
{
    #[ORM\Id]
    #[ORM\Column(type: 'integer', nullable: false)]
    public int $id;
}

if you call

$x = new SchemaTool($entityManager);
$x->getUpdateSchemaSql([$entityManager->getClassMetadata(Foobar::class)])

it returns CREATE TABLE foobar (id INT NOT NULL, PRIMARY KEY(id))

if you now change to

#[ORM\Entity]
#[ORM\Table(schema: 'public')]

you will get

"CREATE TABLE public.foobar (id INT NOT NULL, PRIMARY KEY(id))" and DROP TABLE foobar which will fail because actually for postgresql , if public is in your search_path , both pbulic.foobar and foobar refers to the same table

in term of DBAL's API it means that the issue is that 2 Doctrine\DBAL\Schema\Table (or any other AbstractAsset actually) :

1. one with a namespace set to null
2. one with a namespace set to the value in search_path

will compare as being different

so the question i'm trying to wrap my head around is that

is this the responsability of dbal's Schema/Comparator to know that these two tables are actually the same ?

or

is it the responsability of the SchemaManager (or those filling it , from metadata / checking the actual database ) to always fill it with the actual value ?