Re: PostgreSQL gaps wrt to java, and jdbc

Dave Cramer

On 7 July 2015 at 16:02, 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
>
> Surely this could be compressed ?