[DBAL-412] PostgreSqlSchemaManager::listTableColumns() fails if there are columns defined by domains in multiple schemas Created: 08/Jan/13  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: 2.3.1
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Major
Reporter: Roger Hunwicks Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Postgresql 9.1



 Description   

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.



 Comments   
Comment by Benjamin Eberlei [ 01/May/13 ]

Fixed

Generated at Wed Nov 26 08:05:31 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.