DBAL-168: orm:schema-tool:update will fail if if public scheme has a domains table (PostgreSQL) #1327

Closed
doctrinebot opened this Issue Sep 22, 2011 · 4 comments

2 participants

@doctrinebot

Jira issue originally created by user bountin:

If I create a table 'domains' in the default scheme and execute the update procedure of the schema tool, it will throw a PDOException (SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression)

I've traced the error to the following statement, which is created by Doctrine\DBAL\Platforms\PostgreSqlPlatform->getListTableForeignKeysSQL():

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

This returns two oids because there are two relations with the name domains. One is my table and the other is a view in the information_scheme scheme.

The same problem should occur with the other information_scheme relations.

@doctrinebot

Comment created by @beberlei:

Hm the domains entry doesnt seems to pop up for me in pgadmin when executing your query. Can you help me a bit with details on how to reproduce this?

@doctrinebot

Comment created by bountin:

montbook: postgres$ createdb -E UTF8 -T template0 -l de_AT.UTF-8 foo
montbook:
postgres$ psql foo
foo=# create table domains (id int8 not null);

Now I have a table domains in the default schema and a domains table in the information_schema schema which is created and maintained by postgres. Now executing the query above will produce the mentioned exception.

It was necessary to create a table in the fresh database to create the information_schema. It is a meta schema from where you can select information of the database and it is afaik a sql standard (also mysql has it but as a seperate database).

pgAdmin may hide this schema (and the pg_catalog): http://www.mail-archive.com/pgadmin-support@postgresql.org/msg09332.html

@doctrinebot

Comment created by @beberlei:

Fixed and merged into 2.1.x

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