Re: SQLJSON

On 29 June 2015 at 17:19, Christopher BROWN <brown(at)reflexe(dot)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(at)fastcrypt(dot)com> wrote:
>
>> On 29 June 2015 at 16:22, Markus KARG <markus(at)headcrashing(dot)eu> wrote:
>>
>>> Dave,
>>>
>>>
>>>
>>> I understand that people expect that getObject does something reasonable
>>> - but the case why this does not happen is because PostgreSQL does not
>>> provide something reasonable AFAIK, or is there JSON support in the
>>> protocol (I might have missed that, 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 you think this mailing list
>> is contentious you might want to have a look at hackers. To answer your
>> question though there is no special backend support for JSON, or any other
>> streaming. Yes this is a performance drawback, but it is what it 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 to return FancyJsonType"
>>> you'll face a backwards compatibility problem when you decided for pretty
>>> anything to return but not "FancyJsonType", whether the type you've choosen
>>> looked obvious to the common user, to almost any user or to nobody at 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.However in 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 professional combustion engine
>>> engineers in our main job. Our whole life is dedicated to the difference
>>> between a vanilla BMW 318 into an M3. I hope there is place for such
>>> engineers in your team, too, and it is _not_really_ your attitude that you
>>> are happy once the weels don't fall away once you start the motor. We do
>>> not do it for thank or cheer, we simply do it because it is the attitude of
>>> our main jobs. We cannot forget our knowledge about compression and
>>> consumption just because 99% of the users don't care for. And part of this
>>> knowledge is thinking of efficiency and safety and 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 performance
>>> of 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 keep the 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(at)postgresql(dot)org [mailto:
>>> pgsql-jdbc-owner(at)postgresql(dot)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's if you like) percentile of users. Most and I mean the rest
>>> of the people in that population just expect the driver to do something
>>> reasonable. They don't provide JSON parsers or even know there is a
>>> difference. When they do getObject and that 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 metaphor if you will. I like cars
>>> so I'll chose to use a driveshaft. It connects the engine to the wheels.
>>> There is nothing new about driveshafts, although recently they have been
>>> made out of carbon fibre, but essentially they are tubes which connect the
>>> crankshaft to the wheels. To continue the metaphor, when the driver applies
>>> the gas pedal they expect the car to move. If the driveshaft breaks then
>>> this doesn't happen. Our job is to make sure the driveshaft continues to
>>> connect the engine to the wheels. If we can make it out of carbon fibre so
>>> much the better, but it is a particularly thankless job.
>>>
>>>
>>>
>>>
>>>
>>> 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(at)8kdata(dot)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 reality that an application
>>> either has to use JSON SQL functions OR provide an implementation of the
>>> JSONP API to be able to 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 just because 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 our solution might be
>>> incompatible with JDBC 5, and that our solution imposes work and complexity
>>> on the pgjdbc contributors just 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, of course).
>>>
>>> Yet you only want to stop everything speaking of a class path that
>>> very likely no one will ever experience, or some fears 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(at)postgresql(dot)org [
>>> mailto:pgsql-jdbc-owner(at)postgresql(dot)org <pgsql-jdbc-owner(at)postgresql(dot)org>]
>>> *On Behalf Of *Álvaro Hernández Tortosa
>>> *Sent:* Sonntag, 28. Juni 2015 21:06
>>> *To:* pgsql-jdbc(at)postgresql(dot)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.html in your
>>> SELECT statement. Performing a JSON extraction function natively on the
>>> server and simply use the existing data types in your application not only
>>> reliefs us from dealing with JSON in the driver, but also will work
>>> magnitudes faster particularly on congested networks. That does work by
>>> default already in the existing driver. What you ask for does not provide
>>> any benefit to the user from my view, or I still do 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 result of 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 load any 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 it works 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 of my proposal? I stated in
>>> previous emails the drawbacks I see on not doing it, but I still fail to
>>> see an argument against this.
>>>
>>> Thanks,
>>>
>>> Álvaro
>>>
>>>
>>> --
>>>
>>> Álvaro Hernández Tortosa
>>>
>>>
>>>
>>>
>>>
>>> -----------
>>>
>>> 8Kdata
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *From:* pgsql-jdbc-owner(at)postgresql(dot)org [
>>> mailto:pgsql-jdbc-owner(at)postgresql(dot)org <pgsql-jdbc-owner(at)postgresql(dot)org>]
>>> *On Behalf Of *Álvaro Hernández Tortosa
>>> *Sent:* Sonntag, 28. Juni 2015 11:57
>>> *To:* pgsql-jdbc(at)postgresql(dot)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 class on his classpath, even if
>>> he does not use JDBC at all. This essential is HIS problem as HE wants to
>>> process it. So where does he get that class from other than putting any JSR
>>> 253 implementation in his classpath? Again, it is simply not pgjdbc'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-JSON data. 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 a 64Kb 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(at)8Kdata(dot)com <aht(at)8Kdata(dot)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 definitive form (you can
>>> concoct lots of combinations). It's also quite heavy in terms of
>>> footprint, and embedding it makes no sense, because you'd have to keep
>>> updating the driver to keep up to date with Jackson. Finally, it doesn't
>>> actually implement 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 choose Jackson, but I wouldn't want to impose it
>>> or require that it be bundled with the driver (indeed, that would cause me
>>> classloader 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 implement JSR353 directly
>>> (although wrappers already exist, like
>>> https://github.com/pgelinas/jackson-javax-json). But it does not need
>>> to be the default choice. I did a quick test and wrapping Jackson with
>>> jaackson-javax-json and the set of dependencies to make it work would add
>>> 1102Kb to the Jar file. Not much IMHO, although bigger than current driver
>>> size. I would 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 Jar file. The JSR353 is just
>>> another 32Kb, so in total 96Kb would be added if using the RI rather than
>>> Jackson. I don't consider this 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's therefore an optional
>>> feature for those that require it, and it's not rocket science to add the
>>> necessary APIs to the classpath.
>>> - 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 Reference Implementation
>>> (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 make another choice (basically
>>> create the META-INF/services/javax.json.spi.JsonProvider and that will
>>> override the bundled RI). 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 file and "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 streaming support 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(at)8kdata(dot)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(at)8kdata(dot)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 that it lets you directly create
>>> an target object type. If we got the route of adding methods to PGResultSet
>>> then we could have 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 returns you 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 need to 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 is a bit tricky as "valid
>>> JSON" can mean null, a scalar, an object, or an array. Most people thing of
>>> it as just an object but "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 using the 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 objects and 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 returned via 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 retrieve JSON values as specific object types (i.e. scalar,
>>> object, array). It'd be a bit more type safe as presumably most people
>>> 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 anything that 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 beyond that 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 with json/jsonb types. It'll only
>>> have value though if those types are the same ones that users are using in
>>> the rest of their code. If they're just using them as Map<String,Object>
>>> everywhere then it'd still be a pain for a user to convert to our "native"
>>> PG JSON types to use via JDBC. Having a dedicated API that allows for
>>> interaction using native Java types would 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 it has to convert it
>>> twice. First raw bytes to String, then String to object. A dedicated API
>>> could cut one of those out of the 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 expect however 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 converting to String. That's a
>>> win. The rest of the conversions are inevitable (having the latter one you
>>> pointed out laying in user's realm, beyond driver's responsibility).
>>>
>>> Regards,
>>>
>>> Álvaro
>>>
>>>
>>>
>>> --
>>>
>>> Álvaro Hernández Tortosa
>>>
>>>
>>>
>>>
>>>
>>> -----------
>>>
>>> 8Kdata
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>