The ROW SHARE
lock taken by FOR UPDATE
prevents concurrent write access that would change the physical location of the row. The manual:
This prevents them from being locked, modified or deleted by other
transactions until the current transaction ends. That is, other
transactions that attempt UPDATE
, DELETE
, SELECT FOR UPDATE
, SELECT FOR NO KEY UPDATE
, SELECT FOR SHARE
or SELECT FOR KEY SHARE
of these
rows will be blocked until the current transaction ends;
So the ctid
should be stable for the duration of the command (or the transaction, even) unless you alter the row within the same transaction yourself. ctid
is still a system column for internal use and the project will not offer any guarantees. If you have any unique (combination of) column(s) (including the PK) use that instead of the ctid
.
However, I would use a CTE to materialize the selection and avoid unexpected results.
And without ORDER BY
you select arbitrary rows for deletion. You might as well add SKIP LOCKED
to minimize lock contention with concurrent transactions:
WITH cte AS (
SELECT ctid
FROM calendar_event
WHERE user_id = 5
LIMIT 100
FOR UPDATE SKIP LOCKED
)
DELETE FROM calendar_event WHERE ctid IN (TABLE cte);
Related, with explanation for both considerations:
About ctid
: