Search code examples
postgresqljdbcclojure

clojure.java.jdbc lazy query

I have a query that is basically a select *. In development this table is only 30000 rows, but in production it will much bigger. So I want to consume this query lazily. Why is the query below not lazy? I am using Postgres 9.5.4.1.

(do
  (def pg-uri {:connection-uri "jdbc:postgresql://localhost/..."})
  (def row (atom 0))
  (take 10 (clojure.java.jdbc/query 
          pg-uri
          ["select * from mytable"]
          {:fetch-size 10
           :auto-commit false
           :row-fn (fn [r] (swap! row inc))}))
  @row) ;;=> 300000
Solution

  • clojure.java.jdbc supports lazy processing of large result sets natively these days (the other answers here predate that native support). See the community documentation about it here:

    http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql#processing-a-result-set-lazily

    In particular, see the Additional Options? section for database-specific tweaks you might need. You can specify :auto-commit? false on any function that would open a new connection, and you can specify :fetch-size and the various cursor controls on any query-related function. See this StackOverflow question & answer for details of what PostgreSQL might need:

    Java JDBC ignores setFetchSize?

    Currently, you'll have to dig in the clojure.java.jdbc source or the prepare-statement reference documentation for more of those options. I'm continuing to work on the community documentation to surface all of that information.