Thread: usage for 'with recursive'?
there have been a discussions about how posdtgresql needs 'with recursive' queries. not that i would like to object the idea (new feature is always a good thing), but is anybody able to show me real usage of this kind of queries? as i see it the only usage for 'with recursive' is when one have a tree-structure stored as: create table objects (id serial primary key, parent_id int references objects (id), ...) and one want to do some "deep queries" without client-side recursion/loops. is it the only thing 'with recursive' is useful for? i mean it sounds unrealistic given that better data-structures for tree hierarchies have been proposed and implemented. best regards, depesz -- http://www.depesz.com/ - nowy, lepszy depesz
hubert depesz lubaczewski wrote: > there have been a discussions about how posdtgresql needs 'with > recursive' queries. > > not that i would like to object the idea (new feature is always a > good thing), but is anybody able to show me real usage of this kind of > queries? > as i see it the only usage for 'with recursive' is when one have a > tree-structure stored as: > create table objects (id serial primary key, parent_id int references > objects (id), ...) > and one want to do some "deep queries" without client-side > recursion/loops. > > is it the only thing 'with recursive' is useful for? i mean it sounds > unrealistic given that better data-structures for tree hierarchies > have been proposed and implemented. Better? I think perhaps different. There is materialized path, which requires a very problematic unlimited-length column to hold the path, and there is upper/lower bounds, which again requires client-side row-by-row processing. Both have the unpleasant problem that changes to one row may affect many others. AFAIK, the "WITH RECURSE" allows the simplest data structure, being key/parent_key. The best benefit of this method is that it is a simple foreign key and no action on a row ever affects another row, unlike the other two. If we could query out a list using WITH RECURSE it would become very powerful. > > best regards, > > depesz > -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can?
On 3/1/07, Kenneth Downs <ken@secdat.com> wrote: > Better? I think perhaps different. There is materialized path, which > requires a very problematic unlimited-length column to hold the path, > and there is upper/lower bounds, which again requires client-side > row-by-row processing. Both have the unpleasant problem that changes to > one row may affect many others. there are also other approaches. i know that they are not that well known, but there are, and they allow many things to be done with very simple queries. i use a solution which basically allows me to query every kind of tree-structure data without loops or recursion. drawbacks are 2 and are very limited: 1. it uses some triggers 2. it has some (slight in my opinion) overhead. i wrote an article about it here (http://www.depesz.com/various/various-sqltrees-implementation.php). it's in polish, but has lots of examples so should be easily readable. depesz
hubert depesz lubaczewski wrote: > On 3/1/07, Kenneth Downs <ken@secdat.com> wrote: >> Better? I think perhaps different. There is materialized path, which >> requires a very problematic unlimited-length column to hold the path, >> and there is upper/lower bounds, which again requires client-side >> row-by-row processing. Both have the unpleasant problem that changes to >> one row may affect many others. > > there are also other approaches. > i know that they are not that well known, but there are, and they > allow many things to be done with very simple queries. > i use a solution which basically allows me to query every kind of > tree-structure data without loops or recursion. drawbacks are 2 and > are very limited: > 1. it uses some triggers > 2. it has some (slight in my opinion) overhead. I see you've moved the client-side code to the server, which is a Good Thing in my book. The overhead would be proportional to the number of rows that have to be looked at when a new row goes in. This reminds me of another advantage of the WITH RECURSIVE, which is that it pushes to overhead to SELECT, with no associated write-time overheads. -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can?
On 3/2/07, Kenneth Downs <ken@secdat.com> wrote: > This reminds me of another advantage of the WITH RECURSIVE, which is > that it pushes to overhead to SELECT, with no associated write-time > overheads. hmm .. why do you consider this as advantage? i would say it's rather drawback. depesz
hubert depesz lubaczewski wrote: > On 3/2/07, Kenneth Downs <ken@secdat.com> wrote: >> This reminds me of another advantage of the WITH RECURSIVE, which is >> that it pushes to overhead to SELECT, with no associated write-time >> overheads. > > hmm .. why do you consider this as advantage? i would say it's rather > drawback. One school of thought aims for overall system performance gains by keeping transactions as small as possible. WITH RECURSIVE allows an UPDATE to affect exactly one row, where other methods affect more rows. Therefore the WITH RECURSIVE gives you the smallest possible transaction at write time. Further, it seems the actual number of rows pulled in all approaches should be the same, so now I wonder if there really even is any overhead at SELECT time, making the argument for WITH RECURSIVE rather conclusive I'd say. > > depesz > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Kenneth Downs Secure Data Software, Inc. www.secdat.com / www.andromeda-project.org Office: 631-689-7200 Cell: 631-379-0010 ::Think you may have a problem with programming? Ask yourself this ::question: do you worry about how to throw away a garbage can?