From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Florian Pflug <fgp(at)phlo(dot)org> |
Cc: | Dave Cramer <pg(at)fastcrypt(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Generating a query that never returns |
Date: | 2011-09-19 15:59:45 |
Message-ID: | 20110919155945.GD13009@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2011-09-19 16:00:18 | Re: Range Types - typo + NULL string constructor |
Previous Message | Enrico Pirozzi | 2011-09-19 15:57:09 | Re: A little pg_dump patch |