Re: recursive query crash

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> This crashes, apparently it tries to look up the result type on a NULL
>> planstate:
>> with recursive z(i) as (
>> select *
>> from t
>> union all
>> (with a(i) as (select * from z)
>> select * from a)
>> )
>> select * from z;
>
> Hmm ... I tried to fix this by changing the order in which the subplans
> get initialized, but that just moves the crash to the other subplan.
> The problem really is that what we have here is two mutually recursive
> WITH entries, and neither one can be successfully initialized in the
> executor before the other one is. I begin to see why the SQL spec
> forbids this syntax altogether.
>
> I'm inclined to prevent this case by forbidding recursive references
> inside nested WITH clauses. Thoughts?

I'm a bit puzzled where the root of the problem lies here. Surely the nested
with clause is just equivalent to a plain "select * from z" after all. Is it
just that the WITH clause is making it hard for the recursive planner to
recognize that this is in fact the recursive side of the union and needs
special treatment? What else might confuse it?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!