0

I'm trying to do a simple Postgres SET operation to increment a column in my table by some amount. It should look something like this Increment a value in Postgres

I want to avoid writing a raw SQL statement.

I am aware of the UpdateInvoker that works like this

val q = for { c <- Coffees if c.name === "Espresso" } yield c.price
q.update(10.49)

val statement = q.updateStatement
val invoker = q.updateInvoker

but I need an atomic operation.

Community
  • 1
  • 1
yzernik
  • 1,161
  • 2
  • 13
  • 19

1 Answers1

2

Modification of existing values in update queries is currently not supported in Slick. We have a ticket for it https://github.com/slick/slick/issues/497

What you can do right now is use a transaction in which you first retrieve the value and then write the modified value back.

Make sure to set the isolation level to Serializable.

cvogt
  • 11,260
  • 30
  • 46
  • Transactions do not solve concurrency issues. Concurrent transactions can lead to unexpected results. Plain SQL seems to be a safer option for the time being. – centr Aug 26 '14 at 17:28
  • Please explain. Shouldn't a transaction with the right isolation level solve this? – cvogt Aug 26 '14 at 22:01
  • let me explain with an example: We have two occurrent transactions (e.g. A and B) which each reads a value (e.g. x = 5) from db, increments by 1 and updates db. After both transactions are done, we expect x to be 7 in db. When A and B read from db, x is 5 so both will write 6 as the value for x - so x will be 6 in db not 7. Is this something we can solve with the the right isolation level? – centr Aug 27 '14 at 16:24
  • 1
    Yep. Isolation level Serializable gives the requires consistency. I added it to the answer. By the way, despite Serializable being the default isolation level according to the SQL standard, RDBMS vendors often choose to go with less strict isolation levels by default showing the behavior you described. Probably for performance reasons. We all know default performance is much more important than default correctness. ... Or is it ;)? – cvogt Aug 27 '14 at 19:38