About this list Date view Thread view Subject view Author view Attachment view

From: Sam Vilain (sam_at_vilain.net)
Date: Thu 28 Apr 2005 - 23:22:26 BST


Matthew Nuzum wrote:
> I'm a big postgres fan and closely follow the performance mailing list.
> These features sound intriguing so I'm going to enquire about their status.

Ah, my plan is falling into place... <rubs hands together>

> features are available to me. BTW, one interesting feature that Oracle has
> is the ability to store hierarchical data in a flat db table and pull it out
> in one query. For example:
   [...]
> This takes several queries in PostgreSQL.

It sounds great in theory, doesn't it?

Then I found out that you can't use it for anything 'useful', for instance
by passing in a table column alias to the START WITH from an outer query,
which seemed to me the most natural way to use it;

    select
       t1.id,
       t1.name,
       tn.id as child_id
    from
       mytable t1
    left join
       (select
            t2.id
        from
            mytable t2
        start with
            t2.id = t1.id
        connect by
            prior id = parent_id
        ) tn on top_id = t1.id
     where
        t1.name like '%foo%';

That's not a valid query; in fact I couldn't really see a way I could use
it to generically do 'recursive' joins, to pretend that a heirarchical
relationship is a mapping table or something like that, even using views
and such trickery.

However, it sure is useful for indenting heirarchical results for a
single heirarchy in display, like it's EXPLAIN PLAN statement.

I've seen this a lot with Oracle. Some feature sounds great, then you
try to use it and find it's not as useful as you thought, for a very
trivial yet seemingly unsurmountable reason (and I refuse to learn any
DB-specific 4GLs ;)). I was dumbfounded when a bug in functional indexes
gave me bogus results for a query (if some silly conditions held), and
there was simply no patch available for a supposedly "stable" database.
So we had to upgrade to a new major version (and of course we found other
bugs there too).

Tangram has ways to work around this problem in a DB-independant way, so
I'm not particularly worried :).

The information you found about these features is interesting; it sure
would be great if Pg is maturing enough to be a viable replacement!

Thanks for the off-topic banter :)

Sam.
_______________________________________________
Vserver mailing list
Vserver_at_list.linux-vserver.org
http://list.linux-vserver.org/mailman/listinfo/vserver


About this list Date view Thread view Subject view Author view Attachment view
[Next/Previous Months] [Main vserver Project Homepage] [Howto Subscribe/Unsubscribe] [Paul Sladen's vserver stuff]
Generated on Thu 28 Apr 2005 - 23:22:59 BST by hypermail 2.1.3