Arjen Lentz wrote:
Hi Brian
On 05/02/2009, at 8:22 AM, Brian Aker wrote:
Auto-increments have always had a set behavior... some of this worked
well, and some of it causes bottlenecks.
1) They always increase in value in the table. You can use them as a
"what came before what".
2) Gaps don't (for the most part) exist).
What are people's feelings about the above assumptions? Is it ok to
break them, or would it be better to add a different type which did
not have the above assumptions, and leave the old ones in?
Open Query teaches:
- later inserts usually get a higher number, but don't bet on it (with
NDB and multi-master, it doesn't).
- expect gaps (rollbacks, NDB and multi-master again).
- the only expectation you may have is that it gets a # unique within
the specified table. No more.
This story is actually quite similar to the "rows have no order"
teaching. They may appear to sometimes come out in some kind of
predictable order (order of insert, or index order) but you can't bet on
it and fact there's no reason to. We generally do an exercise that makes
the presumptions fail and people remember.
Exactly. Both good lessons to teach and remember. :)
Anyway, both are about good habits, there's no need to indulge any bad
habits here. For broken app code, let it still be broken.
I figure that whatever Drizzle comes up with will still have potential
gaps and/or out-of-sequence inserts, and IMHO that's perfectly fine;
trying to avoid them involves serialisation which will cause contention
somewhere. Not worth it, not even to appease beancounters.
+1
I agree with Jim that a truncate should not reset the ID generator. It's
a side-effect of the implementation in MyISAM, and it can easily be
avoided even without slowing down anything. So if any other engines do
the same, make 'em not do that.
+1. Basically, any implicit behaviour I am -1 about. It's fine to
reset an auto-incrementing column, just not implicitly.
On a practical note, the naming of both "auto-increment" and "sequence"
is unfortunate, because it causes implicit expectations on the
behaviour. UNIQUE_ID or GENERATOR (thank Jim!) would be clearer, it's
just intended to provide a unique ID, and nothing else.
And while in principle I can see how having it user-definable in terms
of logic can be useful, I don't think it's essential.
In SQL Server, it's called IDENTITY, which I think is a fine name. So
is generator and UNIQUE_ID. I agree with all that auto-increments
*implied* always-incrementing behaviour is not good.
Cheers,
Jay "explicit FTW" Pipes
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
