public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrien Nayrat <[email protected]>
To: Michael Paquier <[email protected]>
Cc: [email protected]
Subject: Re: Mention FK creation take ShareRowExclusiveLock on referenced table
Date: Thu, 20 Sep 2018 08:23:45 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On 9/19/18 4:53 AM, Michael Paquier wrote:
> 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.
>
>> 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.
>
> 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?
Yes, here is the patch
Thanks
--
Adrien
Attachments:
[text/x-patch] mention_lock_fk.patch (1.3K, 2-mention_lock_fk.patch)
download | inline diff:
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
[application/pgp-signature] signature.asc (484B, 3-signature.asc)
download
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Mention FK creation take ShareRowExclusiveLock on referenced table
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox