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

On Mon, Sep 5, 2022 at 2:04 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> 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.
>

Yes, I see now what you are referring to, and thinking maybe an option
would be to also add a reference there back to what will include your
change above.

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 4b219435d4..c52092a45e 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4088,7 +4088,9 @@ CREATE TABLE measurement_y2008m02 PARTITION OF measurement
      As an alternative, it is sometimes more convenient to create the
      new table outside the partition structure, and make it a proper
      partition later. This allows new data to be loaded, checked, and
-     transformed prior to it appearing in the partitioned table.
+     transformed prior to it appearing in the partitioned table; see
+     <link linkend="sql-altertable-attach-partition"><literal>ALTER
TABLE ... ATTACH PARTITION</literal></link>
+     for additional details.
      The <literal>CREATE TABLE ... LIKE</literal> option is helpful
      to avoid tediously repeating the parent table's definition:

> @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..
>

I was thinking there might be either lock escalation issues or perhaps
issues around index attachment that don't surface using create
partition of, but I don't actually see any, in which case that does
seem like a better change all around. But like you, I feel I must be
overlooking something :-)

> commit 723fa7df82f39aed5d58e5e52ba80caa8cb13515
> Author: Justin Pryzby <pryzbyj@telsasoft.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>
>


Robert Treat
https://xzilla.net