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: travis-ci/travis-ci#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. #2893