pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProposal of hierarchical queries, a la Oracle
11+ messages / 4 participants
[nested] [flat]
* Proposal of hierarchical queries, a la Oracle
@ 2002-11-15 09:41 Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-15 16:59 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 19:09 ` Re: Proposal of hierarchical queries, a la Oracle Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 11+ messages in thread
From: Evgen Potemkin @ 2002-11-15 09:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
@ 2002-11-15 16:59 ` Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 11:30 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
1 sibling, 1 reply; 11+ messages in thread
From: Oleg Bartunov @ 2002-11-15 16:59 UTC (permalink / raw)
To: Evgen Potemkin <evgent@ns.terminal.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-15 16:59 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
@ 2002-11-16 11:30 ` Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 16:58 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 11+ messages in thread
From: Evgen Potemkin @ 2002-11-16 11:30 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-15 16:59 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 11:30 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
@ 2002-11-16 16:58 ` Oleg Bartunov <oleg@sai.msu.su>
2002-11-19 12:12 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Oleg Bartunov @ 2002-11-16 16:58 UTC (permalink / raw)
To: Evgen Potemkin <evgent@ns.terminal.ru>; +Cc: pgsql-sql@postgresql.org, Teodor Sigaev <teodor@stack.net>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-15 16:59 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 11:30 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 16:58 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
@ 2002-11-19 12:12 ` Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-19 13:36 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 11+ messages in thread
From: Evgen Potemkin @ 2002-11-19 12:12 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql; Teodor Sigaev <teodor@stack.net>
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-15 16:59 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 11:30 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 16:58 ` Re: Proposal of hierarchical queries, a la Oracle Oleg Bartunov <oleg@sai.msu.su>
2002-11-19 12:12 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
@ 2002-11-19 13:36 ` Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 11+ messages in thread
From: Oleg Bartunov @ 2002-11-19 13:36 UTC (permalink / raw)
To: Evgen Potemkin <evgent@ns.terminal.ru>; +Cc: pgsql-sql@postgresql.org, Teodor Sigaev <teodor@stack.net>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
@ 2002-11-16 19:09 ` Josh Berkus <josh@agliodbs.com>
2002-11-16 20:44 ` Re: Proposal of hierarchical queries, a la Oracle Joe Conway <mail@joeconway.com>
2002-11-19 12:26 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
1 sibling, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2002-11-16 19:09 UTC (permalink / raw)
To: Evgen Potemkin <evgent@ns.terminal.ru>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 19:09 ` Re: Proposal of hierarchical queries, a la Oracle Josh Berkus <josh@agliodbs.com>
@ 2002-11-16 20:44 ` Joe Conway <mail@joeconway.com>
2002-11-19 12:45 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
1 sibling, 1 reply; 11+ messages in thread
From: Joe Conway @ 2002-11-16 20:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Evgen Potemkin <evgent@ns.terminal.ru>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 19:09 ` Re: Proposal of hierarchical queries, a la Oracle Josh Berkus <josh@agliodbs.com>
2002-11-16 20:44 ` Re: Proposal of hierarchical queries, a la Oracle Joe Conway <mail@joeconway.com>
@ 2002-11-19 12:45 ` Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-19 17:27 ` Re: Proposal of hierarchical queries, a la Oracle Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 11+ messages in thread
From: Evgen Potemkin @ 2002-11-19 12:45 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 19:09 ` Re: Proposal of hierarchical queries, a la Oracle Josh Berkus <josh@agliodbs.com>
2002-11-16 20:44 ` Re: Proposal of hierarchical queries, a la Oracle Joe Conway <mail@joeconway.com>
2002-11-19 12:45 ` Re: Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
@ 2002-11-19 17:27 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 11+ messages in thread
From: Joe Conway @ 2002-11-19 17:27 UTC (permalink / raw)
To: Evgen Potemkin <evgent@ns.terminal.ru>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Proposal of hierarchical queries, a la Oracle
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 19:09 ` Re: Proposal of hierarchical queries, a la Oracle Josh Berkus <josh@agliodbs.com>
@ 2002-11-19 12:26 ` Evgen Potemkin <evgent@ns.terminal.ru>
1 sibling, 0 replies; 11+ messages in thread
From: Evgen Potemkin @ 2002-11-19 12:26 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
-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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2002-11-19 17:27 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-15 09:41 Proposal of hierarchical queries, a la Oracle Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-15 16:59 ` Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 11:30 ` Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-16 16:58 ` Oleg Bartunov <oleg@sai.msu.su>
2002-11-19 12:12 ` Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-19 13:36 ` Oleg Bartunov <oleg@sai.msu.su>
2002-11-16 19:09 ` Josh Berkus <josh@agliodbs.com>
2002-11-16 20:44 ` Joe Conway <mail@joeconway.com>
2002-11-19 12:45 ` Evgen Potemkin <evgent@ns.terminal.ru>
2002-11-19 17:27 ` Joe Conway <mail@joeconway.com>
2002-11-19 12:26 ` Evgen Potemkin <evgent@ns.terminal.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox