Re: SQLJSON - Mailing list pgsql-jdbc

For what it's worth, I think bundling the JSONP API with the driver is a bad idea.  As a Maven user, every time I come
acrossa jar file where someone has "helpfully" repackaged some core Java piece into their jar, I curse them repeatedly
andthen have to try to figure out how either to split it out or somehow otherwise reconcile that they have packaged
e.g.javax.ws.rs 2.0b01 and I need to use a 2.1 feature. 

At the very least, if it must be bundled, please make it a separate jar than what Maven users get.  (To be clear I'm
totallyfine with a Maven dependency on whatever JSR spec jar is needed, I only object to bundling it) 

I am also moderately against bundling the RI, although I can at least see how it is a convenience for "get off the
groundrunning" use cases.  In particular I have multiple times run into bugs that have been baked into unrelated
librariesdue to this issue (most recently, a cglib bug preventing the library working on Java 8, with cglib shaded into
anunrelated library) 

Be sure to expect that you will now have to do driver releases for no reason other than to bundle a newer RI with a
criticalbug fix.  There are always more bugs to find, no matter how stable the software is... and by bundling it you
havemade that your problem, rather than "just get a newer version from upstream". 

I would not be opposed to having "Driver rollup" bundles like a "jdbc + jsonp-spec + jsonp-ri" jar.  I just don't want
thatcrap in my Maven build, because it *will* cause problems for moderate-level and above users :) 

On Jun 29, 2015, at 2:21 PM, Dave Cramer <pg@fastcrypt.com> wrote:

