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 <[email protected]>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 <[email protected]> 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.
>
