[
https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12502601
]
Reece Garrett commented on OPENJPA-235:
---------------------------------------
> Would it be sufficient to just always run the deletes first, then the
> updates, and finally the inserts, when there are unique constraints involved?
> Assuming that we optimize away any SQL operations for
> insert-then-delete-before-flush, it would seem that that algorithm would be
> guaranteed to > work when no constraint violations were generated in the
> business code, which I think is all that we can really strive for in the case
> of unique
> constraints.
That won't work because updates can have inner dependencies due to uniqueness
constraints. For instance, you have a table with a unique column and two rows.
The first row has value '1' in the unique column and the second row has value
'2' in the unique column. You want to update the unique column in the first row
to value '2' and the unique column in the second row to value '3'. The second
row must be updated first but the only way to know that is to have access to
the original values of both rows. I see no way to properly deal with unique
keys without snapshots of the original rows.
Like I said, OpenJPA already pulls the origin values and if I could access them
with a method like RowImpl.getOriginal(Column col) then I could modify my patch
to deal with both unique and foreign key constraints in no time flat. The
original values could be put in an array on the row the same way set values for
updates are put in the _vals array.
Also, although blindly putting deletes before inserts and updates works for
uniqueness constraints, it does not work for foreign key constraints because it
is possible for either an update or another delete to depend on that delete.
Putting the delete first only works if all inbound foreign key dependencies are
nulled first. But, there are two problems with that.
1) The patch you committed does not know about dependencies between updates and
the deletes they have caused by breaking relationships to other objects. You
need to know that if you're planning on arbitrarily ordering deletes first for
uniqueness constraints, otherwise, you won't know that an update needs to be
generated to null a foreign key which relies on the row your about to delete.
That's why I store dereferenced states in my patch. By the time your code
executes that information is lost in space.
2) By ordering a delete first for tables with uniqueness constraints without
checking to see if an insert or update actually depends on it (only way is to
check the original values) you could be generating unnecessary updates to null
foreign keys.
In a nutshell, if we want to do unique and foreign key constraints properly we
need original values on both updates and deletes and be able to access those
values using the columns they map to, just like is done for
RowImpl.getSet(Column col). And we need to be able to calculate dependencies
between deletes and updates in cases where the delete was caused by the update.
-Reece
> SQL reordering to avoid non-nullable foreign key constraint violations
> ----------------------------------------------------------------------
>
> Key: OPENJPA-235
> URL: https://issues.apache.org/jira/browse/OPENJPA-235
> Project: OpenJPA
> Issue Type: Improvement
> Components: kernel
> Reporter: Reece Garrett
> Assignee: Patrick Linskey
> Fix For: 0.9.8
>
> Attachments: merge-detached.patch,
> merge-multigen-collection-testcase.zip, openjpa-235-test.jar,
> openjpa-235-test1.jar, openjpa-235-test2.zip, sqlreorder.patch,
> sqlReorder2.patch, sqlReorderTests.patch
>
>
> OpenJPA does not do any SQL statement re-ordering in order to resolve foreign
> key constraints. Instead, objects are always inserted in the order in which
> the user persists the instances. When you persist in an order that would
> violate foreign key constraints, OpenJPA attempts to insert null and then
> update the foreign key value in a separate statement. If you use non-nullable
> constraints, though, you must persist your objects in the correct order.
> This improvement re-orders SQL statements as follows:
> 1. First, all insert statements execute. Inserts which have foreign keys with
> non-nullable constraints execute AFTER the foreign keys which they depend on
> have been inserted since no deferred update is possible.
> 2. Next, all update statements execute. No reordering is necessary.
> 3. Finally, all delete statements execute. Like inserts, deletes execute in
> an order which does not violate non-nullable foreign key constraints.
> If a circular foreign key reference is found during the re-ordering process
> then re-ordering halts and the remaining unordered statements are left as is.
> There is nothing that can be done about the circular reference (other than
> fixing the schema) and the resulting SQL statements will not succeed.
> The net effect is that users do not need to worry about the persistence order
> of their objects regardless of non-nullable foreign key constraints. The only
> class modified was
> org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager. I have included a
> patch which includes my modifications to OperationOrderUpdateManager and test
> cases. The test cases I have provided fail on the current trunk but pass with
> my modifications. I have also verified that I did not break anything by using
> maven to run all test cases with my modifications in place.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
