Re: Odd Shortcut behaviour in PG14

Zahir Lalani <ZahirLalani(at)oliver(dot)agency> writes:
> Sorry Tom - let me try and clarify:
> (ekey is a variable passed into the function)

OK, so if this is a plpgsql function and ekey is a function
variable, the planner will definitely perceive this as a query
parameterized by the value of "ekey". We will consider a
"custom" plan where the value is directly substituted into the
query (allowing plan-time folding based on whether ekey is zero
or not), but we will also consider a "generic" plan where the
value of ekey is not known at plan time so no such folding occurs,
and that's probably where your failure is happening. Replanning
for every query execution is expensive so there's a preference
for using generic plans if we can.

I don't really understand why you wrote

> SELECT
> CASE WHEN (ekey > 0) THEN convert_from(crypto_secretbox_open, 'utf8')::JSON ELSE NULL END AS edata
> FROM crypto_secretbox_open(
> sc.data,
> sc.nonce,
> boxkey)

rather than just

SELECT
CASE WHEN (ekey > 0) THEN convert_from(
crypto_secretbox_open(sc.data,
sc.nonce,
boxkey),
'utf8')::JSON ELSE NULL END AS edata

I see no reason why you should feel entitled to assume that
crypto_secretbox_open won't get called in the first formulation.
The normal understanding of such a SELECT is that we evaluate
FROM and then apply the SELECT expressions to its result, so the
existence of a CASE in the SELECT expression doesn't cause the
function call in FROM to get bypassed.

Likewise, the fact that the JOIN ON condition is false seems
like a poor reason to assume that the join's input relation
won't get evaluated.

Another approach could be to force matters in the plpgsql logic:

IF ekey > 0 THEN
RETURN QUERY query-with-decryption;
ELSE
RETURN QUERY query-without-decryption;
END IF;

which seems a good deal safer than relying on undocumented details
of planner optimization behavior.

I also wonder why you don't make crypto_secretbox_open a bit
more robust --- at the very least mark it strict (RETURNS NULL
ON NULL INPUT).

regards, tom lane