Re: documentation about explicit locking

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