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@iki.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