Date: Fri, 18 Jul 2008 07:56:09 -0700
From: David Fetter <david@fetter.org>
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgsql-patches@postgresql.org, pgsql-hackers@postgresql.org,
	y-asaba@sraoss.co.jp
Subject: Re: [PATCHES] WITH RECUSIVE patches 0717
Message-ID: <20080718145608.GS28307@fetter.org>
References: <20080717.184025.85407905.t-ishii@sraoss.co.jp>
	<20080717160115.GE28307@fetter.org>
	<20080718.104120.08328518.t-ishii@sraoss.co.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20080718.104120.08328518.t-ishii@sraoss.co.jp>
User-Agent: Mutt/1.5.18 (2008-05-17)

On Fri, Jul 18, 2008 at 10:41:20AM +0900, Tatsuo Ishii wrote:
> > > Here is the lastest WITH RECURSIVE patches against CVS HEAD created by
> > > Yoshiyuki Asaba and minor corrections by Tatsuo Ishii.
> > 
> > I tried this patch vs. CVS HEAD used my usual configure option with
> > only --with-prefix set, then tried to make, and got:
> > 
> > make[3]: *** No rule to make target `parse_cte.o', needed by `objfiles.txt'.  Stop.
> > make[3]: Leaving directory `/home/shackle/pgsql/src/backend/parser'
> > make[2]: *** [parser-recursive] Error 2
> > make[2]: Leaving directory `/home/shackle/pgsql/src/backend'
> > make[1]: *** [all] Error 2
> > make[1]: Leaving directory `/home/shackle/pgsql/src'
> > make: *** [all] Error 2
> > 
> > Is there something missing?
> 
> Oops. I forgot to include patches against newly added files. Please
> try included patches.

This now compiles.

I have a test case that hangs and smashes.

WITH t(i) AS (
    SELECT * FROM generate_series(1,5)
)
SELECT
    t1.i,
    2*t2.i
FROM
    t AS t1
JOIN
    t AS t2 USING(i);

server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

An equivalent query without RECURSIVE breaks in a different, in some
sense even more severe, way, as in it just hands out a wrong result
set:

WITH RECURSIVE t(i) AS (
    VALUES(1::int)
UNION ALL
    SELECT i+1 FROM t WHERE i < 5
)
SELECT
    t1.i,
    2*t2.i
FROM
    t AS t1
JOIN
    t AS t2 USING(i);

 i | ?column? 
---+----------
 1 |        2
 2 |        4
 3 |        6
 4 |        8
 5 |       10
(5 rows)

While this case is trivial, others are not.  For example, if someone
wishes to do a k-deep summary on a parts explosion n levels deep, n>k,
one way to do this would be to JOIN the k-deep part of the path
enumeration to the parts greater than k deep.

What would need to be fixed in order to make the above things work?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate