I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12
The query is the following :
UPDATE folder as f set locked = 'locked', date_locked = current_timestamp from ( SELECT * from folder as f2 WHERE f2.locked = 'available' LIMIT 1 FOR UPDATE ) as folderToUpdate WHERE f.id = folderToUpdate.id returning f.*
id is the primary key.
It is executed through a bunch of integration tests. When I am executing only the test doing the query above, I don't have any issue. But, when I am executing all the tests, I randomly get multiple rows. Do you have any suggestions?