recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

On 2024-02-05 Mo 22:06, jian he wrote:

Hi.
this commit [0] changes immutability of jsonb_path_query,
jsonb_path_query_first? If so, it may change other functions also.

demo:

begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')

select jsonb_path_query(s,
'$.timestamp_tz()')::text,'+10.5'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'+10.5'::text,
'time'::text from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'+10.5'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'+10.5'::text,
'date'::text from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'+10.5'::text,
'time_tz'::text from cte;

SET LOCAL TIME ZONE -8;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select jsonb_path_query(s,
'$.timestamp_tz()')::text,'-8'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'-8'::text, 'time'::text
from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'-8'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'-8'::text, 'date'::text
from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'-8'::text,
'time_tz'::text from cte;
commit;

[0]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739e

ouch. Good catch. Clearly we need to filter these like we do for the
.datetime() method.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

On Tue, Feb 6, 2024 at 5:25 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 2024-02-05 Mo 22:06, jian he wrote:

Hi.
this commit [0] changes immutability of jsonb_path_query, jsonb_path_query_first?
If so, it may change other functions also.

Thanks for reporting Jian.

Added checkTimezoneIsUsedForCast() check where ever we are casting
timezoned to non-timezoned types and vice-versa.

Thanks

demo:

begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')

select jsonb_path_query(s,
'$.timestamp_tz()')::text,'+10.5'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'+10.5'::text, 'time'::text
from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'+10.5'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'+10.5'::text, 'date'::text
from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'+10.5'::text,
'time_tz'::text from cte;

SET LOCAL TIME ZONE -8;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select jsonb_path_query(s,
'$.timestamp_tz()')::text,'-8'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'-8'::text, 'time'::text from
cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'-8'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'-8'::text, 'date'::text from
cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'-8'::text,
'time_tz'::text from cte;
commit;

[0]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739e

ouch. Good catch. Clearly we need to filter these like we do for the
.datetime() method.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

--
Jeevan Chalke

*Principal, ManagerProduct Development*

edbpostgres.com

On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Added checkTimezoneIsUsedForCast() check where ever we are casting timezoned to non-timezoned types and vice-versa.

https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?

On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com> wrote:

On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Added checkTimezoneIsUsedForCast() check where ever we are casting

timezoned to non-timezoned types and vice-versa.

https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?

OK. Added a line for the same.

Thanks

--
Jeevan Chalke

*Principal, ManagerProduct Development*

edbpostgres.com

On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com> wrote:

On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Added checkTimezoneIsUsedForCast() check where ever we are casting timezoned to non-timezoned types and vice-versa.

https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?

OK. Added a line for the same.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8..37ae2d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18240,7 +18240,11 @@ ERROR:  jsonpath member accessor can only be
applied to an object
       <type>timestamptz</type>, and <type>time</type> to <type>timetz</type>.
       However, all but the first of these conversions depend on the current
       <xref linkend="guc-timezone"/> setting, and thus can only be performed
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions.  Similarly, other
+      date/time-related methods that convert string to the date/time types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/>.  To preserve the immutability, those can
+      only be performed within timezone-aware <type>jsonpath</type> functions.
      </para>
     </note>

my proposed minor changes:
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions. Similarly, other
+      date/time-related methods that convert string to the date/time types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/> setting. Those conversions can
+      only be performed within timezone-aware <type>jsonpath</type> functions.
I don't have a strong opinion, though.

On Thu, Feb 8, 2024 at 2:22 PM jian he <jian.universality@gmail.com> wrote:

On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com>

wrote:

On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Added checkTimezoneIsUsedForCast() check where ever we are casting

timezoned to non-timezoned types and vice-versa.

https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?

OK. Added a line for the same.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8..37ae2d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18240,7 +18240,11 @@ ERROR:  jsonpath member accessor can only be
applied to an object
<type>timestamptz</type>, and <type>time</type> to
<type>timetz</type>.
However, all but the first of these conversions depend on the
current
<xref linkend="guc-timezone"/> setting, and thus can only be
performed
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions.  Similarly,
other
+      date/time-related methods that convert string to the date/time types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/>.  To preserve the immutability,
those can
+      only be performed within timezone-aware <type>jsonpath</type>
functions.
</para>
</note>

my proposed minor changes:
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions. Similarly,
other
+      date/time-related methods that convert string to the date/time types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/> setting. Those conversions can
+      only be performed within timezone-aware <type>jsonpath</type>
functions.
I don't have a strong opinion, though.

That seems fine as well. Let's leave that to the committer.

Thanks
--
Jeevan Chalke

*Principal, ManagerProduct Development*

edbpostgres.com

On 2024-02-08 Th 21:02, Jeevan Chalke wrote:

On Thu, Feb 8, 2024 at 2:22 PM jian he <jian.universality@gmail.com>
wrote:

On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

On Wed, Feb 7, 2024 at 9:13 PM jian he

<jian.universality@gmail.com> wrote:

On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Added checkTimezoneIsUsedForCast() check where ever we are

casting timezoned to non-timezoned types and vice-versa.

https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?

OK. Added a line for the same.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8..37ae2d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18240,7 +18240,11 @@ ERROR:  jsonpath member accessor can only be
applied to an object
       <type>timestamptz</type>, and <type>time</type> to
<type>timetz</type>.
       However, all but the first of these conversions depend on
the current
       <xref linkend="guc-timezone"/> setting, and thus can only
be performed
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions. 
Similarly, other
+      date/time-related methods that convert string to the
date/time types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/>.  To preserve the
immutability, those can
+      only be performed within timezone-aware
<type>jsonpath</type> functions.
      </para>
     </note>

my proposed minor changes:
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions.
Similarly, other
+      date/time-related methods that convert string to the
date/time types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/> setting. Those conversions can
+      only be performed within timezone-aware
<type>jsonpath</type> functions.
I don't have a strong opinion, though.

That seems fine as well. Let's leave that to the committer.

I edited slightly to my taste, and committed the patch. Thanks.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

On Sat, Feb 10, 2024 at 10:55 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 2024-02-08 Th 21:02, Jeevan Chalke wrote:

On Thu, Feb 8, 2024 at 2:22 PM jian he <jian.universality@gmail.com>
wrote:

On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com>

wrote:

On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:

Added checkTimezoneIsUsedForCast() check where ever we are casting

timezoned to non-timezoned types and vice-versa.

https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?

OK. Added a line for the same.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8..37ae2d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18240,7 +18240,11 @@ ERROR:  jsonpath member accessor can only be
applied to an object
<type>timestamptz</type>, and <type>time</type> to
<type>timetz</type>.
However, all but the first of these conversions depend on the
current
<xref linkend="guc-timezone"/> setting, and thus can only be
performed
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions.  Similarly,
other
+      date/time-related methods that convert string to the date/time
types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/>.  To preserve the immutability,
those can
+      only be performed within timezone-aware <type>jsonpath</type>
functions.
</para>
</note>

my proposed minor changes:
-      within timezone-aware <type>jsonpath</type> functions.
+      within timezone-aware <type>jsonpath</type> functions. Similarly,
other
+      date/time-related methods that convert string to the date/time
types
+      also do the casting and may involve the current
+      <xref linkend="guc-timezone"/> setting. Those conversions can
+      only be performed within timezone-aware <type>jsonpath</type>
functions.
I don't have a strong opinion, though.

That seems fine as well. Let's leave that to the committer.

I edited slightly to my taste, and committed the patch. Thanks.

Thank you, Andrew and Jian.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

--
Jeevan Chalke

*Principal, ManagerProduct Development*

edbpostgres.com