CONNECT BY PRIOR

Hi,

I'm just a little bit confused because I expected postgresql to be able
t "connect by prior" but as I have seen it is not. :-(
Are there any plans to support this in the main distribution? If have
found a patch to porstgres but I don't want to apply any patches but
only use the "vanilla" postgresql.
BTW: The patch is available at http://gppl.moonbone.ru/

Cheers,
Yann

Yann Michel <yann-postgresql(at)spline(dot)de> writes:
> I'm just a little bit confused because I expected postgresql to be able
> t "connect by prior" but as I have seen it is not. :-(
> Are there any plans to support this in the main distribution?

There's some work being done to implement the SQL-standard recursive
WITH feature, which is able to solve the same problems as CONNECT BY.
There's not a whole lot of interest in adopting Oracle's non-standard
syntax ...

regards, tom lane

Yann,

I am working on the standard WITH syntax for recursive query support and
hope to get it into 8.2.

On 11/12/05, Yann Michel <yann-postgresql(at)spline(dot)de> wrote:
>
> Hi,
>
> I'm just a little bit confused because I expected postgresql to be able
> t "connect by prior" but as I have seen it is not. :-(
> Are there any plans to support this in the main distribution? If have
> found a patch to porstgres but I don't want to apply any patches but
> only use the "vanilla" postgresql.
> BTW: The patch is available at http://gppl.moonbone.ru/
>
> Cheers,
> Yann
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Hi,

On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote:
> Yann,
>
> I am working on the standard WITH syntax for recursive query support and
> hope to get it into 8.2.

Fine! Looking forward to that!

Cheers,
Yann

That's GREAT news, hope you can make it to 8.2 .. it will be really a good hit

On 11/13/05, Yann Michel <yann-postgresql(at)spline(dot)de> wrote:
> Hi,
>
> On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote:
> > Yann,
> >
> > I am working on the standard WITH syntax for recursive query support and
> > hope to get it into 8.2.
>
> Fine! Looking forward to that!
>
> Cheers,
> Yann
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote:

> I am working on the standard WITH syntax for recursive query support
> and hope to get it into 8.2.

Sounds interesting.

What approach are you taking to the plan shape? The current approach
would be to have additional plan nodes for each join. Coping with a
dynamic number of operations will do interesting things in the planner.

I face a similar dynamic problem with joins to partitioned tables.

Do you have any thoughts about this area?

Best Regards, Simon Riggs

Hey Simon,
I'm doing some research into recursive query planning in terms of theory
as-well-as actual implementation in other RDBMS. Let me get back to you when
I have some more definitive info.

On 11/14/05, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote:
>
> > I am working on the standard WITH syntax for recursive query support
> > and hope to get it into 8.2.
>
> Sounds interesting.
>
> What approach are you taking to the plan shape? The current approach
> would be to have additional plan nodes for each join. Coping with a
> dynamic number of operations will do interesting things in the planner.
>
> I face a similar dynamic problem with joins to partitioned tables.
>
> Do you have any thoughts about this area?
>
> Best Regards, Simon Riggs
>
>
>

On Tue, Nov 15, 2005 at 01:38:31AM -0500, Jonah H. Harris wrote:
> Hey Simon,
> I'm doing some research into recursive query planning in terms of theory
> as-well-as actual implementation in other RDBMS. Let me get back to you when
> I have some more definitive info.

My first reaction would be to have a sort of Repeat node, with two
subnodes, the Tail and the Loop. The procedure would be to extract a
tuple from the Tail (optionally returning it). Then put that tuple
as the input to the Loop and start pulling tuples out of that.

Problem is, those new tuples may have to be sent through the loop again
so you have a buffering problem. But it seems a fairly generic way of
dealing with it.

Ofcourse, once you've done that, you might be getting very close to a
Turing complete executor, no? :)

Have a nice day,

> On 11/14/05, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >
> > On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote:
> >
> > > I am working on the standard WITH syntax for recursive query support
> > > and hope to get it into 8.2.
> >
> > Sounds interesting.
> >
> > What approach are you taking to the plan shape? The current approach
> > would be to have additional plan nodes for each join. Coping with a
> > dynamic number of operations will do interesting things in the planner.
> >
> > I face a similar dynamic problem with joins to partitioned tables.
> >
> > Do you have any thoughts about this area?
> >
> > Best Regards, Simon Riggs
> >
> >
> >

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On T, 2005-11-15 at 09:53 +0100, Martijn van Oosterhout wrote:
> On Tue, Nov 15, 2005 at 01:38:31AM -0500, Jonah H. Harris wrote:
> > Hey Simon,
> > I'm doing some research into recursive query planning in terms of theory
> > as-well-as actual implementation in other RDBMS. Let me get back to you when
> > I have some more definitive info.
>
> My first reaction would be to have a sort of Repeat node, with two
> subnodes, the Tail and the Loop. The procedure would be to extract a
> tuple from the Tail (optionally returning it). Then put that tuple
> as the input to the Loop and start pulling tuples out of that.

Will this work for both DEPTH FIRST and BREADTH FIRST recursion ?

> Problem is, those new tuples may have to be sent through the loop again
> so you have a buffering problem. But it seems a fairly generic way of
> dealing with it.
>
> Ofcourse, once you've done that, you might be getting very close to a
> Turing complete executor, no? :)
>
> Have a nice day,

--
Hannu Krosing <hannu(at)skype(dot)net>

On Tue, Nov 15, 2005 at 04:13:47PM +0200, Hannu Krosing wrote:
> On T, 2005-11-15 at 09:53 +0100, Martijn van Oosterhout wrote:
> > On Tue, Nov 15, 2005 at 01:38:31AM -0500, Jonah H. Harris wrote:
> > > Hey Simon,
> > > I'm doing some research into recursive query planning in terms of theory
> > > as-well-as actual implementation in other RDBMS. Let me get back to you when
> > > I have some more definitive info.
> >
> > My first reaction would be to have a sort of Repeat node, with two
> > subnodes, the Tail and the Loop. The procedure would be to extract a
> > tuple from the Tail (optionally returning it). Then put that tuple
> > as the input to the Loop and start pulling tuples out of that.
>
> Will this work for both DEPTH FIRST and BREADTH FIRST recursion ?

This would be BREADTH FIRST recursion. If you want depth first you just
need to feed the tuples in reverse order (LIFO rather than FIFO).

The only thing to keep between runs is sets of tuples, and we already
know how to do that (Sort for example).

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.