cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Adam Boyle <abo...@valsphere.com>
Subject ROP - Define order of inserts on large commit to multiple tables with relationships?
Date Tue, 19 Apr 2016 02:50:45 GMT
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



Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message