Ugh - a package designed to require an OpenACS 4/PG feature from the ground up? I missed that when we started moving it to Oracle.
I guess somewhere the message that we're trying to develop a toolkit that supports both Oracle and PG got missed :(
(I know you're not the person who wrote this, Jun)
Ben is working on a tree sortkey solution for Oracle, as it turns out, using the RAW datatype I managed to dig out of the Oracle manual. We'll see how it turns out. He's also uncovered a race condition in the very original (and subsequent) tree_sortkey implementation. I have a fix for this for OpenACS 4.6 that will also speed up insertion and updating of trees.
There are thoughts of doing some benchmarking of this approach vs. CONNECT BY to see if the extra space required might be balanced by easier manipulation of trees (the problem you mention is typical of the situation when using CONNECT BY, the tree_sortkey approach is more flexible). With tree_sortkeys you don't need parent ids as the hierarchy's fully exposed, too, so there are several advantages to this approach.
Still ... dependencies of this sort that aren't modelled in Oracle shouldn't get built into general use packages. Same's true in the opposite direction.
Grrr ... OK it appears that for OpenACS 4.5 ETP is a PG-only package. A fix on this scale just isn't going to happen in time.
