Yes, I agree that java.jdbc should not try to cater to various DBs/drivers’
quirks in terms of exactly what incantations you need to murmur in order to
achieve a specific behavior. I do believe, however, that it should go out of
its way to make sure you can murmur those incantations via java.jdbc without
needing to drop down into Java and mess about trying to create objects and pass
them into java.jdbc.
In this case, three of the criteria you mention – no auto-commit, forward-only
result set, positive fetch-size – can be handled by java.jdbc passing through
options appropriately, with the addition (coming in the next Beta) of
supporting :auto-commit? triggering a call to (.setAutoCommit con opt) on the
connection. I just noticed that to get forward-only results, you also need to
specify a :concurrency option as well (:read-only) – and that’s due to the
signature of the underlying Connection/prepareStatement() function requiring
both.
So, in 0.7.0-beta5 (or whatever it ends up being), you could do this pretty
safely, assuming >= V3 protocol and a single statement:
(into [] (take 2) (jdbc/reducible-query config/db query
{:fetch-size 500 :auto-commit? true :result-type :forward-only :concurrency
:read-only}))
In other words, if you know what options should be set on the connection and
the prepared statement, you should be able to pass them into java.jdbc and it
will make sure they get where they are needed. It doesn’t need to know why
you’re passing those options or what you’re trying to achieve.
And, yes, PostgreSQL is enough of a “special snowflake” that it probably does
deserve a wrapper around java.jdbc that “speaks” PostgreSQL-specific language.
Also note that java.jdbc already understands :dbtype of postgres, postgresql,
and pgsql – with the latter being the Impossibl driver and the other two being
the regular PostgreSQL driver.
Sean Corfield -- (970) FOR-SEAN -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood
From: Luke Burton
Sent: Wednesday, July 5, 2017 11:12 AM
To: Clojure
Cc: Java.JDBC Mailing List
Subject: Re: JDBC Connection auto-commit?: [ANN] clojure.java.jdbc 0.7.0 Beta1
I've been meaning to chime in here …
My take is that clojure.java.jdbc should not be responsible for providing
options to the driver to guarantee streaming lazy results, as much as I
understand how convenient that would be for users of the framework. It should
of course support passing all of these options and not accidentally eat them :)
My understanding so far is that JDBC has nothing to say about declaring a
result set as streamable or lazy in the sense we're looking for. The ResultSet
is stepped through item-by-item and it's an implementation detail of the driver
as to whether that is happening across an in-memory buffer or being
incrementally fetched from the network.
The PostgreSQL docs give a flavor of how many criteria have to be met before a
ResultSet can be treated this way in the specific case of PostgreSQL. If any of
these are violated it will happily fall back to buffering in-memory and you'll
be none the wiser until you OOM:
https://jdbc.postgresql.org/documentation/head/query.html
* >= V3 protocol
* no autocommit
* "forward only" ResultSet type
* single statement, no semicolons
* fetch size > 0
To me, what we need is a Clojure specific PostgreSQL framework that wraps
clojure.java.jdbc and takes care of settings like this. It could also utilize
pgjdbc-ng to provide access to LISTEN and NOTIFY, provide type conversions for
special PG only types, capitalize on core.async channels, and anything else
that could live in a more opinionated framework.
Luke.
On Jul 5, 2017, at 10:31 AM, Sean Corfield <s...@corfield.org> wrote:
Rather than doing something that requires a dependency on specific JDBC driver
classes, it seems that if an :auto-commit option in the db-spec were honored by
get-connection you would get what you needed:
(into [] (take 2) (jdbc/reducible-query (assoc config/db
:auto-commit false) query {:fetch-size 500}))
You can already pass additional options into the DriverManager/getConnection
call as properties (from the db-spec) but autocommit does not appear to be
supported in that format.
Actually, it would be cleaner if get-connection had a 2-arity accepting db-spec
and opts, and then everything could pass opts into get-connection and you could
do:
(into [] (take 2) (jdbc/reducible-query config/db query
{:fetch-size 500 :auto-commit false }))
Are there other settings that folks would find worthwhile to support here?
Feedback / comments: https://dev.clojure.org/jira/browse/JDBC-153
Sean Corfield -- (970) FOR-SEAN -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood
From: Ralf Schmitt
Sent: Wednesday, July 5, 2017 5:42 AM
To: Sean Corfield; Clojure Mailing List
Subject: Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1
Sean Corfield <s...@corfield.org> writes:
>> The required steps to setup streaming are different from database to
>> database and I guess they may change with the driver version being used.
>
> This is really the crux of the problem here – I’m not sure what java.jdbc can
> do generically to make this much easier.
Please take a look at the attachment or
https://gist.github.com/schmir/6e03b3d649950d0108a06bf6fd653dec
This is using the robert.hooke library to hook into prepare-statement.
That hook calls into a protocol function if streaming is required. This
protocol function can be defined by users for the database they are
using.
I think it would be really nice for clojure.java.jdbc to provide this
functionality.
--
Cheers
Ralf
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to clojure+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to clojure+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to clojure+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
