Re: PostgreSQL gaps wrt to java, and jdbc

On 07/07/2015 10:51 PM, Dave Cramer wrote:
> On 7 July 2015 at 15:46, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
>> On 07/07/2015 09:31 PM, Kevin Wooten wrote:
>>
>>> == Binary type coercion ==
>>> The biggest impediment I’ve run into is the fact that PG will not
>>> attempt coercion on binary types. If you specify a binary type in the
>>> protocol, that’s the final word. A better system would be to have PG
>>> perform the same coercion it does for text types and then send the
>>> results back with coerced types, in binary; finally falling back to
>>> text when binary cannot be produced. This would solve almost every
>>> issue we have had to work around in the “ng” driver.
>>>
>>
>> Can you elaborate? I'm confused, because the server can produce binary
>> output for any datatype that has binary output functions. That covers all
>> built-in datatypes and all extension datatypes people use in practice.
>>
>> What I've imagined to be a problem, though, is that you have to either
>> understand the binary representation of all datatypes in the client, or you
>> have to request binary for only those datatypes you can handle. And to know
>> which datatype a result set's column has, you have to Describe it before
>> fetching the results, which adds an extra round-trip. So it would be handy
>> if the driver could provide the server a list of datatypes that should be
>> sent as binary, as a one-time operation at the beginning of the session for
>> example, rather than indicating for each column in each query separately.
>>
> Is there a way to get json and or xml out as binary ?

Well, as far as the protocol is considered, yes. However, the "binary
representation" of json and xml datatypes is not too exciting; here's
the binary send function for json for example:

/*
  * Binary send.
  */
Datum
json_send(PG_FUNCTION_ARGS)
{
         text       *t = PG_GETARG_TEXT_PP(0);
         StringInfoData buf;

         pq_begintypsend(&buf);
         pq_sendtext(&buf, VARDATA_ANY(t), VARSIZE_ANY_EXHDR(t));
         PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
}

I.e. it just sends the string with a bytea header. There is no benefit
to using that instead of the regular text representation. That dummy
representation is used just so that e.g. binary-format pg_dump works.

- Heikki