Fails to insert to a PostgreSQL master-slave config due to SequenceGenerator(NEXTVAL) issues

[mkurzeja]

Background: I have a master-slave connection and I'm using auto-generated id for my entity;

Doctrine ORM fails to insert data because it tries to get the sequence NEXTVAL value from a slave.

public function generate(EntityManager $em, $entity)
    {
        if ($this->_maxValue === null || $this->_nextValue == $this->_maxValue) {
            // Allocate new values
            $conn = $em->getConnection();
            $sql  = $conn->getDatabasePlatform()->getSequenceNextValSQL($this->_sequenceName);

            $this->_nextValue = (int)$conn->fetchColumn($sql); //this line fails
            $this->_maxValue  = $this->_nextValue + $this->_allocationSize;
        }

        return $this->_nextValue++;
    }

PostgreSQL allows to fetch nextval only from a master server, and using fetchColumn runs the query using a slave connection.

I could start a transaction before, but I assume this case should be handled by ORM.

In case someone tries to reproduce this issue, just start a PostgreSQL cluster (docker-compose and https://hub.docker.com/r/bitnami/postgresql/ might be helpful) and try to persist any entity with an auto-generated ID.

[Ocramius]

Handled in #6168