Skip to content

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

Open
@allan-simon

Description

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

Activity

morozov

morozov commented on Sep 24, 2022

@morozov
Member

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

morozov

morozov commented on Oct 9, 2022

@morozov
Member

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

allan-simon

allan-simon commented on Oct 9, 2022

@allan-simon
ContributorAuthor

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

changed the title Schema's getMigrateFromSql always adds CREATE SCHEMA postgresql: Schema's diff generator does not compare default schema as equal when used implicitly/explicitly on Oct 9, 2022
changed the title postgresql: Schema's diff generator does not compare default schema as equal when used implicitly/explicitly postgresql: Schema's Comparator does not compare default schema as equal when used implicitly/explicitly on Oct 9, 2022
allan-simon

allan-simon commented on Oct 9, 2022

@allan-simon
ContributorAuthor

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

morozov commented on Oct 9, 2022

@morozov
Member

@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

allan-simon commented on Oct 15, 2022

@allan-simon
ContributorAuthor

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 ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions

      postgresql: Schema's Comparator does not compare default schema as equal when used implicitly/explicitly · Issue #5692 · doctrine/dbal