Re: JDBC Connection auto-commit?: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Ralf Schmitt]

Sean Corfield  writes:

> 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 }))

That would work, but I have to look at which database driver is being
used here when creating the reducible-query. I would have to pass the
right options depending on the database driver being used instead of
passing a {:use-streaming? true} as options.

In my case config/db is really read from a config.edn file and may point
to a mysql, postgresql or sqlite database.

The extend-protocol form would be provided by the user's code, not by
java.jdbc, so there's no dependency on a specific JDBC driver class.

Anyway I could live with both solutions. And I'm aware of the fact that
an additional protocol to turn on streaming also introduces additional
complexity.

Being able to pass the :auto-commit option would already be a nice
improvement since I could get rid of the outer jdbc/with-db-connection.

I hope I've made my point clear. I'm going to trust your best judgement
on this issue from now on.

Thanks again for your work on java.jdbc.

-- 
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.

Re: JDBC Connection auto-commit?: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Luke Burton]

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 
<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 
> <https://dev.clojure.org/jira/browse/JDBC-153>
>  
> Sean Corfield -- (970) FOR-SEAN -- (904) 302-SEAN
> An Architect's View -- http://corfield.org/ <http://corfield.org/>
> 
> "If you're not annoying somebody, you're not really alive."
> -- Margaret Atwood
>  
> From: Ralf Schmitt <mailto:r...@systemexit.de>
> Sent: Wednesday, July 5, 2017 5:42 AM
> To: Sean Corfield <mailto:s...@corfield.org>; Clojure Mailing List 
> <mailto:clojure@googlegroups.com>
> Subject: Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1
>  
> Sean Corfield <s...@corfield.org <mailto: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 
> <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 
> <mailto: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 
> <mailto:clojure+unsubscr...@googlegroups.com>
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en 
> <http://groups.google.com/group/clojure?hl=en>
> --- 
&

JDBC Connection auto-commit?: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Sean Corfield]

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.

Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Ralf Schmitt]

Sean Corfield  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.
(ns soka-trb.jdbc-streaming
  "streaming results in clojure.java.jdbc, when {:use-streaming? true} is passed
  as option

  hooks into jdbc/prepare-statements and calls turn-on-streaming

  Users need to extend the TurnOnStreaming protocol for their databases
  "
  (:require [clojure.java.jdbc :as jdbc]
[robert.hooke]))

(defprotocol TurnOnStreaming
  (turn-on-streaming [con opts]))

(extend-protocol TurnOnStreaming
  org.sqlite.SQLiteConnection
  (turn-on-streaming [con opts] opts)

  org.postgresql.jdbc.PgConnection
  (turn-on-streaming [con opts]
(.setAutoCommit con false)
(if (pos? (get opts :fetch-size 0))
  opts
  (assoc opts :fetch-size 1000)))

  com.mysql.jdbc.JDBC4Connection
  (turn-on-streaming [con opts]
(assoc opts :fetch-size Integer/MIN_VALUE)))

