Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Implement various jsonpath methods

  1. recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    jian he <jian.universality@gmail.com> — 2024-02-06T03:06:32Z

    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

  2. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    Andrew Dunstan <andrew@dunslane.net> — 2024-02-06T11:55:36Z

    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

  3. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    Jeevan Chalke <jeevan.chalke@enterprisedb.com> — 2024-02-07T11:36:08Z

    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

  4. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    jian he <jian.universality@gmail.com> — 2024-02-07T15:43:16Z

    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?

  5. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    Jeevan Chalke <jeevan.chalke@enterprisedb.com> — 2024-02-08T05:27:04Z

    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

  6. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    jian he <jian.universality@gmail.com> — 2024-02-08T08:52:00Z

    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.

  7. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    Jeevan Chalke <jeevan.chalke@enterprisedb.com> — 2024-02-09T02:02:50Z

    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

  8. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    Andrew Dunstan <andrew@dunslane.net> — 2024-02-10T17:24:58Z

    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

  9. Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability

    Jeevan Chalke <jeevan.chalke@enterprisedb.com> — 2024-02-13T13:04:27Z

    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