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

Commitb0488cb

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 parent3908d6a commitb0488cb

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
@@ -4072,9 +4072,15 @@ CREATE TABLE measurement_y2008m02 PARTITION OF measurement
40724072
</programlisting>
40734073

40744074
As an alternative, it is sometimes more convenient to create the
4075-
new table outside the partition structure, andmake ita proper
4075+
new table outside the partition structure, andattach itas a
40764076
partition later. This allows new data to be loaded, checked, and
40774077
transformed prior to it appearing in the partitioned table.
4078+
Moreover, the <literal>ATTACH PARTITION</literal> operation requires
4079+
only <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the
4080+
partitioned table, as opposed to the <literal>ACCESS
4081+
EXCLUSIVE</literal> lock that is required by <command>CREATE TABLE
4082+
... PARTITION OF</command>, so it is more friendly to concurrent
4083+
operations on the partitioned table.
40784084
The <literal>CREATE TABLE ... LIKE</literal> option is helpful
40794085
to avoid tediously repeating the parent table's definition:
40804086

@@ -4094,11 +4100,6 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
40944100
</programlisting>
40954101
</para>
40964102

4097-
<para>
4098-
The <command>ATTACH PARTITION</command> command requires taking a
4099-
<literal>SHARE UPDATE EXCLUSIVE</literal> lock on the partitioned table.
4100-
</para>
4101-
41024103
<para>
41034104
Before running the <command>ATTACH PARTITION</command> command, it is
41044105
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
@@ -586,12 +586,24 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
586586
</para>
587587

588588
<para>
589-
Operations such as TRUNCATE which normally affect a table and all of its
589+
Operations such as <command>TRUNCATE</command>
590+
which normally affect a table and all of its
590591
inheritance children will cascade to all partitions, but may also be
591-
performed on an individual partition. Note that dropping a partition
592-
with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS
593-
EXCLUSIVE</literal> lock on the parent table.
592+
performed on an individual partition.
594593
</para>
594+
595+
<para>
596+
Note that creating a partition using <literal>PARTITION OF</literal>
597+
requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the
598+
parent partitioned table. Likewise, dropping a partition
599+
with <command>DROP TABLE</command> requires taking
600+
an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
601+
It is possible to use <link linkend="sql-altertable"><command>ALTER
602+
TABLE ATTACH/DETACH PARTITION</command></link> to perform these
603+
operations with a weaker lock, thus reducing interference with
604+
concurrent operations on the partitioned table.
605+
</para>
606+
595607
</listitem>
596608
</varlistentry>
597609

0 commit comments

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