public inbox for [email protected]help / color / mirror / Atom feed
Mention FK creation take ShareRowExclusiveLock on referenced table 6+ messages / 3 participants [nested] [flat]
* Mention FK creation take ShareRowExclusiveLock on referenced table @ 2018-09-18 10:32 Adrien NAYRAT <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adrien NAYRAT @ 2018-09-18 10:32 UTC (permalink / raw) To: [email protected] 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-19 02:53 Michael Paquier <[email protected]> parent: Adrien NAYRAT <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Michael Paquier @ 2018-09-19 02:53 UTC (permalink / raw) To: Adrien NAYRAT <[email protected]>; +Cc: [email protected] 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: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table @ 2018-09-20 06:23 Adrien Nayrat <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adrien Nayrat @ 2018-09-20 06:23 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table @ 2018-09-21 06:13 Michael Paquier <[email protected]> parent: Adrien Nayrat <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Michael Paquier @ 2018-09-21 06:13 UTC (permalink / raw) To: Adrien Nayrat <[email protected]>; +Cc: [email protected] 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: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Mention FK creation take ShareRowExclusiveLock on referenced table @ 2018-09-21 07:09 Adrien NAYRAT <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Adrien NAYRAT @ 2018-09-21 07:09 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [email protected] 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-21 08:49 Michael Paquier <[email protected]> parent: Adrien NAYRAT <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Michael Paquier @ 2018-09-21 08:49 UTC (permalink / raw) To: Adrien NAYRAT <[email protected]>; +Cc: [email protected] 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: [application/pgp-signature] signature.asc (833B, 2-signature.asc) 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 <[email protected]> 2018-09-19 02:53 ` Michael Paquier <[email protected]> 2018-09-20 06:23 ` Adrien Nayrat <[email protected]> 2018-09-21 06:13 ` Michael Paquier <[email protected]> 2018-09-21 07:09 ` Adrien NAYRAT <[email protected]> 2018-09-21 08:49 ` Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox