Dbal: Doctrine\DBAL\Exception\InvalidFieldNameException when working with DB schema on PostgreSQL 10

[skobkin]

Since upgrading to PostgreSQL 10 I have an error in my projects using Doctrine when I trying to generate migrations or just check the schema difference:

$ sf doc:sche:up --dump-sql                                                                                          
  [Doctrine\DBAL\Exception\InvalidFieldNameException]                                                        
  An exception occurred while executing 'SELECT min_value, increment_by FROM "admin"."acl_classes_id_seq"':  
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist                             
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...                                    
                 ^
  [Doctrine\DBAL\Driver\PDOException]                                             
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...         
                 ^
  [PDOException]                                                                  
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...         
                 ^
doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]]

Seems like it's related to nextcloud/server#5930 where @justin-sleep wrote:

This happens specifically because of changes to how PostgreSQL 10 handles sequence metadata.

[wzator]

Suggested solution:
PostgreSqlSchemaManager.php line 292:

    $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

to :

    $version = floatval($this->_conn->getWrappedConnection()->getServerVersion());

    if ($version >= 10) {
       $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM pg_sequences WHERE schemaname = \'public\' AND sequencename = '.$this->_conn->quote($sequenceName));
    }
    else
    {
        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
    }

[justin-sleep]

PostgreSQL 10 is out of beta now: https://www.postgresql.org/about/news/1786/

[Majkl578]

First we will need Travis CI to support it: https://github.com/travis-ci/travis-ci/issues/8537

[iquad]

when it is going to be migrated? it definently solves the problem, and because of new sequence architecture on postgresql 10 we can't define a generic method for that.

[synthead]

Just tried @wzator's changes and it works fine here 👍

[exebece]

have this problem in doctrine2 using in symfony 3.3 version. no matter which ,,auto-identity'' aproach I chose, I got this error too. change suggested by wzator few lines above this solved the problem temporarily. thx for that.

[simPod]

We are waiting for PSQL10 support in Travis I guess. Meanwhile you can use code from my PR. https://github.com/doctrine/dbal/pull/2893

[exebece]

thank you, i'll analyze your commit and use it.

[Zyqsempai]

Guys, what the status of the issue?? When we can expect fix?

[simPod]

Read the comments please

https://github.com/doctrine/dbal/issues/2868#issuecomment-334538085
https://github.com/doctrine/dbal/issues/2868#issuecomment-346591512

[Majkl578]

2893 has been merged and added Postgres 10 support, closing this one as duplicate.

[mca-digital]

@wzator´s suggestion worked perfectly!!!
Thanks!

[Andrioshe]

@wzator's suggestion worked like a charm. You can find this file in

./3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Schema/PostgreSqlSchemaManager.php

[TiFaBl]

Hey,
i am not really sure about this, as i never before actually touched any doctrine implementation, but after the above fix worked a couple of weeks ago i set up a new project and apparently some changes were made from 2.6 to 2.7.

The erroneous $data assignment is now in line 311 and fetchAssoc() is used instead of fetchAll().

For me, the following adjustment worked:
Replace in PostgreSqlSchemaManager.php line 311:

$data      = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

with:

$version = floatval($this->_conn->getWrappedConnection()->getServerVersion());

if ($version >= 10) {
    $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM pg_sequences WHERE schemaname = \'public\' AND sequencename = '.$this->_conn->quote($sequenceName));
}
else
{
    $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
}
$sequence += $data;

[srigi]

This issue should be re-opened. Fetching data for sequence definition is not working with Postgres 10.3. Fix in #2893 in not enough.

Code from @TiFaBl actually fixes the problem. Whats the fastest way for fix to be released? Should I start PR with tests/fixed-code?

[Ocramius]

Whats the fastest way for fix to be released? Should I start PR with tests/fixed-code?

First a failing test case

[PapsOu]

I confirm that the problem still occurs with PostgreSQL 10.5 and doctrine/dbal 2.8.0.

[Majkl578]

DBAL is working as expected with PostgreSQL 10+, this is very likely a configuration issue in your code where you are not specifying serverVersion correctly.

The code path in question is covered by tests and tested against all PostgreSQL 9.x, PostgeSQL 10 and PostgreSQL 11 (this condition is not entered with PostgreSQL 10+ since these metadata are provided by the platform ahead).

Anyway, if you still feel this is a Doctrine issue rather than a configuration issue, please open new issue with failing test.