Re: Generating a query that never returns

On Mon, Sep 19, 2011 at 05:12:15PM +0200, Florian Pflug wrote:
> On Sep19, 2011, at 16:48 , Dave Cramer wrote:
> > I have a need to test timeouts in JDBC, is there a query that is
> > guaranteed not to return ?
>
> WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT * FROM infinite)
> SELECT * FROM infinite
>
> If you declare a cursor for this statement, it will return infinitely many rows
> (all containing the value "1"). If stick a "ORDER BY value" clause at the end of
> the statement, then the first "FETCH" from the cursor will hang (since it'll attempt
> to materialize the infinitely many rows returns by the cursor).
>
> My first try, BTW, was
>
> WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT 1)
> SELECT * FROM infinite
>
> but that returns only two rows. I'd have expected it to returns an infinite
> stream of 1s as well, since the iteration part of the recursive CTE never
> returns zero rows. The behaviour I get is what I'd have expected if I had
> written "UNION" instead of "UNION ALL". Am I missing something, or is that
> a genuine bug?

That's actually the correct behavior. In order to get a recursion (or
iteration, whichever way you want to look at it), you need to refer to
the CTE on the right side of the UNION [ALL] (or the INTERSECT [ALL]
per the SQL standard).

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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