Re: Generating a query that never returns

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?

Just FYI, this question should probably have gone to -general, not -hackers.

best regards,
Florian Pflug