(defn prepare-statement
  ([f con sql] (prepare-statement f con sql {}))
  ([f
^java.sql.Connection con
^String sql
{:keys [return-keys result-type concurrency cursors
fetch-size max-rows timeout use-streaming?] :as opts}]
   (if use-streaming?
 (f con sql (turn-on-streaming con opts))
 (f con sql opts

(defn add-hook
  []
  (robert.hooke/add-hook #'jdbc/prepare-statement #'prepare-statement))

RE: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Sean Corfield]

> 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.

Reviewing the code, you have found a bug! The options passed to reducible-query 
are passed to the reducible result set but they are not passed to 
db-query-with-result-set which is where the :fetch-size option should be passed 
down into prepare-statement. Ugh. I’ll fix that and put out a new release.

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: Tuesday, July 4, 2017 1:29 PM
To: Sean Corfield; Clojure Mailing List
Subject: Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1

Sean Corfield <s...@corfield.org> writes:

> Did you try this:
>
> (jdbc/with-db-connection [conn config/db]
>   (.setAutoCommit (jdbc/db-find-connection conn) false)
>   (into [] (take 2) (jdbc/reducible-query conn query {:fetch-size 500}))
>
> (I don’t have your sort of data set to test on with PostgreSQL)

Hi Sean,

that didn't work, i.e. I got an OutOfMemoryError. Even if it did, it
uses a lot of boilerplate code and looks rather 'pale' in comparison to

(into [] (take 2) (jdbc/reducible-query conn query {:use-streaming? true}))

It would also be nice from a callers perspective since he does not need
to know if the query should use streaming; it would be part of the
options passed to reducible-query.

I think it would make sense to add support for streaming into java.jdbc.

I'm not sure if this is feasible.

The required steps to setup streaming are different from database to
database and I guess they may change with the driver version being used.

I think you would need some functionality to let the user hook into the
process of setting up a connection for streaming. Though java.jdbc could
provide those hooks for some commonly used databases.

-- 
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.

Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Ralf Schmitt]

Sean Corfield  writes:

> Did you try this:
>
> (jdbc/with-db-connection [conn config/db]
>   (.setAutoCommit (jdbc/db-find-connection conn) false)
>   (into [] (take 2) (jdbc/reducible-query conn query {:fetch-size 500}))
>
> (I don’t have your sort of data set to test on with PostgreSQL)

Hi Sean,

that didn't work, i.e. I got an OutOfMemoryError. Even if it did, it
uses a lot of boilerplate code and looks rather 'pale' in comparison to

(into [] (take 2) (jdbc/reducible-query conn query {:use-streaming? true}))

It would also be nice from a callers perspective since he does not need
to know if the query should use streaming; it would be part of the
options passed to reducible-query.

I think it would make sense to add support for streaming into java.jdbc.

I'm not sure if this is feasible.

The required steps to setup streaming are different from database to
database and I guess they may change with the driver version being used.

I think you would need some functionality to let the user hook into the
process of setting up a connection for streaming. Though java.jdbc could
provide those hooks for some commonly used databases.

-- 
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.

RE: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Sean Corfield]

Did you try this:

(jdbc/with-db-connection [conn config/db]
  (.setAutoCommit (jdbc/db-find-connection conn) false)
  (into [] (take 2) (jdbc/reducible-query conn query {:fetch-size 500}))

(I don’t have your sort of data set to test on with PostgreSQL)

Sent from Mail for Windows 10

From: Ralf Schmitt
Sent: Tuesday, July 4, 2017 5:54 AM
To: Clojure Mailing List; Sean Corfield
Cc: Java.JDBC Mailing List
Subject: Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1

Sean Corfield <s...@corfield.org> writes:

> A new function, reducible-query, has been added that accepts a db-spec
> and a SQL/parameters vector, and returns a “reducible collection”. The
> query doesn’t actually run until you reduce it. At that point, it sets
> up the PreparedStatement, runs the query, and processes the ResultSet
> – by creating an interim “reducible collection” version of that result
> set and delegating your reduce operation to that. The connection is
> automatically closed when reduction completes, either by processing
> the entire result set or by returning a ‘reduced’ value.

How does one force the jdbc driver to return results lazily when using
reducible-query?

Currently I'm using the following code to force postgresql into
streaming mode:

(jdbc/with-db-connection [conn config/db]
  (.setAutoCommit (jdbc/db-find-connection conn) false)
  (jdbc/query conn
  query
  {:fetch-size 500
   :result-set-fn (fn [coll] (doall (take 2 coll)))}))

(config/db is map describing a postgresql connection, query is an sql
query)

This works as expected.

Trying to to the same with reducible-query with something like:

(into [] (take 2) (jdbc/reducible-query config/db query {:fetch-size 500}))

results in a 'GC overhead limit exceeded' error. The postgresql driver
isn't streaming the results, but instead returns the whole result in one
large response.

I don't have the ability to call .setAutoCommit on the connection in
that case. 

I would very much like an easy way to turn on streaming (e.g. by passing
{:use-streaming? true} as an option).

Do you have any plans for that?

Thanks for your work on java.jdbc.

-- 
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.

Re: [ANN] clojure.java.jdbc 0.7.0 Beta 1

[Ralf Schmitt]

Sean Corfield  writes:

> A new function, reducible-query, has been added that accepts a db-spec
> and a SQL/parameters vector, and returns a “reducible collection”. The
> query doesn’t actually run until you reduce it. At that point, it sets
> up the PreparedStatement, runs the query, and processes the ResultSet
> – by creating an interim “reducible collection” version of that result
> set and delegating your reduce operation to that. The connection is
> automatically closed when reduction completes, either by processing
> the entire result set or by returning a ‘reduced’ value.

How does one force the jdbc driver to return results lazily when using
reducible-query?

Currently I'm using the following code to force postgresql into
streaming mode:

(jdbc/with-db-connection [conn config/db]
  (.setAutoCommit (jdbc/db-find-connection conn) false)
  (jdbc/query conn
  query
  {:fetch-size 500
   :result-set-fn (fn [coll] (doall (take 2 coll)))}))

(config/db is map describing a postgresql connection, query is an sql
query)

This works as expected.

Trying to to the same with reducible-query with something like:

(into [] (take 2) (jdbc/reducible-query config/db query {:fetch-size 500}))

results in a 'GC overhead limit exceeded' error. The postgresql driver
isn't streaming the results, but instead returns the whole result in one
large response.

I don't have the ability to call .setAutoCommit on the connection in
that case. 

I would very much like an easy way to turn on streaming (e.g. by passing
{:use-streaming? true} as an option).

Do you have any plans for that?

Thanks for your work on java.jdbc.

-- 
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.

[ANN] clojure.java.jdbc 0.7.0 Beta 1

[Sean Corfield]

What?
Clojure’s contrib wrapper for JDBC.

Where?
https://github.com/clojure/java.jdbc
[org.clojure/java.jdbc “0.7.0-beta1”]

Summary?
Adds reducible queries; Drops support for Clojure 1.4.0 (see below).

Details?

A new function, reducible-query, has been added that accepts a db-spec and a 
SQL/parameters vector, and returns a “reducible collection”. The query doesn’t 
actually run until you reduce it. At that point, it sets up the 
PreparedStatement, runs the query, and processes the ResultSet – by creating an 
interim “reducible collection” version of that result set and delegating your 
reduce operation to that. The connection is automatically closed when reduction 
completes, either by processing the entire result set or by returning a 
‘reduced’ value. If you’re on Clojure 1.7 or later, you can therefore use 
reducible-query with transducers. Note that the reducible collection returned 
by reducible-query can be processed multiple times and the query will be run 
each time. There’s also a reducible-result-set helper function which will 
transform a ResultSet into a single-pass reducible collection if you need to 
drop down to that level (which you can use with the existing 
db-query-with-resultset function).

Clojure version support?
This beta release supports Clojure 1.5 forward.
It implements reducible queries using CollReduce on Clojure 1.5/1.6.
It implements reducible queries using IReduce on Clojure 1.7+
I’d prefer to use IReduceInit and only support Clojure 1.7+ going 
forward.
Please provide feedback on how that might affect you:
https://www.surveymonkey.com/r/MR2HRFD

Thanks to?
Kevin Downey for opening https://dev.clojure.org/jira/browse/JDBC-99 
three years ago and Ghadi Shayban for the outline of the reducible ResultSet!

Sean Corfield -- (904) 302-SEAN -- (970) FOR-SEAN
An Architect's View -- http://corfield.org/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)




-- 
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.