I have sets of tables that contain versioned data; one table acts as the parent
and the other contains the revisions of the parent. The parent record stores a
long PK and a string ID. The revisions table stores a long PK, the long PK of
the parent (with FK relationship), a replacement date (if null, this is the
current revision), and the versioned data fields. If I want to get the current
revision from a parent object I perform an ObjectSelect query to find the
revision with the null date field.
Today I realized that this is a costly way to retrieve the current revision, so
I added a current_revision_pid column to all the parent tables and a FK
relationship to the revision tables. At this point you may be thinking "that's
a circular FK constraint", but I mitigated this by making the
current_revision_pid column non-mandatory and resolving to only set that column
on new records AFTER the parent and new revision have been committed.
Since adding the new FK relationships I am seeing errors in code that was
working fine before the change to add the additional relationships. It appears
the table insertions are not occurring in the correct order.
Code:
TestScript ts = context.newObject(TestScript.class);
ts.setId("9.1 - general info tab");
ts.setIdForDisplay("9.1 - General Info Tab");
TestScriptHist tsh = context.newObject(TestScriptHist.class);
tsh.setDescription(Txt.getObject(context, "This script will test the function
of the General Info tab on the Master Instrument Record screen.", false));
tsh.setDisabled(false);
tsh.setMajorVersion(0);
tsh.setMinorVersion(0);
tsh.setParent(ts);
tsh.setRevisionCheckIn(checkIn);
<do other stuff>
context.commitChanges();
Is it possible that the new "circular" FK relationships are confusing Cayenne
into inserting records into the tables in the wrong order? If so, is there a
way to define the order of insertions aside from performing frequent commits on
data that should really all be part of a single transaction?
Here is some log output from the server side which shows hist (revision)
records being inserted before the parent records (I've clipped some of the log
and highlighted the important bits):
<snip>
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script_hist')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script_hist')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script_hist')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script_hist')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script')
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - SELECT
nextval('vidval.pk_test_script')
<snip>
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - [batch
bind: 1->row_number:0, 2->test_project_hist_pid:200,
3->test_project_phase_pid:200]
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - ===
updated 2 rows.
I would expect to see the insertions for test_script here.
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - INSERT
INTO vidval.test_script_hist (approval_pid, check_in_pid, description_txt_pid,
disabled, major_version, minor_version, pid, prereq_txt_pid, replaced_ts,
test_script_pid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - [batch
bind: 1->approval_pid:NULL, 2->check_in_pid:201, 3->description_txt_pid:244,
4->disabled:'false', 5->major_version:0, 6->minor_version:0, 7->pid:200,
8->prereq_txt_pid:NULL, 9->replaced_ts:NULL, 10->test_script_pid:202]
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - [batch
bind: 1->approval_pid:NULL, 2->check_in_pid:201, 3->description_txt_pid:270,
4->disabled:'false', 5->major_version:0, 6->minor_version:0, 7->pid:201,
8->prereq_txt_pid:NULL, 9->replaced_ts:NULL, 10->test_script_pid:203]
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - [batch
bind: 1->approval_pid:NULL, 2->check_in_pid:201, 3->description_txt_pid:298,
4->disabled:'false', 5->major_version:0, 6->minor_version:0, 7->pid:202,
8->prereq_txt_pid:NULL, 9->replaced_ts:NULL, 10->test_script_pid:201]
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - [batch
bind: 1->approval_pid:NULL, 2->check_in_pid:201, 3->description_txt_pid:255,
4->disabled:'false', 5->major_version:0, 6->minor_version:0, 7->pid:203,
8->prereq_txt_pid:NULL, 9->replaced_ts:NULL, 10->test_script_pid:200]
[qtp1755855970-14] INFO org.apache.cayenne.log.CommonsJdbcEventLogger - ***
error.
org.postgresql.util.PSQLException: ERROR: insert or update on table
"test_script_hist" violates foreign key constraint
"test_script_hist_test_script_pid_fkey"
Detail: Key (test_script_pid)=(202) is not present in table "test_script".
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
at org.apache.cayenne.access.jdbc.BatchAction.runAsBatch(BatchAction.java:125)
at org.apache.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:90)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:302)
at
org.apache.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:234)
at
org.apache.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:155)
at org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:643)
at org.apache.cayenne.access.DataDomain$2.perform(DataDomain.java:608)
at org.apache.cayenne.access.DataDomain$2.perform(DataDomain.java:605)
at
org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
at org.apache.cayenne.access.DataDomain.onSyncNoFilters(DataDomain.java:605)
at
org.apache.cayenne.access.DataDomain$DataDomainSyncFilterChain.onSync(DataDomain.java:760)
at org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:586)
at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:744)
at org.apache.cayenne.access.DataContext.onContextFlush(DataContext.java:708)
at org.apache.cayenne.BaseContext.onSync(BaseContext.java:487)
at
org.apache.cayenne.access.ClientServerChannel.onSync(ClientServerChannel.java:78)
at
org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:43)
at
org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:134)
at sun.reflect.GeneratedMethodAccessor28.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:396)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
at
org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at
org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at
org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at
org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at
org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
at
org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
at
org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
at
org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
at
org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
at
org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
at
org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at
org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
at
org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.Server.handle(Server.java:499)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Thread.java:745)
[qtp1755855970-14] INFO
org.apache.cayenne.remote.hessian.service.HessianService - Exception processing
message org.apache.cayenne.remote.SyncMessage of type flush-cascade-sync
org.apache.cayenne.CayenneRuntimeException: [v.4.0.M2 Feb 26 2015 08:16:32]
Commit Exception
at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:776)
at org.apache.cayenne.access.DataContext.onContextFlush(DataContext.java:708)
at org.apache.cayenne.BaseContext.onSync(BaseContext.java:487)
at
org.apache.cayenne.access.ClientServerChannel.onSync(ClientServerChannel.java:78)
at
org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:43)
at
org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:134)
at sun.reflect.GeneratedMethodAccessor28.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:396)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
at
org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at
org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at
org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at
org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at
org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
at
org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
at
org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
at
org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
at
org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
at
org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
at
org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at
org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
at
org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.Server.handle(Server.java:499)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table
"test_script_hist" violates foreign key constraint
"test_script_hist_test_script_pid_fkey"
Detail: Key (test_script_pid)=(202) is not present in table "test_script".
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
at org.apache.cayenne.access.jdbc.BatchAction.runAsBatch(BatchAction.java:125)
at org.apache.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:90)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:97)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:302)
at
org.apache.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:234)
at
org.apache.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:155)
at org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:643)
at org.apache.cayenne.access.DataDomain$2.perform(DataDomain.java:608)
at org.apache.cayenne.access.DataDomain$2.perform(DataDomain.java:605)
at
org.apache.cayenne.tx.DefaultTransactionManager.performInTransaction(DefaultTransactionManager.java:53)
at org.apache.cayenne.access.DataDomain.onSyncNoFilters(DataDomain.java:605)
at
org.apache.cayenne.access.DataDomain$DataDomainSyncFilterChain.onSync(DataDomain.java:760)
at org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:586)
at org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:744)
... 45 more
Apr 18, 2016 1:28:19 AM com.caucho.hessian.server.HessianSkeleton invoke
WARNING: org.apache.cayenne.CayenneRuntimeException: [v.4.0.M2 Feb 26 2015
08:16:32] Exception processing message org.apache.cayenne.remote.SyncMessage of
type flush-cascade-sync
org.apache.cayenne.CayenneRuntimeException: [v.4.0.M2 Feb 26 2015 08:16:32]
Exception processing message org.apache.cayenne.remote.SyncMessage of type
flush-cascade-sync
at
org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:154)
at sun.reflect.GeneratedMethodAccessor28.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:396)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
at
org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at
org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at
org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at
org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
at
org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
at
org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
at
org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
at
org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
at
org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
at
org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
at
org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at
org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at
org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)
at
org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.Server.handle(Server.java:499)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.Exception: ERROR: insert or update on table
"test_script_hist" violates foreign key constraint
"test_script_hist_test_script_pid_fkey"
Detail: Key (test_script_pid)=(202) is not present in table "test_script".
at
org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:150)
... 40 more
