cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Gentry <mgen...@masslight.net>
Subject Re: Transaction Fails - when saving interdependent entities.
Date Wed, 06 Jun 2012 13:08:35 GMT
PostgreSQL is a nice DB.  Sadly, we are using MySQL here and can't
defer the constraints, so we tend to not use constraints.

Glad it works nicely for you, though!

mrg


On Wed, Jun 6, 2012 at 7:58 AM, Bryan Lewis <jbryanlewis@gmail.com> wrote:
> This is so cool.  The tip about deferred constraints allowed me to get rid
> of my custom EntitySorter code that I've been maintaining for years.  This
> morning I ran scripts to drop all my foreign-key constraints and re-add
> them as initially deferred, for example:
>
>  ALTER TABLE alert ADD FOREIGN KEY (add_id) REFERENCES employee (id)
>    DEFERRABLE INITIALLY DEFERRED;
>
> I deleted my CustomEntitySorter code and ran all my tests, including the
> acid test that does an insert/delete on all my entities.  No failures.
>
> This technique still protects my data against ham-fisted manual database
> edits, or the exceedingly rare bug in my code.  Thanks, Michael.
>
> The embarrassing thing is, deferrable constraints have existed since Oracle
> 8i.  I could've used this feature ten years ago if I'd known about it.
> (I'm on Postgres 9 now.)
>
>
>
> On Tue, Jun 5, 2012 at 4:00 PM, Michael Gentry <mgentry@masslight.net>wrote:
>
>> Hi Gilberto,
>>
>> With Cayenne, you model the relationships between tables/objects and
>> Cayenne can build a dependency graph of what needs inserted first
>> (mainly useful for auto-generated keys), but currently there is no
>> mechanism in Cayenne to model the database constraints which can
>> further influence the dependency graph.
>>
>> I'd say deferred constraints can be useful even for small amounts of
>> data.  From my perspective, I care about the integrity of the data at
>> the end of the transaction, not in the middle, even if the transaction
>> only has a few statements.
>>
>> Turning on the logs (if they aren't already on) is unlikely to help in
>> this case.  I know from past experience that the dependency graph
>> produced by the EntitySorter isn't always the same from run-to-run
>> (there can be different, but valid, dependency graphs for a set of
>> operations).  Most likely deferring the constraints will solve the
>> issue.  And at least PostgreSQL allows you to do that.  :-)
>>
>> mrg
>>
>>
>> On Tue, Jun 5, 2012 at 3:44 PM, gilbertoca <gilbertoca@gmail.com> wrote:
>> >
>> > Michael Gentry-2 wrote
>> >>
>> >> Cayenne currently does not know about how your database constraints are
>> >> structured (even though
>> >> when it generates the schema it can add some constraints).
>> >>
>> >
>> > I think Cayenne is very intelligent and know the correct order to execute
>> > the statements, doesn't it?
>> >
>> > Michael, in my little experience in Database I would say that the common
>> use
>> > of the deferred integrity constraints is when dealing with large volumes
>> of
>> > data being inserted into tables.[1]
>> >
>> > Maybe enabling  the Cayenne log to show us how the statements are
>> ordered we
>> > could see another symptom.
>> > Regards
>> >
>> > Gilberto
>> >
>> >
>> > --
>> > View this message in context:
>> http://cayenne.195.n3.nabble.com/Transaction-Fails-when-saving-interdependent-entities-tp4024215p4024219.html
>> > Sent from the Cayenne - User mailing list archive at Nabble.com.
>>

Mime
View raw message