Re: ATTACH/DETACH partitions and locking

On 13.06.2019 23:07, Alvaro Herrera wrote:
> On 2019-Jun-13, Pavel Luzanov wrote:
>
>> Hello,
>>
>> According to patch[1] and after playing with v12 beta1 I think that this
>> item can be dropped from "5.11.3. Implementation Using Inheritance" section
>> of v12 docs:
>>
>> "Some operations require a stronger lock when using declarative partitioning
>> than when using table inheritance. For example, adding or removing a
>> partition to or from a partitioned table requires taking an ACCESS EXCLUSIVE
>> lock on the parent table, whereas a SHARE UPDATE EXCLUSIVE lock is enough in
>> the case of regular inheritance."
> Hmm ... while you're correct that ALTER TABLE ATTACH PARTITION no longer
> uses AccessExclusive lock, ALTER TABLE DETACH PARTITION continues to.
> So we could remove the "adding to" bit of the paragraph, but not remove
> it completely.
>
> https://www.postgresql.org/message-id/CA%2BTgmoY13KQZF-%3DHNTrt9UYWYx3_oYOQpu9ioNT49jGgiDpUEA%40mail.gmail.com
You are right, I missed the point with DETACH.

Does it make sense to change this way?
"Some operations require a stronger lock when using declarative partitioning
than when using table inheritance. For example, removing a
partition from a partitioned table requires taking an ACCESS EXCLUSIVE
lock on the parent table, whereas a SHARE UPDATE EXCLUSIVE lock is enough in
the case of regular inheritance."

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company