Thread: Mention FK creation take ShareRowExclusiveLock on referenced table
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,
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
Attachment
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
Attachment
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
Attachment
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?
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