There is some useless code in the schema manager for PostgreSQL for creating and dropping a database. Moreover if either of those operations fails, the schema manager stays in an inconsistent state because the temporary connection that is set in the schema manager is not reverted back to the original connection.

The table is successfully created after a schema update, but when another schema update is executed without any changes to the entity, an ALTER query is executed:

ALTER TABLE test ALTER name TYPE CHAR(2);

After I searched in the code of doctrine dbal, I discovered that the length of the char column is not fetched correctly in `PostgreSqlSchemaManager::_getPortableTableColumnDefinition()`. In my example, length results as `null` and will be later set to 255 in the `Comparator`, which isn't the correct length from the database column. The comparation of the schemas results in a change of char-length.

For simplicity, I extended the if-condition in `PostgreSqlSchemaManager` to:

The fix I've contributed does help to parse the length in my case, but when I look into `PostgreSqlPlatform::initializeDoctrineTypeMappings()` there could be other types with the same problem, that are mapped to the doctrine string type.

To me this problem is caused by the bigger issue of some DBs defaulting to case-sensitive and some to case-insensitive. From my searching I have been unable to find a solution to make doctrine operate the same across different collations/databases.

Perhaps the issue would be better described as:Doctrine does not provide consistency between case-sensitive and case insensitive collations/databases

IMHO it would be very beneficial if Doctrine implemented some method for consistency.

I'm not sure if this is the right place to go into more detail, so I'll refrain for now.

Could you please clarify a bit on "From my searching I have been unable to find a solution to make doctrine operate the same across different collations/databases."? What exactly is broken in Doctrine? Can you give examples?

When I create the index manually, without resorting to Doctrine, it does not detect it when updating the schema (might be a bug that was fixed in the latest version IIRC), so I guess this is a workaround in the meantime, but a dangerous one because one can't safely upgrade. Any other ideas ?

Because PostgreSQL will return the expression with a lot of
parentheses we cannot TRIM() them easily. It is easier and more
correct to adapt to what PostgreSQL returns. That means the declaration
of partial indexes must be updated as follow:

Before:
``options=

{"where": "other_id IS NULL"}

``

After:
``options=

{"where": "(other_id IS NULL)"}

``

And fore more complexe conditions, that would be:
``options=

{"where": "(((id IS NOT NULL) AND (other_id IS NULL)) AND (name IS NULL))"}

---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
Dropping database schema...
./bin/doctrine-module orm:schema-tool:drop --force --verbose
---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
Dropping database schema...
[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid =
(
SELECT c.oid
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'
,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace
)
AND r.contype = 'f'':
SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression
Exception trace:
() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:82
Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:116
Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:833
Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4
[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression
Exception trace:
() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:94
Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4
[PDOException]
SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression
Exception trace:
() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
PDO->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4
orm:schema-tool:drop [--dump-sql] [-f|--force] [--full-database]

When dealing with legacy schema it would be nice to be able to map default values and not have the schema spit out `ALTER` statements each time. This works correctly today for basic integer and string columns, but does not handle columns with special types such as `boolean` or `datetime`.

However, repeating the same schema generation will result in the same `ALTER` statement each time, because it will always detect that the default value has changed. The same is true for boolean columns.

This simple change prevents this situation from happening and correctly detects that the column default has not changed. It is specific to PostgreSQL.

If the database have different schemes, with objects, that the actual logged in user has no rights, the existing statements will collect all objects (sequences and tables) and try to read them in later steps. This will throws exceptions. The reason for that is the fact, that both procedures getListSequencesList() and getListTablesSQL() will receive all known database objects from postgres catalogs. But the actual logged-in user, maby has no read permissions to object inside other scheme-owner. The additional parts inside both sql-statements will reduce the result to only objects that the user are able to see.

Hi
I'm using dbal to generate schmea from database via Schema-Manager. The problem is that my primary field 'id' have default value of 'nextval('test1_id_seq'::regclass)' but when I retrive columns using Doctrine\DBAL\Schema\AbstractSchemaManager::listTableDetails() or Doctrine\DBAL\Schema\Table::getColumns() , default value of the column 'id' is null.
In Doctrine\DBAL\Schema\PostgreSqlSchemaManager::_getPortableTableColumnDefinition() method at line 292 default value replaced with null, I don't know why but I guess It's because Driver compatibility.
Also Doctrine\DBAL\Schema\Sequence has no method to retrieve that table.
So I don't have the default value (pointing at sequence) and I can't find out what Sequence is linked to this table either.

Using PostgreSQL and column with "timestamp without time zone" returns datetime in format Y-m-d H:i:s.u but not always. If u is zero the PHP driver returns Y-m-d H:i:s format only. Specification of function getDateTimeFormatString is missing for this case too. It's required to resolve described different two formats and add getDateTimeFormatString with usec to postgres platform
It's similar as DBAL-397 but for not time zone datetimes. It throws the same exception as in linked issue.

If those are reserved keywords, they should be added to the "PostgreSQLKeywords" class and they will be quoted by Doctrine. As far as I can see those keywords mentioned are not present in this class. Maybe there is something missing?

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, _getPortableSequencesList (see line 135 of AbstractSchemaManager):

This function goes out and does a _getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the _getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

The source of this problem is the difference between strategy="IDENTITY" and strategy="SEQUENCE"

With SEQUENCE doctrine creates the table schema with field type BIGINT and no specified. It then creates a seperate sequence and as far as I can tell takes care of getting and inserting the next id number itself.

With IDENTITY doctrine creates the table schema with field type BIGSERIAL and no specified default. Now postgres automatically creates a sequence and creates the column with type BIGINT and sets the DEFAULT to the pgpsql statement required to get the nextval from the sequence.

At this point the two differently configured tables will work successfully and identically, except SEQUENCE tables will only get a correct new ID when run through the doctrine code while IDENTITY tables will get the correct new ID whenever an insert is done to the table.

Because in the case of an IDENTITY field postgresql creates the field with a default value refering to the sequence the sequence can not be deleted before the table reference is removed.

For my case I need the IDENTITY fields to work as we have a RADIUS server that needs to insert into one table which is managed and mapped to an entity in Doctrine.

Swapping the order of DROP TABLE and DROP SEQUENCE commands in Doctrine/DBAL/Schema/Visitor/DropSchemaSqlCollector.php in getQueries() line 159. Does not work as a quick fix. The following error occurs as the sequence is quite correctly be dropped along with the table.

The error occurs because PostgreSqlSchemaManager returns the bare table name from getPortableTablesList() if the schema is the first one in the search path.

The full explanation is...

AbstractSchemaManager::tablesExist() calls $this->getPortableTablesList() before checking if the tables exist.

PostgreSqlSchemaManager overrides this in _getPortableTableDefinition() by comparing the schema for the table with the search path for the connection. If the table schema is the first one in the search path, then it returns the bare table name, if it isn't then it returns the schema-qualified table name (i.e. schema.table).

tablesExist() does an array_intersect to check that all the tables in the search array exist in the database.

If one of the tables in the search array was schema-qualified but also in the first schema on the search path, then you end up checking:

array_intersect(array('test_schema.test_table'), array('test_table'))

which fails.

One way to fix it would be to override tablesExist() in PostgreSqlSchemaManager so that it passes the search array through getPortableTableDefinition() before doing the array_intersect: