On Sun, 2002-01-27 at 00:25, Don Baccus wrote:
> Peter Eisentraut wrote:
> > I was under the impression that the typical way to handle tree structures
> > in relational databases was with recursive unions. It's probably
> > infinitely slower than stuffing everything into one datum, but it gets you
> > all the flexibility that the DBMS has to offer.
I see no reason why WITH RECURSIVE should be inherently slower than other
approaches except that checks for infinite recursion could be pricey.
Other than that getting rows by index should be more or less equal in both
cases.
> As I explained to Oleg privately (I think it was privately, at least) a
> key-based approach doesn't work well for DAGs because in essence you
> need a set of keys, one for each path that can reach the node. One of
> my websites tracks bird sightings for people in the Pacific NW and our
> geographical database is a DAG, not a tree (we have wildlife refuges
> that overlap states, counties etc). In that system I use a
> parent-child table to track the relationships.
>
> My impression is that there's no single "best way" to handle trees or
> graphs in an RDBMS that doesn't provide internal support (such as Oracle
> with its "CONNECT BY" extension).
The full SQL3 syntax for it is much more powerful and complex (see
attachment).
I think that this is what should eventually go into postgresql.
> Someone asked about using an integer array to store the hierarchical
> information. I looked at that a few months back but it would require
> providing custom operators, so rejected it in favor of the approach
> we're now using. It is important to us that users be able to fire up
> OpenACS 4 on a vanilla PG, such as the one installed by their Linux or
> BSD distribution. That rules out special operators that require contrib
> code or the like.
>
> We do use Oleg's full-text search stuff, but searching's optional and
> can be added in after the user's more comfortable with our toolkit,
> PostgreSQL, etc. A lot of our users are new to Postgres, or at least
> have a lot more Oracle experience than PG experience.
>
> But the integer array approach might well work for folks who don't mind
> the need to build in special operators.
I'll try if I can build the operators in PL/PGSL so one would not
"really" need to build special operators ;)
Tell me if this is something impossible.
------------------
Hannu
