PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Question

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.

Answer 1

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.

• Chris M.: Thanks Erwin; this seems to be part of the issue, but other optimizations seem to play a role as well; for example, in WITH firstquery AS (SELECT * FROM emptytable WHERE false), nonemptycheck AS (SELECT assert(count(*) = 42, 'check failed') FROM firstquery) SELECT * FROM firstquery, nonemptycheck; the exception is not thrown, even though the outer SELECT references the inner ones. EXPLAIN ANALYZE says "never executed" for nonemptycheck. With all that in mind, is there a way to define a robust assert function?
• Erwin Brandstetter: @ChrisM.: That's a second excellent question, hidden in a comment. I suggest you edit it into your question, this might become a "go-to" question to reference in the future. I added a possible solution.