Re: Postgresql 9.4 / JSONB / JDBC

Correct me if I’m wrong, but streaming a parameter value is impossible unless you know its size beforehand:
http://www.postgresql.org/docs/9.4/static/protocol-message-formats.html

Under “Bind”, which does prepared statement parameter binding:

Next, the following pair of fields appear for each parameter:

Int32

The length of the parameter value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL parameter value. No value bytes follow in the NULL case.

Byten

The value of the parameter, in the format indicated by the associated format code. n is the above length.

I don’t know how you figure out the payload size without encoding the whole thing. Note that array parameters are serialized into a string before being sent over the wire.

Thanks,
- Hans

From: Christopher BROWN <brown(at)reflexe(dot)fr<mailto:brown(at)reflexe(dot)fr>>
Date: Wednesday, December 31, 2014 at 2:18 PM
To: Mikko Tiihonen <Mikko(dot)Tiihonen(at)nitorcreations(dot)com<mailto:Mikko(dot)Tiihonen(at)nitorcreations(dot)com>>
Cc: Dave Cramer <pg(at)fastcrypt(dot)com<mailto:pg(at)fastcrypt(dot)com>>, List <pgsql-jdbc(at)postgresql(dot)org<mailto:pgsql-jdbc(at)postgresql(dot)org>>
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Unless there is a requirement to devise a new solution, in the absence of any formal standard, I am aware of two similar specifications:

- http://bsonspec.org/ (for MongoDB)
- http://wiki.fasterxml.com/SmileFormatSpec (for Jackson JSON)

I've used and continue to use the latter and it works well for me.

Does the PostgreSQL client/server protocol allow for protocol upgrades, in a similar way to what HTTP defines for SPDY or websockets? That might address Mikko's remarks about backward compatibility.

How is "hstore" data transmitted? Is it optimized and could JSONB "piggyback" it?

--
Christopher

On 31 December 2014 at 19:29, Mikko Tiihonen <Mikko(dot)Tiihonen(at)nitorcreations(dot)com<mailto:Mikko(dot)Tiihonen(at)nitorcreations(dot)com>> wrote:

Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko

________________________________
From:pgsql-jdbc-owner(at)postgresql(dot)org<mailto:pgsql-jdbc-owner(at)postgresql(dot)org> <pgsql-jdbc-owner(at)postgresql(dot)org<mailto:pgsql-jdbc-owner(at)postgresql(dot)org>> on behalf of Dave Cramer <pg(at)fastcrypt(dot)com<mailto:pg(at)fastcrypt(dot)com>>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 29 December 2014 at 05:08, Christopher BROWN <brown(at)reflexe(dot)fr<mailto:brown(at)reflexe(dot)fr>> wrote:
Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec)

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown(at)reflexe(dot)fr<mailto:brown(at)reflexe(dot)fr>> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher