Mention FK creation take ShareRowExclusiveLock on referenced table

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

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

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

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