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> > --- > 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 > <mailto:clojure+unsubscr...@googlegroups.com>. > For more options, visit https://groups.google.com/d/optout > <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.
