Implement a ReturningIdGenerator that leverage INSERT INTO .... RETURNING id #7243

New Issue

admin · 2026-01-22T15:47:46+01:00

admin commented

2026-01-22 15:47:46 +01:00

Originally created by @allan-simon on GitHub (Nov 4, 2023).

Feature Request

Q	A
New Feature	yes
RFC	yes/no
BC Break	no

Summary

Add in AbstractIdGenerator a method

isInsertReturningGenerator()

which will return false by default

Note: a "cleaner" way would have been to replace isPostInsertGenerator by a "getGeneratorKind" that return an enum pre/during/post but that would break BC

create a ReturningIdGenerator (I'm bad at naming) that will have function isInsertReturningGenerator { return true;}

so that after in BasicEntityPersister can be modified to have getInsertSQL to add a RETURNING %idColumn

and then in executeInserts we can do

$generatedId $stmt->executeQuery()->fetchOne;

that will divide by two the number of request required when doing inserts (as you will not need the pre or post insert request to get the id )

especially as this keyword is supported by several vendors (potgresql , mariadb , oracle among others ) and it will be opt-in for the user.

If that seems like a good idea I may work on a PR

Originally created by @allan-simon on GitHub (Nov 4, 2023). ### Feature Request  | Q | A |------------ | ------ | New Feature | yes | RFC | yes/no | BC Break | no #### Summary Add in `AbstractIdGenerator` a method ``` isInsertReturningGenerator() ``` which will return false by default Note: a "cleaner" way would have been to replace isPostInsertGenerator by a "getGeneratorKind" that return an enum pre/during/post but that would break BC create a `ReturningIdGenerator` (I'm bad at naming) that will have `function isInsertReturningGenerator { return true;}` so that after in `BasicEntityPersister` can be modified to have `getInsertSQL` to add a `RETURNING %idColumn` and then in `executeInserts` we can do ``` $generatedId $stmt->executeQuery()->fetchOne; ``` that will divide by two the number of request required when doing inserts (as you will not need the pre or post insert request to get the id ) especially as this keyword is supported by several vendors (potgresql , mariadb , oracle among others ) and it will be opt-in for the user. If that seems like a good idea I may work on a PR

admin commented

2026-01-22 15:47:47 +01:00

@awwar commented on GitHub (Nov 10, 2023):

I apologize for being off-topic, but this is a really cool idea and could be developed further.

During our work, we often encounter concurrency problems. I know that there are millions of ways to solve a problem when we try to:

INSERT INTO users (first_name, email, age) VALUES ('Antony', 'antony@gmail.com', 25); -- email is unique

... but 9 more processes are trying to insert poor Antony into the database. 1 will succeed, but 9 will throw an exception and their EntityManager will be closed. In such situations, me often enter the “OH MY LORD WHY IS IT SO HARD TO INSERT THIS ENTRY?” mode and rewrite everything into pure sql.

I use this query:

INSERT INTO users (first_name, email, age) VALUES ('Antony', 'antony@gmail.com', 25)
ON CONFLICT (email) 
    DO UPDATE SET id = users.id -- hack for RETURNING something in any case
RETURNING id, first_name, email, age

As a result, I will either insert a record or get an existing one. (Then I do a few dirty tricks so that the original entity changes the data to the ones in the database, but does not trigger the update on the next ->flush())

It would be very cool if there was some kind of #[IdempotentEntity(conflict: 'index, index2')] attribute that, during ->flush(), would turn all insert requests for this entity into the query I indicated above. Of course this whole thing is optional.

@awwar commented on GitHub (Nov 10, 2023): I apologize for being off-topic, but this is a really cool idea and could be developed further. During our work, we often encounter concurrency problems. I know that there are millions of ways to solve a problem when we try to: ```sql INSERT INTO users (first_name, email, age) VALUES ('Antony', 'antony@gmail.com', 25); -- email is unique ``` ... but 9 more processes are trying to insert poor Antony into the database. `1` will succeed, but `9` will throw an exception and their `EntityManager` will be closed. In such situations, me often enter the “OH MY LORD WHY IS IT SO HARD TO INSERT THIS ENTRY?” mode and rewrite everything into pure sql. I use this query: ```sql INSERT INTO users (first_name, email, age) VALUES ('Antony', 'antony@gmail.com', 25) ON CONFLICT (email) DO UPDATE SET id = users.id -- hack for RETURNING something in any case RETURNING id, first_name, email, age ``` As a result, I will either insert a record or get an existing one. (_Then I do a few dirty tricks so that the original entity changes the data to the ones in the database, but does not trigger the update on the next `->flush()`_) It would be very cool if there was some kind of `#[IdempotentEntity(conflict: 'index, index2')]` attribute that, during `->flush()`, would turn all insert requests for this entity into the query I indicated above. Of course this whole thing is optional.

admin commented

2026-01-22 15:47:47 +01:00

@FluffyDiscord commented on GitHub (Dec 29, 2023):

+1

@FluffyDiscord commented on GitHub (Dec 29, 2023): +1

admin commented

2026-01-22 15:47:47 +01:00

@DAdq26 commented on GitHub (Jun 28, 2024):

+1

@DAdq26 commented on GitHub (Jun 28, 2024): +1

admin commented

2026-01-22 15:47:47 +01:00

@DAdq26 commented on GitHub (Jun 28, 2024):

Mariadb example with timestamp is a really good one

https://mariadb.com/kb/en/insertreturning/

CREATE OR REPLACE TABLE t2 (id INT, animal VARCHAR(20), t TIMESTAMP);

INSERT INTO t2 (id) VALUES (2),(3) RETURNING id,t;
+------+---------------------+
| id | t |
+------+---------------------+
| 2 | 2021-04-28 00:59:32 |
| 3 | 2021-04-28 00:59:32 |
+------+---------------------+

@DAdq26 commented on GitHub (Jun 28, 2024): Mariadb example with timestamp is a really good one https://mariadb.com/kb/en/insertreturning/ CREATE OR REPLACE TABLE t2 (id INT, animal VARCHAR(20), t TIMESTAMP); INSERT INTO t2 (id) VALUES (2),(3) RETURNING id,t; +------+---------------------+ | id | t | +------+---------------------+ | 2 | 2021-04-28 00:59:32 | | 3 | 2021-04-28 00:59:32 | +------+---------------------+

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#7243

Implement a ReturningIdGenerator that leverage INSERT INTO .... RETURNING id #7243

Feature Request

Summary

Implement a ReturningIdGenerator that leverage `INSERT INTO .... RETURNING id` #7243