From: Tom Lane <tgl@sss.pgh.pa.us>
To: Zahir Lalani <ZahirLalani@oliver.agency>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Odd Shortcut behaviour in PG14
Date: Fri, 24 Nov 2023 13:43:32 -0500
Message-ID: <3336302.1700851412@sss.pgh.pa.us> (raw)
In-Reply-To: <DB9P251MB0546A166C121F4767D5462B9A7B8A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM>
References: <DB9P251MB0546E102DB0B567C40205976A7B9A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM>
<CANzqJaCUroe03WUn1gdGK334ivyYPus338CfvMsBknudWGqFpg@mail.gmail.com>
<3170786.1700768723@sss.pgh.pa.us>
<DB9P251MB05462CC8CAB741157521ECEEA7B8A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM>
<3307508.1700840085@sss.pgh.pa.us>
<DB9P251MB0546A166C121F4767D5462B9A7B8A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM>
Zahir Lalani <ZahirLalani@oliver.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, ZahirLalani@oliver.agency, ronljohnsonjr@gmail.com
Subject: Re: Odd Shortcut behaviour in PG14
In-Reply-To: <3336302.1700851412@sss.pgh.pa.us>
References: <DB9P251MB0546E102DB0B567C40205976A7B9A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM> <CANzqJaCUroe03WUn1gdGK334ivyYPus338CfvMsBknudWGqFpg@mail.gmail.com> <3170786.1700768723@sss.pgh.pa.us> <DB9P251MB05462CC8CAB741157521ECEEA7B8A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM> <3307508.1700840085@sss.pgh.pa.us> <DB9P251MB0546A166C121F4767D5462B9A7B8A@DB9P251MB0546.EURP251.PROD.OUTLOOK.COM> <3336302.1700851412@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox