Uploaded image for project: 'Doctrine DBAL'
  1. Doctrine DBAL
  2. DBAL-412

PostgreSqlSchemaManager::listTableColumns() fails if there are columns defined by domains in multiple schemas


    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.1
    • Fix Version/s: 2.3.4
    • Component/s: Platforms, Schema Managers
    • Security Level: All
    • Labels:
    • Environment:
      Postgresql 9.1


      We use Postgresql Domains to ensure consistent column definitions across multiple tables. We also have multiple schemas in a database. Therefore we can have two domains defined in the same database with the same name, but in different schemas.

      In this situation, PostgreSqlSchemaManager::listTableColumns() fails when called for a table with a column defined by one of those domains. This means that PostgreSqlSchemaManager::createSchema() also fails for a schema containing such a table.

      SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

      This happens because of an error in the query definition in PostgreSqlPlatform::getListTableColumnsSQL(). The domain_complete_type column in the query is defined by matching the name only, without reference to the schema:

      (SELECT format_type(t2.typbasetype, t2.typtypmod) FROM   
        pg_catalog.pg_type t2                                                        
                             WHERE t2.typtype = 'd' AND t2.typname = format_type(a.atttypid, a.atttypmod)) AS domain_complete_type,                             

      The error can be corrected by making sure that the correct domain is matched by using the OID instead of the name to match:

      (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

      I can create a GitHub PR if it helps.


        rhunwicks Roger Hunwicks created issue -
        beberlei Benjamin Eberlei added a comment -


        beberlei Benjamin Eberlei added a comment - Fixed
        beberlei Benjamin Eberlei made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.3.4 [ 10421 ]
        Resolution Fixed [ 1 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={expand=changesets[0:20].revisions[0:29],reviews, query=DBAL-412}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)


          • Assignee:
            beberlei Benjamin Eberlei
            rhunwicks Roger Hunwicks
          • Votes:
            0 Vote for this issue
            2 Start watching this issue


            • Created: