Hibernate Reactive can't persist a many-to-many relation when one of the entities is a child (with the "joined" inheritance type) and the batch size is greater than 1

[MarciaBM]

Describe the bug

Consider an entity type A, which is a child of entity type B (with the inheritance type set to "joined"), and its ID is generated by a sequence generator (the generation type is "sequence"). Subsequently, numerous entities of type A are created, persisted, and flushed with a batch size greater than one. Following this, in the same transaction, many-to-many relations with entities of type A are established and persisted. However, during the persistence of these entities, an exception is thrown, indicating a foreign key violation with an entity of type A, despite having flushed the type A entities.

This exception is no longer triggered when the batch size is set to 1 or when the inheritance type is changed to any other type. However, this is not an ideal solution, as in certain scenarios, the "joined" inheritance type is essential, and a batch size greater than 1 is a crucial functionality.

Expected behavior

All the entities should've been correctly persisted in the database.

Actual behavior

{
  "details": "Error id d68b39de-fadc-4115-a196-54541c33a6ac-1, org.hibernate.exception.ConstraintViolationException: error executing SQL statement [ERROR: insert or update on table \"cars_clients\" violates foreign key constraint \"fkcwe0tbshviwufxbqkxpv3647u\" (23503)] [insert into cars_clients (location,car_id,client_id) values ($1,$2,$3)]",
  "stack": "org.hibernate.exception.ConstraintViolationException: error executing SQL statement [ERROR: insert or update on table \"cars_clients\" violates foreign key constraint \"fkcwe0tbshviwufxbqkxpv3647u\" (23503)] [insert into cars_clients (location,car_id,client_id) values ($1,$2,$3)]\r\n\tat org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:97)\r\n\tat org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)\r\n\tat org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)\r\n\tat org.hibernate.reactive.pool.impl.SqlClientConnection.convertException(SqlClientConnection.java:161)\r\n\tat org.hibernate.reactive.pool.impl.SqlClientConnection.lambda$preparedQueryBatch$9(SqlClientConnection.java:246)\r\n\tat java.base/java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:934)\r\n\tat java.base/java.util.concurrent.CompletableFuture$UniHandle.tryFire(CompletableFuture.java:911)\r\n\tat java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510)\r\n\tat java.base/java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:2194)\r\n\tat io.vertx.core.Future.lambda$toCompletionStage$3(Future.java:583)\r\n\tat io.vertx.core.impl.future.FutureImpl$4.onFailure(FutureImpl.java:188)\r\n\tat io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:81)\r\n\tat io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:265)\r\n\tat io.vertx.sqlclient.impl.QueryResultBuilder.tryFail(QueryResultBuilder.java:94)\r\n\tat io.vertx.core.Promise.fail(Promise.java:89)\r\n\tat io.vertx.core.Promise.handle(Promise.java:53)\r\n\tat io.vertx.core.Promise.handle(Promise.java:29)\r\n\tat io.vertx.core.impl.future.FutureImpl$4.onFailure(FutureImpl.java:188)\r\n\tat io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:81)\r\n\tat io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:265)\r\n\tat io.vertx.core.impl.future.PromiseImpl.onFailure(PromiseImpl.java:54)\r\n\tat io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:43)\r\n\tat io.vertx.sqlclient.impl.TransactionImpl.lambda$wrap$0(TransactionImpl.java:72)\r\n\tat io.vertx.core.impl.future.FutureImpl$4.onFailure(FutureImpl.java:188)\r\n\tat io.vertx.core.impl.future.FutureBase.lambda$emitFailure$1(FutureBase.java:75)\r\n\tat io.vertx.core.impl.ContextImpl.execute(ContextImpl.java:305)\r\n\tat io.vertx.core.impl.DuplicatedContext.execute(DuplicatedContext.java:181)\r\n\tat io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:72)\r\n\tat io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:265)\r\n\tat io.vertx.core.impl.future.PromiseImpl.onFailure(PromiseImpl.java:54)\r\n\tat io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:43)\r\n\tat io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:23)\r\n\tat io.vertx.sqlclient.impl.command.CommandResponse.fire(CommandResponse.java:46)\r\n\tat io.vertx.sqlclient.impl.SocketConnectionBase.handleMessage(SocketConnectionBase.java:324)\r\n\tat io.vertx.pgclient.impl.PgSocketConnection.handleMessage(PgSocketConnection.java:114)\r\n\tat io.vertx.sqlclient.impl.SocketConnectionBase.lambda$init$0(SocketConnectionBase.java:137)\r\n\tat io.vertx.core.impl.ContextImpl.emit(ContextImpl.java:335)\r\n\tat io.vertx.core.impl.ContextImpl.emit(ContextImpl.java:328)\r\n\tat io.vertx.core.net.impl.NetSocketImpl.handleMessage(NetSocketImpl.java:378)\r\n\tat io.vertx.core.net.impl.ConnectionBase.read(ConnectionBase.java:159)\r\n\tat io.vertx.core.net.impl.VertxHandler.channelRead(VertxHandler.java:153)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)\r\n\tat io.netty.channel.CombinedChannelDuplexHandler$DelegatingChannelHandlerContext.fireChannelRead(CombinedChannelDuplexHandler.java:436)\r\n\tat io.vertx.pgclient.impl.codec.PgDecoder.fireCommandResponse(PgDecoder.java:52)\r\n\tat io.vertx.pgclient.impl.codec.PgCommandCodec.handleReadyForQuery(PgCommandCodec.java:137)\r\n\tat io.vertx.pgclient.impl.codec.PgDecoder.decodeReadyForQuery(PgDecoder.java:248)\r\n\tat io.vertx.pgclient.impl.codec.PgDecoder.channelRead(PgDecoder.java:107)\r\n\tat io.netty.channel.CombinedChannelDuplexHandler.channelRead(CombinedChannelDuplexHandler.java:251)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:442)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)\r\n\tat io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)\r\n\tat io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)\r\n\tat io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)\r\n\tat io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)\r\n\tat io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:788)\r\n\tat io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:724)\r\n\tat io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:650)\r\n\tat io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:562)\r\n\tat io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)\r\n\tat io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)\r\n\tat io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)\r\n\tat java.base/java.lang.Thread.run(Thread.java:1583)\r\nCaused by: java.sql.SQLException: ERROR: insert or update on table \"cars_clients\" violates foreign key constraint \"fkcwe0tbshviwufxbqkxpv3647u\" (23503)\r\n\t... 63 more"
}

How to Reproduce?

To reproduce the bug you can access this small example that I've created: https://github.com/MarciaBM/Quarkus-Issue

1. Download the project;
2. Create a PostgreSQL database and set its credentials in the application.properties file;
3. Compile and run the project;
4. Execute the endpoint called hello, the only one that exists.

Output of uname -a or ver

Windows 11 23H2

Output of java -version

21.0.2

Quarkus version or git rev

3.7.2

Build tool (ie. output of mvnw --version or gradlew --version)

Maven version: 3.9.5

Additional information

I'm using Quarkus Reactive with Hibernate and Panache

[quarkus-bot]

/cc @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[yrodiere]

Thanks for reporting.

Can you reproduce this problem using the Hibernate ORM test case templates?
If you can, the problem/bug is probably in Hibernate and should be reported directly to that project.

[MarciaBM]

I'm not used to Hibernate ORM, but if I've done everything correctly, the bug didn't happen. Maybe it can be related to the fact that I'm using Hibernate Reactive...

Anyway, here is the test case https://github.com/MarciaBM/Hibernate-Test-Case

[yrodiere]

Maybe it can be related to the fact that I'm using Hibernate Reactive...

@MarciaBM That information is certainly relevant.

Hey @DavideD , could you please guide @MarciaBM to Hibernate Reactive test case templates? I couldn't find them in https://github.com/hibernate/hibernate-test-case-templates/ ...

[MarciaBM]

I left that information in the additional information filed, hibernate reactive with panache

[DavideD]

I've never added a template, but they can use one of the examples or adapt one of the test cases.