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

2008/9/9 Tatsuo Ishii <ishii@sraoss.co.jp>:
>> 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
>> >>

I am  blind, I didn't find any reason, why materialisation isn't useable.

Regards
Pavel

>> >> 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
>> >
>