Re: Ltree - how to sort nodes on parent node

On Tue, Apr 20, 2010 at 1:58 PM, Alban Hertroys
<dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
> On 20 Apr 2010, at 11:59, cojack wrote:
>
>
>> I am not interested about recursive queries, i think this kill ltree idea.
>
>
> And IMHO it should. ltree is from a time when we didn't have any other means to describe data organised as a tree in Postgres. Navigating a tree is inherently recursive, so recursion is most likely the proper way to go about it.
>
> A solution omitting recursion (like ltree) can be faster, but you will run into limitations like the one you're currently struggling with.
>
> A solution with recursive queries will probably be more flexible and allows for referential integrity without having to write your own triggers and stuff - for example, what happens if you decide that Archeology isn't a Science but a Colour? What makes sure it's child-nodes get moved into Colors as well?
>

I've only been peripherally following this thread, so the following
may be overkill for the requirements, but the non-recursive / flat
query, solution is usually the set / subset pattern. It's been
popularized by Joe Celko and he has gone as far as writing a book on
the topic "Trees and hierarchies in SQL for smarties". If you don't
have many requirements for reordering the tree this solution works
well. It can be more of a pain if you need a GUI for tree management
(but can be done). We use this type of solution to manage trees up to
about 100,000 nodes in size with good performance. Other
non-recursive solutions include Vadim Tropashko's (now with Oracle)
Nested Interval Tree Encoding methods, which map directly to the
dotted path (1.1.3) type tree notations in the examples in this thread
and are a variation on the set / subset models.

--
Peter Hunsberger