Using PostgreSQL 9.3 I have been trying to define an assert
helper function to check for empty query results and similar things as follows:
CREATE FUNCTION public.assert (
in_assertion boolean,
in_errormessage text
)
RETURNS boolean
IMMUTABLE
LANGUAGE plpgsql
SECURITY INVOKER
AS $function$
BEGIN
IF NOT in_assertion THEN
RAISE EXCEPTION 'assertion failed: %', in_errormessage;
END IF;
RETURN in_assertion;
END;
$function$
;
Upon testing I found that the exception is not thrown as I would expect. For example, with CREATE TABLE emptytable (somecolumn text);
and
CREATE FUNCTION public.testassert_buggy (
out somevalue text
)
LANGUAGE sql
SECURITY DEFINER
AS $function$
WITH firstquery AS (
SELECT * FROM emptytable
), nonemptycheck AS (
SELECT assert(count(*) = 42, 'nonemptycheck failed') FROM firstquery
) SELECT * FROM firstquery;
$function$
;
I would expect a call like SELECT testassert_buggy();
to throw the exception, but instead the result is
somevalue
-----------
(1 row)
(Note that firstquery
actually returns 0 rows; the 1 row is due to this being a function with out
parameters.)
With the following small change in the second-to-last line, the exception IS thrown.
CREATE FUNCTION public.testassert (
out somevalue text
)
LANGUAGE sql
SECURITY DEFINER
AS $function$
WITH firstquery AS (
SELECT * FROM emptytable
), nonemptycheck AS (
SELECT assert(count(*) = 42, 'nonemptycheck failed') FROM firstquery
) SELECT firstquery.* FROM nonemptycheck, firstquery;
$function$
;
If I rewrite the last query switching the table list (i.e. with FROM firstquery, nonemptycheck
) there is again no exception. I'm puzzled. Is the query optimized in some way that ignores side-effects like exceptions? I tried to remove IMMUTABLE
from the definition of assert
, but that didn't make a difference.