Re: PostGreSQL and recursive queries...

"Tatsuo Ishii" <ishii(at)postgresql(dot)org> writes:

> We decided to start working on WITH RECURSIVE too. Currently one of
> our engineers is about to start to look at what has been done and what
> is remaining. We hope to work together with you!

Here's the original message where I posted what I think we need in the
executor to make this work:

http://archives.postgresql.org/pgsql-hackers/2007-01/msg01495.php

Here's another thread where we discussed some further issues:

http://archives.postgresql.org/pgsql-hackers/2007-02/msg01229.php

This is all about the executor though, which I've since learned not to expect
to be the source of the headaches. The planner is infinitely more complex and
subtle.

Hopefully at the cte call sites we'll be able to gin up enough information to
fill in the subquery information enough for the planner above to work with it.
I could imagine problems the planner would have to deal with though, such as
what type is "bogon" in this query?

WITH RECURSIVE x(bogon) AS (select bogon from x) select * from x;

what about something like:

WITH RECURSIVE x(bogon) AS (select bogon+1 from x) select * from x;

note that the usual case is something like:

WITH RECURSIVE x(bogon)
AS (SELECT 1
UNION ALL
SELECT bogon+1
FROM x)
SELECT *
FROM x
WHERE bogon < ?

So the we can't refuse just anything where the types are recursively
dependent. We might have to do something weird like make the types of a
recursive call "unknown" until it's planned then go back and replan recursive
queries making use of the new information to catch things like:

create function foo(int) returns text ...
create function foo(text) returns int ...

with recursive x(bogon)
as (select 1 union all select foo(bogon) from x)
select * from x

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