Re: [HACKERS] WITH RECUSIVE patches 0723

Hello

I played with CTE and I have to say, it's great feature - great work.

One questions - can I enforce materialisation of query?

It would be usefull for some analytical queries like:

with tmp as (select a, sum(b) as b from test) select * from tmp union
all select 'all', sum(b) from tmp;

regards
Pavel Stehule

2008/7/27 Tatsuo Ishii <ishii@postgresql.org>:
>> At David's request I've been looking through this patch.
>>
>> Regarding documentation: if it would help, I can write some; I have
>> already made a start on writing down what is going on internally in
>> order to understand it myself.
>
> Thanks. There was some docs written in Japanese by Yoshiyuki. Recently
> he updagted it. I will translate into English and post here.
>
>> I've found three more bugs so far:
>>
>> 1)
>>
>> create view v2(id) as values (1);
>> with recursive t(id) as (select id from v2
>> union all select id+1 from t where id < 5)
>> select * from t;
>> ERROR: could not open relation 1663/16384/24588: No such file or directory
>>
>> Here it seems that rewriting is simply not being applied to CTEs where
>> a recursive clause is present; the reference to "v2" remains in the
>> query up until execution time, at which point it errors out (in
>> ExecInitSeqScan called from InitPlan).
>
> Yes, we need to make the rewrite system to understand CTEs. Probably
> fireRIRrules() needs to have lines something like:
>
> if (rte->rtekind == RTE_RECURSIVE)
> {
> rte->non_recursive_query = fireRIRrules(rte->non_recursive_query, activeRIRs);
> continue;
> }
>
> But I still see the error message. Will look into more.
>
> For below, I will ask Yoshiyuki.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>> 2)
>>
>> with recursive t(id) as (values (1)
>> union all select id+1 from t where id < 5
>> union all values (2))
>> select * from t;
>> ERROR: table "t" has 0 columns available but 1 columns specified
>>
>> This seems to be caused by incorrect assumptions in checkWellFormedCte
>> and checkCteSelectStmt (which should have been rejecting the query).
>> The query tree as seen by checkWellFormedCte here is (values(1) union
>> all select ...) union all (values (2)), and when the left subtree is
>> passed to checkCteSelectStmt, it believes it to be non-recursive due
>> to the lack of any From clause. The unexpected error is produced
>> later.
>>
>> 3)
>>
>> with recursive t(id)
>> as (values (1)
>> union all select t.id+1
>> from t left join (values (1)) as s(x) on (false)
>> where t.id < 5)
>> select * from t;
>> id
>> ----
>> 1
>> 2
>> (2 rows)
>>
>> This behaviour is clearly intentional, since the entire mechanism of
>> estate->es_disallow_tuplestore exists for no other reason, but it
>> seems to me to be clearly wrong. What is the justification for it?
>>
>> --
>> Andrew (irc:RhodiumToad)
>>
>> --
>> 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
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers