We have a table m1with millions of records. We would like to create a table m2with calculations for each record in m1. We are currently running it as follows:
(jdbc/with-db-transaction [tx connection]
(jdbc/query tx
[(jdbc/prepare-statement (:connection tx)
"select * from m1"
{:fetch-size 1000})]
{:result-set-fn (process! [tx result-set] ...)}))
where process!-
(defn process! [tx result-set]
(jdbc/with-db-transaction [tx tx]
(jdbc/insert-multi! tx :m2 [:m2_column]
(mapv (fn [r] [(calculate r)])
result-set))))
The query selectuses cursors and is consumed lazily. See clojure.java.jdbc lazy request . This is the reason it is enclosed in an external transaction.
Questions:
- Is a (nested) transaction with millions of records problematic for Postgres? clojure.java.jdbc docs say that nested transactions are absorbed by an external one, so effectively we have only one transaction. Is it correct?
- , ? , ?