Re: Common Table Expressions (WITH RECURSIVE) patch

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?

> 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