You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Michael Gentry <mg...@masslight.net> on 2010/05/10 17:50:24 UTC
Deletes before Inserts?
Is there a reason why INSERTs occur before DELETEs? It looks like
this is the bit of code that orders it (at least in 3.0):
DataDomainFlushAction.java / preprocess
insertBucket.appendQueries(queries);
flattenedBucket.appendInserts(queries);
updateBucket.appendQueries(queries);
flattenedBucket.appendDeletes(queries);
deleteBucket.appendQueries(queries);
There seems to be an issue if you have a DB constraint (unique column,
for example). Say the "key" column needs to be unique and you delete
the record with key="timeout" and insert another new record with
key="timeout". The commit will fail because the insert occurs before
the delete.
Thanks,
mrg
Re: Deletes before Inserts?
Posted by Michael Gentry <mg...@masslight.net>.
OK, thanks for the feedback Andrus!
mrg
On Tue, May 11, 2010 at 2:06 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
>>
>> Is there a reason why INSERTs occur before DELETEs?
>
> Yes, the current operation ordering is designed for presumably the most
> common scenario. But it is definitely not working for all cases.
> Specifically INSERT goes in before DELETE, as deleted objects may have FK
> references from other objects replaced by inserted object. So DELETE can
> only be done after INSERT and UPDATE.
>
>> Say the "key" column needs to be unique and you delete
>> the record with key="timeout" and insert another new record with
>> key="timeout".
>
> Some edge cases of this scenario (e.g. delete/insert of the same join record
> of a many-to-many) are handled by Cayenne as an UPDATE. But others where PK
> is different for two records but some other UNIQUE column is same can't be
> handled as UPDATE.
>
> I'd go as far as saying that without deferred constraint checking at the DB
> level it is not even possible to always satisfy an arbitrary set of DB
> constraints just by reordering operations. So a user solution to that would
> be doing multiple commits (I know this sucks)...
>
> On Cayenne end we can investigate the same multi-commit approach, but done
> in the same internal transaction, so that (a) it is transparent to the user
> and (b) an atomic rollback is possible. Not sure if that approach would
> satisfy constraint checkers on all DB's though.
>
> Andrus
>
>
>
> On May 10, 2010, at 6:50 PM, Michael Gentry wrote:
>
>> Is there a reason why INSERTs occur before DELETEs?
>
>> It looks like
>> this is the bit of code that orders it (at least in 3.0):
>>
>> DataDomainFlushAction.java / preprocess
>>
>> insertBucket.appendQueries(queries);
>> flattenedBucket.appendInserts(queries);
>> updateBucket.appendQueries(queries);
>> flattenedBucket.appendDeletes(queries);
>> deleteBucket.appendQueries(queries);
>>
>>
>> There seems to be an issue if you have a DB constraint (unique column,
>> for example). Say the "key" column needs to be unique and you delete
>> the record with key="timeout" and insert another new record with
>> key="timeout". The commit will fail because the insert occurs before
>> the delete.
>>
>> Thanks,
>>
>> mrg
>>
>
>
Re: Deletes before Inserts?
Posted by Andrus Adamchik <an...@objectstyle.org>.
>
> Is there a reason why INSERTs occur before DELETEs?
Yes, the current operation ordering is designed for presumably the
most common scenario. But it is definitely not working for all cases.
Specifically INSERT goes in before DELETE, as deleted objects may have
FK references from other objects replaced by inserted object. So
DELETE can only be done after INSERT and UPDATE.
> Say the "key" column needs to be unique and you delete
> the record with key="timeout" and insert another new record with
> key="timeout".
Some edge cases of this scenario (e.g. delete/insert of the same join
record of a many-to-many) are handled by Cayenne as an UPDATE. But
others where PK is different for two records but some other UNIQUE
column is same can't be handled as UPDATE.
I'd go as far as saying that without deferred constraint checking at
the DB level it is not even possible to always satisfy an arbitrary
set of DB constraints just by reordering operations. So a user
solution to that would be doing multiple commits (I know this sucks)...
On Cayenne end we can investigate the same multi-commit approach, but
done in the same internal transaction, so that (a) it is transparent
to the user and (b) an atomic rollback is possible. Not sure if that
approach would satisfy constraint checkers on all DB's though.
Andrus
On May 10, 2010, at 6:50 PM, Michael Gentry wrote:
> Is there a reason why INSERTs occur before DELETEs?
> It looks like
> this is the bit of code that orders it (at least in 3.0):
>
> DataDomainFlushAction.java / preprocess
>
> insertBucket.appendQueries(queries);
> flattenedBucket.appendInserts(queries);
> updateBucket.appendQueries(queries);
> flattenedBucket.appendDeletes(queries);
> deleteBucket.appendQueries(queries);
>
>
> There seems to be an issue if you have a DB constraint (unique column,
> for example). Say the "key" column needs to be unique and you delete
> the record with key="timeout" and insert another new record with
> key="timeout". The commit will fail because the insert occurs before
> the delete.
>
> Thanks,
>
> mrg
>