Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
doctrinebot opened this issue Oct 22, 2014 · 19 comments
Open

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

doctrinebot opened this issue Oct 22, 2014 · 19 comments
Assignees
Labels
Bug

Comments

@doctrinebot
Copy link

@doctrinebot doctrinebot commented Oct 22, 2014

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 1c9c24a
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]
@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 22, 2014

Comment created by @Ocramius:

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

@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 22, 2014

Comment created by intellix:

Uploaded CSV of that table

@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 22, 2014

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

@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 22, 2014

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```                           
@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 22, 2014

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

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 22, 2014

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_*,
                ),
            ),
        );
    }
}
@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Oct 23, 2014

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.

@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Jul 28, 2015

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?

@doctrinebot

This comment has been minimized.

Copy link
Author

@doctrinebot doctrinebot commented Aug 4, 2015

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

@epoplive

This comment has been minimized.

Copy link

@epoplive epoplive commented Apr 27, 2017

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.

@tdubuffet

This comment has been minimized.

Copy link

@tdubuffet tdubuffet commented Nov 2, 2017

+1

@jwilson-cee

This comment has been minimized.

Copy link

@jwilson-cee jwilson-cee commented Jun 14, 2018

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

@jpmarcotte

This comment has been minimized.

Copy link

@jpmarcotte jpmarcotte commented Oct 2, 2018

+1

@Ocramius

This comment has been minimized.

Copy link
Member

@Ocramius Ocramius commented Oct 2, 2018

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

@jpmarcotte

This comment has been minimized.

Copy link

@jpmarcotte jpmarcotte commented Oct 3, 2018

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

@Ocramius

This comment has been minimized.

Copy link
Member

@Ocramius Ocramius commented Oct 3, 2018

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

jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Jan 2, 2019
…rn more than one row in getListTableForeignKeysSQL function
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Jan 2, 2019
…rn more than one row in getListTableForeignKeysSQL function
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Jan 2, 2019
…rn more than than one row in getListTableForeignKeysSQL function
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 14, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 14, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 14, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 14, 2019
…ppease the ReferenceViaFallbackGlobalName travis code sniffer error
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 14, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 15, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 15, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 21, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Feb 21, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 4, 2019
… and getListTableIndexesSQL to use LIMIT 1. Triggering deprecation when not including schema with table.
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 5, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 5, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 5, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 6, 2019
… use IN. This is necessary for that query to return all the indexes for the table. Removed doc hyperlink that no longer exists.
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 6, 2019
jwilson-cee added a commit to jwilson-cee/dbal that referenced this issue Nov 7, 2019
Issue doctrine#950: The cardinality error would happen when the schema wasn't
prefixed in the $table parameter. As the subquery in the WHERE = clause
would return more than one result if there was the same table name in
two different schemas. (This is specific to postgres only.)

This adds:
 - LIMIT 1 to the subquery of the WHERE = (SELECT ...) clause
 - A clearer parameter name $tableWithSchema and doc blocks to convey
   what's expected for this function
 - A deprecation message for when the schema is not included
@jwilson-cee

This comment has been minimized.

Copy link

@jwilson-cee jwilson-cee commented Nov 11, 2019

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:

  1. 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).
  2. 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')
);
@blackandred

This comment has been minimized.

Copy link

@blackandred blackandred commented Nov 15, 2019

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

@blackandred

This comment has been minimized.

Copy link

@blackandred blackandred commented Nov 16, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
7 participants
You can’t perform that action at this time.