PostgreSQL and "auto_increment" problems

I have found problem with nimut/testing-framework in combination with PostgreSQL.

MySQL and PostgreSQL differs on how the auto_increment is working, but the doctrine/dbal handles this for us.

If I have a fixture with following content simplified (pages).

uid	title
1	home
2	contact
3	about us

CREATE TABLE pages (
  uid int(11) unsigned NOT NULL auto_increment,
  title varchar(50) DEFAULT '' NOT NULL,

  PRIMARY KEY (qid),
) ENGINE=InnoDB;

When I then have a test, that insert data into this. Let's say we have a function adding pages, but only adding titles as uid is auto_increment.

$this->addPage('New page title') will then throw an exception.

Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "pages_pkey"
DETAIL:  Key (uid)=(1) already exists.

This will happen the first 3 times I run the tests, the 4th time, it will add it as the uid 4 isn't set yet.

When looking at the PostgreSQL index, after the fixtures is include.

SELECT nextval('pages_uid_seq');

It will return 1, as no records are inserted yet from PostgreSQL point of view.

I don't know how this should be fixed, but if I run the test with the typo3/testing-framework instead, it's working.

One difference as I see it is that the typo3/testing-framework is using the

$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($tableName);
$connection->insert($tableName, $insertArray, $types);

whereas the nimut/testing-framework is using

protected function getDatabaseConnection()
{
    if (null === $this->database) {
        $this->database = new Database();
    }

    return $this->database;
}

Constructor of Database is

public function __construct(Connection $connection = null)
{
    $this->connection = $connection ?? GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\ConnectionPool')
            ->getConnectionByName(ConnectionPool::DEFAULT_CONNECTION_NAME);
}


$database = $this->getDatabaseConnection();
$database->insertArray($tableName, $insertArray);

So if the diff is "only" the build up of the connection ->getConnectionForTable($tableName) and ->getConnectionByName(ConnectionPool::DEFAULT_CONNECTION_NAME) I don't know.

Hope that it somehow makes sense what I'm writting. I'll be happy to try to help fix and test this.

Nimut / testing-framework

PostgreSQL and "auto_increment" problems #150