Обсуждение: Proposal of hierarchical queries, a la Oracle
Hi there! I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; I've seen this type of queries often made by adding a new type, which stores position of row in the tree. But sorting such tree are very tricky (i think). Patch allows result tree to be sorted, i.e. subnodes of each node will be sorted by ORDER BY clause. with regards, evgen
Evgen, you'd need to post your message and patch to hackers mailing list. btw, did you try contrib/ltree module ? regards, Oleg On Fri, 15 Nov 2002, Evgen Potemkin wrote: > > Hi there! > > I want to propose the patch for adding the hierarchical queries posibility. > It allows to construct queries a la Oracle for ex: > SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; > > I've seen this type of queries often made by adding a new type, which stores > position of row in the tree. But sorting such tree are very tricky (i > think). > > Patch allows result tree to be sorted, i.e. subnodes of each node will be > sorted by ORDER BY clause. > > with regards, evgen > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Evgen, read info about ltree module http://www.sai.msu.su/~megera/postgres/gist/ltree/ Do you have implemented indexed access ? regards, Oleg On Sat, 16 Nov 2002, Evgen Potemkin wrote: > I was have made a module like ltree, i don't know how you implement it, > but mine doesn't fit. > > The problem : for ex. you need to select a tree, but with childs of > each parent is sorted. > in my module i've defined a tree path as set of > numbers separated by dot ('1.234.456.789'::hier for ex.), a comparision > operators <,>,<=,<=,= , operators for checking for ancestors/descedants > <<,>>,<<=,>>=. > create table t (field text, node::hier); > then i do "select field from t where where node <<'1' order by node"; > 'order by' - for tuples being in order of tree (first is root,then first > child, then first child of first child, .. so on). works perfectly well. > > THE MATTER OF PROBLEM: > tuples can't be sorted alphabetically by 'field', because of 'order by node' > clause. 'order by node,field' doesn't really work because 'node' is first > key, if 'order by field,node' - then we don't get a tree. > > if i do nodes on one level is equal, then childs of one node in some cases > are migrate to another node of same level :) as far as i understand problem > is in pg's sort. it's not a bug but feature of sort alghorithm. > > The minor problem is that such tree a bit difficult to maintain. for ex. if i need > to move some node to elsewhere in tree, i need to update all childs, sub > childs, ... of this node. > > regards > --- > .evgen > > On Fri, 15 Nov 2002, Oleg Bartunov wrote: > > > Evgen, > > > > you'd need to post your message and patch to hackers mailing list. > > btw, did you try contrib/ltree module ? > > > > > Regards, > > Oleg > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Evgren, > I want to propose the patch for adding the hierarchical queries > posibility. > It allows to construct queries a la Oracle for ex: > SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; You'll be thrilled to know that this has already been implemented in PostgreSQL 7.3, as a /contrib module, by Joe Conway. Download 7.3b5 now if you can't wait; Joe would like more people to test his module, anyway. -Josh Berkus
I was have made a module like ltree, i don't know how you implement it,
but mine doesn't fit.
The problem : for ex. you need to select a tree, but with childs of
each parent is sorted.
in my module i've defined a tree path as set of
numbers separated by dot ('1.234.456.789'::hier for ex.), a comparision
operators <,>,<=,<=,= , operators for checking for ancestors/descedants
<<,>>,<<=,>>=.
create table t (field text, node::hier);
then i do "select field from t where where node <<'1' order by node";
'order by' - for tuples being in order of tree (first is root,then first
child, then first child of first child, .. so on). works perfectly well.
THE MATTER OF PROBLEM:
tuples can't be sorted alphabetically by 'field', because of 'order by node'
clause. 'order by node,field' doesn't really work because 'node' is first
key, if 'order by field,node' - then we don't get a tree.
if i do nodes on one level is equal, then childs of one node in some cases
are migrate to another node of same level :) as far as i understand problem
is in pg's sort. it's not a bug but feature of sort alghorithm.
The minor problem is that such tree a bit difficult to maintain. for ex. if i need
to move some node to elsewhere in tree, i need to update all childs, sub
childs, ... of this node.
regards
---
.evgen
On Fri, 15 Nov 2002, Oleg Bartunov wrote:
> Evgen,
>
> you'd need to post your message and patch to hackers mailing list.
> btw, did you try contrib/ltree module ?
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>
Josh Berkus wrote: > Evgren, > > >>I want to propose the patch for adding the hierarchical queries >>posibility. >>It allows to construct queries a la Oracle for ex: >>SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; > > > You'll be thrilled to know that this has already been implemented in > PostgreSQL 7.3, as a /contrib module, by Joe Conway. > > Download 7.3b5 now if you can't wait; Joe would like more people to > test his module, anyway. > I have it on my personal TODO to approach this for 7.4, but I'll be happy to focus on other things if you'd like to take this on. The connectby() function in contrib/tablefunc could be a good starting point for you. Joe
Oleg, i've read that info. mine CONNECT BY patch is based on internal pg's query processing, from that point it's a kind of ORDER BY or GROUP BY clause. Index access is implemented by lower layer. regards, --- .evgen On Sat, 16 Nov 2002, Oleg Bartunov wrote: > Evgen, > > read info about ltree module > http://www.sai.msu.su/~megera/postgres/gist/ltree/ > > Do you have implemented indexed access ? > > regards, > > Oleg >
-Josh, i don't be so thrilled:), with current implementation of /contrib/tablefunc mine problem sill remains: i need to sort tree leaves in some order, independent of tree itself. this can be fixed (in tablefunc) by addition to connectby() one more field like 'order_by' or so. but it will be more difficult to scale. for ex. if i need to make subquery with connect by, with tablefunc i need to create view to do so. what if i need several different such queries? of course this can be done with tablefunc, but IMHO mine approach is a bit more 'native':) --- .evgen On Sat, 16 Nov 2002, Josh Berkus wrote: > Evgren, > > > I want to propose the patch for adding the hierarchical queries > > posibility. > > It allows to construct queries a la Oracle for ex: > > SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; > > You'll be thrilled to know that this has already been implemented in > PostgreSQL 7.3, as a /contrib module, by Joe Conway. > > Download 7.3b5 now if you can't wait; Joe would like more people to > test his module, anyway. > > -Josh Berkus >
Joe, i've made it already,as send first 'Proposal ...' message,but found a small bug. within nearest days i'll fix it, and postthe patch to pgsql-patches. i've described why i can't use connectby() in message to Josh Berkus. i'll enhance CONNECT BY as clause, but not as function. i think it's more 'right' approach to make it:) regards, --- .evgen On Sat, 16 Nov 2002, Joe Conway wrote: > Josh Berkus wrote: > > Evgren, > > > > > >>I want to propose the patch for adding the hierarchical queries > >>posibility. > >>It allows to construct queries a la Oracle for ex: > >>SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; > > > > > > You'll be thrilled to know that this has already been implemented in > > PostgreSQL 7.3, as a /contrib module, by Joe Conway. > > > I have it on my personal TODO to approach this for 7.4, but I'll be happy to > focus on other things if you'd like to take this on. The connectby() function > in contrib/tablefunc could be a good starting point for you. > > Joe
On Tue, 19 Nov 2002, Evgen Potemkin wrote: > Oleg, > > i've read that info. mine CONNECT BY patch is based on internal pg's query processing, > from that point it's a kind of ORDER BY or GROUP BY clause. Index access is implemented by > lower layer. ok. I see. > > regards, > --- > .evgen > > On Sat, 16 Nov 2002, Oleg Bartunov wrote: > > > Evgen, > > > > read info about ltree module > > http://www.sai.msu.su/~megera/postgres/gist/ltree/ > > > > Do you have implemented indexed access ? > > > > regards, > > > > Oleg > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Evgen Potemkin wrote: > Joe, > > i've made it already,as send first 'Proposal ...' message, > but found a small bug. within nearest days i'll fix it, and post the patch > to pgsql-patches. Please note that there was no patch attached to your initial proposal (assuming this is the message you are referring to): http://archives.postgresql.org/pgsql-sql/2002-11/msg00226.php -- I think that's why people proposed so many alternatives to you. In any case, we'll look forward to your patch! Regards, Joe