Re: WITH RECURSIVE patches V0.1 TODO items

> On Tue, May 27, 2008 at 10:10:13AM +0900, Tatsuo Ishii wrote:
> > Hi,
> >
> > Thanks to all who respnoded to the WITH RECURSIVE patches V0.1. Here
> > are TODO items so far. Lines starting with "*" are my comments and
> > questions.
> >
> > - SEARCH clause not supported
> >
> > * do we need this for 8.4?
>
> This would be very handy.
>
> > - CYCLE clause not supported
> >
> > * do we need this for 8.4?
> >
> > - the number of "partition" is limited to up to 1
> >
> > * do we need this for 8.4?
> >
> > - "non_recursive_term UNION recursive_term" is not supported. Always
> > UNION ALL" is requried. (i.e. "non_recursive_term UNION ALL
> > recursive_term" is supported)
> >
> > * do we need this for 8.4?
>
> Probably not.
>
> > - mutually recursive queries are not supported
> >
> > * do we need this for 8.4?
> >
> > - mutually recursive queries are not detected
> >
> > * do we need this for 8.4?
> >
> > - cost of Recursive Scan is always 0
>
> This should probably be fixed, but it leads to problems like:
>
> > - infinit recursion is not detected
> >
> > * Tom suggested let query cancel and statement_timeout handle it.
>
> Right for this case. Is there some way to estimate this short of a
> full-on materialized views implementation? I'm guessing we'd need to
> be able to cache the transitive closure of such searches.

I did some discussion with Gregory Stark and Michael Makes at
PGCon. We tend to agree that very low constant cost for Recursive Scan
(probably plain 0 is not good though) is not so bad, since this would
emit plan which hashes the result of Recusive scan in a hash join plan
which is probably not so bad for most cases.

Also I talked with him that it would be nice we could have a kind of
distributed source repository to co-develop patches. The repository
would be a very short life one (until the patches are committed). What
I have in my mind is, creating a repository for that sake on pgfoundry
or whatever place to initialy import CVS head of pgsql then give
commit rights to those who wish to work on the patches. The committers
in my mind currently are Yoshiyuki, I, David Fetter, Gregory Stark and
Michael Makes (of course new volunteers are always
welcome). Periodically (once a week or so) I incorporate diffs from
pgsql CVS head then resolve conflicts if any.

The most handy place for me to settle a CVS repository is:

http://pgfoundry.org/projects/pgpool/

I know it's a little bit miss use but...

What do you think?

> > - only the last SELECT of UNION ALL can include self recursion name
> >
> > - outer joins for recursive name and tables does not work
>
> This would be good to fix.
>
> > - need regression tests
> >
> > - need docs (at least SELECT reference manual)
>
> I started on some of that, patch attached.

Great. I will take look at.
--
Tatsuo Ishii
SRA OSS, Inc. Japan