Nested database transactions (WARNING: there is already a transaction in progress)

[misaugstad]

Brief description of problem/feature

We are seeing this error in our postgres logs (/var/lib/postgresql/data/log/postgresql-2024-05-20_234031.log) whenever we create a new anon user account. Haven't checked where else it may be happening.

WARNING: there is already a transaction in progress

@yhtill suggested that it may have to be from us trying to nest transactions. Sure enough, he found an example in UserRoleTable.scala where we are doing that. We can rewrite this:

  def setRole(userId: UUID, newRole: String, communityService: Option[Boolean]): Int = db.withTransaction { implicit session =>
    setRole(userId, roleMapping(newRole), communityService)
  }

  def setRole(userId: UUID, newRole: Int, communityService: Option[Boolean]): Int = db.withTransaction { implicit session =>
    ...
  }

As this:

  def setRole(userId: UUID, newRole: String, communityService: Option[Boolean]): Int = {
    setRole(userId, roleMapping(newRole), communityService)
  }

  def setRole(userId: UUID, newRole: Int, communityService: Option[Boolean]): Int = db.withTransaction { implicit session =>
    ...
  }

Unfortunately, we still get the error when creating a new anon user account. Need to dig deeper on it! Nested transactions are generally a bad idea. Might just be hard to find all the places where we made this mistake, unless it shows up consistently as that warning in our postgres logs.

Link to Slick documentation on transactions: https://scala-slick.org/doc/2.1.0/connection.html#session-handling

Here's a Stackoverflow post I found helpful: https://stackoverflow.com/a/22236221