[HACKERS] 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 -- 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
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
-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
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
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
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