Re: Common Table Expressions (WITH RECURSIVE) patch - Mailing list pgsql-hackers

> Hello
> 
> 2008/9/9 Tatsuo Ishii <ishii@postgresql.org>:
> >> On Tue, 2008-09-09 at 13:45 +0900, Tatsuo Ishii wrote:
> >> > Thanks for the review.
> >> >
> >> > >   The standard specifies that non-recursive WITH should be evaluated
> >> > >   once.
> >> >
> >> > What shall we do? I don't think there's a easy way to fix this. Maybe
> >> > we should not allow WITH clause without RECURISVE?
> >>
> >> My interpretation of 7.13: General Rules: 2.b is that it should be
> >> single evaluation, even if RECURSIVE is present.
> >>
> >> The previous discussion was here:
> >>
> >> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php
> >>
> >> The important arguments in the thread seemed to be:
> >>
> >> 1. People will generally expect single evaluation, so might be
> >> disappointed if they can't use this feature for that purpose.
> >>
> >> 2. It's a spec violation in the case of volatile functions.
> >>
> >> 3. "I think this is a "must fix" because of the point about volatile
> >> functions --- changing it later will result in user-visible semantics
> >> changes, so we have to get it right the first time."
> >>
> >> I don't entirely agree with #3. It is user-visible, but only in the
> >> sense that someone is depending on undocumented multiple-evaluation
> >> behavior.
> >>
> >> Tom Lane said that multiple evaluation is grounds for rejection:
> >> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01318.php
> >>
> >> Is there hope of correcting this before November?
> >
> > According to Tom, to implement "single evaluation" we need to make big
> > infrastructure enhancement which is likely slip the schedule for 8.4
> > release which Tom does not want.
> 
> why? why don't use a materialisation?

See:
http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php

> >
> > So as long as Tom and other people think that is a "must fix", there
> > seems no hope probably.
> >
> > Anyway I will continue to work on existing patches...
> > --
> 
> I would to see your patch in core early. I am working on grouping sets
> and I cannot finish my patch before your patch will be commited.
> 
> Regards
> Pavel Stehule
> 
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> >
> >> > I will try to fix this. However detecting the query being not a
> >> > non-linear one is not so easy.
> >>
> >> If we don't allow mutual recursion, the only kind of non-linear
> >> recursion that might exist would be multiple references to the same
> >> recursive query name in a recursive query, is that correct?
> >>
> >> > > * DISTINCT should supress duplicates:
> >> > >
> >> > >   with recursive foo(i) as
> >> > >     (select distinct * from (values(1),(2)) t
> >> > >     union all
> >> > >     select distinct i+1 from foo where i < 10)
> >> > >   select * from foo;
> >> > >
> >> > >   This outputs a lot of duplicates, but they should be supressed
> >> > > according to the standard. This query is essentially the same as
> >> > > supporting UNION for recursive queries, so we should either fix both for
> >> > > 8.4 or block both for consistency.
> >> >
> >> > I'm not sure if it's possible to fix this. Will look into.
> >> >
> >>
> >> Can't we just reject queries with top-level DISTINCT, similar to how
> >> UNION is rejected?
> >>
> >> > > * outer joins on a recursive reference should be blocked:
> >> > >
> >> > >   with recursive foo(i) as
> >> > >     (values(1)
> >> > >     union all
> >> > >     select i+1 from foo left join (values(1)) t on (i=column1))
> >> > >   select * from foo;
> >> > >
> >> > >   Causes an infinite loop, but the standard says using an outer join
> >> > >   in this situation should be prohibited. This should be fixed for 8.4.
> >> >
> >> > Not an issue, I think.
> >>
> >> Agreed, Andrew Gierth corrected me here.
> >>
> >> Regards,
> >>       Jeff Davis
> >>
> >>
> >> --
> >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-hackers
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >