DBAL-412: PostgreSqlSchemaManager::listTableColumns() fails if there are columns defined by domains in multiple schemas #1606

Closed
doctrinebot opened this Issue Jan 8, 2013 · 2 comments

2 participants

@doctrinebot

Jira issue originally created by user rhunwicks:

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.

@doctrinebot

Comment created by @beberlei:

Fixed

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.3.4 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment