Docs: clarify cursor behavior

[tomconnors]

Using clojure.jdbc w/ Hikari CP and Postgresql 11.

When attempting to create a cursor as described in the docs the process still ends up trying to load the whole result set into memory.

(def conn (jdbc/connection pool))
(def cursor (jdbc/fetch-lazy conn "SELECT \"uuid\", data_bytes FROM visitor_data;"))
(def cursor-seq (jdbc/cursor->lazyseq cursor)) ;; hangs here

If I explicitly set auto-commit and the fetch size I don't have that problem:

(def conn (jdbc/connection pool))
(def cursor (jdbc/fetch-lazy conn "SELECT \"uuid\", data_bytes FROM visitor_data;"))
(.setFetchSize (.-stmt cursor) 50)
(.setAutoCommit (.getConnection (.-stmt cursor)) false)
(def cursor-seq (jdbc/cursor->lazyseq cursor))

is this a documentation problem or is the library expected to modify those settings for us?

[shilder]

That's postgresql-specific, but yes, docs could be improved

https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor

[shilder]

You can pass fetch-size as option to fetch-lazy and evaluate code inside transaction to avoid setAutoCommit so you code should look like this:

(jdbc/atomic conn
  (with-open [cursor (jdbc/fetch-lazy conn "SELECT \"uuid\", data_bytes FROM visitor_data" {:fetch-size 50})]
    (doseq [row (jdbc/cursor->lazyseq cursor)]
      ;; Do something with each row
      )))

atomic will open transaction (setting autoCommit to false) and fetch-size will be passed to prepared statement by fetch-lazy (that should be mentioned in docs). You also need to close cursor after processing data.

[tomconnors]

Ah, I wasn't aware that :fetch-size was an option for fetch-lazy. That'll solve the problem for me. Might I suggest a couple documentation changes (that I'd be happy to make)?

• Describe the options map for fetch-lazy and prepared-statement in the docstrings or with specs. The only place I see this in the code is in jdbc.impl - it appears the options are :result-type, :result-concurency (note that "concurrency" is misspelled), :fetch-size, :max-rows, :holdability, and :returning, but I don't see the usage of those described on the documentation site.
• Add "api documentation" as a header to the doc site so it's in the table of contents.

[shilder]

I think any documentation improvements would be nice and useful