Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: neutral (nike.apache.org: local policy)
MIME-Version: 1.0
In-Reply-To: 
 <CAFYr+8bkV-2r-x2jJmeB2Bj-iAK1xqAf3SiPXk8=cJS3sLSoyA@mail.gmail.com>
References: 
 <CAFxxW5wOGeTW0ebSZ+Ci4Hm2ULivEnpKZLgU-tqj056nvL-byw@mail.gmail.com>
 <CAA-upzo+UOKfE6RAtgyszBCGk3j+dZU5bbMMLgF5Z_GAZyQ-aQ@mail.gmail.com>
 <1338925480358-4024219.post@n3.nabble.com>
 <CAA-upzpYjwd7KCfAnpUx72ouFEJXecvXpY=eSECrzRMNjgQetQ@mail.gmail.com>
 <CAFYr+8bkV-2r-x2jJmeB2Bj-iAK1xqAf3SiPXk8=cJS3sLSoyA@mail.gmail.com>
From: Michael Gentry <mgentry@masslight.net>
Date: Wed, 6 Jun 2012 09:08:35 -0400
Message-ID: 
 <CAA-upzrp9Un9ST1zr_DDefoz=5kC_7_Zdq1Pm4HSwfDKcNMa0g@mail.gmail.com>
Subject: Re: Transaction Fails - when saving interdependent entities.
To: user@cayenne.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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. =A0The tip about deferred constraints allowed me to get =
rid
> of my custom EntitySorter code that I've been maintaining for years. =A0T=
his
> morning I ran scripts to drop all my foreign-key constraints and re-add
> them as initially deferred, for example:
>
> =A0ALTER TABLE alert ADD FOREIGN KEY (add_id) REFERENCES employee (id)
> =A0 =A0DEFERRABLE 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. =A0No failures.
>
> This technique still protects my data against ham-fisted manual database
> edits, or the exceedingly rare bug in my code. =A0Thanks, Michael.
>
> The embarrassing thing is, deferrable constraints have existed since Orac=
le
> 8i. =A0I 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>wro=
te:
>
>> 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. =A0From 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. =A0I 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). =A0Most likely deferring the constraints will solve the
>> issue. =A0And at least PostgreSQL allows you to do that. =A0:-)
>>
>> 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 a=
re
>> >> 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 exec=
ute
>> > the statements, doesn't it?
>> >
>> > Michael, in my little experience in Database I would say that the comm=
on
>> use
>> > of the deferred integrity constraints is when dealing with large volum=
es
>> of
>> > data being inserted into tables.[1]
>> >
>> > Maybe enabling =A0the 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-interdepe=
ndent-entities-tp4024215p4024219.html
>> > Sent from the Cayenne - User mailing list archive at Nabble.com.
>>