Unclear how to use transactions

[amacdougall]

(If it is in fact impossible to use transactions, feel free to update the issue title, but I didn't want to assume too much.)

Description

The JDBC functions clojure.java.jdbc/with-db-transaction and clojure.java.jdbc/db-set-rollback-only! expect to operate on a "database connection" object compatible with the get-connection function. It appears that most people pass in the db spec they used to set up their normal connection, whether it's a DB URI, a hash with connection parameters, or whatever.

However, Foundation expects to use a javax.sql.DataSource-compatible object for every query. Since one of the allowed get-connection arguments is a {:datasource <ds>, ...} object, I tried doing this:

(clojure.java.jdbc/with-db-transaction [t-conn {:datasource db/conn}]
  (jdbc/db-set-rollback-only! t-conn)
  (pg/qry-> (:datasource t-conn) (db/alter-some-stuff! params)))

Where db/conn is the result of a def-datasource call. Although this syntax does alter the database, those changes persist.

This is not surprising: with-db-transaction is supposed to bind t-conn to a connection object. In this case, it binds a hash:

{:datasource
 #object[com.impossibl.postgres.jdbc.PGDataSource 0x5f9231ae "com.impossibl.postgres.jdbc.PGDataSource@5f9231ae"],
 :connection
 #object[com.impossibl.postgres.jdbc.PGConnectionImpl 0x129eb816 "com.impossibl.postgres.jdbc.PGConnectionImpl@129eb816"],
 :level 1,
 :rollback #<Atom@79e53c70: true>}

My theory is that the :connection part of that hash is the transaction connection, and if I were to operate on that, the changes would be rolled back. But Foundation expects to work on a PGDataSource, and it gets a new connection from it, one which does not have the transaction or rollback properties.

Am I right? And if so, is there a workaround for this situation, or just a better way of achieving my goal? I think you might have some unwelcome tension between Foundation's way of doing things and the bare-bones JDBC functions people may wish to apply.

[mikeball]

Yep, you are right. There's not really a work-around because foundation departs completely from java.jdbc, so with-db-transaction and db-set-rollback-only! won't have any effect.

Sorry for the docs not being clear, but presently we create, open and close a transaction automatically for you automatically inside of the trx-> macro. (Note the qry-> macro is not transactional). Any exceptions inside of the trx-> will cause a rollback of all statements, and return of false to indicate failure.

This means that at present there isn't a way to control/begin and manually roll back a transaction outside of the trx-> block. Transactional control outside of the trx-> block, as well as specification of isolation levels is something I've yet to dive into.

Here is how I would write your function, but again you can't control the rollback.

(pg/trx-> my-db-datasource 
          (db/alter-some-stuff! params))

How big of an issue if programatic control of rollback for you?

Thanks so much for the detailed report and for continuing to try out foundation! Feel free to email me with any questions/problems/suggestions.

[mikeball]

To follow up, I've long know I needed to add some sort of customization in terms of rollback, exceptions and isolation. This is roughly what I was thinking of adding in terms of syntax.

(pg/def-trx-> test-trx-> 
  {:rollback-fn (fn [rs] true) ; always rolls back...
   :error-fn    (fn [ex] "custom error handling here...")
   :isolation   :read-committed})

(test-trx-> mydb
            (db/alter-some-stuff! params))

What I don't quite know, is if we should move the transaction up out of the trx-> macro to allow for more control.

[amacdougall]

My immediate use case is unit testing. Start a transaction, monkey around with the DB, test the results, roll the transaction back. Your proposed syntax would work fine for that.

I've never needed fine control over transactions in my own work, so I'm not qualified to comment on the general applicability of the proposal. It seems to cover the bases, but you should get a second opinion!

[mikeball]

Just a quick update, I built out something that allows you to control rollback, but it effects the return value from trx-> and I'm not real happy with it. I will need some time to think, try and consider other options, most likely passing a transaction object instead of a datasource object into the trx-> query. Given I'm going into a very busy period on other client work it's going to be at least a month or 2. Sorry about that.

[amacdougall]

No problem—I'm just continuing to use YeSQL for now. It's not perfect, but it's fine, and I can switch back over to Foundation later.