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 <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
