Re: OO and relation "impedance mismatch"

Kenneth Downs wrote:
> Hmmm, good question. At a first glance, the unique constraint should
do a
> dirty read and the RI should not. Uniques need to err on the side of
> catching all possible violations, hence the dirty read. RI needs to
err on
> the side of not giving an OK on a value that may not yet be
committed, so
> you do the opposite.
>
> If the constraints are coded this way, user b's RI will fail, as it
should.
>
> Thanks for raising the point.

That's OK. And I'll take it a bit further with 2 more examples.

Example 1:
-- Assume a parent row with pid=123 exists already, but no related child rows exist
User A> insert into child (pid, cid) values (123, 456); -- Succeeds
User B> delete from parent where pid = 123; -- Dirty read sees User A's insert and so fails the delete

Example 2:
-- Assume a parent row with pid=123 exists already, AND that related child rows exist

User A> delete from child where pid = 123; -- Succeeds
User B> delete from parent where pid = 123; -- Dirty read sees User A's delete and so SUCCEEDS User A> rollback;
-- Oh dear, now we have orphans again!

So here the very same DELETE trigger needs to do a dirty read in one case and a clean read in another. Perhaps the answer is to do both type of read in the trigger, and fail if either finds a record?

> > Another question: how can you generate the code for your triggers
from
> > the data dictionary, when your data dictionary doesn't know about
> > primary keys, unique keys, foreign keys?
>
> Since I designed my dd, I put the keys into it. As stated in the
other
> sub-thread on this, by dd I mean my own set of information, not
anything
> provided by the vendor.

Understood. Duplication of effort, though, since the "system catalog" can do that.

> > Why not (just to please me!) create all the proper, declarative
> > constraints AND generate your triggers from them.
>
> Because if the declarative constraint exists you cannot control the
DBMS's
> implementation of it, you cannot control firing order, and so you
have
> given up the only motivation for the trigger in the first place,
which is
> uniform error handling.

I was assuming that (as in Oracle) these triggers would be set up to fire BEFORE the insert actually happened, and so BEFORE the constraints kicked in. So whatever errors your trigger catches are handled as you want, and since the insert is abandoned no constraint checks take place. "So why bother with the constraints?" Answer: because I believe once in a while your triggers will let invalid data through (see above), and THEN the constraints will save you.

> To recap:
>
> My use of triggers is currently experimental. I know for a fact that
I will
> trade performance to get my preference for show-me-all-errors. I
believe
> *intuitively* that I can get 100% data protection, but I have not
proven it
> yet to my own satisfaction. Stay tuned, you will undoubtedly hear
more
> from me as I work on the project.

I look forward to it. As I have said before, I know for a fact that your approach wouldn't work 100% for Oracle (which doesn't even support dirty reads), but I can't be so sure about other DBMSs. Received on Wed Oct 06 2004 - 15:30:27 CEST