With an implicit val session: DBSession in scope, concretely as a scalikejdbc.AutoSession:
Updates work
sql"""
update payments set status=${status.name} where id in ($ids)
""".update().apply()
And selects work
sql"""
select id
from payments
where status='valid'
""".map(_.long).list().apply()
But an update returning columns fails because the transaction is set as read-only.
sql"""
update payments
set status='submitted'
where status='pending'
and scheduled <= ${ZonedDateTime.now.toInstant}
returning id
""".map(_.long).iterable().apply().toIterator
org.postgresql.util.PSQLException: ERROR: cannot execute UPDATE in a read-only transaction.
The session matches inside SQLToResult with an assumption that it should be read only:
case AutoSession | ReadOnlyAutoSession => DB.readOnly(f)
I've tried creating my own DBSession in order to avoid matching this pattern, but gave up on that approach. The closest I got to getting it working was:
val writeableSession: DBSession = DBSession(session.connection, isReadOnly = false)
def inner()(implicit session: DBSession): Iterator[Payment] = {
sql"""
update payments
set status='submitted'
where status='pending'
returning id
""".map(_.long).iterable().apply().toIterator
}
inner()(writeableSession)
This failed because session.connection was null.
How can I coerce this to be a localTx instead of readOnly?
In general, AutoSession behaves as an auto-commit session for DDL and the insert/update/delete ops whereas it works as a read-only session for select queries.
It seems to be doing as follows is the straight-forward way.
DB.localTx { implicit session =>
// Have both the update operation and select query inside this block
}