From: Justin Pryzby <pryzby@telsasoft.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: update ALTER TABLE with ATTACH PARTITION lock mode
Date: Sun, 27 Oct 2019 19:12:07 -0500
Message-ID: <20191028001207.GB23808@telsasoft.com> (raw)
commit #898e5e32 (Allow ATTACH PARTITION with only ShareUpdateExclusiveLock)
updates ddl.sgml but not alter_table.sgml, which only says:
https://www.postgresql.org/docs/12/release-12.html
|An ACCESS EXCLUSIVE lock is held unless explicitly noted.
Find attached patch, which also improve language in several related places.
"Without such a constraint": SUCH could refer to either of the constraints..
"because it is no longer necessary.": In our use case, we prefer to keep the
redundant constraint, to avoid having to add it back if we detach/reattach
again in the future..
Attachments:
[text/x-diff] v1-0001-Mention-reduced-locking-strength-of-ATTACH-PARTIT.patch (1.0K, ../20191028001207.GB23808@telsasoft.com/2-v1-0001-Mention-reduced-locking-strength-of-ATTACH-PARTIT.patch)
download | inline diff:
From c820a81fba0a6c2388ec58fc0204ca833523e81e Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 27 Oct 2019 18:54:24 -0500
Subject: [PATCH v1 1/2] Mention reduced locking strength of ATTACH PARTITION..
See commit 898e5e32
---
doc/src/sgml/ref/alter_table.sgml | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex c8dfa19..a184bed 100644--- a/doc/src/sgml/ref/alter_table.sgml+++ b/doc/src/sgml/ref/alter_table.sgml@@ -900,6 +900,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
the scan of the new partition, it is always skipped when the default
partition is a foreign table.
</para>
++ <para>+ Attaching a partition acquires a <literal>SHARE UPDATE EXCLUSIVE</literal>+ lock on the partitioned table, in addition to an+ <literal>ACCESS EXCLUSIVE</literal> lock on the partition.+ </para>+
</listitem>
</varlistentry>
--
2.7.4
[text/x-diff] v1-0002-Tweak-language-for-ATTACH-PARTITION-docs.patch (4.0K, ../20191028001207.GB23808@telsasoft.com/3-v1-0002-Tweak-language-for-ATTACH-PARTITION-docs.patch)
download | inline diff:
From b1c9c50228ebd3d2d511382ebd6cbae08788e376 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 27 Oct 2019 18:38:23 -0500
Subject: [PATCH v1 2/2] Tweak language for ATTACH PARTITION docs
---
doc/src/sgml/ddl.sgml | 8 ++++----
doc/src/sgml/ref/alter_table.sgml | 12 ++++++------
2 files changed, 10 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgmlindex b097b80..8b60d8b 100644--- a/doc/src/sgml/ddl.sgml+++ b/doc/src/sgml/ddl.sgml@@ -3972,14 +3972,14 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
<para>
Before running the <command>ATTACH PARTITION</command> command, it is
recommended to create a <literal>CHECK</literal> constraint on the table to
- be attached describing the desired partition constraint. That way,+ be attached matching the desired partition constraint. That way,
the system will be able to skip the scan which is otherwise needed to validate the implicit
- partition constraint. Without such a constraint, the table will be+ partition constraint. Without the <literal>CHECK</literal> constraint, the table will be
scanned to validate the partition constraint while holding an
<literal>ACCESS EXCLUSIVE</literal> lock on that partition
and a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the parent table.
- One may then drop the constraint after <command>ATTACH PARTITION</command>- is finished, because it is no longer necessary.+ It may be desired to drop the redundant <literal>CHECK</literal> constraint+ after <command>ATTACH PARTITION</command> is finished.
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex a184bed..91ec626 100644--- a/doc/src/sgml/ref/alter_table.sgml+++ b/doc/src/sgml/ref/alter_table.sgml@@ -841,7 +841,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
or as a default partition by using <literal>DEFAULT</literal>.
For each index in the target table, a corresponding
one will be created in the attached table; or, if an equivalent
- index already exists, will be attached to the target table's index,+ index already exists, it will be attached to the target table's index,
as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed.
Note that if the existing table is a foreign table, it is currently not
allowed to attach the table as a partition of the target table if there
@@ -864,17 +864,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
already exist.
If any of the <literal>CHECK</literal> constraints of the table being
attached are marked <literal>NO INHERIT</literal>, the command will fail;
- such a constraint must be recreated without the <literal>NO INHERIT</literal>+ such constraints must be recreated without the <literal>NO INHERIT</literal>
clause.
</para>
<para>
If the new partition is a regular table, a full table scan is performed
- to check that no existing row in the table violates the partition+ to check that existing rows in the table do not violate the partition
constraint. It is possible to avoid this scan by adding a valid
- <literal>CHECK</literal> constraint to the table that would allow only- the rows satisfying the desired partition constraint before running this- command. It will be determined using such a constraint that the table+ <literal>CHECK</literal> constraint to the table that allows only+ rows satisfying the desired partition constraint before running this+ command. The <literal>CHECK</literal> constraint will be used to determine that the table
need not be scanned to validate the partition constraint. This does not
work, however, if any of the partition keys is an expression and the
partition does not accept <literal>NULL</literal> values. If attaching
--
2.7.4
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, robertmhaas@gmail.com
Subject: Re: update ALTER TABLE with ATTACH PARTITION lock mode
In-Reply-To: <20191028001207.GB23808@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox