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 weed this for 8.4?

- CYCLE clause not supported

* do weed this for 8.4?

- the number of "partition" is limited to up to 1

* do weed 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 weed this for 8.4?

- mutually recursive queries are not supported

* do weed this for 8.4?

- mutually recursive queries are not detected

* do weed this for 8.4?

- cost of Recursive Scan is always 0

- infinit recursion is not detected

* Tom suggested let query cancel and statement_timeout handle it.

- only the last SELECT of UNION ALL can include self recursion name

- outer joins for recursive name and tables does not work

- need regression tests

- need docs (at least SELECT reference manual)

- some queries crash. Examples are following:

--non recursive term only case: crashed with V0.1 patches
WITH RECURSIVE subdepartment AS
(
-- non recursive term
SELECT * FROM department WHERE name = 'A'
)
SELECT * FROM subdepartment ORDER BY name;

-- recursive term only case: crashed with V0.1 patches
WITH RECURSIVE subdepartment AS
(
-- recursive term
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, May 27, 2008 at 12:40:58PM +0900, Tatsuo Ishii wrote:
> > > - 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.

It's good to know someone with the knowledge has some better estimate

> Also I talked with him that it would be nice we could have a kind of
> distributed source repository to co-develop patches.

This is just the kind of thing git
<http://wiki.postgresql.org/wiki/Working_with_Git> was designed for.

Who has tried it in your organization?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tatsuo Ishii wrote:
>
> 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?
>
>

Tatsuo-san,

Would it not be better to use git for this purpose?

See http://git.postgresql.org

If not we can certainly create a short life pgfoundry project for you -
that seems better than abusing the pgpool CVS repo just because you have
control of it.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> > > 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.
>
> It's good to know someone with the knowledge has some better estimate

Tom has no idea either. So it seems there's no one in the community
who could do the better estimation.

> > Also I talked with him that it would be nice we could have a kind of
> > distributed source repository to co-develop patches.
>
> This is just the kind of thing git
> <http://wiki.postgresql.org/wiki/Working_with_Git> was designed for.
>
> Who has tried it in your organization?

No one. From what I understannd from the URL above, it still needs to
exchange each member's work as diff files, which is why I want to make
up new CVS repostory. Correct me if I'm wrong.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> Tatsuo Ishii wrote:
> >
> > 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?
> >
> >
>
> Tatsuo-san,
>
> Would it not be better to use git for this purpose?
>
> See http://git.postgresql.org

As far as I can tell from the URL above to share foo's work,

1) foo publish his work to ~foo on git.postgresql.org

2) bar retrieve patches from 1) then apply to his own git braches.

I'm not sure this would improve existing method.

The method I propose would be something like this:

1) commit foo's work into the CVS repository.

2) bar does cvs update on his CVS working file.

It seems later method is easy to use. Please correct me if I'm wrong.

> If not we can certainly create a short life pgfoundry project for you -
> that seems better than abusing the pgpool CVS repo just because you have
> control of it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tatsuo Ishii <ishii@postgresql.org> writes:
> It seems later method is easy to use. Please correct me if I'm wrong.

>> If not we can certainly create a short life pgfoundry project for you -
>> that seems better than abusing the pgpool CVS repo just because you have
>> control of it.

I agree with Andrew's point that you should not permanently mess up
pgpool's CVS history with work that is entirely unrelated to pgpool.

I don't care whether you use git or CVS, but please set up a separate
repository for this effort.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

> 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 weed this for 8.4?
>
> - CYCLE clause not supported
>
> * do weed this for 8.4?
>
> - the number of "partition" is limited to up to 1
>
> * do weed 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 weed this for 8.4?
>
> - mutually recursive queries are not supported
>
> * do weed this for 8.4?
>
> - mutually recursive queries are not detected
>
> * do weed this for 8.4?
>
> - cost of Recursive Scan is always 0
>
> - infinit recursion is not detected
>
> * Tom suggested let query cancel and statement_timeout handle it.
>
> - only the last SELECT of UNION ALL can include self recursion name
>
> - outer joins for recursive name and tables does not work

Further investigations showed that it seems the standard does not
allow some cases including above. I found these in a Japanese book
which was written by someone who are one of the SQL standard
committees. Note that the book was written for SQL:1999. May be some
of the restrictions are removed in SQL:2008(still in a draft phase)
but not sure. I guess most of these will be carried in SQL:2008 since
these are required to ensure that the recursive query has a fixed
point however.

In query expressions in the WITH clause:

- EXCEPT which has a recursive query name in the right hand operator
is not allowed

- function which has recursive query name as an operator is not
allowed

- subquery which includes a recursive query name is not allowed. Note
that in the most outer query in the WITH clause subquery which
includes a recursive query name is allowed

- query which has a selection list including recursive query name
and aggregate function is not allowed

- query which has a selection list including recursive query name
and HAVING clause

- query including recursive query name and INTERSECT ALL or EXCEPT
ALL is not allowed

- query including recursive query name and FULL OUTER JOIN is not
allowed

- outer join query is not allowed if the right hand side of LEFT OUTER
JOIN has recursive query name

- outer join query is not allowed if the left hand side of RIGHT OUTER
JOIN has recursive query name

> - need regression tests
>
> - need docs (at least SELECT reference manual)
>
> - some queries crash. Examples are following:
>
> --non recursive term only case: crashed with V0.1 patches
> WITH RECURSIVE subdepartment AS
> (
> -- non recursive term
> SELECT * FROM department WHERE name = 'A'
> )
> SELECT * FROM subdepartment ORDER BY name;
>
> -- recursive term only case: crashed with V0.1 patches
> WITH RECURSIVE subdepartment AS
> (
> -- recursive term
> SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
> WHERE d.parent_department = sd.id
> )
> SELECT * FROM subdepartment ORDER BY name;
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, May 27, 2008 at 10:10:13AM +0900, Tatsuo Ishii wrote:
> 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 weed this for 8.4?
>
> - CYCLE clause not supported
>
> * do weed this for 8.4?
> ...

As long as none of these missing features asks for a complete rewrite
I'd say no to all of your "do weed this for 8.4" questions. Let's get
the basic feature in there and improve upon this for 8.5 et al.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, May 26, 2008 at 07:23:24PM -0700, David Fetter wrote:
> 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.

You'd like to cache the whole closure? Or just some stats about it?

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers