RE: Odd Shortcut behaviour in PG14

> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Friday, November 24, 2023 6:44 PM
> To: Zahir Lalani <ZahirLalani@oliver.agency>
> Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-
> generallists.postgresql.org <pgsql-general@lists.postgresql.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
placesrather 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