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

[doctrinebot]

Jira issue originally created by user phopfgartner:

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

[doctrinebot]

Comment created by @beberlei:

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.

[doctrinebot]

Comment created by phopfgartner:

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

Sorry the noise,

Peter

[doctrinebot]

Comment created by @beberlei:

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

[doctrinebot]

Issue was closed with resolution "Invalid"

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.