Thread: CASE WHEN idiomatic for functions with side-effect?

CASE WHEN idiomatic for functions with side-effect?

From
"Joel Jacobson"
Date:
Is it idiomatic and safe to use

SELECT
  CASE boolean_expression WHEN TRUE THEN function_with_side_effects() END
...

in a query to ensure the function_with_side_effects() is only
execute if boolean_expression is true?

function_with_side_effects() is known to be a normal function,
and not an aggregate function.

The documentation at

   https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

says the following:

"But this is safe:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;"

On the other hand, it also says:

"related cases that don't obviously involve constants can occur in queries executed within functions, since the values of function arguments and local variables can be inserted into queries as constants for planning purposes. Within PL/pgSQL functions, for example, using an IF-THEN-ELSE statement to protect a risky computation is much safer than just nesting it in a CASE expression."

Can I trust PostgreSQL not to execute function_with_side_effects() unless boolean_expression IS TRUE?

Or should I rewrite my code to use a PL/pgSQL function with IF-THEN-ELSE here instead?

The affected real code: https://github.com/truthly/uniphant/blob/rls/FUNCTIONS/api/verify_assertion.sql

Many thanks for clarifying.

/Joel

Re: CASE WHEN idiomatic for functions with side-effect?

From
"David G. Johnston"
Date:
On Tue, Jan 12, 2021 at 1:14 AM Joel Jacobson <joel@compiler.org> wrote:
Is it idiomatic and safe to use

SELECT
  CASE boolean_expression WHEN TRUE THEN function_with_side_effects() END

As long as function_with_side_effects() is defined volatile it is forced to be executed at runtime, once per row.  That situation is always protected by the case expression.


"related cases that don't obviously involve constants can occur in queries executed within functions, since the values of function arguments and local variables can be inserted into queries as constants for planning purposes. Within PL/pgSQL functions, for example, using an IF-THEN-ELSE statement to protect a risky computation is much safer than just nesting it in a CASE expression."


The relevant function takes in a column argument - it is thus impossible for the planner to evaluate the expression.  And, as above, the planner respects the "volatile" attribute of functions.

David J.

Re: CASE WHEN idiomatic for functions with side-effect?

From
Tom Lane
Date:
"Joel Jacobson" <joel@compiler.org> writes:
> Is it idiomatic and safe to use
> SELECT
>   CASE boolean_expression WHEN TRUE THEN function_with_side_effects() END
> in a query to ensure the function_with_side_effects() is only
> execute if boolean_expression is true?

As long as function_with_side_effects() is properly marked volatile,
it's safe (whether it's idiomatic is in the eye of the beholder).

The issue with the divide-by-zero example is that the division operator
is marked immutable, so if the planner sees that it has constant arguments
it will try to simplify-on-sight, even within a CASE expression.

            regards, tom lane



Re: CASE WHEN idiomatic for functions with side-effect?

From
"Joel Jacobson"
Date:
Many thanks Tom and David for clarifying the semantics.

/Joel