Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8f90381

Browse files
committed
Doc: mention CREATE+ATTACH PARTITION with CREATE TABLE...PARTITION OF.
Clarify that ATTACH/DETACH PARTITION can be used to perform partitionmaintenance with less locking than straight CREATE TABLE/DROP TABLE.This was already stated in some places, but not emphasized.Back-patch to v14 where DETACH PARTITION CONCURRENTLY was added.(We had lower lock levels for ATTACH PARTITION before that, butthis wording wouldn't apply.)Justin Pryzby, reviewed by Robert Treat and Jakub Wartak;a little further wordsmithing by meDiscussion:https://postgr.es/m/20220718143304.GC18011@telsasoft.com
1 parentbbf18fe commit8f90381

File tree

2 files changed

+23
-10
lines changed

2 files changed

+23
-10
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3951,9 +3951,15 @@ CREATE TABLE measurement_y2008m02 PARTITION OF measurement
39513951
</programlisting>
39523952

39533953
As an alternative, it is sometimes more convenient to create the
3954-
new table outside the partition structure, andmake ita proper
3954+
new table outside the partition structure, andattach itas a
39553955
partition later. This allows new data to be loaded, checked, and
39563956
transformed prior to it appearing in the partitioned table.
3957+
Moreover, the <literal>ATTACH PARTITION</literal> operation requires
3958+
only <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the
3959+
partitioned table, as opposed to the <literal>ACCESS
3960+
EXCLUSIVE</literal> lock that is required by <command>CREATE TABLE
3961+
... PARTITION OF</command>, so it is more friendly to concurrent
3962+
operations on the partitioned table.
39573963
The <literal>CREATE TABLE ... LIKE</literal> option is helpful
39583964
to avoid tediously repeating the parent table's definition:
39593965

@@ -3973,11 +3979,6 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
39733979
</programlisting>
39743980
</para>
39753981

3976-
<para>
3977-
The <command>ATTACH PARTITION</command> command requires taking a
3978-
<literal>SHARE UPDATE EXCLUSIVE</literal> lock on the partitioned table.
3979-
</para>
3980-
39813982
<para>
39823983
Before running the <command>ATTACH PARTITION</command> command, it is
39833984
recommended to create a <literal>CHECK</literal> constraint on the table to

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 16 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -577,12 +577,24 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
577577
</para>
578578

579579
<para>
580-
Operations such as TRUNCATE which normally affect a table and all of its
580+
Operations such as <command>TRUNCATE</command>
581+
which normally affect a table and all of its
581582
inheritance children will cascade to all partitions, but may also be
582-
performed on an individual partition. Note that dropping a partition
583-
with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS
584-
EXCLUSIVE</literal> lock on the parent table.
583+
performed on an individual partition.
585584
</para>
585+
586+
<para>
587+
Note that creating a partition using <literal>PARTITION OF</literal>
588+
requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the
589+
parent partitioned table. Likewise, dropping a partition
590+
with <command>DROP TABLE</command> requires taking
591+
an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
592+
It is possible to use <link linkend="sql-altertable"><command>ALTER
593+
TABLE ATTACH/DETACH PARTITION</command></link> to perform these
594+
operations with a weaker lock, thus reducing interference with
595+
concurrent operations on the partitioned table.
596+
</para>
597+
586598
</listitem>
587599
</varlistentry>
588600

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp