Open
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 |
Activity
morozov commentedon Sep 24, 2022
@allan-simon in the future, please use some other place for your personal notes and reminders.
morozov commentedon Oct 9, 2022
@allan-simon how much time do you need to provide the details?
allan-simon commentedon Oct 9, 2022
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 omittedso for example in 441 the issue is that
PostgreSQLSchemaManager.php::listSchemaNames
returnpublic
so the Schema coming from purely data present in the database has the information that thepublic
schema exists=> 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 commentedon Oct 9, 2022
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 ofsearch_path
morozov commentedon Oct 9, 2022
@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 commentedon Oct 15, 2022
so basically if we go a level higher we have the following issue (which is related to doctrine/migrations#1196 )
admitting you have
if you call
it returns
CREATE TABLE foobar (id INT NOT NULL, PRIMARY KEY(id))
if you now change to
you will get
"CREATE TABLE public.foobar (id INT NOT NULL, PRIMARY KEY(id))"
andDROP TABLE foobar
which will fail because actually for postgresql , ifpublic
is in yoursearch_path
, bothpbulic.foobar
andfoobar
refers to the same tablein term of DBAL's API it means that the issue is that 2 Doctrine\DBAL\Schema\Table (or any other AbstractAsset actually) :
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 ?