Hi Dave Thanks for your message. I will comment inline
On Thu, Apr 25, 2019 at 8:36 PM David Hoffer <[email protected]> wrote: > We are using JOOQ 3.10.8 using the auto generated DAOs via > jooq-codegen-maven > > We are using PostgreSql with postgresql-42.2.2.jar with > SQLDialect.POSTGRES_9_4 > > In our JOOQ Settings we have ReturnRecordToPojo=false > > We have a situation were occasionally we get a > org.jooq.exception.DataAccessException when we do a batch insert of 100 > records where each record has a FK to another record that was previously > just inserted as well. Our code is like this: > > elsetDao.insert(elsets); // First 100 records created. > > episodeDao.update(episode); // Single record update. > > episodePointDao.insert(points); // Second 100 records created. Each of > these points to one of the elset records inserted previously. > > We are assuming that each of these lines are atomic and synchronous (e.g. > fully committed to the DB before each method returns. However what is > happening is that occasionally the 3rd line will fail with > DataAccessException saying that the associated elset record does not exist > in the DB. > What makes you think so? jOOQ makes no such guarantees, it simply executes SQL on a JDBC driver. Are you running these statements in individual transactions? > Here is an example stack trace. > > INFO processReachableVolumeResult(): number of elsets built from cloud > results: 100 > INFO processReachableVolumeResult(): number of elsets after equals > comparator eval: 100 > INFO Episode: uuid=11BA946DCA9340E3A0B2269FE6D11E07; name=SR_17APR_0910 > INFO updateEpisodeWithCloudInfo(): Persisted elsets, duration=191(ms), > count=100 for episode uuid: 11BA946DCA9340E3A0B2269FE6D11E07 > INFO updateEpisodeWithCloudInfo(): Updated episode for uuid: > 11BA946DCA9340E3A0B2269FE6D11E07 > ERROR org.jooq.exception.DataAccessException: SQL [insert into > "odin_owner"."episode_point" ("episode_point_uuid", "episode_uuid", > "vector_uuid", "elset_uuid", "priority") values (?, ?, ?, ?, ?)]; Batch > entry 93 insert into "odin_owner"."episode_point" ("episode_point_uuid", > "episode_uuid", "vector_uuid", "elset_uuid", "priority") values > ('283C198BD074443DAAE9CFAC92F4752E', '11BA946DCA9340E3A0B2269FE6D11E07', > NULL, '283C198BD074443DAAE9CFAC92F4752E', 2.0) was aborted: ERROR: insert > or update on table "episode_point" violates foreign key constraint > "episode_point_elset_uuid_fkey" > Detail: Key (elset_uuid)=(283C198BD074443DAAE9CFAC92F4752E) is not present > in table "elset". Call getNextException to see other errors in the batch. > > When we inspect the DB after this occurs it does in fact contain the elset > record that the above stacktrace says is missing. > But since you're assuming that there are transactions, which are atomic, are you *sure* that first transaction is committed when the third transaction starts? Otherwise, it might not see your data. > What is going on here? Is it not true that JOOQ always persists all > records to the DB before returning from each DAO method? The above code > works 99% of the time but its the 1% we are trying to resolve. > At first sight, I don't see anything that jOOQ could do wrong here. The listed methods are simply "convenience" on top of what you'd do manually with JDBC. I suspect there's something wrong in the way you handle your transactions. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
