Postgres has auto commit on by default making commit / rollback not an option

[janvladimirmostert]

val dbConnection = PgConnection.create("jdbc:postgresql://localhost:5432/mydb")
val transaction = dbConnection.createTransaction()
update(TestEntity).set(
    TestEntity.name, "BLEH ${Random().nextInt()}"
).where(TestEntity.id eq 1).executeOn(transaction)
transaction.commit()

This causes a postgres error:

Cannot commit when autoCommit is enabled.

C3P0, Hikari and regular JDBC allows you to turn off autocommit on the connection itself.

Where would be a reasonable place to add such a property, on PgConnection and its parent classes themselves?

val dbConnection = PgConnection.create(
    "jdbc:postgresql://localhost:5432/mydb", 
    ConnectionOptions(autoCommit = false
))

The workaround for now is to switch off autocommit on the DB level which is not always an option when using a DB hosted by someone else.

[codesplode]

Hi Jan,

Yes, you are correct on where to add the options. I for example use a connection pool interface that I pass through to that parses the jdbc url from json as a url property and map of options. Depending on your application, and your want for access to more options / more flexibility in creating connections, you can wrap your own connections with a squash Transaction / statement using the code you post. This is a simple example from one of my projects:

	override fun createTransaction() = createConnection().createTransaction()

	private fun createConnection():JDBCConnection = MySqlConnection {
		datasource.connection
	}

In this example, datasource.connection is a computed property that gives a connection from our connection pool each time. The createConnection() method is wrapping the pool connection in a squash MySqlConnection (squash JDBCConnection), and the JDBCConnection interface has a method to createTransaction() from it.

This allows you full control of your connections and hopefully serves to both solve your problem, and potentially help you help the project by adding options to Postgres.