pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedMention FK creation take ShareRowExclusiveLock on referenced table
6+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2018-09-21 ce9cf8e7e6 3d4b143dc7 Document lock taken on referenced table when adding a foreign key
* Mention FK creation take ShareRowExclusiveLock on referenced table
@ 2018-09-18 10:32 Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-19 02:53 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 6+ messages in thread
From: Adrien NAYRAT @ 2018-09-18 10:32 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table
2018-09-18 10:32 Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
@ 2018-09-19 02:53 ` Michael Paquier <michael@paquier.xyz>
2018-09-20 06:23 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Adrien Nayrat <adrien.nayrat@anayrat.info>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Paquier @ 2018-09-19 02:53 UTC (permalink / raw)
To: Adrien NAYRAT <adrien.nayrat@anayrat.info>; +Cc: pgsql-docs@lists.postgresql.org
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?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table
2018-09-18 10:32 Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-19 02:53 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
@ 2018-09-20 06:23 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2018-09-21 06:13 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 6+ messages in thread
From: Adrien Nayrat @ 2018-09-20 06:23 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-docs@lists.postgresql.org
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:
mention_lock_fk.patch (text/x-patch, 1.3K) download
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
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table
2018-09-18 10:32 Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-19 02:53 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
2018-09-20 06:23 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Adrien Nayrat <adrien.nayrat@anayrat.info>
@ 2018-09-21 06:13 ` Michael Paquier <michael@paquier.xyz>
2018-09-21 07:09 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Paquier @ 2018-09-21 06:13 UTC (permalink / raw)
To: Adrien Nayrat <adrien.nayrat@anayrat.info>; +Cc: pgsql-docs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table
2018-09-18 10:32 Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-19 02:53 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
2018-09-20 06:23 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Adrien Nayrat <adrien.nayrat@anayrat.info>
2018-09-21 06:13 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
@ 2018-09-21 07:09 ` Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-21 08:49 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 6+ messages in thread
From: Adrien NAYRAT @ 2018-09-21 07:09 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-docs@lists.postgresql.org
On 9/21/18 8:13 AM, Michael Paquier wrote:
> 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.
thanks! As it could happen even on previous version, should we backpatch
for the documentation?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table
2018-09-18 10:32 Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-19 02:53 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
2018-09-20 06:23 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Adrien Nayrat <adrien.nayrat@anayrat.info>
2018-09-21 06:13 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Michael Paquier <michael@paquier.xyz>
2018-09-21 07:09 ` Re: Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
@ 2018-09-21 08:49 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Paquier @ 2018-09-21 08:49 UTC (permalink / raw)
To: Adrien NAYRAT <adrien.nayrat@anayrat.info>; +Cc: pgsql-docs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-09-21 08:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-18 10:32 Mention FK creation take ShareRowExclusiveLock on referenced table Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-19 02:53 ` Michael Paquier <michael@paquier.xyz>
2018-09-20 06:23 ` Adrien Nayrat <adrien.nayrat@anayrat.info>
2018-09-21 06:13 ` Michael Paquier <michael@paquier.xyz>
2018-09-21 07:09 ` Adrien NAYRAT <adrien.nayrat@anayrat.info>
2018-09-21 08:49 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox