Re: PostgreSQL gaps wrt to java, and jdbc

> On Jul 7, 2015, at 1:30 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>
> 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
>

Apologies I wasn’t paying enough attention to see the first example wasn’t JSONB. Seems crazy to add another format indirection with the “version” field. Seems the field itself would have solved whatever problems would have been encountered with just spitting out the binary type in the first place.