Sounds like you want reducible-query? @mattsfrey

(built into clojure.java.jdbc)

ah this looks like a good lead, thanks!

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

In particular, pay attention to the Additional Options? section because getting databases to actually fetch results in batches is sometimes non-trivial.

The JDBC world has changed a lot in Clojure in the last few years.

auto-commit? should be false right?

Yeah, if your DB needs that.

And you may need :result-type :forward-only and probably :fetch-size (set according to however your DB wants it).

ah I see, thanks

am I correct to make the assumption that

(jdbc/query db-spec expiration-query
              {:row-fn handle-row})

It depends what you mean by "lazily" there...

jdbc/query realizes the entire result, which is independent of whether the database fetches "lazily" (in batches/chunks).

In your code above, it would return a (fully-realized) sequence of all the results from calling handle-row on each row fetched from the database.

That's the main difference with reducible-query -- it doesn't actually do anything until you reduce it somehow, and you can control what happens with the result accumulation (so you can process a row and essentially "throw away" the result).

The "lazy" in the docs is intended to mean you can process an arbitrarily large result set without bringing it all into memory.

yes that last part is what I mean, I am really just trying to scan over a large db table without loading the entire result set into memory

you need to use a cursor and do that manually (at least with pg) otherwise the jdbc driver will just send all the result over the wire even if you do not consume it (lazily or not)

declare cursor foo for select * from bar then fetch 1000 from foo for every fetch and close foo at the end. You can turn that into a lazy seq with some effort.

not sure reducible-query helps doing that at all, at least with pg, but I am not familiar with it

@mpenet No, you do not need to manually use a cursor for this. Setting auto-commit false on the connection, setting result-type forward-only and a fetch-size is all you should need. Folks have actually verified that works on Postgres with java.jdbc BTW.

ah, that's new to me

is it documented somewhere?

(I don't use Postgres -- I have to rely on others to test & verify this stuff for me -- but all that stuff in the docs, and the underlying features I added to java.jdbc, is specifically about getting the DB to stream chunks of a result set)

If you look through the closed JIRA tickets for java.jdbc you should see where folks were testing and verifying the lazy processing behavior...

yeah, trying to find something in jira is quite hard 😛

I see only these: https://dev.clojure.org/jira/secure/IssueNavigator.jspa?

damnit, can't even link to a search

anyway... I can't find it. I ll take your word for it. I just remember I had to do the cursor dance (quite some time ago tho)

Hmm, looks like the main discussion happened somewhere else and it isn't linked in the tickets. The auto-commit : false option was definitely part of the fallout from that and was key to making Postgres stream result sets apparently...

I am fairly sure I had tried changing auto-commit back then.

The auto-commit option on opening a connection is fairly new to java.jdbc.

I was using hikaricp, so probably setting that there

sounds like documenting how to do that might be a good idea