> -----Original Message-----
> From: Tom Lane <tgl@xxxxxxxxxxxxx>
> Sent: Friday, November 24, 2023 3:35 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
> You *still* haven't defined what you mean by "fails". We can't help you
> effectively with such tiny dribs and drabs of information.
> At the very least I'd like to see the whole query, because the fragment you've
> shown us does not reveal what ekey is or why you think the system should
> believe that it is or is not zero.
> But it's also unclear why that should matter.
>
> Having said that ... if the statement is being executed with a cached plan (via a
> named statement, or PREPARE, or inside plpgsql) then maybe the problem
> occurs if the plan switches from custom to generic?
> If so, messing with the plan_cache_mode setting might provide a workaround.
>
> regards, tom lane
Sorry Tom - let me try and clarify:
(ekey is a variable passed into the function)
RETURN QUERY
SELECT
CASE WHEN (ekey = 0) THEN person.first_name ELSE (enc.edata->>'firstname')::text END AS first_name,
CASE WHEN (ekey = 0) THEN person.last_name ELSE (enc.edata->>'lastname')::text END AS last_name,
CASE WHEN (ekey = 0) THEN person.email ELSE (enc.edata->>'emailaddress')::text END AS email,
CASE WHEN (ekey = 0) THEN person.first_name || ' ' || person.last_name ELSE (enc.edata->>'firstname')::text || ' ' || (enc.edata->>'lastname')::text END AS full_name
FROM "public".person AS person
-- join to decrypt person data
LEFT OUTER JOIN "public".secure AS sc ON
CASE WHEN (ekey > 0) THEN sc.fk_org_id = org_id and sc.fk_entity_id = 6 and sc.fk_entity_obj_id = person.id ELSE false END
LEFT JOIN lateral (
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)
) enc ON (ekey > 0)
WHERE person.id = person_id
-----------------------
We have certain clients with encrypted data and this query needs to apply the decryption only if the record is deemed to be encrypted - this is determined by the variable ekey being > 0. The "failure" is the invocation of the lateral join when ekey is 0 - i.e. its not encrypted data. The crypto function will fail if sc.data is null - as will be the case for a non-encrypted record.
Our assumption - probably incorrectly it seems, is that if the expression (ekey > 0) is false, PG will not invoke the lateral join and hence not execute crypto. This has worked in PG11 and worked in 14.9. What is very odd is that in 14.10 this still seems to work in many cases, but not all. This particular function works in psql, and sometimes in the application but not always. The parameters are identical for when it works as expected and for when it does not - i.e, when it invokes the lateral even though ekey = 0
To prove that the issue lies in this area alone, we have split the above function and run specific code for the two cases of ekey which guarantees that it will never do the unexpected - this fixes the issue, but I would still like to know if our fundamental assumption about when the lateral runs was misguided
Hope that gives more context
Z
