On Tue, Apr 20, 2010 at 1:58 PM, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
