Thread: documentation about explicit locking
Hi. I wonder why we mention on the following page that CREATE COLLATION requires SHARE ROW EXCLUSIVE lock https://www.postgresql.org/docs/devel/static/explicit-locking.html I know that's the lock taken on the pg_collation catalog, but do we need to mention locks taken by a DDL command on the catalogs it affects? All other commands mentioned on the page require to specify the table name that the lock will be taken on. Thanks, Amit
On Wed, Jul 4, 2018 at 3:09 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote: > I wonder why we mention on the following page that CREATE COLLATION > requires SHARE ROW EXCLUSIVE lock > > https://www.postgresql.org/docs/devel/static/explicit-locking.html > > I know that's the lock taken on the pg_collation catalog, but do we need > to mention locks taken by a DDL command on the catalogs it affects? All > other commands mentioned on the page require to specify the table name > that the lock will be taken on. Yes, that looks odd. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2018/07/05 23:02, Robert Haas wrote: > On Wed, Jul 4, 2018 at 3:09 AM, Amit Langote > <Langote_Amit_f8@lab.ntt.co.jp> wrote: >> I wonder why we mention on the following page that CREATE COLLATION >> requires SHARE ROW EXCLUSIVE lock >> >> https://www.postgresql.org/docs/devel/static/explicit-locking.html >> >> I know that's the lock taken on the pg_collation catalog, but do we need >> to mention locks taken by a DDL command on the catalogs it affects? All >> other commands mentioned on the page require to specify the table name >> that the lock will be taken on. > > Yes, that looks odd. OK, here is a patch. I see that it was one of Peter E's commits that added that, so cc'd him. Thanks, Amit
Attachment
On 06.07.18 04:00, Amit Langote wrote: > On 2018/07/05 23:02, Robert Haas wrote: >> On Wed, Jul 4, 2018 at 3:09 AM, Amit Langote >> <Langote_Amit_f8@lab.ntt.co.jp> wrote: >>> I wonder why we mention on the following page that CREATE COLLATION >>> requires SHARE ROW EXCLUSIVE lock >>> >>> https://www.postgresql.org/docs/devel/static/explicit-locking.html >>> >>> I know that's the lock taken on the pg_collation catalog, but do we need >>> to mention locks taken by a DDL command on the catalogs it affects? All >>> other commands mentioned on the page require to specify the table name >>> that the lock will be taken on. >> >> Yes, that looks odd. > > OK, here is a patch. > > I see that it was one of Peter E's commits that added that, so cc'd him. The reason this is mentioned is that CREATE COLLATION takes a SHARE ROW EXCLUSIVE lock on pg_collation whereas similar CREATE commands only take a ROW EXCLUSIVE lock on their catalogs. (So you can only have one CREATE COLLATION running at a time. The reasons for this are explained in pg_collation.c.) I think mentioning this was requested during patch review. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018/07/18 18:30, Peter Eisentraut wrote: > On 06.07.18 04:00, Amit Langote wrote: >> On 2018/07/05 23:02, Robert Haas wrote: >>> On Wed, Jul 4, 2018 at 3:09 AM, Amit Langote >>> <Langote_Amit_f8@lab.ntt.co.jp> wrote: >>>> I wonder why we mention on the following page that CREATE COLLATION >>>> requires SHARE ROW EXCLUSIVE lock >>>> >>>> https://www.postgresql.org/docs/devel/static/explicit-locking.html >>>> >>>> I know that's the lock taken on the pg_collation catalog, but do we need >>>> to mention locks taken by a DDL command on the catalogs it affects? All >>>> other commands mentioned on the page require to specify the table name >>>> that the lock will be taken on. >>> >>> Yes, that looks odd. >> >> OK, here is a patch. >> >> I see that it was one of Peter E's commits that added that, so cc'd him. > > The reason this is mentioned is that CREATE COLLATION takes a SHARE ROW > EXCLUSIVE lock on pg_collation whereas similar CREATE commands only take > a ROW EXCLUSIVE lock on their catalogs. (So you can only have one > CREATE COLLATION running at a time. The reasons for this are explained > in pg_collation.c.) I think mentioning this was requested during patch > review. I see. Although, which lock we take on the system catalog for implementing a particular command seems to be an internal detail. What's clearly user-visible in this case is that CREATE COLLATION command cannot be used simultaneously by concurrent sessions, so it should be pointed out in the CREATE COLLATION command's documentation. On a quick check, it doesn't seem to be. So, I have updated my patch to also add a line about that on CREATE COLLATION page. What do you think? When playing with this, I observed that a less user-friendly error message is emitted if multiple sessions race to create the same collation. Session 1: begin; create collation collname (...); Session 2: create collation collname (...); <blocks for lock on pg_collation> Session 1: commit; Session 2: ERROR: duplicate key value violates unique constraint "pg_collation_name_enc_nsp_index" DETAIL: Key (collname, collencoding, collnamespace)=(collname, 6, 2200) already exists. I figured that's because the order in CollationCreate of locking the catalog and checking in syscache whether a duplicate exists. I think we should check the syscache for duplicate *after* we have locked the catalog, as done in the other patch that's attached. Thanks, Amit
Attachment
Hello. At Thu, 19 Jul 2018 13:17:14 +0900, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote in <85dc6464-eb59-ba59-75d3-09b292fa853d@lab.ntt.co.jp> > On 2018/07/18 18:30, Peter Eisentraut wrote: > > On 06.07.18 04:00, Amit Langote wrote: > >> On 2018/07/05 23:02, Robert Haas wrote: > >>> On Wed, Jul 4, 2018 at 3:09 AM, Amit Langote > >>> <Langote_Amit_f8@lab.ntt.co.jp> wrote: > >>>> I wonder why we mention on the following page that CREATE COLLATION > >>>> requires SHARE ROW EXCLUSIVE lock > >>>> > >>>> https://www.postgresql.org/docs/devel/static/explicit-locking.html > >>>> > >>>> I know that's the lock taken on the pg_collation catalog, but do we need > >>>> to mention locks taken by a DDL command on the catalogs it affects? All > >>>> other commands mentioned on the page require to specify the table name > >>>> that the lock will be taken on. > >>> > >>> Yes, that looks odd. > >> > >> OK, here is a patch. > >> > >> I see that it was one of Peter E's commits that added that, so cc'd him. > > > > The reason this is mentioned is that CREATE COLLATION takes a SHARE ROW > > EXCLUSIVE lock on pg_collation whereas similar CREATE commands only take > > a ROW EXCLUSIVE lock on their catalogs. (So you can only have one > > CREATE COLLATION running at a time. The reasons for this are explained > > in pg_collation.c.) I think mentioning this was requested during patch > > review. > > I see. Although, which lock we take on the system catalog for > implementing a particular command seems to be an internal detail. What's > clearly user-visible in this case is that CREATE COLLATION command cannot > be used simultaneously by concurrent sessions, so it should be pointed out > in the CREATE COLLATION command's documentation. On a quick check, it > doesn't seem to be. So, I have updated my patch to also add a line about > that on CREATE COLLATION page. What do you think? I'm not Peter but I have a comment on this. + Note that only one of the concurrent sessions can run + <command>CREATE COLLATION</command> at a time. The description seems to me to be failing to give clear idea of what operation causes what behavior. I agree that the description in the explicit-locking section is out of the place since it is not a lock on *specified* tables. I'd like to have a description with the similar level here instead, like this: Note that CREATE COLLATION takes a SHARE ROW EXLUCSIVE lock on pg_collation system calatlog, which blocks other concurrent CREATE COLLATION. > When playing with this, I observed that a less user-friendly error message > is emitted if multiple sessions race to create the same collation. > > Session 1: > begin; > create collation collname (...); > > Session 2: > create collation collname (...); > <blocks for lock on pg_collation> > > Session 1: > commit; > > Session 2: > ERROR: duplicate key value violates unique constraint > "pg_collation_name_enc_nsp_index" > DETAIL: Key (collname, collencoding, collnamespace)=(collname, 6, 2200) > already exists. > > I figured that's because the order in CollationCreate of locking the > catalog and checking in syscache whether a duplicate exists. I think we > should check the syscache for duplicate *after* we have locked the > catalog, as done in the other patch that's attached. Such cases in other commands usually have a very narrow window but the said lock widens the window very much in the case:p So +1 from me to this change. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Horiguchi-san, Thanks for taking a look. On 2018/07/19 18:23, Kyotaro HORIGUCHI wrote: > Hello. > > At Thu, 19 Jul 2018 13:17:14 +0900, Amit Langote wrote: >> On 2018/07/18 18:30, Peter Eisentraut wrote: >>> The reason this is mentioned is that CREATE COLLATION takes a SHARE ROW >>> EXCLUSIVE lock on pg_collation whereas similar CREATE commands only take >>> a ROW EXCLUSIVE lock on their catalogs. (So you can only have one >>> CREATE COLLATION running at a time. The reasons for this are explained >>> in pg_collation.c.) I think mentioning this was requested during patch >>> review. >> >> I see. Although, which lock we take on the system catalog for >> implementing a particular command seems to be an internal detail. What's >> clearly user-visible in this case is that CREATE COLLATION command cannot >> be used simultaneously by concurrent sessions, so it should be pointed out >> in the CREATE COLLATION command's documentation. On a quick check, it >> doesn't seem to be. So, I have updated my patch to also add a line about >> that on CREATE COLLATION page. What do you think? > > I'm not Peter but I have a comment on this. > > + Note that only one of the concurrent sessions can run > + <command>CREATE COLLATION</command> at a time. > > The description seems to me to be failing to give clear idea of > what operation causes what behavior. I agree that the description > in the explicit-locking section is out of the place since it is > not a lock on *specified* tables. I'd like to have a description > with the similar level here instead, like this: > > Note that CREATE COLLATION takes a SHARE ROW EXLUCSIVE lock on > pg_collation system calatlog, which blocks other concurrent > CREATE COLLATION. We don't explicitly mention what locks we take on system catalogs elsewhere, but CREATE COLLATION is different from other commands, so I'm fine with adding more details as you suggest, so updated the text. >> When playing with this, I observed that a less user-friendly error message >> is emitted if multiple sessions race to create the same collation. >> >> Session 1: >> begin; >> create collation collname (...); >> >> Session 2: >> create collation collname (...); >> <blocks for lock on pg_collation> >> >> Session 1: >> commit; >> >> Session 2: >> ERROR: duplicate key value violates unique constraint >> "pg_collation_name_enc_nsp_index" >> DETAIL: Key (collname, collencoding, collnamespace)=(collname, 6, 2200) >> already exists. >> >> I figured that's because the order in CollationCreate of locking the >> catalog and checking in syscache whether a duplicate exists. I think we >> should check the syscache for duplicate *after* we have locked the >> catalog, as done in the other patch that's attached. > > Such cases in other commands usually have a very narrow window > but the said lock widens the window very much in the case:p So +1 > from me to this change. Attached updated patches. Thanks, Amit
Attachment
On 20/07/2018 02:30, Amit Langote wrote: > We don't explicitly mention what locks we take on system catalogs > elsewhere, but CREATE COLLATION is different from other commands, so I'm > fine with adding more details as you suggest, so updated the text. committed the documentation change -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 19/07/2018 06:17, Amit Langote wrote: > When playing with this, I observed that a less user-friendly error message > is emitted if multiple sessions race to create the same collation. > > Session 1: > begin; > create collation collname (...); > > Session 2: > create collation collname (...); > <blocks for lock on pg_collation> > > Session 1: > commit; > > Session 2: > ERROR: duplicate key value violates unique constraint > "pg_collation_name_enc_nsp_index" > DETAIL: Key (collname, collencoding, collnamespace)=(collname, 6, 2200) > already exists. You get the same behavior with for example CREATE FUNCTION or CREATE TYPE. I don't think we need to fix this specifically for CREATE COLLATION. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018/07/31 5:25, Peter Eisentraut wrote: > On 20/07/2018 02:30, Amit Langote wrote: >> We don't explicitly mention what locks we take on system catalogs >> elsewhere, but CREATE COLLATION is different from other commands, so I'm >> fine with adding more details as you suggest, so updated the text. > > committed the documentation change Thank you. Regards, Amit
On 2018/07/31 5:27, Peter Eisentraut wrote: > On 19/07/2018 06:17, Amit Langote wrote: >> When playing with this, I observed that a less user-friendly error message >> is emitted if multiple sessions race to create the same collation. >> >> Session 1: >> begin; >> create collation collname (...); >> >> Session 2: >> create collation collname (...); >> <blocks for lock on pg_collation> >> >> Session 1: >> commit; >> >> Session 2: >> ERROR: duplicate key value violates unique constraint >> "pg_collation_name_enc_nsp_index" >> DETAIL: Key (collname, collencoding, collnamespace)=(collname, 6, 2200) >> already exists. > > You get the same behavior with for example CREATE FUNCTION or CREATE > TYPE. I don't think we need to fix this specifically for CREATE COLLATION. Hmm, yeah. Although fixing the race for CREATE COLLATION seems easier than other cases due to the self-exclusive lock on the catalog, that doesn't necessarily mean we have to. Thanks, Amit