Recently friend of mine sent me a funny picture:

Cool, ha?!

Then I thought about plpgsql and created IsEven function:

CREATE OR REPLACE FUNCTION IsEven(n integer) 
RETURNS boolean AS $$
DECLARE 
    maxint CONSTANT integer := 2147483647;
BEGIN
    IF n = maxint THEN
	RETURN false;
    END IF;	
    RETURN IsEven(n + 2);
EXCEPTION
    WHEN numeric_value_out_of_range THEN
        RETURN true;
END;
$$
LANGUAGE plpgsql;

This was my first working plpgsql function ever! OK, let’s try it:

vseprovse=# \timing
Timing is on.
vseprovse=# SELECT IsEven(2147483647);
 iseven
--------
 f
(1 row)

Time: 3,430 ms
vseprovse=# SELECT IsEven(2147483600);
 iseven
--------
 t
(1 row)

Time: 21,731 ms
vseprovse=# SELECT IsEven(2147483000);
 iseven
--------
 t
(1 row)

Time: 55,960 ms
vseprovse=# SELECT IsEven(2147480000);
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter 
"max_stack_depth" (currently 2048kB), after 
ensuring the platform's stack depth limit 
is adequate.
CONTEXT:  SQL statement "SELECT IsEven(n + 2)"
PL/pgSQL function iseven(integer) line 8 at RETURN
SQL statement "SELECT IsEven(n + 2)"
PL/pgSQL function iseven(integer) line 8 at RETURN
...(and a hundred lines of the same error string)

Oops! Not funny. Of course, I may increase “max_stack_depth”, but… I decided to rewrite function from scratch!

CREATE OR REPLACE FUNCTION IsEvenER(n integer) 
RETURNS boolean AS $$
DECLARE 
    maxint CONSTANT integer := 2147483647;
BEGIN
    LOOP
	IF n = maxint THEN
	    RETURN false;
	END IF;	
	n := n + 2;
    END LOOP;
EXCEPTION
    WHEN numeric_value_out_of_range THEN
        RETURN true;
END;
$$
LANGUAGE plpgsql;

Let’s try:

vseprovse=# SELECT IsEvenER(2147483647);
 isevener
----------
 f
(1 row)

Time: 2,250 ms
vseprovse=# SELECT IsEvenER(2147480000);
 isevener
----------
 t
(1 row)

Time: 5,753 ms
vseprovse=# SELECT IsEvenER(2100000000);
 isevener
----------
 t
(1 row)

Time: 59088,985 ms
vseprovse=# SELECT IsEvenER(0);
^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  PL/pgSQL function isevener(integer) line 6 at IF
Time: 1173804,198 ms

I waited for 20 minutes… almost. But then I killed it. What are your timings?