Skip site navigation (1) Skip section navigation (2)

Re: PostGreSQL and recursive queries...

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Tatsuo Ishii" <ishii(at)postgresql(dot)org>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: PostGreSQL and recursive queries...
Date: 2007-11-30 13:00:27
Message-ID: 87ir3j51n8.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
"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!

In response to

Responses

pgsql-hackers by date

Next:From: Gevik BabakhaniDate: 2007-11-30 13:31:44
Subject: .NET or Mono functions in PG
Previous:From: Magnus HaganderDate: 2007-11-30 11:32:37
Subject: Re: Time to update list of contributors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group