Thread: Generating a query that never returns
I have a need to test timeouts in JDBC, is there a query that is guaranteed not to return ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Dave Cramer <pg@fastcrypt.com> writes:
> I have a need to test timeouts in JDBC, is there a query that is
> guaranteed not to return ?
You could just do an unconstrained join between several large tables.
Or "select pg_sleep(largevalue)", depending on whether you'd like the
backend to be spitting data at you or not,
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> I have a need to test timeouts in JDBC, is there a query that is
> guaranteed not to return ?
Not *never*, but close enough:
select pg_sleep(999999999999999999999999999999999999999999999999);
Or if you want to be strict:
CREATE FUNCTION noreturn() RETURNS VOID LANGUAGE plperl AS $$ while (1) { select (undef,undef,undef,0.1) } $$;
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201109191104
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAk53WvYACgkQvJuQZxSWSsiItACg+BXmjoR9ecJWuU/AOka+/CBX
rAcAoOQi0MhHk0cWp2aFc87yvZOyY5T1
=wnlW
-----END PGP SIGNATURE-----
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
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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
On Sep19, 2011, at 17:59 , David Fetter wrote: > On Mon, Sep 19, 2011 at 05:12:15PM +0200, Florian Pflug wrote: >> 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). Interesting. Thanks for the explanation! best regards, Florian Pflug