Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-227

Driver for PostgreSQL: DBAL fails to list foreign keys if multiple tables with the same name exist, each in a different schema

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.2.1
    • Fix Version/s: None
    • Component/s: Drivers
    • Security Level: All
    • Labels:
      None
    • Environment:
      Ubuntu 10.04

      Description

      PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression' in /usr/share/php/Doctrine/DBAL/Connection.php:620
      Stack trace:
      #0 /usr/share/php/Doctrine/DBAL/Connection.php(620): PDO->query('SELECT r.connam...')
      #1 /usr/share/php/Doctrine/DBAL/Connection.php(571): Doctrine\DBAL\Connection->executeQuery('SELECT r.connam...', Array)
      #2 /usr/share/php/Doctrine/DBAL/Schema/AbstractSchemaManager.php(262): Doctrine\DBAL\Connection->fetchAll('SELECT r.connam...')
      #3 /usr/share/php/Doctrine/DBAL/Schema/AbstractSchemaManager.php(229): Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys('logs')
      #4 /usr/share/php/Doctrine/DBAL/Schema/AbstractSchemaManager.php(214): Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails('logs')
      #5 /usr/share/php/Doctrine/DBAL/Schema/AbstractSchemaManager.php(764): Doctrine\DBAL\Schema\AbstractSchemaManager->listTables()
      #6 /home/phopfgartner/devel/doctrine_tests/conn.php in /usr/share/php/Doctrine/DBAL/Connection.php on line 620

      The failing SQL statement is:

      SELECT r.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 = 'logs' AND n.oid = c.relnamespace
      )
      AND r.contype = 'f'

      Indeed, if I execute:

      SELECT c.relname, n.nspname
      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 = 'logs' AND n.oid = c.relnamespace

      I get:

      relname | nspname
      ===============
      logs | sbr_stats
      logs | public

      Regards,

      Peter

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Are you sure you are on 2.2.1? Because for me the query there is:

        SELECT r.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 = 'ddc227logs' AND n.nspname = ANY(string_to_array((select setting from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace
                          )
                          AND r.contype = 'f'"
        

        Which gives the correct result.

        Show
        Benjamin Eberlei added a comment - Are you sure you are on 2.2.1? Because for me the query there is: SELECT r.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 = 'ddc227logs' AND n.nspname = ANY(string_to_array((select setting from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace ) AND r.contype = 'f'" Which gives the correct result.
        Hide
        Peter Hopfgartner added a comment -

        Your're right! It was version 2.1. With 2.2 it's perfectly fine.

        Sorry the noise,

        Peter

        Show
        Peter Hopfgartner added a comment - Your're right! It was version 2.1. With 2.2 it's perfectly fine. Sorry the noise, Peter
        Hide
        Benjamin Eberlei added a comment -

        There won't be another DBAL 2.1 release so i am closing this, please update your library.

        Show
        Benjamin Eberlei added a comment - There won't be another DBAL 2.1 release so i am closing this, please update your library.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Peter Hopfgartner
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: