Re: SQL/JSON: functions

On 7/14/20 9:47 PM, Nikita Glukhov wrote:
> Attached 49th version of the patches with two new patches #5 and #6.
>
> On 15.07.2020 00:09, Andrew Dunstan wrote:
>> On 7/14/20 1:00 PM, Andrew Dunstan wrote:
>>> On 7/5/20 1:29 PM, Justin Pryzby wrote:
>>>> On Mon, Mar 23, 2020 at 08:28:52PM +0300, Nikita Glukhov wrote:
>>>>> Attached 47th version of the patches.
>>>> The patch checker/cfbot says this doesn't apply ; could you send a rebasified
>>>> version ?
>>>>
>>> To keep things moving, I've rebased these patches. However, 1) the docs
>>> patches use <replaceble class="parameter"> in many cases where they
>>> should now just use <parameter> 
> I haven't touched <replaceable class="parameter"> yet, because I'm not sure
> if <replaceable> or <parameter> is correct here at all.



Here's the relevant commit message that explains the policy:


commit 47046763c3
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon May 4 13:48:30 2020 -0400

    Doc: standardize markup a bit more.
   
    We had a mishmash of <replaceable>, <replaceable class="parameter">,
    and <parameter> markup for operator/function arguments.  Use <parameter>
    consistently for things that are in fact names of parameters (including
    OUT parameters), reserving <replaceable> for things that aren't.  The
    latter class includes some made-up-by-the-docs type class names, like
    "numeric_type", as well as placeholders for arguments that don't have
    well-defined types.  Possibly we could do better with those categories
    as well, but for the moment I'm content not to have parameter names
    marked up in different ways in different places.



>
>> Turns out these patches also need to get the message on the new way of
>> writing entries in func.sgml - I'll publish some updates on that in the
>> next day or so so that "make doc" will succeed.
> I can do it by myself, but I just need to understand what to fix and how.



Here's an example that shows how it was transformed for json_agg:


-     <row>
-      <entry>
-       <indexterm>
-        <primary>json_agg</primary>
-       </indexterm>
-       <function>json_agg(<replaceable
class="parameter">expression</replaceable>)</function>
-      </entry>
-      <entry>
-       <type>any</type>
-      </entry>
-      <entry>
-       <type>json</type>
-      </entry>
-      <entry>No</entry>
-      <entry>aggregates values, including nulls, as a JSON array</entry>
-     </row>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>json_agg</primary>
+        </indexterm>
+        <function>json_agg</function> ( <type>anyelement</type> )
+        <returnvalue>json</returnvalue>
+       </para>
+       <para role="func_signature">
+        <indexterm>
+         <primary>jsonb_agg</primary>
+        </indexterm>
+        <function>jsonb_agg</function> ( <type>anyelement</type> )
+        <returnvalue>jsonb</returnvalue>
+       </para>
+       <para>
+        Collects all the input values, including nulls, into a JSON array.
+        Values are converted to JSON as per <function>to_json</function>
+        or <function>to_jsonb</function>.
+       </para></entry>
+       <entry>No</entry>
+      </row>
 



>>> and b) patch 4 fails when run under force_parallel=regress.
> Fixed parallel-safety check for RETURNING clause of JSON_EXISTS().
>
> On 05.04.2020 19:50, Alexander Korotkov wrote:
> > 1) Uniqueness checks using JsonbUniqueCheckContext and
> > JsonUniqueCheckContext have quadratic complexity over number of keys.
> > That doesn't look good especially for jsonb, which anyway sorts object
> > keys before object serialization.
> > 2) We have two uniqueness checks for json type, which use
> > JsonbUniqueCheckContext and JsonUniqueState.  JsonUniqueState uses
> > stack of hashes, while JsonbUniqueCheckContext have just plain array
> > of keys.  I think we can make JsonUniqueState use single hash, where
> > object identifies would be part of hash key.  And we should replace
> > JsonbUniqueCheckContext with JsonUniqueState.  That would eliminate
> > extra entities and provide reasonable complexity for cases, which now
> > use JsonbUniqueCheckContext.
>
> Unique checks were refactored as Alexander proposed.
>
> > 3) New SQL/JSON clauses don't use timezone and considered as immutable
> > assuming all the children are immutable.  Immutability is good, but
> > ignoring timezone in all the cases is plain wrong.  The first thing we
> > can do is to use timezone and make SQL/JSON clauses stable.  But that
> > limits their usage in functional and partial indexes.  I see couple of
> > things we can do next (one of them or probably both).
> > 3.1) Provide user a way so specify that we should ignore timezone in
> > particular case (IGNORE TIMEZONE clause or something like that).  Then
> > SQL/JSON clause will be considered as immutable.
> > 3.2) Automatically detect whether jsonpath might use timezone.  If
> > jsonpath doesn't use .datetime() method, it doesn't need timezone for
> > sure.  Also, from the datetime format specifiers we can get that we
> > don't compare non-timezoned values to timezoned values.  So, if we
> > detect this jsonpath never uses timezone, we can consider SQL/JSON
> > clause as immutable.
>
> Implemented second variant with automatic detection.
>
> I also tried to add explicit IGNORE TIMEZONE / IMMUTABLE clauses, but all of
> them lead to shift/reduce conflicts that seem not easy to resolve.




Not sure if this helps:


https://wiki.postgresql.org/wiki/Debugging_the_PostgreSQL_grammar_(Bison)



>
>
>
> Patch #5 implements functions for new JSON type that is expected to appear in
> the upcoming SQL/JSON standard:
>
>  - JSON() is for constructing JSON typed values from JSON text.
>    It is almost equivalent to text::json[b] cast, except that it has additional
>    ability to specify WITH UNIQUE KEYS constraint.
>
>  - JSON_SCALAR() is for constructing JSON typed values from SQL scalars.
>    It is equivalent to to_json[b]().
>
>  - JSON_SERIALIZE() is for serializing JSON typed values to character strings.
>    It is almost equivalent to json[b]::character_type cast, but it also
>    supports output to bytea.
>
> Upcoming Oracle 20c will have JSON datatype and these functions [1], so we
> decided also to implement them for compatibility, despite that they do not make
> sense for real PG users.
>


Are these functions in the standard, or are they Oracle extensions? If
the latter maybe they belong in an options extension.



>
> Patch #6 allows the user to use PG jsonb type as an effective implementation of
> SQL JSON type.  By explicitly setting GUC sql_json = jsonb, JSON will be mapped
> to jsonb, and JSON TEXT (may be named named differently) will be mapped to json.
>
> This seems to be a hack, but we failed to propose something more simpler.



What is going to be the effect of that on things like index expressions?
This strikes me at first glance as something of a potential footgun, but
maybe I'm being overcautious.


cheers


andrew




-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services