Re: PostgreSQL gaps wrt to java, and jdbc

On 07/07/2015 11:08 PM, Kevin Wooten wrote:
>
>> 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:
>>> 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.
>
> 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?

Json is stored as text. It's really nothing more than a text field with
a validation step in the input function to check that it's valid json.
It preserves formatting and all.

Jsonb is stored in a binary format, but funnily enough its "binary
representation" in the protocol is mostly text:

> /*
> * jsonb type send function
> *
> * Just send jsonb as a version number, then a string of text
> */
> Datum
> jsonb_send(PG_FUNCTION_ARGS)
> {
> Jsonb *jb = PG_GETARG_JSONB(0);
> StringInfoData buf;
> StringInfo jtext = makeStringInfo();
> int version = 1;
>
> (void) JsonbToCString(jtext, &jb->root, VARSIZE(jb));
>
> pq_begintypsend(&buf);
> pq_sendint(&buf, version, 1);
> pq_sendtext(&buf, jtext->data, jtext->len);
> pfree(jtext->data);
> pfree(jtext);
>
> PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
> }

There's a version number there, so perhaps we'll replace that with
something more binary in the future ;-)

- Heikki