Re: Using JSONB directly from application

On Mon, Feb 26, 2018 at 09:36:52PM +0800, Craig Ringer wrote:
> On 26 February 2018 at 04:05, Anthony Communier <anthony(dot)communier(at)gmail(dot)com
> > wrote:
> > It would be nice if application connected to a Postrgesql database could
> > send and receive JSONB in binary. It could save some useless text
> > conversion. All works could be done on application side which are often
> > more scalable than database itself.
>
> To support this, you'd need to extract PostgreSQL's jsonb support into a C
> library that could be used independently of backend server infrastructure
> like 'palloc' and memory contexts, ereport(), etc. Or write a parallel
> implementation.
>
> It's one of the reasons some people questioned the wisdom of doing jsonb as
> a bespoke format not using protobufs or whatever. I'm not one of them,
> since I wasn't the one doing the work, and I also know how hard it can be
> to neatly fit general purpose library code into the DB server where we
> expect it to do little things like not abort() on malloc() failure.
>
> If you're interested in writing such a library, I suggest proposing a broad
> design for how you intend to do it here. I suspect that duplicating enough
> server backend infrastructure to make the existing jsonb implementation
> friendly to frontend code would be frustrating, but maybe it's not as bad
> as I think. Certainly if you did such a thing, many people would thank you,
> because the inability to use ereport() and elog(), PG_TRY, the List API,
> etc, in FRONTEND code is a constant if minor source of irritation in
> PostgreSQL development.

A good starting point might be to take the description of the JSONB
on-disk encoding from src/include/utils/jsonb.h, fill in some of the
blanks (the encoding of scalar types is not discussed in sufficient
detail in that file, IIRC), and put it in an XML document, perhaps with
some ASCII art. Eventually this could become yet another binary
encoding of JSON that could be standardized at the IETF, say.

The JSONB format is actually really neat, and deserves wider use outside
PG, both as an interchange format, and as a storage format.

Nico
--