PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

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.

Unreferenced CTEs are not executed at all - except data-modifying CTEs.

Related thread on pgsql-bugs with Tom Lane explaining the behavior.

In your first example you have:

SELECT * FROM firstquery;

No reference to the CTE nonemptycheck. So the CTE is never executed.

In the second example you have:

SELECT firstquery.* FROM nonemptycheck, firstquery;

nonemptycheck is referenced, so it is executed, resulting in the exception.
No row

Your added test case in the comment fails for a similar reason. Since the first CTE returns no row, the outer SELECT returns no row. There is no need to execute the second CTE, since the result will not be displayed. The optimizer's job is to avoid fruitless work ..

Appending nonemptycheck as cross-joined subquery (CROSS JOIN or appended after comma) instead of a second CTE does not help, either. A similar optimization avoids execution: Since firstquery returns no row, there is no point in evaluating the nonemptycheck, even in a subquery:

WITH firstquery AS (
   SELECT *
   FROM   emptytable
   WHERE  FALSE
   )
SELECT f.*     -- even if you append ", n.*" to SELECT list
FROM   firstquery f
    , (
   SELECT assert(count(*) = 42, 'check failed')
   FROM   firstquery
   ) n;         -- not executed

Solution

You can force evaluation with a FULL OUTER JOIN:

WITH firstquery AS (
   SELECT *
   FROM   emptytable
   WHERE  false
   )
SELECT f.*
FROM   firstquery f
FULL   JOIN (
   SELECT assert(count(*) = 42, 'check failed')
   FROM   firstquery
   ) nonemptycheck ON true;  -- always executed

Side effect: This would return a single row filled with NULL values, when firstquery returns no row. Not in this particular case, though, since your assert raises an exception in this case.

StackExchange Database Administrators Q#69648, answer score: 9