postgresql - clojure.java.jdbc lazy query

Question

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

Answer 1

First, see https://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor.

Solved it like this.

(jdbc/with-db-transaction [tx connection]
  (jdbc/query tx
    [(jdbc/prepare-statement (:connection tx)
                              "select * from mytable"
                              {:fetch-size 10})]
     {:result-set-fn (fn [result-set] ...)}))

where :result-set-fn is a function that consumes the lazy result set.

with-db-transaction takes care of autoCommit set to false. :fetch-size is not passed from query so you have to make a prepare-statement yourself.