Re: [GENERAL] Does a row lock taken out in a CTE stay in place? - Mailing list pgsql-general

Seamus Abshere <seamus@abshere.net> 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