Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

[David G. Johnston]

On Tue, Jul 11, 2017 at 8:36 AM, Seamus Abshere  wrote:

> 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 persist to the end of the transaction.  Using a CTE doesn't
constitute creating a new 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
> ​.
>

​Pretty sure it will not be.  The EXPLAIN​ command should be able to
provide a more definitive answer.

If the UPDATE was inside the CTE it definitely would be run regardless of
outer query references.  I'm not sure if the FOR UPDATE impacts whether the
select needs to be executed by I'm thinking no since it doesn't change the
semantics of the query.

David J.

Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

[Tom Lane]

Seamus Abshere  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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Does a row lock taken out in a CTE stay in place?

[Seamus Abshere]

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?

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)

Thanks!
Seamus

--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general