1

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.*

The 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?

5

It's quite normal you get multiple rows. The update selects all rows from folder, and for each of them selects a row from folder f2 (I guess the same, if id is PK). If locked = 'available', the record is updated. On to the next row.

The correct syntax is here, Postgres UPDATE ... LIMIT 1, and consists in using a CTE.

Without CTE, your result depends on how PostgresSQL decides to execute your update, starting with the outer (multiple rows are possible) or with the inner select.

Not the answer you're looking for? Browse other questions tagged or ask your own question.