pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From Clause Conditional
3+ messages / 3 participants
[nested] [flat]

* From Clause Conditional
@ 2023-02-02 12:54 Zahir Lalani <ZahirLalani@oliver.agency>
  2023-02-02 15:28 ` Re: From Clause Conditional Erik Wienhold <ewie@ewie.name>
  2023-02-02 15:29 ` Re: From Clause Conditional Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Zahir Lalani @ 2023-02-02 12:54 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Confidential

Hello All

We are testing a upgrade from pg11 to pg14 and have some issues to overcome. One of these is that we have upgraded pgsodium to the latest and there is a functional change - this question is not about sodium BTW.

So here is a sample bit of code that I will use to explain the issue - this would usually be params passed in, but I have hard coded one particular case that does not need the decryption. The code below would return decrypted data if the key is supplied (non 0) otherwise return null. This code used to work because the secret box call would allow null params. It no longer does. When the key is 0, the data passed in would be null.

LEFT JOIN lateral (
SELECT
                                CASE
WHEN (0 > 0) THEN
convert_from(crypto_secretbox_open, 'utf8')::JSON
ELSE
NULL
END AS edata
FROM
                                crypto_secretbox_open(coalesce(null, '')::bytea, coalesce(null, '')::bytea,0)
where (0>0)
) enc ON true

The issue is that, even when the key is 0, the select is still run (its part of a lateral join) and what we need to achieve is to effectively have a conditional where we only run the select if the key > 0 otherwise we return null - I have a brain freeze on this! I am sure there is an easy solution, but right now I can't see it.

Thx in advance

Z

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: From Clause Conditional
  2023-02-02 12:54 From Clause Conditional Zahir Lalani <ZahirLalani@oliver.agency>
@ 2023-02-02 15:28 ` Erik Wienhold <ewie@ewie.name>
  1 sibling, 0 replies; 3+ messages in thread

From: Erik Wienhold @ 2023-02-02 15:28 UTC (permalink / raw)
  To: Zahir Lalani <ZahirLalani@oliver.agency>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> On 02/02/2023 13:54 CET Zahir Lalani <zahirlalani@oliver.agency> wrote:
>
> Confidential
>
> Hello All
>
> We are testing a upgrade from pg11 to pg14 and have some issues to overcome.
> One of these is that we have upgraded pgsodium to the latest and there is a
> functional change – this question is not about sodium BTW.
>
> So here is a sample bit of code that I will use to explain the issue – this
> would usually be params passed in, but I have hard coded one particular case
> that does not need the decryption. The code below would return decrypted data
> if the key is supplied (non 0) otherwise return null. This code used to work
> because the secret box call would allow null params. It no longer does. When
> the key is 0, the data passed in would be null.
>
> LEFT JOIN lateral (
> SELECT
> CASE
> WHEN (0 > 0) THEN
> convert_from(crypto_secretbox_open, 'utf8')::JSON
> ELSE
> NULL
> END AS edata
> FROM
> crypto_secretbox_open(coalesce(null, '')::bytea, coalesce(null, '')::bytea,0)
> where (0>0)
> ) enc ON true
>
> The issue is that, even when the key is 0, the select is still run (its part
> of a lateral join) and what we need to achieve is to effectively have a
> conditional where we only run the select if the key > 0 otherwise we return
> null – I have a brain freeze on this! I am sure there is an easy solution,
> but right now I can’t see it.

Looks similar to a post from a few days ago:
https://www.postgresql.org/message-id/flat/CAALojA-nHoxDr7B2k0e1-EtGMPsGPZiCVeS_ds0aHG0SEOrPxg%40mai...

I don't know pgsodium but the query optimizer will evalute crypto_secretbox_open
if the function is immutable and is called with constant arguments.

How is the key and the data passed to this query?  Is it a prepared statement or
is the query generated on the fly for specific key and data that is already
known?  In the latter case you can handle the case expression with two different
queries (one with crypto_secret_box and one without) depending on whether a key
exists or not.

--
Erik

PS: Please don't hijack threads on this mailing list.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: From Clause Conditional
  2023-02-02 12:54 From Clause Conditional Zahir Lalani <ZahirLalani@oliver.agency>
@ 2023-02-02 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2023-02-02 15:29 UTC (permalink / raw)
  To: Zahir Lalani <ZahirLalani@oliver.agency>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Zahir Lalani <ZahirLalani@oliver.agency> writes:
> LEFT JOIN lateral (
> SELECT
>                                 CASE
> WHEN (0 > 0) THEN
> convert_from(crypto_secretbox_open, 'utf8')::JSON
> ELSE
> NULL
> END AS edata
> FROM
>                                 crypto_secretbox_open(coalesce(null, '')::bytea, coalesce(null, '')::bytea,0)
> where (0>0)
> ) enc ON true

> The issue is that, even when the key is 0, the select is still run (its part of a lateral join) and what we need to achieve is to effectively have a conditional where we only run the select if the key > 0 otherwise we return null - I have a brain freeze on this! I am sure there is an easy solution, but right now I can't see it.

Is crypto_secretbox_open marked IMMUTABLE?  I think recent PG versions
are capable of folding this all to a constant if so, whereas older
ones might not have.  If you care about when/whether crypto_secretbox_open
runs, then it must have side-effects, so it shouldn't be IMMUTABLE.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-02-02 15:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-02 12:54 From Clause Conditional Zahir Lalani <ZahirLalani@oliver.agency>
2023-02-02 15:28 ` Erik Wienhold <ewie@ewie.name>
2023-02-02 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>

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