Horton

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Pavel Luzanov <p.luzanov@postgrespro.ru>

View thread View on postgr.es
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
To: Amit Langote <amitlangote09@gmail.com>, Álvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Tom Lane <tgl@sss.pgh.pa.us>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2021-05-05T10:58:59Z
Lists: pgsql-hackers 
Hello,

I found this in the documentation, section '5.11.3. Partitioning Using 
Inheritance'[1]:
"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."

This point is no longer valid with some restrictions. If the table has a 
default partition, then removing a partition still requires taking an 
ACCESS EXCLUSIVE lock.

May be make sense to add some details about DETACH CONCURRENTLY to the 
section '5.11.2.2. Partition Maintenance' and completely remove this point?

1. 
https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE

Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Commits

  1. Make detach-partition-concurrently-4 less timing sensitive

  2. Improve documentation on DETACH PARTITION lock levels

  3. Track detached partitions more accurately in partdescs

  4. Allow a partdesc-omitting-partitions to be cached

  5. Fix relcache inconsistency hazard in partition detach

  6. Don't add a redundant constraint when detaching a partition

  7. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY

  8. Let ALTER TABLE Phase 2 routines manage the relation pointer

  9. Check default partitions constraints while descending