Skip to content

Loading…

DDC-569: Wrong working with sequences in multi-insertion of one entity #5075

Closed
doctrinebot opened this Issue · 21 comments

1 participant

@doctrinebot

Jira issue originally created by user drevolution:

In unit test I wrote couple of insertions of one entity, but there is only one SELECT NEXTVAL('tableidseq') query at the beggining. It seems that ID value for the second a other entities is somehow computed inside Doctrine. I think that it's wrong, because there could be another transactions with the same requests. And finally in the end of the script there is no refreshing of sequence value.

I don't know it works in way how I describe or it is my fault in configuration.

@doctrinebot

Comment created by romanb:

Sequences are explained here:

http://www.postgresql.org/docs/8.3/static/sql-createsequence.html

and here:

http://www.postgresql.org/docs/8.3/static/functions-sequence.html

Especially note the INCREMENT BY option which corresponds to the allocationSize in Doctrine( and that defaults to 10) and the documentation about the nextval function.

If you want 1 query per sequence value use allocationSize=1 in Doctrine or change the sequence in the database. Just make sure allocationSize in Doctrine is the same as the INCREMENT BY in the database.

If you still see a problem after carefully reading the above documents, please report back.

Thanks.

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot

Comment created by drevolution:

Even if I set both variables on the same value (1) the result is the same, test failed.

I don't know if I understand the behaviour of this aspect of Doctrine2, but let's take an example. I have two request created at the same time. One will get sequence value i.e. 11, the second will get sequence value 12. And after that each request will insert 1000 rows (entities). As I understand it, the second request will failed because it will store the same values of primary key only shifted by one.

And finally one stupid question. Why is allocationSize defaults to 10 if default INCREMENT BY value in common postgresql sequence is 1?

@doctrinebot

Comment created by romanb:

There is nothing special about Doctrine's behavior.

Assuming:

1) INCREMENT BY 10 MINVALUE 1 START WITH 1
2) 2 concurrent requests

One request gets: 1 (this request can use 1-10 before it needs the next nextval())
The other request gets: 11 (this request can use 11-20 before it needs the next nextval())

Where is the problem? If INCREMENT BY is X you basically get X numbers for 1 nextval(). I thought that is clear in the PostgreSQL documentation I linked to.

Whatever the INCREMENT BY is, even if its 1, "this is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value." (this is from the documentation I linked to...)

Why 10 by default? Because there are enough numbers (-9223372036854775808 to 9223372036854775807) and a default higher than 1 allows to take some load from the database in case of multiple inserts in the same request. If you want 1, you can configure it.

@doctrinebot

Comment created by romanb:

If you can show some code that produces duplicate numbers for Doctrine objects that would be great. We can't fix something without reproducing it :)

@doctrinebot

Comment created by drevolution:

Small example of wrong working with sequences in PostgreSQL.

@doctrinebot

Comment created by romanb:

Thanks for the example but it is not correct. SERIAL will create a sequence with INCREMENT BY 1. The default sequence in Doctrine will have allocationSize 10. So they dont match. Even more SERIAL != SEQUENCE for Doctrine. SERIAL is an equivalent to "auto-increment". On PostgreSQL with strategy="AUTO" Doctrine will choose SEQUENCE, which means not SERIAL.

And yes, it is the same DDC-390 because you're doing the same mistake.

You have to do one of the following:

A) Create the table through Doctrine (CLI / SchemaTool). See the documentation for details. Insert code like this in your example:

...
$em = EntityManager::create($connectionOptions, $config);

$schemaTool = new \Doctrine\ORM\Tools\SchemaTool($em);
try {
    $schemaTool->dropSchema(array($em->getClassMetadata('Model\Article')));
    $schemaTool->createSchema(array($em->getClassMetadata('Model\Article')));
} catch (Exception $ignored) {}

for ($i = 0; $i < 10; $i<ins></ins>) { // or 1000 or 100000 whatever
...

You will see that Doctrine does not use SERIAL.

or

B) Create a sequence manually that matches the Sequence settings in Doctrine, i.e.:

"CREATE SEQUENCE articleidseq START 1 INCREMENT BY 10;"

or

C) Use strategy="IDENTITY". This will make Doctrine use SERIAL

If you create your database sequences manually you have to make sure they have the same settings as the ones used by Doctrine.

If I am allowed to ask, have you never used sequences without SERIAL before?

@doctrinebot

Comment created by romanb:

Feels like we're talking past each other here :)

@doctrinebot

Comment created by drevolution:

As I try my small example with INCREMENT BY sets to 1 and allocationSize sets to 1 too it works fine. So in my unit test above I had probably bug somewhere on my side. Altough I think that default value sets to 10 in allocation size will be confusing for a lot of users. I think that default allocationSize should be 1.

Thaks for your time.

@doctrinebot

Comment created by romanb:

I think you just need to understand that SERIAL is an "AUTO_INCREMENT" replacement. It "emulates" auto-increment by creating a sequence with increment by 1 and a default value on the column that generates a new sequence value. This means the sequence value is generated on insert. This is not how you usually work with sequences. The advantage of sequences is that you can generate identifiers independently (and prior) of the insert.

strategy="AUTO" picks the "preferred strategy of the platform" and for PostgreSQL that is not SERIAL, it is "real", separate sequences.
strategy="IDENTITY" means an "identity column" like IDENTITY in MSSQL or AUTO_INCREMENT in MySQL. In PostgreSQL the equivalent is SERIAL.

So the mistake in your example was that the database was created using SERIAL but Doctrine was configured to work with a "real", separate sequence, which is the default on PostgreSQL with strategy="AUTO".

Maybe that helps.

@doctrinebot

Comment created by drevolution:

I know sequences and serial pseudotype in Postgres. My misunderstanding was that I didn't expect that Doctrine will required my sequence will be incremented by 10.

@doctrinebot

Comment created by drevolution:

"strategy="AUTO" picks the "preferred strategy of the platform" and for PostgreSQL that is not SERIAL, it is "real", separate sequences."

Could it be writen directly in the documentation? It should help a lot fo people using classic SERIAL pseudotype for working with tables and switching to Doctrine 2.

@doctrinebot

Comment created by romanb:

Yes, but that is because Doctrine does not use SERIAL anyway :) except if you force it to (strategy="IDENTITY")

When using SERIAL, of course the INCREMENT BY is 1. When you tell Doctrine to use a SERIAL (strategy="IDENTITY") it does that, too and does not use any other increment.

@doctrinebot

Comment created by romanb:

OK, I will add that to the documentation.

@doctrinebot

Comment created by romanb:

Yes, IDENTITY on PostgreSQL means SERIAL.

IDENTITY =

MSSQL: IDENTITY
MySQL: AUTO_INCREMENT
PostgreSQL: SERIAL
Oracle: not supported

That is the current state. On Oracle it could be emulated with triggers or so but we have not dont that yet.

More formally, the IDENTITY strategy means "An identity column whose (unique) value is automatically generated and assigned by the database on INSERT."

@doctrinebot

Comment created by jantichy:

Hi Roman, current default value for allocationSize=10 is CONFUSING and may (and surely WILL) cause problems and misunderstoods for many people in future.

The core of the problem is that we define table structure manually, separately from entity annotations. Many people do that. And while the default allocationSize is 10, I have to remember each time I write new table and new entity, that:

a) I have to define each the sequence in database manually with INCREMENT BY 10
b) OR I have to define in each entity manually the annotation for @SequenceGenerator - allocationSize=1
c) OR I have to manually use strategy="IDENTITY", but according to documentation it is NOT supported by PostgreSQL

Shortly, I have to remember and explicitly WRITE SOMETHING as to avoid problems caused by wrong default value and to get myself to common default (increment by 1) considered by common meaning

I think that default value (without writing any explicit definitions) should always correspond to common default usage and common presumptions. And only in case anybody needs something special (like "preloading" 10 ids at one time) he should WRITE SOMETHING, write any additional explicit definitions.

Thus, is there any possibility to change the default value of $definition['allocationSize'] in Doctrine/ORM/Mapping/ClassMetaFactory.php from 10 to 1, or at least make it configurable in doctrine config?

If so, you will avoid many problems and many confusions from many further Doctrine users.

@doctrinebot

Comment created by romanb:

I updated the documentation: http://www.doctrine-project.org/documentation/manual/2_0/en/basic-mapping#identifiers-/-primary-keys:identifier-generation-strategies

If you still think the allocationSize should default to 1, even for "real" sequences (not SERIAL), please open a new enhancement request.

Thanks for your suggestions.

@doctrinebot

Comment created by jantichy:

Roman, thank you.

The new request opened, see http://www.doctrine-project.org/jira/browse/[DDC-577](http://www.doctrine-project.org/jira/browse/DDC-577).

I think this issue can be closed.

@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.