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?

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