Seamus Abshere <seamus@xxxxxxxxxxx> writes: > Given an update that uses CTEs like this: > WITH > lock_rows AS ( > SELECT 1 FROM tbl WHERE [...] FOR UPDATE > ) > UPDATE [...] > Will the rows in `tbl` remain locked until the UPDATE is finished? Yes, locks are associated with a transaction not a statement or sub-statement. > Also, does it matter if `lock_rows` is referenced? (IIUC the query > wouldn't be run if the CTE isn't referenced if it was for a SELECT, but > since it's an UPDATE, it will be run anyway) Yes, it does --- unreferenced SELECT CTEs are discarded. I thought maybe there was an exception for FOR UPDATE, but a look at the code says differently. In any case we would only lock rows the sub-select had actually read, so if it's not called by the outer statement it would still be a no-op. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
