My original question: In Postgres, suppose t is a table (for simplicity we'll imagine it has a single row) and f is a function. If we do select f(t.id) from t for update, when f runs, will the row be locked or not?
I tried this with a function f that sleeps for a minute, so that while it's sleeping I could check in another connection whether the row is locked. It turned out that I could update the row while f was running, but then it would run again and this time its transaction held a lock. If I didn't do a concurrent update, the query would only execute f once.
My updated question: How exactly is this query executed? What happens when and what does the behavior depend on?
The documentation says the following but I don't find it detailed enough:
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated version of the row that is locked and returned to the client.
My meta question: Is there any comprehensive resource that contains the answer other than the source code?