Clojurians Log v2

Hmm, this caught my eye as I’ve implemented something like this. Basic scenario is I want to query (“stream”) data from a table and insert those results to another table. I’m using prepared insert statement, with execute-batch! and using plan (this happens inside with-transaction):

(with-open [prepared (next/prepare db [(insert-statement target-table columns)])]
    (next/execute-batch!
      prepared
      (next/plan db [query-str (into-array String ids)] opts)))

@snurppa My first reaction is: I’m surprised that even works at all! I’ll have to dig in the code to see what it is actually doing…

execute-batch! expects “parameter groups” so that should be a vector-of-vectors — are you using an array builder on plan? Or have you omitted some critical reducing function over plan?

Hi Sean, yes I’m using array builder as-unqualified-arrays, sorry I forgot to put that to the example. The opts for plan are:

{:fetch-size  1000
 ; <https://cljdoc.org/d/seancorfield/next.jdbc/1.0.462/doc/getting-started/tips-tricks#streaming-result-sets-1>
 :auto-commit false
 :concurrency :read-only,
 :cursors     :close,
 :result-type :forward-only
 :builder-fn  rs/as-unqualified-arrays}

OK. I’ll have to give this some thought and experiment with it a bit. Like I say, I’m a bit surprised it works at all… and it may be just fine (there’s no obvious “state” that I can think of in consuming an array-based plan)… and if it’s safe then it might well be something I should put in the docs because it’s not an approach I’d even thought of for this…

It looks like that code is “safe”. It is working because each row of the result set is being realized as set-parameters calls seq on it (and, you’re right that it is being reduced via the run! call — but it needs each row to be realized as well for plan to be consumed correctly).

So it is safe by virtue of the implementation of set-parameters rather than being inherently “safe”. I think I’m going to add notes to execute-batch! and set-parameters so that they preserve this behavior — I think it’s actually rather nice to be able to just execute-batch! over plan to insert the results of a query, so I’ll document that as well.

https://github.com/seancorfield/next-jdbc/issues/165

Hi Sean. Ok great, I am relieved 😄 Yes I remember the construct being pretty handy when I came up with that about 1yr ago. Don’t remember the details how it ended like that, maybe I just found that “ok seems to work” and left it like that and continued elsewhere 🙂 > It is working because each row of the result set is being realized as `set-parameters` calls `seq` on it (and, you’re right that it is being reduced via the `run!` call — but it needs each row to be realized as well for `plan` to be consumed correctly). Thanks, good information, I will check this also myself so I have better understanding! > So it is safe by virtue of the implementation of… Yeah indeed, I thought that this may be the case when you said you’re surprised it even works and it has been working in production for almost a year, haha! Thanks again for this and all your efforts for the community 🙂

I noticed your GH comment. Appreciate the testing of the approach and I agree with you that this approach seems in the end to be discouraged :thumbsup: Thanks.