Re: WITH RECURSIVE patch V0.1

On May 19, 1:17 am, da(dot)(dot)(dot)(at)fetter(dot)org (David Fetter) wrote:
> On Mon, May 19, 2008 at 12:21:20AM -0400, Gregory Stark wrote:
> > It's quite possible to have clauses which will limit the output but
> > not in a way the database can determine. Consider for example a
> > tree-traversal for a binary tree stored in a recursive table
> > reference. The DBA might know that the data contains no loops but
> > the database doesn't.
>
> I seem to recall Oracle's implementation can do this traversal on
> write operations, but maybe that's just their marketing.

That's how I implement (id, name, parent)-trees as a DBA, having an
insert/update trigger function check_no_loops(), but I'm not sure that
it would be faster than the hash method suggested by Hannu Krosing. I
guess it depends on whether you're inserting/updating or selecting
more. Does it make sense to leave the option to the user, whether to
check for infinite recursion just in time or not?

Kev