Hi,
I recently noticed that ATTACH PARTITION also recursively locks the
default partition with ACCESS EXCLUSIVE mode when its constraints do
not explicitly exclude the to-be-attached partition, which I couldn't
find documented (has been there since PG10 I believe).
PFA a patch that documents just that.
With regards,
Matthias van de Meent.
From 2bf23cd8018c7e2cbff4f00be4aba1e806750998 Mon Sep 17 00:00:00 2001
From: Matthias van de Meent <boekew...@gmail.com>
Date: Thu, 15 Apr 2021 20:43:00 +0200
Subject: [PATCH v1] ATTACH PARTITION locking documentation for DEFAULT
partitions.
---
doc/src/sgml/ddl.sgml | 18 ++++++++++++++++++
1 file changed, 18 insertions(+)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 30e4170963..f001f0b8a3 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3934,6 +3934,9 @@ ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
+ALTER TABLE measurement_default ADD CONSTRAINT excl_y2008m02
+ CHECK ( (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01') IS FALSE );
+
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );
</programlisting>
@@ -3953,6 +3956,21 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
constraint after <command>ATTACH PARTITION</command> is finished.
</para>
+ <para>
+ Similarly, if you have a default partition on the parent table, it is
+ recommended to create a <literal>CHECK</literal> constraint that excludes
+ the to be attached partition constraint. Here, too, without the
+ <literal>CHECK</literal> constraint, this table will be scanned to
+ validate that the updated default partition constraints while holding
+ an <literal>ACCESS EXCLUSIVE</literal> lock on the default partition.
+ </para>
+
+ <para>
+ Note that the locks on both the table that is being attached and the
+ default partition are both recursive to all their child partitions, if
+ these tables are partitioned tables themselves.
+ </para>
+
<para>
As explained above, it is possible to create indexes on partitioned tables
so that they are applied automatically to the entire hierarchy.
--
2.20.1
