Re: PostgreSQL gaps wrt to java, and jdbc

> On Jul 7, 2015, at 1:02 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>
> On 07/07/2015 10:51 PM, Dave Cramer wrote:
>> On 7 July 2015 at 15:46, Heikki Linnakangas <hlinnaka(at)iki(dot)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

This confused me when we went to implement this as a binary type. Isn’t it stored in a binary format? Or is that just an intermediate format that’s cached around inside the server?