dbal: DBAL-1020: Postgres and using Schema tool throws cardinality errors

Jira issue originally created by user intellix:

Postgres: 9.3.5.0 (Postgres App for OSX) w/ PostGIS extensions doctrine/common: 2.4.x-dev ae92d076442e27b6910dd86a1292a8867cf5cfe4 doctrine/dbal: dev-master 1c9c24a7e2295b71249ae2a719ce38861fccd551 creof/doctrine2-spatial: https://github.com/intellix/doctrine2-spatial 4023ca8fbe703043012c31d6df26b9bc7b0a972d

It seems every now and again when I come to use the schema-tool I’m getting exceptions which can only be fixed by dropping the database and recreating from scratch.

The following SQL looks to be generated here: \Doctrine\DBAL\Platforms\AbstractPlatform::getListTableForeignKeysSQL

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'

The full stack trace is as follows:

 ---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>--
  Dropping database schema...
      ./bin/doctrine-module orm:schema-tool:drop --force --verbose
---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>---<sub>---</sub>--
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]

About this issue

Original URL
State: closed
Created 10 years ago
Comments: 24 (4 by maintainers)

Commits related to this issue

Issue #950: Fixed cardinality issue where subquery could return more than one row in getListTableForeignKeysSQL function — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Fixed cardinality issue where subquery could return more than one row in getListTableForeignKeysSQL function — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Fixed cardinality issue where subquery could return more than than one row in getListTableForeignKeysSQL function — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Added test case for PostgreSqlPlatform->listTableForeignKeys() — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Update to current phpunit — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Code style updates and fix for phpunit 8 compliance — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Changed db user to postgres. And more code style updates. — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: More code style updates. And another attempt to appease the ReferenceViaFallbackGlobalName travis code sniffer error — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Added use function in_array. And updated $connectionParameters type hint. — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Skipping test for non postgresql coverage tests — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Skipping test for non postgresql coverage tests - by checking available drivers — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Skipping test for non postgresql coverage tests - by checking connection driver — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Another attempt to pass appveyor by checking for loaded extension — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Added use function extension_loaded to pass travis code sniffer — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Sorting use statements to pass travis code sniffer — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Changed sub selects in getListTableForeignKeysSQL and getListTableIndexesSQL to use LIMIT 1. Triggering deprecation when not including schema with table. — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Deprecating database parameter — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Updates after rebasing — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Code style compliance clean up — committed to jwilson-cee/dbal by jwilson-cee 5 years ago
Issue #950: Reverting sub select in getListTableIndexesSQL to use IN. This is necessary for that query to return all the indexes for the table. Removed doc hyperlink that no longer exists. — committed to jwilson-cee/dbal by jwilson-cee 5 years ago

Most upvoted comments

Comment created by jaroslav:

Replacing “=” with “IN” in PostreSqlPlatform.php 290 line fixes the problem. I haven’t tested it thoroughly, but it looks like it’s working (I’ve been able to successfully update my schema without dropping it).

doctrinebot on Dec 6, 2015

Hi here,

I got the same issue too. I deal with it by creating a custom PlatformService

doctrine:
  dbal:
    driver: 'pdo_pgsql'
    server_version: '13.1'
    charset: 'utf8'
    platform_service: Infrastructure\Database\Doctrine\DBAL\Platforms\CustomPostgreSqlPlatform

And then in the CustomPostgreSqlPlatform class, you can set the correct schema by providing this value in the constructor or like you want … By example:

use Doctrine\DBAL\Platforms\PostgreSQL100Platform;

class CustomPostgreSqlPlatform extends PostgreSQL100Platform
{
    public function getListTableForeignKeysSQL($table, $database = null)
    {
        $yourCustomSchema = 'schematest';

         //Adapt here
         $table = stristr('public.', $table) ? $table : $yourCustomSchema.'.'. $table;
       
        return parent::getListTableForeignKeysSQL($table, $database);
    }

}

drochette on Apr 27, 2021

I managed to workaround this issue by moving all of my data from named schema to “public” schema that is a default one. The migrations are working. I don’t have any other users and schemas in the database as this db is an exclusive instance for the application.

blackandred on Nov 16, 2019

I’m also affected by this issue. I’m trying to migrate from MySQL to PostgreSQL using pgloader.

blackandred on Nov 15, 2019

@Ocramius Apologies. Is there a better way to note that a multi-year old issue issue is still around and causing issues and doesn’t get forgotten? Especially one where the fix has seemingly been posted, so there isn’t much more discussion to contribute? (Aside from the pre-mentioned PR, which I haven’t had a chance to work on because I just discovered this today.) Some places have issue voting, but GH doesn’t really. I’ve seen other repos use +1s, and whether it’s annoying or not may vary by individual.

Unless I’m missing something, there’s also not a CONTRIBUTING.md or equivalent section in README.md either to guide how a someone noticing an issue should continue.

Again, apologies for being annoying, it definitely wasn’t the intent.

jpmarcotte on Oct 3, 2018