Re: doc: mentioned CREATE+ATTACH PARTITION as an alternative to CREATE TABLE..PARTITION OF

On Thu, Aug 04, 2022 at 01:45:49AM -0400, Robert Treat wrote:
> After reading this again, it isn't clear to me that this advice would
> be more appropriately placed into Section 5.11, aka
> https://www.postgresql.org/docs/current/ddl-partitioning.html, but in
> lieu of a specific suggestion for where to place it there (I haven't
> settled on one yet), IMHO, I think the first sentence of the suggested
> change should be rewritten as:
>
> <para>
> Note that creating a partition using <literal>PARTITION OF<literal>
> requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
> It may be preferable to first CREATE a separate table...

Thanks for looking. I used your language.

There is some relevant information in ddl.sgml, but not a lot, and it's
not easily referred to, so I removed the part of the patch that tried to
cross-reference.

@Robert: I wonder why shouldn't CREATE..PARTITION OF *also* be patched
to first create a table, and then attach the partition, transparently
doing what everyone would want, without having to re-read the updated
docs or know to issue two commands? I wrote a patch for this which
"doesn't fail tests", but I still wonder if I'm missing something..

commit 723fa7df82f39aed5d58e5e52ba80caa8cb13515
Author: Justin Pryzby <pryzbyj(at)telsasoft(dot)com>
Date: Mon Jul 18 09:24:55 2022 -0500

doc: mention CREATE+ATTACH PARTITION as an alternative to CREATE TABLE..PARTITION OF

In v12, 898e5e329 (Allow ATTACH PARTITION with only ShareUpdateExclusiveLock)
allows attaching a partition with a weaker lock than in CREATE..PARTITION OF,
but it does that silently. On the one hand, things that are automatically
better, without having to enable the option are the best kind of feature.

OTOH, I doubt many people know to do that, because the docs don't say
so, because it was implemented as an transparent improvement. This
patch adds a bit of documentations to make that more visible.

See also: 898e5e3290a72d288923260143930fb32036c00c
Should backpatch to v12

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 360284e37d6..66138b9299d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4092,7 +4092,9 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02

<para>
The <command>ATTACH PARTITION</command> command requires taking a
- <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the partitioned table.
+ <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the partitioned table,
+ as opposed to the <literal>Access Exclusive</literal> lock which is
+ required by <literal>CREATE TABLE .. PARTITION OF</literal>.
</para>

<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index c14b2010d81..54dbfa72e4c 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -619,6 +619,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
with <literal>DROP TABLE</literal> requires taking an <literal>ACCESS
EXCLUSIVE</literal> lock on the parent table.
</para>
+
+ <para>
+ Note that creating a partition using <literal>PARTITION OF<literal>
+ requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the parent
+ table. It may be preferable to first create a separate table and then
+ attach it, which does not require as strong a lock.
+ See <link linkend="sql-altertable-attach-partition">ATTACH PARTITION</link>
+ for more information.
+ </para>
+
</listitem>
</varlistentry>