>
>
>
> On 29 June 2015 at 17:19, Christopher BROWN <brown@reflexe.fr> wrote:
> Hello,
>
> In summary, is this what is planned ?
>     • Bundle the JSONP API with the driver (specific version)
>     • Bundle the JSONP RI with the driver (specific version, with modified package names)
>     • Override getObject() to return a "JSON DOM" (buffering data once, then reconstructing it again as an object
model)
> Is that correct?  Could you remind me what does getObject() currently return for JSONB (I don't have a test case to
hand)?
>
> That is the current proposal, yes. I would expect getObject to return a string at this point. But I haven't tested it
either
>
> Dave
>
> Thanks,
> Christopher
>
>
> On 29 June 2015 at 22:58, Dave Cramer <pg@fastcrypt.com> wrote:
> On 29 June 2015 at 16:22, Markus KARG <markus@headcrashing.eu> wrote:
> Dave,
>
>
>
> I understand that people expect that getObject does something reasonable - but the case why this does not happen is
becausePostgreSQL does not provide something reasonable AFAIK, or is there JSON support in the protocol (I might have
missedthat, actually, then please just tell me)? 
>
>
>
> Well things in PostgreSQL  move at rather a glacial pace as far as features go. The emphasis is on reliability. If
youthink this mailing list is contentious you might want to have a look at hackers. To answer your question though
thereis no special backend support for JSON, or any other streaming.  Yes this is a performance drawback, but it is
whatit is.   
>
>
> But I need to tell you that you have to deal with the risk that if JDBC 5 comes up with the definition "getObject has
toreturn FancyJsonType" you'll face a backwards compatibility problem when you decided for pretty anything to return
butnot "FancyJsonType", whether the type you've choosen looked obvious to the common user, to almost any user or to
nobodyat all. Anyways, you're the boss, and I trust in you to do the right choice. :-) 
>
>
> Doing nothing is surely the wrong choice. The question is what to do. I don't pretend to know the right answer
here.Howeverin the absence of the spec, I'm sure whatever we do will be a compromise. 
>
>
>
> Regarding your metaphor, please in turn understand thar some guys in this form, like Vladimir and me, are
professionalcombustion engine engineers in our main job. Our whole life is dedicated to the difference between a
vanillaBMW 318 into an M3. I hope there is place for such engineers in your team, too, and it is _not_really_ your
attitudethat you are happy once the weels don't fall away once you start the motor. We do not do it for thank or cheer,
wesimply do it because it is the attitude of our main jobs. We cannot forget our knowledge about compression and
consumptionjust because 99% of the users don't care for. And part of this knowledge is thinking of efficiency and
safetyand cost of production and unassembly for recycling, and usefulness and maintainability and testability of the
car,too, even if no user _ever_ would think about when sitting is his ride. If our demands on the professionality and
performanceof the end product is not what you like to achieve and you like to simply replace the pipe with a chromed
one,then please let us know. Otherwise our PRs will one day produce a stack overflow on GitHub, which nobody wants. ;-) 
>
>
> As I said in my earlier email I am grateful for your contributions and I would love them to continue, but we need to
keepthe majority of the users in mind. We also need to make sure in our quest for performance that we don't break
anything.
>
> Regards,
>
> Dave
>
>
>
> Have fun
>
> -Markus
>
>
>
>
>
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dave Cramer
> Sent: Montag, 29. Juni 2015 21:23
> To: Álvaro Hernández Tortosa
> Cc: List
> Subject: Re: [JDBC] SQLJSON
>
>
>
> Markus,
>
>
>
> I really value your recent input to the driver and would like it to continue but we need to keep things in
perspective.The mere fact that you are engaging in discussion on this list puts you in the 99.99999th (keep going with
9'sif you like) percentile of users. Most and I mean the rest of the people in that population just expect the driver
todo something reasonable. They don't provide JSON parsers or even know there is a difference. When they do getObject
andthat object is a json value they expect something magical to happen. We return an object they can use. 
>
>
>
> The other less than exciting fact about the driver is that it is not the central feature of PostgreSQL. Allow me a
metaphorif you will. I like cars so I'll chose to use a driveshaft. It connects the engine to the wheels. There is
nothingnew about driveshafts, although recently they have been made out of carbon fibre, but essentially they are tubes
whichconnect the crankshaft to the wheels. To continue the metaphor, when the driver applies the gas pedal they expect
thecar to move. If the driveshaft breaks then this doesn't happen. Our job is to make sure the driveshaft continues to
connectthe engine to the wheels. If we can make it out of carbon fibre so much the better, but it is a particularly
thanklessjob. 
>
>
>
>
>
> Kind Regards,
>
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
> On 29 June 2015 at 15:01, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:
>
>
>     Markus:
>
> On 29/06/15 18:26, Markus KARG wrote:
>
> Unfortunately, yes, it is weird to ask, as what you do actually is working already once you accept the technical
realitythat an application either has to use JSON SQL functions OR provide an implementation of the JSONP API to be
ableto process JsonObject. 
>
>
>     It was rhetorical. Precisely I'm saying let's include an implementation so that you could process a JsonValue. Of
course. 
>
>
>
> I actually cannot see what is so specific to PostgreSQL that pgjdbc has to support a use case that no other driver
supports.
>
>
>     Maybe that PostgreSQL has JSON, really good one, while others don't? Or let's ruin all the work done into JSON
justbecause you fear of an absolutely unrealistic class path? Really? Please.... 
>
>
>
>
>
> The argument against it is that it provides classpath clashes once pgjdbc is used in a Java EE environment, and that
oursolution might be incompatible with JDBC 5, and that our solution imposes work and complexity on the pgjdbc
contributorsjust for the sake to make you happy, without providing you any measurable benefit. 
>
>
>     Markus, when you say "to make you happy"....  please take back your words and stick to rational arguments. If you
cannot,please abandon this otherwise respectful and enriching debate. 
>
>     I'm speaking for all the users, all of them that want to use 9.4's best thing (with logical decoding permission,
ofcourse). 
>
>     Yet you only want to stop everything speaking of a class path that very likely no one will ever experience, or
somefears about JDBC5 which might be light years ahead. We will worry about JDBC5 then, but now we need to help our
users,not help them (with your help) to go to MongoDB. 
>
>
>
>
>     Álvaro
>
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>
>
>
>
>
> -Markus
>
>
>
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández Tortosa
> Sent: Sonntag, 28. Juni 2015 21:06
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
>
>
>
> On 28/06/15 17:09, Markus KARG wrote:
>
> If your application does not want to deal with JSON data then don't request it from the database, but use
http://www.postgresql.org/docs/9.4/static/functions-json.htmlin your SELECT statement. Performing a JSON extraction
functionnatively on the server and simply use the existing data types in your application not only reliefs us from
dealingwith JSON in the driver, but also will work magnitudes faster particularly on congested networks. That does work
bydefault already in the existing driver. What you ask for does not provide any benefit to the user from my view, or I
stilldo not understand you exact scenario. 
>
>
>     Markus, here you are trying to tell users what to do. I prefer to give them freedom, works best.
>
>     All I'm saying is:
>
> - Give users an API to get a javax.json.JsonValue out of a PostgreSQL column which is of type json, jsonb or the
resultof an expression which evaluates to any of those. 
>
> - Embed a JSON parser in the driver (like the RI implementation, which adds only 64Kb) so that users don't need to
loadany other code, unless they want to override the default JSON parser. 
>
>     From there, I don't care whether the user uses JSON functions at the server or the JSON API. What I want is that
itworks by default and that you can query JSON. Is this that weird to ask? 
>
>     I'm sorry, I fail to understand your solution to the problem. Would you please mind elaborating on the drawbacks
ofmy proposal? I stated in previous emails the drawbacks I see on not doing it, but I still fail to see an argument
againstthis. 
>
>     Thanks,
>
>     Álvaro
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>
>
>
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández Tortosa
> Sent: Sonntag, 28. Juni 2015 11:57
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
>
>
>
> On 28/06/15 11:49, Markus KARG wrote:
>
> You miss one essential point against bundling: An application author who wants to process JsonObject must have that
classon his classpath, even if he does not use JDBC at all. This essential is HIS problem as HE wants to process it. So
wheredoes he get that class from other than putting any JSR 253 implementation in his classpath? Again, it is simply
notpgjdbc's problem as in that scenario no JDBC is used at all. 
>
>     I don't agree, it's not a must. I may want to extract JSON data from the database and then manipulate it as
non-JSONdata. It all depends on your domain Objects. 
>
>     I already stated how bad for the user is not to have a driver that works by default. It may be as hard as adding
a64Kb to the driver. I don't understand how is this a problem, vs. the problem it creates for the user. 
>
>     Regards,
>
>     Álvaro
>
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>
>
>
>
>
> From: Álvaro Hernández Tortosa [mailto:aht@8Kdata.com]
> Sent: Sonntag, 28. Juni 2015 11:41
> To: Christopher BROWN
> Cc: Sehrope Sarkuni; Markus KARG; Dave Cramer; List
> Subject: Re: [JDBC] SQLJSON
>
>
>
>
>
> On 28/06/15 09:34, Christopher BROWN wrote:
>
> Hello,
>
>
>
> Quick contribution, I'm not answering in-line because there are already too many in-line answers and it's getting
unreadable.
>
>     • In my own applications, I use Jackson, but it's many up of different ".jar" files and has therefore no
definitiveform (you can concoct lots of combinations).  It's also quite heavy in terms of footprint, and embedding it
makesno sense, because you'd have to keep updating the driver to keep up to date with Jackson. Finally, it doesn't
actuallyimplement JSR353 (although it would be possible to create a thin wrapper), out-of-the-box (via a compatibility
API)it can read JSR-353 but it basically rebuilds a Jackson representation out of a "standard" representation.  I might
chooseJackson, but I wouldn't want to impose it or require that it be bundled with the driver (indeed, that would cause
meclassloader issues as I often update to the latest version of Jackson). 
>     Although I mentioned Jackson as a good candidate for a default implementation, you are right it does not
implementJSR353 directly (although wrappers already exist, like https://github.com/pgelinas/jackson-javax-json). But it
doesnot need to be the default choice. I did a quick test and wrapping Jackson with jaackson-javax-json and the set of
dependenciesto make it work would add 1102Kb to the Jar file. Not much IMHO, although bigger than current driver size.
Iwould not be scared however to see a 2Mb jdbc jar file. 
>
>     However, the Reference Implementation (https://jsonp.java.net/) is probably good enough and only adds 64Kb to the
Jarfile. The JSR353 is just another 32Kb, so in total 96Kb would be added if using the RI rather than Jackson. I don't
considerthis offensive. 
>
>     In summary: why not bundle then the RI? Works out of the box and does not conflict with Jackson. Want to use
Jackson?Go for it. The rest of the world would have something running out-of-the-box. 
>
>
>
>     • You can compile the driver against the JSONP API without embedding either the interfaces or an implementation.
It'stherefore an optional feature for those that require it, and it's not rocket science to add the necessary APIs to
theclasspath. 
>     • I disagree that bundling interfaces + implementation is "making it easy".  For some users, perhaps, but for
others,you're going to cause headaches due to creating classloader conflicts (when it's already bundled in their
application).
>     Technically, it's possible. But from a user perspective, not bundling an implementation means:
>
> - Understanding why the code I wrote fails with a "Provider org.glassfish.json.JsonProviderImpl not found".
> - Google that. Find that you need to add the dependency and very likely create a SPI file
(META-INF/services/javax.json.spi.JsonProvider).
> - Blame PostgreSQL for not doing that by default and shipping a half-baked driver that is not finished (won't be my
opinion,but might be user's opinion). 
> - Google again to see what JSR353-compliant implementations are out there.
> - Blame PostgreSQL again for not making this choice for you.
> - Struggling to find a compatible implementation. Find Jackson but realize requires third-party driver. Question the
"quality"of that wrapper and consider whether that would be "supported" with PostgreSQL driver. 
> - Luckily enough a search points you to a StackOverflow link that suggests to use either this Jackson wrapper or the
ReferenceImplementation (there are not many JSR353 implementations, after all). 
> - Choose without knowing which JSON parser is best.
> - Bundle the dependency, check now it works. Clean your sweat.
> - Wonder why not to choose MongoDB next time, it works out of the box.
>
>     Not funny.
>
>     The alternative is: bundle a default (the RI) and let advanced users not happy with the default implementation to
makeanother choice (basically create the META-INF/services/javax.json.spi.JsonProvider and that will override the
bundledRI). You have the best of both worlds. 
>
>     Pareto (https://en.wikipedia.org/wiki/Pareto_principle): let's make it work for 80% and let that 20% to edit a
fileand "suffer" a 64Kb size increase in the driver, rather than ask everybody to go through the above process. 
>
>
>
>
>     • If as Dave Cramer says, the PG protocol doesn't currently support streaming, it still makes sense to add
streamingsupport that reads from already fully-read resources... because in that way, if the protocol improves in the
future,client code using the streaming API will benefit (with no changes to client code) in the future. 
>     JSR353 already has a Streaming API. I don't understand what do we need to do here, it's already done.
>
>     Best regards,
>
>     Álvaro
>
>
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>
>
>
>
>
> --
>
> Christopher
>
>
>
>
>
> On 28 June 2015 at 01:53, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:
>
>
>
> On 28/06/15 00:55, Sehrope Sarkuni wrote:
>
> On Sat, Jun 27, 2015 at 6:25 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:
>
>     Hi Sehrope!
>
>
>
> Hi Álvaro! :D
>
>
>
>     To me, this is the least important question. If based on JSR353's SPI, it's trivial to swap the default, included
one,for another one. Just picking a sensible default (Jackson, for instance) is probably good enough. 
>
>
>
> I think I've used Jackson almost every time I've had to deal with JSON in Java. The mapping API is pretty cool in
thatit lets you directly create an target object type. If we got the route of adding methods to PGResultSet then we
couldhave something like: <T> T getJsonAsType(String, Class<T> clazz) 
>
>
>     That might be a nice addition. But I believe that goes beyond driver's responsibility: I think it ends when it
returnsyou the JSON type you queried (JsonObject in my previous email, but I'm correcting now myself: JsonValue) 
>
>
>
>
>
>
> I'm not wedded to Jackson though. Honestly if JS353 is the standard then that's what we should be using. We'd still
needto figure out how to handle older JVMs or maybe just selectively disable the feature (JDK8+?). 
>
>
>     JSR353 is targeted for JavaSE 6 :)
>
>
>
>
>
>
>
> #2 is driven a lot by #1 as depending on the parser implementation there may be different object types returned. JSON
isa bit tricky as "valid JSON" can mean null, a scalar, an object, or an array. Most people thing of it as just an
objectbut "foobar" is valid JSON as well. This leads us to #3... 
>
>
>     The object type to return has to be IMHO JsonObject:
http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html
>
>
>
> Not always though. All these are valid JSON too:
>
>
>
> => SELECT '1'::json AS num, '"test"'::json AS string, '[1,2,3]'::json AS arr, '{"foo":"bar"}'::json AS obj;
>
>  num | string |   arr   |      obj
>
> -----+--------+---------+---------------
>
>  1   | "test" | [1,2,3] | {"foo":"bar"}
>
> (1 row)
>
>
>
> We'll need separate getters/setters for the scalar and array types as well. I agree that most people will just be
usingthe object type though (and maybe the array). 
>
>
>     You are right here. Please s/JsonObject/JsonValue/g JsonValue is a container for any of the above including
objectsand arrays. So it would be enough just with JsonValue getJsonValue(....) 
>
>
>
>
>
>
> #3 doesn't have a straight answer as there is no getJSON(...) methods in the JDBC spec. It'd probably have to be
returnedvia getObject(...). 
>
> An alternative is to provide PGResultSet and PGPreparedStatement classes similar to PGConnection that provides PG
extensions.They could have the get/set methods (ex: getJsonScalar(...) or setJsonObject(Map<String,Object> ...)) to
retrieveJSON values as specific object types (i.e. scalar, object, array). It'd be a bit more type safe as presumably
mostpeople using json/jsonb types know the top level type of what they're storing. 
>
>
>     Probably adding methods to PG classes would be better than getObject and force explicit casts. Regarding the
methods,if they simply return JsonObject, you already have a full API there to parse and extract and process. So
anythingthat returns a JsonObject from a column (identifier or #) would be enough for me. 
>
>
>
> For most cases I think it'd be fine. I think the custom mapping I mentioned above would cover the rest. Anything
beyondthat would be a full on transformation and would be very application specific. 
>
>
>     Yepp
>
>
>
>
>
>
> For #4 I see two possible wins. First off on the usability side, there's some convenience to natively interfacing
withjson/jsonb types. It'll only have value though if those types are the same ones that users are using in the rest of
theircode. If they're just using them as Map<String,Object> everywhere then it'd still be a pain for a user to convert
toour "native" PG JSON types to use via JDBC. Having a dedicated API that allows for interaction using native Java
typeswould make this more convenient. 
>
> The other win I can see for #4 is on performance. Right now JSON is converted to a String. That means everybody using
ithas to convert it twice. First raw bytes to String, then String to object. A dedicated API could cut one of those out
ofthe way. Given how the wire protocol is implemented in the driver, it wouldn't be a direct reading from the input
stream(it'll be buffered in a byte array), but at least it won't be copied twice. 
>
>
>     As far as I know, most users are using JsonObject, so returning that is a perfect match for pgjdbc. I don't
expecthowever big performance wins as JSON is sent as a String over the wire... 
>
>
>
> The performance gain isn't on the wire, it's from not having to convert bytes => String => JsonObject. It'd be bytes
=>JsonObject or bytes => CustomObject. Less work and less GC. The bigger the JSON string, the bigger the savings too. 
>
>
>     You are right in that JSR353 allows you to create a parser directly out of an InputStream, so you would avoid
convertingto String. That's a win. The rest of the conversions are inevitable (having the latter one you pointed out
layingin user's realm, beyond driver's responsibility). 
>
>     Regards,
>
>     Álvaro
>
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>