Обсуждение: JSON output from psql

On Fri, May 8, 2020 at 7:32 PM Gurjeet Singh <gurjeet@singh.im> wrote:
> There's no standard format that comes to mind, but perhaps an output format similar to that of (array of
row_to_json())would be desirable. For example, `select relname, relnamespace from pg_class;` would emit the following:
 
>
> [
> {"relname": "pgclass", "relnamespace": 11},
> {"relname": "pg_statistic", "relnamespace": 11},
> ]

I don't see why psql needs any special support. You can already
generate this using the existing server side functions, if you want
it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, May 11, 2020 at 4:42 PM Gurjeet Singh <gurjeet@singh.im> wrote:
> That's a good point! It might still be desirable, perhaps for performance trade-off of JSON conversion on the
client-sideinstead of on the server-side.
 

If there's a performance problem with the server's code here, we
should probably try to fix it, instead of adding the same feature on
the client side.

But also, we shouldn't start by deciding we need feature X and then
looking for the reason why we need it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, May 13, 2020 at 12:50 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, May 11, 2020 at 4:42 PM Gurjeet Singh <gurjeet@singh.im> wrote:
> > That's a good point! It might still be desirable, perhaps for performance trade-off of JSON conversion on the
client-sideinstead of on the server-side.
 
>
> If there's a performance problem with the server's code here, we
> should probably try to fix it, instead of adding the same feature on
> the client side.

Performance problem is not just about how much CPU/RAM is used on
server-side but other resources like network consumption to get the
results to the client. Anecdotally, I have heard of a case where
Oracle implemented custom Huffman Encoding for a customer to speed up
delivery of their resultset that contained just rows of true/false.

Arguably, delivering JSON (with its repeating attribute names in every
element of the array, dquotes and commas) is more network intensive
than converting the resultset to JSON on network side.

> But also, we shouldn't start by deciding we need feature X and then
> looking for the reason why we need it.

That's better than, or at least on par with, the excuses like "We
should do it because some other database does it, too" :-)

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

On Wed, May 13, 2020 at 1:14 PM Gurjeet Singh <gurjeet@singh.im> wrote:
>
> Arguably, delivering JSON (with its repeating attribute names in every
> element of the array, dquotes and commas) is more network intensive
> than converting the resultset to JSON on network side.

s/network side/client side/

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/

On 05/13/20 16:16, Gurjeet Singh wrote:
> On Wed, May 13, 2020 at 1:14 PM Gurjeet Singh <gurjeet@singh.im> wrote:
>>
>> Arguably, delivering JSON (with its repeating attribute names in every
>> element of the array, dquotes and commas) is more network intensive
>> than converting the resultset to JSON on [client] side.

Does this suggest perhaps some sort of hybrid approach, where jsonbc
could be available as a binary on-the-wire format and the client only
needs the ability to deparse it: a query on the server could marshal
the results into that form, the client negotiates the binary transfer
format, and deparses to normal JSON syntax on its end?

It seems the server-side "compression" to jsonbc should be optimizable
when what is happening is marshaling of a tabular result: what the
repeating keys are going to be is known up front.

Maybe could use a transient (or session lifetime?) 'external' dictionary
that gets generated and sent to the client. but not stored in
pg_jsonb_dict?

Seems like a lot of work just to get json-shaped query results from psql,
but maybe the ability to receive jsonbc on the wire would be of interest
to drivers generally.

Regards,
-Chap

On Wed, May 13, 2020 at 2:01 PM Chapman Flack <chap@anastigmatix.net> wrote:
>
> Seems like a lot of work just to get json-shaped query results from psql,

+1. If we look at the amount of work needed for the hybrid approach
you describe, compared to running CSV result through something like
csv2json, there's a 100% chance of the idea being shot down :-)

> but maybe the ability to receive jsonbc on the wire would be of interest
> to drivers generally.

I'm not sure of that, but then I don't have visibility into the needs
of consumers of our drivers.

Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/