Doc: clarify locking requirements for ALTER TABLE ADD FOREIGN KEY.

tglsfdc · tglsfdc · commit 61a9b814c553 · 2020-04-10T13:12:58.000-04:00
The docs explained that a SHARE ROW EXCLUSIVE lock is needed on the referenced table, but failed to say the same about the table being altered. Since the page says that ACCESS EXCLUSIVE lock is taken unless otherwise stated, this left readers with the wrong conclusion. Discussion: https://postgr.es/m/834603375.3470346.1586482852542@mail.yahoo.com
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
@@ -99,9 +99,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
   <para>
    <command>ALTER TABLE</command> changes the definition of an existing table.
    There are several subforms described below. Note that the lock level required
-   may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is held
-   unless explicitly noted. When multiple subcommands are listed, the lock
-   held will be the strictest one required from any subcommand.
+   may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is
+   acquired unless explicitly noted. When multiple subcommands are given, the
+   lock acquired will be the strictest one required by any subcommand.
 
   <variablelist>
    <varlistentry>
@@ -313,8 +313,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
     <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]</literal></term>
     <listitem>
      <para>
-      This form adds a new constraint to a table using the same syntax as
-      <xref linkend="SQL-CREATETABLE">, plus the option <literal>NOT
+      This form adds a new constraint to a table using the same constraint
+      syntax as <xref linkend="sql-createtable">, plus the option <literal>NOT
       VALID</literal>, which is currently only allowed for foreign key
       and CHECK constraints.
      </para>
@@ -335,6 +335,17 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
       endterm="sql-altertable-notes-title"> below for more information
       about using the <literal>NOT VALID</literal> option.
      </para>
+
+     <para>
+      Although most forms of <literal>ADD
+      <replaceable class="parameter">table_constraint</replaceable></literal>
+      require an <literal>ACCESS EXCLUSIVE</literal> lock, <literal>ADD
+      FOREIGN KEY</literal> requires only a <literal>SHARE ROW
+      EXCLUSIVE</literal> lock.  Note that <literal>ADD FOREIGN KEY</literal>
+      also acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock on the
+      referenced table, in addition to the lock on the table on which the
+      constraint is declared.
+     </para>
     </listitem>
    </varlistentry>
 
