Обсуждение: WITH RECURSIVE patches V0.1 TODO items
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 UNIONALL 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
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. > - 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. 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
Вложения
> 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
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
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
> > > 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
> 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
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
> 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 subquerywhich 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
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!
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!
On Tue, May 27, 2008 at 04:40:41PM +0900, Tatsuo Ishii wrote: > - 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 Ah, the standard already has this. Tatsuo-san you might remember us talking about negative subqueries. This is exactly the reason for most of these rules. A recursively defined table referencing itself negativly will only be evaluatable under special circumstances and with the right set of data. I take it all these rule refer to using the recursive query inside its own definition. It's perfectly fine to use a recursively defined query inside another with clause defining a different query as a subselect. 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!
* Tatsuo Ishii <ishii@postgresql.org> [080527 01:55]: >> Would it not be better to use git for this purpose? > > > > See http://git.postgresql.org I'll not contribute to the RECURSIVE patch, but I'm more than willing to help anybody setup/learn GIT, and the various workflows it supports (yes, it can support multiple types of workflows) > 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. That's certainly *one* way. Of course, you can use CVS w/ it's web interface the same way ;-) > 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. Something like this is easily done in GIT as well:git fetch ## Fetch any new commits done in the origin to the localrepogit merge origin/master ## or any other branch you want..git push ## publish your work for others to fetch And if you really don't care about the whole "remote repo", you can usegit pull $remote $branch to combine the fetch and merge into a single command. And if you have conflicts you'll have to resolve them of course, but the tools to help resolve them are much better in GIT than in CVS. But by all means, if learning the nuanceses of a new SCM right now isn't for you, go ahead and use CVS. Us GITs will still be able to use GIT on top of the CVS repo ;-) In fact, if some of you want GIT and others just "simple CVS", GIT actually contains a "cvs server" that can server a GIT repo through the CVS protocol. But if any of you are interested in GIT, and need some help, feel free to contact me... a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
"Aidan Van Dyk" <aidan@highrise.ca> writes: > Something like this is easily done in GIT as well: > git fetch ## Fetch any new commits done in the origin to the local repo > git merge origin/master ## or any other branch you want.. > git push ## publish your work for others to fetch I would very much like to start using GIT to do this. The main difference is that when a contributor wants to merge back the changes from upstream GIT knows which changes upstream correspond to the commits the contributor made. So it can avoid a lot of conflicts when the upstream version has subsequent changes to the same areas. The end result is also a lot cleaner. Instead of a lot of commit messages that just say "applying patch from Foo" all the original separate commits can be preserved. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
> "Aidan Van Dyk" <aidan@highrise.ca> writes: > > > Something like this is easily done in GIT as well: > > git fetch ## Fetch any new commits done in the origin to the local repo > > git merge origin/master ## or any other branch you want.. > > git push ## publish your work for others to fetch > > > I would very much like to start using GIT to do this. The main difference is > that when a contributor wants to merge back the changes from upstream GIT > knows which changes upstream correspond to the commits the contributor made. > So it can avoid a lot of conflicts when the upstream version has subsequent > changes to the same areas. > > The end result is also a lot cleaner. Instead of a lot of commit messages that > just say "applying patch from Foo" all the original separate commits can be > preserved. I don't stick to CVS at all. If contributors are comfortable, let's go with GIT. BTW, does this setting requrie a local GIT server be installed? If so, that might be a problem for me since I don't have resource for that. -- Tatsuo Ishii SRA OSS, Inc. Japan
"Tatsuo Ishii" <ishii@postgresql.org> writes: > If contributors are comfortable, let's go with GIT. > > BTW, does this setting requrie a local GIT server be installed? If so, > that might be a problem for me since I don't have resource for that. Selena was saying that there was a community git server we could use for this. I didn't catch who to speak to (Josh?) to set up an account. And would we all need accounts if we want to push back changes? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Tue, May 27, 2008 at 10:30:44AM -0400, Gregory Stark wrote: > "Tatsuo Ishii" <ishii@postgresql.org> writes: > > > If contributors are comfortable, let's go with GIT. > > > > BTW, does this setting requrie a local GIT server be installed? If > > so, that might be a problem for me since I don't have resource for > > that. > > Selena was saying that there was a community git server we could use > for this. I didn't catch who to speak to (Josh?) to set up an > account. Peter Eisentraut, as I recall. > And would we all need accounts if we want to push back changes? Yep. 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
> > BTW, does this setting requrie a local GIT server be installed? If so, > > that might be a problem for me since I don't have resource for that. > > Selena was saying that there was a community git server we could use for this. > I didn't catch who to speak to (Josh?) to set up an account. > > And would we all need accounts if we want to push back changes? In my understanding, yes. And I think even if we would have accounts on the community git server, we cannot push (commit) to the repository. Probably all we can do is, get the diff between someone's pushed data and the origin. -- Tatsuo Ishii SRA OSS, Inc. Japan
* Tatsuo Ishii <ishii@postgresql.org> [080527 10:12]: > I don't stick to CVS at all. If contributors are comfortable, let's go > with GIT. > > BTW, does this setting requrie a local GIT server be installed? If so, > that might be a problem for me since I don't have resource for that. GIT is a completely distributed VCS/SCM. This means that every single local clone of a repository is a completely self-sufficent repository. So you never need to have a "local server" to do anything in GIT. When people think of "git servers", they are generally thinking of 2 things: 1) Gitweb - the "web interface" to a git repo 2) "public repositories" via git:// git protocol Neither of these are necessary to use git "locally", but are means for exchaning/sharing the current state of a repository. The git protocol is a normal send/receive transfer mechanism, of the same sort as CVS. Most people using git use it over SSH when pushing their changes to public places. The git-daemon server serves the "git://" protocol over any port (usually 9418) and is a way to give anonymous access to a git repo (usually read-only, but can be read-write) without needing to give SSH access, like cvs pserver. But the short of it is, git.postgresql.org runs both gitweb and git-daemon for you, so if you want to use git, all you need is a local git package, and SSH access to git.postgresql.org, which can do all the public serving/sharing for you. I guess I should have had a GIT talk/intro/anything over lunch or something last week at PGCon. I hadn't thought of it then... Bummer... a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
On Tue, May 27, 2008 at 12:11:54PM +0200, Michael Meskes wrote: > 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? This is getting way past my knowledge. What kind of stats could be kept? Since WITH RECURSIVE doesn't require that any DDL be issued in advance, we'd need some kind of infrastructure--possibly we have it today--which could collect those statistics on DML calls. 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
* Tatsuo Ishii <ishii@postgresql.org> [080527 10:40]: > In my understanding, yes. If you want to push back to the same location, yes, you'll all need accounts at the same location giving you permission to push there. Technically, you could all "share" an account there too, but the drawbacks to lack of accountability usually mean separate accounts is a better solution. > And I think even if we would have accounts on the community git > server, we cannot push (commit) to the repository. Probably all we can > do is, get the diff between someone's pushed data and the origin. No, it's easy to set it up so you can directly push to a shared repository, or each push to your individual repositories and "pull/merge" others changes into your own. Or any combination of the above. a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
hello everybody,
i did some testing with the existing WITH RECURSIVE patch.
i found two issues with patch version 6.
here are the details:
this works nicely and gives me the correct result. if i add a DISTINCT clause to the scenario i get a core dump inside the planner code:
test=# explain select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT DISTINCT n+1 FROM t ) SELECT * FROM t WHERE n < 5000000000) as t WHERE n < 100;server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.
the second problem seems to be even a little more tricky:
test=# select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM t ) SELECT * FROM t WHERE n < 5000000000) as t WHERE n < 100; count ------- 99(1 row)
this gives me proper answers - 99 is absolutely correct. it even executes fast so it is not producing the giant subselect before applying the outer WHERE clause.all perfect. but what happens when the < 100 is replaced with a subselect containing a WITH RECURSIVE?
test=# select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM t ) SELECT * FROM t WHERE n < 5000000000) as t WHERE n < ( select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM t ) SELECT * FROM t WHERE n < 5000000000) as t WHERE n < 100) ; count ------- 1(1 row)
the result should definitely not be 1 if i am not totally wrong.
the subselect will give me 99; so the next level should see 99 and give me 98 as the answer.
my plan looks like that:
is this a known issue already?
best regards,
hans
On May 27, 2008, at 4:23 AM, David Fetter wrote:
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. Hereare TODO items so far. Lines starting with "*" are my comments andquestions.- 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. AlwaysUNION ALL" is requried. (i.e. "non_recursive_term UNION ALLrecursive_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 0This 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 afull-on materialized views implementation? I'm guessing we'd need tobe able to cache the transitive closure of such searches.- only the last SELECT of UNION ALL can include self recursion name- outer joins for recursive name and tables does not workThis would be good to fix.- need regression tests- need docs (at least SELECT reference manual)I started on some of that, patch attached.Cheers,David.--David Fetter <david@fetter.org> http://fetter.org/Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetterSkype: davidfetter XMPP: david.fetter@gmail.comRemember to vote!--Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)To make changes to your subscription:
--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com
Thanks for the report. > hello everybody, > > i did some testing with the existing WITH RECURSIVE patch. > i found two issues with patch version 6. > here are the details: > > test=# explain select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT > 1 UNION ALL SELECT n+1 FROM t ) SELECT * FROM t WHERE n < 5000000000) > as t WHERE n < 100; > QUERY PLAN > ------------------------------------------------------------------------ > - > Aggregate (cost=0.06..0.07 rows=1 width=0) > -> Recursion on t (cost=0.00..0.05 rows=2 width=0) > -> Append (cost=0.00..0.03 rows=2 width=4) > -> Result (cost=0.00..0.01 rows=1 width=0) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 > width=4) > (5 rows) > > > this works nicely and gives me the correct result. > if i add a DISTINCT clause to the scenario i get a core dump inside > the planner code: > > test=# explain select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT > 1 UNION ALL SELECT DISTINCT n+1 FROM t ) SELECT * FROM t WHERE n < > 5000000000) as t WHERE n < 100; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. This is new to me. I will add this to the TODO list. > the second problem seems to be even a little more tricky: > > test=# select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION > ALL SELECT n + 1 FROM t ) SELECT * FROM t WHERE n < 5000000000) > as t WHERE n < 100; > count > ------- > 99 > (1 row) > > this gives me proper answers - 99 is absolutely correct. it even > executes fast so it is not producing the giant subselect before > applying the outer WHERE clause. > all perfect. but what happens when the < 100 is replaced with a > subselect containing a WITH RECURSIVE? > > test=# select count(*) from ( WITH RECURSIVE t(n) AS ( > SELECT 1 UNION ALL SELECT n + 1 FROM t > ) > SELECT * FROM t WHERE n < 5000000000) as t WHERE n < > ( > select count(*) from ( WITH RECURSIVE t(n) AS > ( > SELECT 1 UNION ALL SELECT n + 1 FROM t > ) > SELECT * FROM t WHERE n < 5000000000) as t WHERE n < > 100) ; > count > ------- > 1 > (1 row) > > > the result should definitely not be 1 if i am not totally wrong. > the subselect will give me 99; so the next level should see 99 and > give me 98 as the answer. > my plan looks like that: > > Aggregate (cost=0.13..0.14 rows=1 width=0) > InitPlan > -> Aggregate (cost=0.06..0.07 rows=1 width=0) > -> Recursion on t (cost=0.00..0.05 rows=2 width=0) > -> Append (cost=0.00..0.03 rows=2 width=4) > -> Result (cost=0.00..0.01 rows=1 width=0) > -> Recursive Scan on t (cost=0.00..0.00 > rows=1 width=4) > -> Recursion on t (cost=0.00..0.06 rows=2 width=0) > -> Append (cost=0.00..0.04 rows=2 width=4) > -> Result (cost=0.00..0.01 rows=1 width=0) > One-Time Filter: (1 < $0) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 > width=4) > (12 rows) > > is this a known issue already? This is new too. Other issue I found yesterday was VIEW + WITH RECURSIVE combo case. I will update the TODO list today. -- Tatsuo Ishii SRA OSS, Inc. Japan > best regards, > > hans > > > > > > > On May 27, 2008, at 4:23 AM, David Fetter wrote: > > > 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. > > > >> - 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. > > > > 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<recursive_query-6.patch.bz2> > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > > > -- > Cybertec Schönig & Schönig GmbH > PostgreSQL Solutions and Support > Gröhrmühlgasse 26, 2700 Wiener Neustadt > Tel: +43/1/205 10 35 / 340 > www.postgresql-support.de, www.postgresql-support.com >