Panache Reactive Batch Insert

[explorer4ever]

Hello community members, I am implementing an app in Quarkus using panache reactive, and have been running into an issue, that I have exhausted all my knowledge to determine what might be going wrong. I am not able to share any code to give the details, but I will try to explain the situation, and see if anyone can offer any hint on what might be going wrong to help me further troubleshoot this.

I have three tables A, B, C. A is the parent table for B, and B is the parent for C. When an API call is made, I select entries from A (about 90 of them), and create in A a tenant specific instance of these entries using a batch insert, and chain an insertion into table B (about 700 entries) by selecting all child records of the previously selected entries from A, and create tenant specific entries in B using a batch insert, and finally for each entry in B, create all child C records using another batch insert.

I know the limitation of batch insert in reactive, that the batch insert does not return the newly created IDs, so I run a select from each of the tables to get the IDs for the newly persisted entries so that those IDs are available for insertion into foreign key columns in child tables.

What I am running into is, it appears that insertions into table A are successful, but the insertions in to table B starts to fail (as a result I never get to table C) with the following error. Any help is greatly appreciated.

ERROR [io.qua.mut.run.MutinyInfrastructure] (vert.x-eventloop-thread-3) Mutiny had to drop the following exception: io.vertx.pgclient.PgException: ERROR: insert or update on table "B" violates foreign key constraint "B_x_A_fkey" (23503)
Before this specific error, there are a whole bunch of errors like the below. If I correlate both errors it appears that the parent ID seems to be not getting generated, and "replaceDelayedEntityIdentityInsertKeys" is running into some Null Pointer Error and hance the above error.

ERROR [org.jbo.res.rea.com.cor.AbstractResteasyReactiveContext] (vert.x-eventloop-thread-3) Request failed: java.lang.NullPointerException
	at org.hibernate.engine.internal.StatefulPersistenceContext.replaceDelayedEntityIdentityInsertKeys(StatefulPersistenceContext.java:1546)
	at org.hibernate.action.internal.EntityIdentityInsertAction.postInsert(EntityIdentityInsertAction.java:146)
	at org.hibernate.reactive.engine.impl.ReactiveEntityIdentityInsertAction.lambda$reactiveExecute$3(ReactiveEntityIdentityInsertAction.java:79)

[quarkus-bot[bot]]

/cc @FroMage, @loicmathieu

[gsmet]

Also /cc @DavideD

[explorer4ever]

I think I found a lead, and here is some pseudo code to explain exactly where the issue is getting introduced. I can't reason it as to what might be happening though

forCollectionofAs
  persist(collectionOfTenantAs)
  .chain(done->getCollectionOfTenantAs()) //For getting newly inserted Ids
  .chain(insertedAsWithIds->forCollectionofAs()
    getCollectionOfBs())
    .chain(collectionOfBs->insertCollectionOfTenantBs(insertedAsWithIds, collectionOfBs))
    .chain(done->getCollectionOfTenantBs()) //For getting newly inserted Ids

It is when I try to getCollectionOfTenantBs for getting the newly inserted Bs with Ids, I get the error
ERROR [io.qua.mut.run.MutinyInfrastructure] (vert.x-eventloop-thread-3) Mutiny had to drop the following exception: io.vertx.pgclient.PgException: ERROR: insert or update on table "B" violates foreign key constraint "B_x_A_fkey" (23503)

If I don't get the collection of tenant Bs inserted, As and Bs get inserted without any error. But I need to get the collection of tenant Bs with Ids, to map the subsequent entities C for the tenant.

This is remotely a good explanation of the situation, but wanted to share some more detail, if anyone knows what might be happening.

[DavideD]

Any chance you can create a small project that we can run? I can't see anything wrong in what you are showing us.

[DavideD]

I prefer not commit the transactions in between as that might leave the DB in an inconsistent state.

Makes sense. When I wrote the comment I was thinking that maybe you were doing some kind of multenancy configuration. But it seems everything is on one database/schema.

I will try to create a sample project as soon as I get a chance. At this point I spent couple of days on this without any luck.

Thanks a lot

[explorer4ever]

Hi @DavideD

I was finally able to create a sample project with the scenario, and the repository is available here.

Example Panache Reactive Bulk Insert

The Application Details section has some details on the tables and API endpoint for testing that is used to trigger the DB read and insert process. This example does use a Postgres database as it is one of the supported reactive databases.

Any insight is greatly appreciated.

[explorer4ever]

While totally unrelated, the following accepted solution for a question on SO gave me some direction to solve the problem. The issue with the example project is gone once I started to use the Sequences instead of Generated Ids. It appears the issue relates to how Hibernate handles GeneratedIds and Batch inserts. I will now apply this solution to my actual project to see if it resolves the issue.

[DavideD]

Nice. I didn't have the time to look into this.
But if you keep the example on-line, I will get back to it eventually

[explorer4ever]

Thanks @DavideD. I will leave the example on. The approach of using Sequences instead of Generated Identity got me over this hump even in my original project.