Re: Why doesn't EOF sort our PK problems itself ?
Re: Why doesn't EOF sort our PK problems itself ?
- Subject: Re: Why doesn't EOF sort our PK problems itself ?
- From: Chuck Hill <email@hidden>
- Date: Wed, 23 Jan 2008 14:30:59 -0800
On Jan 23, 2008, at 1:38 PM, Mike Schrag wrote:
You're correct in thinking such a fix would be a DB-specific
thing. One advantage though is that at least as far as I know, PK
constraints are rarely deferred as there is no reason I know of to
do so. At least with Posstgres, PK constraints cannot be deferred
as far as I know. It completely defeats the point of a primary key.
I'll have to test this now out of scientific curiosity ... If I had
to venture a guess, I would think they would fail fast also.
Also, you ought to know why EO does not use auto-increment PKs in
the first place. To do so would require a huge rethink of the way
the EOAccess layer operates. At the moment it grabs all of the
new PKs from the DB in one piece of code and then actually issues
the changes in another piece.
With auto-increment PKs you would not know the PK until after you
did the insert. That means that the PK propagation phase would
have to be done concurrently with the insertion phase. Right now
that's not possible because the inserts aren't ordered in any
particular manner. For instance, what do you do when you've got a
hierarchical structure with one PK propagating into a compound PK
on another entity?
This seems like it would be the same scenario as using a database
without deferrable foreign key constraints. I believe Chuck ran
into this with MS SQLServer a couple months ago, actually, and
ended writing an ordering db context delegate for the ops before
they process. Cyclic dependencies would screw you (or rather ..
Chuck :) ) in the same way, here, I imagine.
Worse yet, what do you do when you have cyclical dependencies?
This is a completely valid point ... There are definitely cyclic
cases that would just be impossible to resolve without pre-computed
keys.
I don't think they are even possible in a database without deferrable
constraints. Even with pre-generated keys , if a row in Table A has
the FK for a row in Table B that has the FK for a row in Table A,
there is no way to insert them unless they are nullable.
I have worked around this by decreeing that such relationships will
not be used. As they are very rare in a well designed schema (IME)
this has not been a problem.
I mostly only use FrontBase and PG, so I feel no pain of
autoincrement cols :), but I can only assume people who use auto
inc with MySQL only get away with this because they must not be
using InnoDB + transactions when they use auto_inc columns -- or
there's some crazy custom facility in MySQL to support FK refs to
auto_inc PK's of other rows (which would have to be some magic like
EO's tempgid=>gid proces) -- I REALLY doubt it, though. God
knows ... The times we have had to integrate with auto inc columns
it has generally been some insane php app that we need to talk to,
so that says something.
Not really related to this but... I remember some years back
asserting on the list that it was impossible for EOF work with auto-
increment columns. Someone proved me wrong but I don't recall who or
what the solution was. It was pretty low level IIRC. Insert cursors
or some such so that EOF could get the PK after insert and use it for
the global ID.
Anyway, the bottom line is that EOF's style of PK generation is a
feature, not a bug.
Well, it's a feature for anyone using a DB with proper sequences :)
This is different, though ... This is a failure in the pk
generator, which is pretty straightforward to catch and handle
in the plugin. The problem you're talking about is a failure
during commit, after the plugin's pk generator has already run.
That is true. But it's still before EOF commits to actually using
the specific key values. So it's possible to correct these
problems. Assuming PK constraints are not deferred then
regardless of database you should get an exception immediately on
the insert line. Then you get into a situation where you need to
figure out why you got the exception. That's DB dependent but on
a DB like Postgres it will be a failure on the index which has a
well-known name. So simply grepping the error message for the
index name should be sufficient for postgres and a similar
solution can be used for most other databases.
You are absolutely right on this one ... I actually never went
through the ordering of "committing" the generated PK to EOF
relative to the transaction commit in the DB. It does appear that
the switch out the temp GID for the real GID only happens after a
successful commit, so you COULD definitely trap this and force a
renegotiation of the pk ... It doesn't look like there's a good
delegate to hook into at that level, so i think you'd have to deal
with it higher up, but some combination of how Wonder does its pre-
saveChanges-pk-generation with catching that exception might work.
If you (he) DO (does) use Wonder, I would be a little careful of
depending on GID's getting reset again if you ever
use .primaryKeyInTransaction, because I don't think that gets
"rolled back" within the EC even if there is a failure (which makes
sense given the intent of that method).
Do I think this is something that ought to go into EOF proper?
Not really. But the original poster was looking for a way to
automatically fix his PK sequences and so I'm giving him the
outline of how he could do this. He may decide (and I would if I
were him) that ultimately it's cheaper to remind yourself to
update the PK sequence if you make changes outside of EOF.
I would tend to support that "just update the pk table" will be the
conclusion, though I do feel his pain given that sometimes it's
mucked with by some other process that you can't necessarily easily
detect.
The best reference on this stuff is Chuck and Sacha's book with
the bumble-bee cover but even that does not (believe it or not) go
deep enough.
The best reference on this stuff is decompiled
EODatabaseContext.class :)
QFT
Chuck
--
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden