RE: Odd Shortcut behaviour in PG14

> -----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Sent: Friday, November 24, 2023 6:44 PM
> To: Zahir Lalani <ZahirLalani(at)oliver(dot)agency>
> Cc: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>; pgsql-
> generallists.postgresql.org <pgsql-general(at)lists(dot)postgresql(dot)org>
> Subject: Re: Odd Shortcut behaviour in PG14
>
> 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

Thank you for the detailed explanation Tom - much appreciated.

So our assumption was made as it worked for so long - ok - so we have learnt not to do that again....

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

This is indeed what we have done - we were trying to keep code maintenance down as the above requires updates in two places rather than 1 - but better that than a broken system!

> 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).

This is a third party plugin (pgsodium).

Thank you to this group for your expertise!

Z