usage for 'with recursive'?

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?