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
[email protected]
http://list.linux-vserver.org/mailman/listinfo/vserver
