Horton

Thread

  1. JSON output functions.

    Andrew Dunstan <andrew.dunstan@pgexperts.com> — 2012-02-01T23:48:28Z

    I've just been running some timings of my JSON-producing functions, in 
particular array_to_json, and comparing them with the current 
XML-producing functions. Here's a typical result:

    andrew=# explain analyse select array_to_json(array_agg(q),true)
    from (select * from pg_attribute) q;
                                                          QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------
      Aggregate  (cost=70.77..70.78 rows=1 width=203) (actual
    time=38.919..38.920 rows=1 loops=1)
        ->  Seq Scan on pg_attribute  (cost=0.00..65.01 rows=2301
    width=203) (actual time=0.007..1.454 rows=2253 loops=1)
      Total runtime: 39.300 ms
    (3 rows)

    Time: 62.753 ms
    andrew=# explain analyse select table_to_xml('pg_attribute',
    true,false,'');
                                            QUERY PLAN
    ----------------------------------------------------------------------------------------
      Result  (cost=0.00..0.26 rows=1 width=0) (actual
    time=519.170..526.737 rows=1 loops=1)
      Total runtime: 526.780 ms
    (2 rows)


As you can see, producing the JSON is a heck of a lot faster than 
producing the equivalent XML. I had thought it might be necessary for 
good performance to cache the type output info in the FunctionCallInfo 
structure, rather than fetch it for each Datum we output, but that 
doesn't seem to be so. For now I'm inclined not to proceed with that, 
and leave it as an optimization to be considered later if necessary. 
Thoughts?

cheers

andrew

  2. Re: JSON output functions.

    Abhijit Menon-Sen <ams@toroid.org> — 2012-02-02T09:35:55Z

    At 2012-02-01 18:48:28 -0500, andrew.dunstan@pgexperts.com wrote:
>
> For now I'm inclined not to proceed with that, and leave it as an
> optimization to be considered later if necessary. Thoughts?

I agree, there doesn't seem to be a pressing need to do it now.

-- ams

  3. Re: JSON output functions.

    Andrew Dunstan <andrew@dunslane.net> — 2012-02-02T15:15:33Z

    
On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote:
> At 2012-02-01 18:48:28 -0500, andrew.dunstan@pgexperts.com wrote:
>> For now I'm inclined not to proceed with that, and leave it as an
>> optimization to be considered later if necessary. Thoughts?
> I agree, there doesn't seem to be a pressing need to do it now.
>


OK, here's my final version of the patch for constructor functions. If 
there's no further comment I'll go with this.

cheers

andrew

  4. Re: JSON output functions.

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-02-02T17:20:26Z

    2012/2/2 Andrew Dunstan <andrew@dunslane.net>:
>
>
> On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote:
>>
>> At 2012-02-01 18:48:28 -0500, andrew.dunstan@pgexperts.com wrote:
>>>
>>> For now I'm inclined not to proceed with that, and leave it as an
>>> optimization to be considered later if necessary. Thoughts?
>>
>> I agree, there doesn't seem to be a pressing need to do it now.
>>
>
>
> OK, here's my final version of the patch for constructor functions. If
> there's no further comment I'll go with this.

These function are super, Thank you

Do you plan to fix a issue with row attribute names in 9.2?

Regards

Pavel

>
> cheers
>
> andrew
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

  5. Re: JSON output functions.

    Andrew Dunstan <andrew@dunslane.net> — 2012-02-02T23:46:47Z

    
On 02/02/2012 12:20 PM, Pavel Stehule wrote:
> 2012/2/2 Andrew Dunstan<andrew@dunslane.net>:
>>
>> On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote:
>>> At 2012-02-01 18:48:28 -0500, andrew.dunstan@pgexperts.com wrote:
>>>> For now I'm inclined not to proceed with that, and leave it as an
>>>> optimization to be considered later if necessary. Thoughts?
>>> I agree, there doesn't seem to be a pressing need to do it now.
>>>
>>
>> OK, here's my final version of the patch for constructor functions. If
>> there's no further comment I'll go with this.
> These function are super, Thank you
>
> Do you plan to fix a issue with row attribute names in 9.2?



Yeah. Tom did some initial work which he published here: 
<http://archives.postgresql.org/message-id/28413.1321500388%40sss.pgh.pa.us>, 
noting:

    It's not really ideal with respect to
    the ValuesScan case, because what you get seems to always be the
    hard-wired "columnN" names for VALUES columns, even if you try to
    override that with an alias
    ...
    Curiously, it works just fine if the VALUES can be folded

and later he said:

    Upon further review, this patch would need some more work even for the
    RowExpr case, because there are several places that build RowExprs
    without bothering to build a valid colnames list.  It's clearly soluble
    if anyone cares to put in the work, but I'm not personally excited
    enough to pursue it ..

I'm going to look at that issue first, since the unfolded VALUES clause seems like something of an obscure corner case. Feel free to chime in if you can.

cheers


andrew

  6. Re: JSON output functions.

    Stefan Keller <sfkeller@gmail.com> — 2012-02-05T19:31:02Z

    Hi Andrew

Nice work!

Just for completeness: Did you also think of including geometry types
in JSON output functions in later releases? There's a nice extension
of JSON called GeoJSON for a starting point.

Yours, Stefan


2012/2/3 Andrew Dunstan <andrew@dunslane.net>:
>
>
> On 02/02/2012 12:20 PM, Pavel Stehule wrote:
>>
>> 2012/2/2 Andrew Dunstan<andrew@dunslane.net>:
>>>
>>>
>>> On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote:
>>>>
>>>> At 2012-02-01 18:48:28 -0500, andrew.dunstan@pgexperts.com wrote:
>>>>>
>>>>> For now I'm inclined not to proceed with that, and leave it as an
>>>>> optimization to be considered later if necessary. Thoughts?
>>>>
>>>> I agree, there doesn't seem to be a pressing need to do it now.
>>>>
>>>
>>> OK, here's my final version of the patch for constructor functions. If
>>> there's no further comment I'll go with this.
>>
>> These function are super, Thank you
>>
>> Do you plan to fix a issue with row attribute names in 9.2?
>
>
>
>
> Yeah. Tom did some initial work which he published here:
> <http://archives.postgresql.org/message-id/28413.1321500388%40sss.pgh.pa.us>,
> noting:
>
>   It's not really ideal with respect to
>   the ValuesScan case, because what you get seems to always be the
>   hard-wired "columnN" names for VALUES columns, even if you try to
>   override that with an alias
>   ...
>   Curiously, it works just fine if the VALUES can be folded
>
> and later he said:
>
>   Upon further review, this patch would need some more work even for the
>   RowExpr case, because there are several places that build RowExprs
>   without bothering to build a valid colnames list.  It's clearly soluble
>   if anyone cares to put in the work, but I'm not personally excited
>   enough to pursue it ..
>
> I'm going to look at that issue first, since the unfolded VALUES clause
> seems like something of an obscure corner case. Feel free to chime in if you
> can.
>
>
> cheers
>
>
> andrew
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

  7. Re: JSON output functions.

    Andrew Dunstan <andrew@dunslane.net> — 2012-02-05T19:56:20Z

    
On 02/05/2012 02:31 PM, Stefan Keller wrote:
> Hi Andrew
>
> Nice work!
>
> Just for completeness: Did you also think of including geometry types
> in JSON output functions in later releases? There's a nice extension
> of JSON called GeoJSON for a starting point.


[side note: please don't top-reply on -hackers. See 
<http://idallen.com/topposting.html>]

Currently, in array_to_json and row_to_json the only special cases are:

  * record types are output as JSON records
  * array types are output as JSON arrays
  * numeric types are output without quoting
  * boolean types are output as unquoted true or false
  * NULLs are output as NULL


Everything else is output as its text representation, suitably quoted 
and escaped.

If you want to change how those operate, now rather than later would be 
the best time. But later we could certainly add various other 
foo_to_json functions for things like geometry types and hstores.

cheers

andrew