Re: PostgreSQL gaps wrt to java, and jdbc

On 7 July 2015 at 16:02, Heikki Linnakangas <hlinnaka@iki.fi> wrote:

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