Subject: Mention FK creation take ShareRowExclusiveLock on referenced table

From: Adrien NAYRAT <[email-masked]>
Date: Tue, 18 Sep 2018 12:32:54 +0200

Hello,

A few days ago I was surprised a CREATE TABLE containing FK constraint 
was stuck due to an automatic vacuum freeze (which took 
ShareUpdateExclusiveLock if I remember) on referenced table.

After digging into the code I found theses lines in tablecmds.c :

/*
  * Grab ShareRowExclusiveLock on the pk table, so that someone doesn't
  * delete rows out from under us.
  */

Maybe it should be documented in theses pages?

https://www.postgresql.org/docs/current/static/sql-createtable.html
https://www.postgresql.org/docs/current/static/sql-altertable.html

If you agree I can send a patch.

Regards,

---

From: Michael Paquier <[email-masked]>
Date: Wed, 19 Sep 2018 11:53:02 +0900

On Tue, Sep 18, 2018 at 12:32:54PM +0200, Adrien NAYRAT wrote:
> A few days ago I was surprised a CREATE TABLE containing FK constraint was
> stuck due to an automatic vacuum freeze (which took ShareUpdateExclusiveLock
> if I remember) on referenced table.

Right.  See the top of vacuum_rel() where lmode is set.

> If you agree I can send a patch.

That looks like a good idea.  Are you thinking about adding a comment
about that in "ADD table_constraint" for the ALTER TABLE page, and in
"FOREIGN KEY" for the CREATE TABLE page?
--
Michael

---

From: Adrien Nayrat <[email-masked]>
Date: Thu, 20 Sep 2018 08:23:45 +0200

> "FOREIGN KEY" for the CREATE TABLE page?

Yes, here is the patch

Thanks

-- 
Adrien

diff --combined doc/src/sgml/ref/alter_table.sgml
index 1e4a327327,ec6b4c3311..0000000000
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@@ -377,11 -377,6 +377,11 @@@ WITH ( MODULUS <replaceable class="para
        are added to partitioned tables; see <xref linkend="sql-createtable" />.
       </para>
  
 +     <para>
 +     Adding a foreign key requires taking a <literal>SHARE ROW EXCLUSIVE<literal>
 +     lock on the referenced table.
 +     </para>
 +
      </listitem>
     </varlistentry>
  
diff --combined doc/src/sgml/ref/create_table.sgml
index a974bea6b1,5a19f94ce9..0000000000
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@@ -993,8 -993,6 +993,8 @@@ WITH ( MODULUS <replaceable class="para
        unique or primary key constraint in the referenced table.  The user
        must have <literal>REFERENCES</literal> permission on the referenced table
        (either the whole table, or the specific referenced columns).
 +      Addding a foreign key constraint requires taking a <literal>SHARE ROW
 +      EXCLUSIVE <literal> lock on the referenced table.
        Note that foreign key constraints cannot be defined between temporary
        tables and permanent tables.  Also note that while it is possible to
        define a foreign key on a partitioned table, it is not possible to

---

From: Michael Paquier <[email-masked]>
Date: Fri, 21 Sep 2018 15:13:52 +0900

On Thu, Sep 20, 2018 at 08:23:45AM +0200, Adrien Nayrat wrote:
> Yes, here is the patch.

Thanks Adrien.  I have reworded a bit the thing, fixed a typo, and
pushed down to v11 where this applied without conflicts.
--
Michael

---

From: Adrien NAYRAT <[email-masked]>
Date: Fri, 21 Sep 2018 09:09:36 +0200

> pushed down to v11 where this applied without conflicts.

thanks! As it could happen even on previous version, should we backpatch 
for the documentation?

---

From: Michael Paquier <[email-masked]>
Date: Fri, 21 Sep 2018 17:49:31 +0900

On Fri, Sep 21, 2018 at 09:09:36AM +0200, Adrien NAYRAT wrote:
> Thanks! As it could happen even on previous version, should we
> backpatch for the documentation?

I have patched HEAD, and then down until conflicts happened, which is
v10, thinking about it as a documentation improvement.  The behavior
exists for ages, so I have not bothered much...
--
Michael