Horton

Thread

Commits

  1. Document lock taken on referenced table when adding a foreign key

  1. Mention FK creation take ShareRowExclusiveLock on referenced table

    Adrien NAYRAT <adrien.nayrat@anayrat.info> — 2018-09-18T10:32:54Z

    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,

  2. Re: Mention FK creation take ShareRowExclusiveLock on referenced table

    Michael Paquier <michael@paquier.xyz> — 2018-09-19T02:53:02Z

    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

  3. Re: Mention FK creation take ShareRowExclusiveLock on referenced table

    Adrien NAYRAT <adrien.nayrat@anayrat.info> — 2018-09-20T06:23:45Z

    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

  4. Re: Mention FK creation take ShareRowExclusiveLock on referenced table

    Michael Paquier <michael@paquier.xyz> — 2018-09-21T06:13:52Z

    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

  5. Re: Mention FK creation take ShareRowExclusiveLock on referenced table

    Adrien NAYRAT <adrien.nayrat@anayrat.info> — 2018-09-21T07:09:36Z

    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?

  6. Re: Mention FK creation take ShareRowExclusiveLock on referenced table

    Michael Paquier <michael@paquier.xyz> — 2018-09-21T08:49:31Z

    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