Thread: ATTACH/DETACH partitions and locking
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." Small patch attached. [1] ATTACH/DETACH PARTITION CONCURRENTLY https://www.postgresql.org/message-id/flat/CAKJS1f9QjUwQrio20Pi=yCHmnouf4z3SfN8sqXaAcwREG6k0zQ@mail.gmail.com -- ----- Pavel Luzanov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Attachment
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 -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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
On 2019-Jun-14, Pavel Luzanov wrote: > 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." It took long -- pushed now. Thanks for reporting -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services