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]

Oct 22 '14 23:10 doctrinebot

Comment created by @ocramius:

What are the contents of pg*catalog.pg*class ?

Oct 22 '14 23:10 doctrinebot

Comment created by intellix:

Uploaded CSV of that table

Oct 22 '14 23:10 doctrinebot

Comment created by intellix:

After running the subquery as suggested in IRC:

  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

oid

40152 39687

Oct 22 '14 23:10 doctrinebot

Comment created by @ocramius:

Can you run the query:

    c.*
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```

Oct 22 '14 23:10 doctrinebot

Comment created by intellix:

  relname varchar,
  relnamespace oid,
  reltype oid,
  reloftype oid,
  relowner oid,
  relam oid,
  relfilenode oid,
  reltablespace oid,
  relpages int,
  reltuples real,
  relallvisible int,
  reltoastrelid oid,
  reltoastidxid oid,
  relhasindex bool,
  relisshared bool,
  relpersistence char(1),
  relkind char(1),
  relnatts smallint,
  relchecks smallint,
  relhasoids bool,
  relhaspkey bool,
  relhasrules bool,
  relhastriggers bool,
  relhassubclass bool,
  relispopulated bool,
  relfrozenxid xid,
  relminmxid xid,
  relacl _aclitem,
  reloptions _text

state,2200,40154,0,10,0,40152,0,0,0,0,0,0,true,false,p,r,2,0,false,true,false,true,false,true,6694,1,NULL,NULL
state,39587,39689,0,10,0,39687,0,0,0,0,39694,0,true,false,p,r,15,3,false,true,false,false,false,true,6629,1,NULL,NULL

Oct 22 '14 23:10 doctrinebot

Comment created by intellix:

My ZF2 onBootstrap as well, in case it changes anything:

<?php
namespace Flatscanner;

use Doctrine\ORM\Mapping\UnderscoreNamingStrategy;
use ZF\Apigility\Provider\ApigilityProviderInterface;
use Zend\Uri\UriFactory;
use Doctrine\DBAL\Types\Type;

class Module implements ApigilityProviderInterface
{
    public function getConfig()
    {
        return include *_DIR_* . '/../../config/module.config.php';
    }

    public function onBootstrap($e)
    {
        Type::addType('geometry', 'CrEOF\Spatial\DBAL\Types\GeometryType');
        Type::addType('point', 'CrEOF\Spatial\DBAL\Types\Geometry\PointType');
        UriFactory::registerScheme('chrome-extension', 'Zend\Uri\Uri');

        // Set naming strategy
        $em = $e->getTarget()->getServiceManager()->get('doctrine.entitymanager.orm_default');
        $em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping("_text", "text"); // assuming it is a text LOB
        $em->getConfiguration()->setNamingStrategy(new UnderscoreNamingStrategy(CASE_LOWER));
    }

    public function getAutoloaderConfig()
    {
        return array(
            'ZF\Apigility\Autoloader' => array(
                'namespaces' => array(
                    *_NAMESPACE__ => __DIR_*,
                ),
            ),
        );
    }
}

Oct 22 '14 23:10 doctrinebot

Comment created by @deeky666:

Most probably also affects 2.4 as the codebase has not changed at the critical places. Possibly 2.3 is also affected by this. Could need a check.

Oct 23 '14 10:10 doctrinebot

Comment created by jaroslav:

Can confirm, that problem still exists on both 2.4.4 and 2.5.0. Is there any progress on this?

Jul 28 '15 05:07 doctrinebot

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).

Aug 04 '15 09:08 doctrinebot

Just wanted to mention that this just kind of bit me too. I happen to have a table named 'state' with a list of US states in it, and I also have POSTGIS tiger geocoder installed which also contains a table named state. Luckily I haven't populated the tiger tables yet, so I was able to temporarily drop the tiger extension and delete it's schemas, which made the error go away.

Apr 27 '17 19:04 epoplive

+1

Nov 02 '17 13:11 tdubuffet

+1 Just wanted to give this a bump as I've also experienced this bug.

Also want to note that the patch mentioned by jaroslav worked for me

Replacing "=" with "IN" in PostreSqlPlatform.php 290 line

But would be great to have this fixed in the package instead of doing this patch each time we do a composer install.

Thanks.

Jun 14 '18 17:06 jwilson-cee

+1

Oct 02 '18 19:10 jpmarcotte

@tdubuffet @jwilson-cee @jpmarcotte +1 is not helpful, and is actually just more noise for maintainers. If you have a patch and a test scenario, feel free to send it, but +1 is really just annoying.

Oct 02 '18 20:10 Ocramius

@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.

Oct 03 '18 04:10 jpmarcotte

@jpmarcotte besides providing a patch yourself linking back here, there is not much to be done: we have hundreds of issues in the backlog, and we're not really looking into them ourselves, unless it is critical.

Oct 03 '18 21:10 Ocramius

For those googling this issue that are getting the SQL cardinality error, a workaround for this bug would be to include the schema prefix in the $table parameter - i.e. some_schema.some_table

For the Doctrine team (@greg0ire @morozov, @Ocramius), I recommend that this be fixed. I'm willing to work on this, but I'll need clear direction on how you want it fixed.

To me, the simplest fix would be to correct the WHERE = SELECT(...) clause in the getListTableForeignKeysSQL to be WHERE IN SELECT(...). But @greg0ire feels this would be 'malicious compliance' as it would return a list of foreign keys from multiple tables with the same name in different schemas. I disagree with this for two reasons:

The other two functions getListTableIndexesSQL and getListTableColumnsSQL are already returning a list of objects from multiple tables with the same name in different schemas (see my notes and SQL at the end of this comment).
If we go by the name of the function, it returns what is expected. The name is kind of Yoda'ish - "get list table foreign keys SQL" - translates to "Get SQL that will return a list of foreign keys for a given table". There is no mention of schema in this function. It's only expecting a table. So if it returns a list of all foreign keys it finds for that table name, it's doing its job.

If you feel these functions should not return a list of objects from multiple tables in different schemas, then all the SQL in these functions need to be rewritten so that they won't do that, and the functions should require the schema be included with the table name.

Notes on what I've found in the PostgreSqlPlatform code:

I've dug into all the getListTable...SQL() functions and I've discovered that all of them return SQL that will generate a list of all objects for a table name that is in multiple schemas (the getListTableForeignKeysSQL is the only one that crashes because of the = operator in the WHERE clause)

Here is SQL you can run on a postgres db to see the results. I've noted which functions generate the SQL for each.

CREATE SCHEMA test_schema1;
CREATE SCHEMA test_schema2;
CREATE TABLE test_schema1.test_foreign1 ( id integer PRIMARY KEY );
CREATE TABLE test_schema1.test_table ( test_column1 varchar(5), test_foreign1_id integer constraint fk_test_foreign1 references test_schema1.test_foreign1 (id) );
CREATE INDEX idx_test_column1 ON test_schema1.test_table (test_column1);
CREATE TABLE test_schema2.test_foreign2 ( id integer PRIMARY KEY );
CREATE TABLE test_schema2.test_table ( test_column2 varchar(5), test_foreign2_id integer constraint fk_test_foreign2 references test_schema2.test_foreign2 (id) );
CREATE INDEX idx_test_column2 ON test_schema2.test_table (test_column2);
set search_path = "test_schema1", "test_schema2";

-- getListTableForeignKeysSQL('test_table')
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 IN -- The equals '=' operator will fail here
      (
          SELECT c.oid
          FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
          WHERE
          -- getTableWhereClause('test_table')
          n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
              AND c.relname = 'test_table'
              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
          -- END getTableWhereClause
      )
  AND r.contype = 'f';
-- Returns
/*
| conname          | condef                                                      | 
|------------------|-------------------------------------------------------------| 
| fk_test_foreign1 | FOREIGN KEY (test_foreign1_id) REFERENCES test_foreign1(id) | 
| fk_test_foreign2 | FOREIGN KEY (test_foreign2_id) REFERENCES test_foreign2(id) | 
 */

-- getListTableIndexesSQL('test_table')
SELECT quote_ident(relname) as relname, pg_index.indisunique, pg_index.indisprimary,
       pg_index.indkey, pg_index.indrelid,
       pg_get_expr(indpred, indrelid) AS where
FROM pg_class, pg_index
WHERE oid IN (
    SELECT indexrelid
    FROM pg_index si, pg_class sc, pg_namespace sn
    WHERE
    -- getTableWhereClause('test_table')
    sn.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
      AND sc.relname = 'test_table'
      AND sn.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"',user),' ','') from pg_catalog.pg_settings where name = 'search_path'),','))
      AND sn.oid = sc.relnamespace
    -- END getTableWhereClause
    AND sc.oid=si.indrelid
    AND sc.relnamespace = sn.oid
) AND pg_index.indexrelid = oid;
-- Returns
/*
| relname          | indisunique | indisprimary | indkey | indrelid | where |
|------------------|-------------|--------------|--------|----------|-------|
| idx_test_column1 | false       | false        | 1      | 3561506  |       |
| idx_test_column2 | false       | false        | 1      | 3561519  |       |
 */

-- getListTableColumnsSQL('test_table')
SELECT
    a.attnum,
    quote_ident(a.attname) AS field,
    t.typname AS type,
    format_type(a.atttypid, a.atttypmod) AS complete_type,
    (SELECT tc.collcollate FROM pg_catalog.pg_collation tc WHERE tc.oid = a.attcollation) AS collation,
    (SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type,
    (SELECT format_type(t2.typbasetype, t2.typtypmod) FROM
        pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type,
    a.attnotnull AS isnotnull,
    (SELECT 't'
     FROM pg_index
     WHERE c.oid = pg_index.indrelid
       AND pg_index.indkey[0] = a.attnum
       AND pg_index.indisprimary = 't'
    ) AS pri,
    (SELECT pg_get_expr(adbin, adrelid)
     FROM pg_attrdef
     WHERE c.oid = pg_attrdef.adrelid
       AND pg_attrdef.adnum=a.attnum
    ) AS default,
    (SELECT pg_description.description
     FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
    ) AS comment
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
WHERE
  -- getTableWhereClause('test_table')
  n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
      AND c.relname = 'test_table'
      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
  -- END getTableWhereClause
  AND a.attnum > 0
  AND a.attrelid = c.oid
  AND a.atttypid = t.oid
  AND n.oid = c.relnamespace
ORDER BY a.attnum;
-- Returns
/*
| attnum | field            | type    | complete_type        | collation | domain_type | domain_complete_type | isnotnull | pri | default | comment |
|--------|------------------|---------|----------------------|-----------|-------------|----------------------|-----------|-----|---------|---------|
| 1      | test_column1     | varchar | character varying(5) |           |             |                      | false     |     |         |         |
| 1      | test_column2     | varchar | character varying(5) |           |             |                      | false     |     |         |         |
| 2      | test_foreign1_id | int4    | integer              |           |             |                      | false     |     |         |         |
| 2      | test_foreign2_id | int4    | integer              |           |             |                      | false     |     |         |         |
 */

-- This is a useless function that generates SQL that will never return any results
-- getListTableConstraintsSQL('test_table')
SELECT
    quote_ident(relname) as relname
FROM
    pg_class
WHERE oid IN (
    SELECT indexrelid
    FROM pg_index, pg_class
    WHERE pg_class.relname = 'test_table'
      AND pg_class.oid = pg_index.indrelid
      AND (indisunique = 't' OR indisprimary = 't')
);

Nov 11 '19 15:11 jwilson-cee

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

Nov 15 '19 11:11 blackandred

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.

Nov 16 '19 09:11 blackandred

@blackandred we also affected by this issue. I'm trying to migrate from MariaDB to PostgreSQL using pgloader.

Can you explain how you "moving all of my data from named schema to "public" schema that is a default one" ?

May 11 '20 13:05 andreybolonin

We fix this issue with:

create schema in PostgreSQL with doctrine bin/console d:s:u --force
migrate from MariaDB with https://github.com/dimitri/pgloader with create no tables option

p.s. make schema public is not solve the issue in my case

May 12 '20 13:05 andreybolonin

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);
    }

}

Apr 27 '21 20:04 drochette

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);
    }

}

This solution will not work if you have defined schema in your entity. Now in order to resolve this issue (mentioned in title on this page) and in case your are using not default schema "public" in Postgres you just need to create necessary shema before starting migration by symfony migration bundle, f.e.: CREATE SCHEMA IF NOT EXISTS schematest; Note: In general this issue related to symfony migration bundle which is creating table 'migration_versions' in public schema and it is doing this even before you first migration will start. So if you have your migration f.e. with definition for creating your custom schema you can see that table 'migration_versions' will be created in public schema which is second default schema after user related schema and it is defined in search_path property for the db (https://www.postgresql.org/docs/9.3/ddl-schemas.html). After that you can see the same error mentioned in this ticket.

Solution: If you are using docker compose it can be done by executing init db script, f.e.: volumes: - ./docker/postgres/init-db.sql:/docker-entrypoint-initdb.d/init-db.sql

And init-db.sql should contain next rows: CREATE SCHEMA IF NOT EXISTS schematest;

After that if you connect to the db with user schematest it means that default shema will be schematest (user related according to search_path) and symfony migration bundle will create table 'migration_versions' in correct schema " schematest".

Nov 01 '21 14:11 dimadeush

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

Oct 04 '22 00:10 github-actions[bot]