In case the rant above causes anyone to miss my first point ...
the SELECT FOR UPDATE is correct ... and I think will actually work in this case because I don't think it hits the locking problem I've been talking about.
So if you:
1. Add the appropriate SELECT FOR UPDATE
2. Add the tracking of the maximum child key to objects
3. Switch to using object_id for the first level sortkey for
all objects (including those with NULL context_id)
I think we're home.
Now the odd question - why are objects with context_id set to the security root considered to be at level 0 rather than level 1, because the security root object itself is at level 0 (and has a context_id set NULL).
Answer - I'm not sure. Dan Wickstrom wrote the original version of the tree sortkey code after talking to me, including the triggers. Later I wrote the code to use varbit rather than char as the datatype to store the sort keys, including the short key space saving hack, but I didn't change anything else.
Logically it would seem to me that the all objects with context_id set NULL - including the security root object - should be level 0, while all other objects - including those with security root object as their context_id - should have level > 0.
No? Comments, Jeff?
I really want to get this right once and for all, which is why I'd earlier decided to forget about it until 5.0 was out.