Re: [HACKERS] Generating a query that never returns

2011-09-19 Thread Tom Lane 
Dave Cramer p...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Generating a query that never returns

2011-09-19 Thread Greg Sabino Mullane 

-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();

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 g...@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-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Generating a query that never returns

2011-09-19 Thread Florian Pflug 
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Generating a query that never returns

2011-09-19 Thread David Fetter 
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

Re: [HACKERS] Generating a query that never returns

2011-09-19 Thread Florian Pflug 
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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers