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(at)telsasoft(dot)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(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>
>

Robert Treat
https://xzilla.net