DDC-390: Doctrine\ORM\Id\SequenceGenerator broken for PostgreSql with bulk data #485

New Issue

admin · 2026-01-22T12:40:00+01:00

admin commented

2026-01-22 12:40:00 +01:00

Originally created by @doctrinebot on GitHub (Mar 5, 2010).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user mzach:

Doctrine\ORM\Id\SequenceGenerator does not work if you use Strategy="AUTO" or "SEQUENCE" with an allocationSize > 1

PostgreSQL uses sequence tables which do not automatically take the next number from the corresponding table but the next value from the sequence, so if you have an exception thrown, Doctrine would need to start at MAX(id) 1, whereas Postgre doesn't know about it and thereby Doctrine generates a duplicate entry.

Took us about half an hour to locate the Bug and find a Workaround - Solution 1 (NOT to be used);

    public function generate(EntityManager $em, $entity)
    {
        // disable if() so the NEXTVAL query is executed each time
        // if ($this->*maxValue === null || $this->_nextValue == $this->*maxValue) {
            // Allocate new values
            $conn = $em->getConnection();
            $sql = $conn->getDatabasePlatform()->getSequenceNextValSQL($this->_sequenceName);
            $this->_nextValue = $conn->fetchColumn($sql);
            $this->*maxValue = $this->_nextValue </ins> $this->*allocationSize;
        // }
        return $this->_nextValue<ins></ins>;
    }

Solution 2: set "allocationSize=1", but still this might cause failures!

So, to conclude, the problem definitely lies on Doctrine's side in this case - we ran into it doing bulk inserts of 5000 entries with relations (only testdata, but still a biggie for production).

Originally created by @doctrinebot on GitHub (Mar 5, 2010). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user mzach: Doctrine\ORM\Id\SequenceGenerator does not work if you use Strategy="AUTO" or "SEQUENCE" with an allocationSize > 1 PostgreSQL uses sequence tables which do not automatically take the next number from the corresponding table but the next value from the sequence, so if you have an exception thrown, Doctrine would need to start at MAX(id) <ins>1, whereas Postgre doesn't know about it and thereby Doctrine generates a duplicate entry. Took us about half an hour to locate the Bug and find a Workaround - Solution 1 (NOT to be used); ``` public function generate(EntityManager $em, $entity) { // disable if() so the NEXTVAL query is executed each time // if ($this->*maxValue === null || $this->_nextValue == $this->*maxValue) { // Allocate new values $conn = $em->getConnection(); $sql = $conn->getDatabasePlatform()->getSequenceNextValSQL($this->_sequenceName); $this->_nextValue = $conn->fetchColumn($sql); $this->*maxValue = $this->_nextValue </ins> $this->*allocationSize; // } return $this->_nextValue<ins></ins>; } ``` Solution 2: set "allocationSize=1", but still this might cause failures! So, to conclude, the problem definitely lies on Doctrine's side in this case - we ran into it doing bulk inserts of 5000 entries with relations (only testdata, but still a biggie for production).

admin added the Bug label 2026-01-22 12:40:00 +01:00

admin closed this issue

2026-01-22 12:40:00 +01:00

admin commented

2026-01-22 12:40:02 +01:00

@doctrinebot commented on GitHub (Mar 5, 2010):

depends on DDC-452: Add section about IdGenerators in Basic Mapping Chapter

@doctrinebot commented on GitHub (Mar 5, 2010): - depends on [DDC-452: Add section about IdGenerators in Basic Mapping Chapter](http://www.doctrine-project.org/jira/browse/DDC-452)

admin commented

2026-01-22 12:40:03 +01:00

@doctrinebot commented on GitHub (Mar 5, 2010):

Comment created by @beberlei:

can we see the code how you get to this "exception" which leads to this?

do you persist each entity each and call flush for each entity?! Do you re-use the entity manager when an exception is thrown from the unit of work flush()?

@doctrinebot commented on GitHub (Mar 5, 2010): Comment created by @beberlei: can we see the code how you get to this "exception" which leads to this? do you persist each entity each and call flush for each entity?! Do you re-use the entity manager when an exception is thrown from the unit of work flush()?

admin commented

2026-01-22 12:40:04 +01:00

@doctrinebot commented on GitHub (Mar 6, 2010):

Comment created by mzach:

Classes: http://pastie.org/private/czbqfighkwvcycvam88nww

PHPUnit TestCase: http://pastie.org/private/adcr7juft0zpwchc8hazg

NOTE: Namespaces have been stripped from this test!

Before the change of the @Id field we had it all in the @MappedSuperclass and used "AUTO" as strategy, but as soon as the inserts reached $maxValue and the SELECT NEXTVAL() call was executed Doctrine took the value from Postgre, which was lower than the actual entries in the table itself.

Example:

Table had Ids 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65 --> $nextValue should be 66, but last call from Doctrine to NEXTVAL() was at Id 55, so Postgre provided 56 at calling NEXTVAL() [also tested manually!], Doctrine added +1, so it tried to use Id 56, which of course already existed, thereby throwing a PDOException about a duplicate key.

It works in two scenarios:

only inserting 1 row at a time (so NEXTVAL() is called)
using the SEQUENCE strategy and setting allocationSize=1

Both scenarios work as long as no data error occurs, after that you might get the behaviour described above.

Solutions:

call NEXTVAL() every time in Postgre
use the existing behaviour, if NEXTVAL() should be called ($nextValue > $maxValue), SELECT MAX($identifier) and compare it to SELECT NEXTVAL(), if NEXTVAL() < MAX($identifier) modify the sequence using an ALTER SEQUENCE .. RESTART WITH ..

If you have other ideas or solutions I'd like to hear them

Hope this helps tracking down the issue

@doctrinebot commented on GitHub (Mar 6, 2010): Comment created by mzach: Classes: http://pastie.org/private/czbqfighkwvcycvam88nww PHPUnit TestCase: http://pastie.org/private/adcr7juft0zpwchc8hazg NOTE: Namespaces have been stripped from this test! Before the change of the @Id field we had it all in the @MappedSuperclass and used "AUTO" as strategy, but as soon as the inserts reached $maxValue and the SELECT NEXTVAL() call was executed Doctrine took the value from Postgre, which was lower than the actual entries in the table itself. Example: Table had Ids 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65 --> $nextValue should be 66, but last call from Doctrine to NEXTVAL() was at Id 55, so Postgre provided 56 at calling NEXTVAL() [also tested manually!], Doctrine added +1, so it tried to use Id 56, which of course already existed, thereby throwing a PDOException about a duplicate key. It works in two scenarios: - only inserting 1 row at a time (so NEXTVAL() is called) - using the SEQUENCE strategy and setting allocationSize=1 Both scenarios work as long as no data error occurs, after that you _might_ get the behaviour described above. Solutions: - call NEXTVAL() every time in Postgre - use the existing behaviour, if NEXTVAL() should be called ($nextValue > $maxValue), SELECT MAX($identifier) and compare it to SELECT NEXTVAL(), if NEXTVAL() < MAX($identifier) modify the sequence using an ALTER SEQUENCE .. RESTART WITH .. If you have other ideas or solutions I'd like to hear them Hope this helps tracking down the issue

admin commented

2026-01-22 12:40:05 +01:00

@doctrinebot commented on GitHub (Mar 20, 2010):

Comment created by @beberlei:

@Michael: Do you generate the Sequence using the SchemaTool? For me its generating the following SQL:

/****
 * @Entity
 */
class SequenceEntity
{
    /****
     * @Id
     * @column(type="integer")
     * @GeneratedValue(strategy="SEQUENCE")
     * @SequenceGenerator(allocationSize=5,sequenceName="person*id*seq")
     */
    public $id;
}

to SQL:

CREATE SEQUENCE person*id*seq INCREMENT BY 5 MINVALUE 1 START 1

Then the sequence is incremented by 5 values for each call to NEXTVALUE().

@doctrinebot commented on GitHub (Mar 20, 2010): Comment created by @beberlei: @Michael: Do you generate the Sequence using the SchemaTool? For me its generating the following SQL: ``` /**** * @Entity */ class SequenceEntity { /**** * @Id * @column(type="integer") * @GeneratedValue(strategy="SEQUENCE") * @SequenceGenerator(allocationSize=5,sequenceName="person*id*seq") */ public $id; } ``` to SQL: ``` CREATE SEQUENCE person*id*seq INCREMENT BY 5 MINVALUE 1 START 1 ``` Then the sequence is incremented by 5 values for each call to NEXTVALUE().

admin commented

2026-01-22 12:40:07 +01:00

@doctrinebot commented on GitHub (Mar 21, 2010):

Comment created by mzach:

Hi Benjamin,

it's an existing Database, SEQUENCEs increment by 1 per default, no SchemaTool was used so far.

@doctrinebot commented on GitHub (Mar 21, 2010): Comment created by mzach: Hi Benjamin, it's an existing Database, SEQUENCEs increment by 1 per default, no SchemaTool was used so far.

admin commented

2026-01-22 12:40:07 +01:00

@doctrinebot commented on GitHub (Mar 21, 2010):

Comment created by @beberlei:

This is the problem then, the allocationSize has to equal the increment.

@Roman: Should we decouple those two?

@doctrinebot commented on GitHub (Mar 21, 2010): Comment created by @beberlei: This is the problem then, the allocationSize has to equal the increment. @Roman: Should we decouple those two?

admin commented

2026-01-22 12:40:08 +01:00

@doctrinebot commented on GitHub (Mar 24, 2010):

Comment created by romanb:

@Benjamin: What do you mean by "decoupling" ? I dont see what Doctrine can do here (without costly database introspection). It is the responsibility of the developer to ensure that the allocationSize configured in the mapping is the same as the allocation size used by the sequence in the database ("INCREMENT BY"), if the database schema is not created through Doctrine. In the case where the sequence is generated through Doctrine, the allocation size is guaruanteed to match.

All I see we can do is to document this better in the context of the ID generators.

@doctrinebot commented on GitHub (Mar 24, 2010): Comment created by romanb: @Benjamin: What do you mean by "decoupling" ? I dont see what Doctrine can do here (without costly database introspection). It is the responsibility of the developer to ensure that the allocationSize configured in the mapping is the same as the allocation size used by the sequence in the database ("INCREMENT BY"), **if** the database schema is not created through Doctrine. In the case where the sequence is generated through Doctrine, the allocation size is guaruanteed to match. All I see we can do is to document this better in the context of the ID generators.

admin commented

2026-01-22 12:40:08 +01:00

@doctrinebot commented on GitHub (Mar 25, 2010):

Comment created by mzach:

I'd wish that it's at least pointed out somewhere, otherwise my colleagues in another department might change it in their domain models from 1 to 5 thinking they improve performance and start noticing strange behaviours. There are lots of legacy databases out there which might not want to switch their sequences or auto_increment values to match Doctrine's default.

Anyway, thank you for investigating.

@doctrinebot commented on GitHub (Mar 25, 2010): Comment created by mzach: I'd wish that it's at least pointed out somewhere, otherwise my colleagues in another department might change it in their domain models from 1 to 5 thinking they improve performance and start noticing strange behaviours. There are lots of legacy databases out there which might not want to switch their sequences or auto_increment values to match Doctrine's default. Anyway, thank you for investigating.

admin commented

2026-01-22 12:40:09 +01:00

@doctrinebot commented on GitHub (Mar 28, 2010):

Comment created by @beberlei:

Closed as not an issue, however took up the problem in DDC-452 and documented the Sequence Id Generation strategy in more detail also with a word of warning on the allocationSize value.

@doctrinebot commented on GitHub (Mar 28, 2010): Comment created by @beberlei: Closed as not an issue, however took up the problem in [DDC-452](http://www.doctrine-project.org/jira/browse/DDC-452) and documented the Sequence Id Generation strategy in more detail also with a word of warning on the allocationSize value.

admin commented

2026-01-22 12:40:09 +01:00

@doctrinebot commented on GitHub (Mar 28, 2010):

Issue was closed with resolution "Invalid"

@doctrinebot commented on GitHub (Mar 28, 2010): Issue was closed with resolution "Invalid"

admin referenced this issue

2026-01-22 15:59:08 +01:00

[PR #485] [CLOSED] Back ported changes of SimpleConditionalExpression #8265

admin referenced this issue

2026-01-22 15:59:09 +01:00

[PR #485] Back ported changes of SimpleConditionalExpression #8270

Sign in to join this conversation.