Update returning queries in ScalikeJDBC

Question

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?

score 2 · Accepted Answer · answered Mar 18 '19 at 01:24

2

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
}

answered Mar 18 '19 at 01:24

Kazuhiro Sera

1,822
12
15

Thanks. Yes, I'd prefer to do it this way, but I'm inside playframework with nothing but an eagerly bound Autosession to work with. – Synesso Mar 18 '19 at 01:31
1

I see. `scalikejdbc.TxBoundary.Future` may be useful for your use cases. http://scalikejdbc.org/documentation/transaction.html#futurelocaltx-block – Kazuhiro Sera Mar 18 '19 at 02:45
I'm sorry. Of course, I could invoke `DB.localTx`. I'm not sure why I thought I couldn't. :) – Synesso Mar 18 '19 at 05:09

Update returning queries in ScalikeJDBC

1 Answers1