> -----Original Message----- > From: Tom Lane <tgl@xxxxxxxxxxxxx> > Sent: Friday, November 24, 2023 6:44 PM > To: Zahir Lalani <ZahirLalani@oliver.agency> > Cc: Ron Johnson <ronljohnsonjr@xxxxxxxxx>; pgsql- > generallists.postgresql.org <pgsql-general@xxxxxxxxxxxxxxxxxxxx> > 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
