On Mon Nov 21 21:48:59 2011, Durchholz, Joachim wrote:
For Oracle, the constraint needs to be configured for "deferrable" checking
(available since 8i, don't know how well it works today). That postpones constraint
checks to commit time, and I think it is not the default.
In other words, in a legacy schema, Oracle's constraint checking will be on a
per-DML-statement basis.
SET CONSTRAINTS ALL DEFERRED will make Oracle postpone all constraint checking
to the next commit (the Oracle docs claim this is ANSI SQL92 syntax and
semantics).
Oracle in general is bad at reporting what data exactly caused some constraint
violation; deferring checks to commit time is going to make that worse if at
all possible.
Just enumerating the issues.
I sure would like to see deferred checking in Cayenne. I have absolutely no
idea whether it's worth the challenges though.
How is this a Cayenne issue? Isn't that just a property of the database as a
whole which you set up at config time?
1) Nope, SET CONSTRAINTS ALL DEFERRED is an in-session command. It affects just
the currently running session, until the next COMMIT (at which point SET
CONSTRAINTs is reset to whatever the database default is, usuall SET
CONSTRAINTS ALL IMMEDIATE).
2) It is a Cayenne issue in that Cayenne issues COMMIT commands. Also, in that
Cayenne's operation is affected by the setting - if constraint checking is
deferred, Cayenne could do INSERTs/UPDATEs/DELETEs in any order.
After having spent a long time struggling with this in mysql when upgrading
schemas, I have to say it would be a very nice feature to have in my db of
choice. Oh, I forgot to mention the mysql workaround:
1. Set the db constraints to OFF
2. Do your schema stuff
3. Set the db constraints back to ON
4. Check that you didn't break anything:
http://forge.mysql.com/tools/tool.php?id=11
That procedure works really well.
foreign_key_checks is a system variable. In other words: you switch it, you
affect all sessions, not just your currently running transaction.
The given script checks whether the data is inconsistent now, but it must check
ALL data in the database. That's too much to run after each commit.
The Oracle setting just checks those rows that were actually modified. That
should be MUCH faster.
Also, it does not allow a COMMIT to go through if any constraints are violated;
things will go into automatic ROLLBACK instead.
Regards,
Jo
I agree that deferred constraint checks are better than the script I
posted (if you are running Oracle or MSSQL). But that is the only
workaround for mysql I found. Useful for doing major database
refactoring. I think it would be very difficult for Cayenne to use
deferred checks as part of the commit process, since so few databases
support it, and it is rarely a problem in normal everyday use of a
database.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
