PostgreSQL: \Doctrine\DBAL\Connection::lastInsertId() throws an error if the value of the primary key is provided explicitly in the insert data

[savemetenminutes]

Bug Report

Q	A
Version	x.y.z
Previous Version if the bug is a regression	x.y.z

Summary

Originally discovered while creating a data migration on the PostgreSQL platform.

$this->connection->insert('table', ['id' => 1);
$lastInsertId = $connection->lastInsertId();

Current behavior

If the id value is provided as part of the data, it will not be generated by calling nextval on the sequence tied to the column if on a PostgreSQL
platform. This will make calling lastval (on which \Doctrine\DBAL\Connection::lastInsertId() relies) meaningless and an exception will occur:

  [Doctrine\DBAL\Exception\DriverException (7)]
  An exception occurred in the driver: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval is not yet defined in this session

previous:

  [Doctrine\DBAL\Driver\PDO\Exception (7)]
  SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval is not yet defined in this session

previous:

  [PDOException (55000)]
  SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval is not yet defined in this session

Expected behavior

I believe it is possible to extend the abstraction layer to detect whether the primary key has had an explicit value passed and memorize it for use instead of automatically proxying to the pdo_pgsql method for retrieving the last insert id. This will make the DBAL more platform-independent.

In this case the sequence will also need to be reset properly to the new value using:

                            SELECT
                                setval(
                                    pg_get_serial_sequence('$table', '$primaryKeyName'),
                                    (SELECT MAX($primaryKeyName) FROM $table)
                                )

How to reproduce

To be provided...