Well, when documentation is not enough it is possible to looking directly in code. I will give links to already released PostgreSQL 13.1
(REL_13_1
tag) in order to avoid possible line number changes in future.
I probably will not describe the entire code path of the command from the client. By grammar we know that we need IndexStmt
command node - same grammar for both concurrently and non-concurrently create index. Everything useful will start in ProcessUtilitySlow
And here, in comment to case T_IndexStmt
, can be found direct answer for question:
/*
* .... To avoid lock upgrade hazards, it's
* important that we take the strongest lock that will
* eventually be needed here, so the lockmode calculation
* needs to match what DefineIndex() does.
*/
lockmode = stmt->concurrent ? ShareUpdateExclusiveLock
: ShareLock;
In general PostgreSQL will do not upgrade lock during command execution. And will take the strongest lock that will eventually be needed. For CREATE INDEX CONCURRENTLY
it's ShareUpdateExclusiveLock
. Not a SHARE
lock as mentioned in question.
But let's continue reading:
- in case of partitioned table
find_all_inheritors
will acquire same lockmode on inheritors.
- in
transformIndexStmt
can be found some locking modes, but NoLock
and AccessShareLock
- they are weaker than ShareUpdateExclusiveLock
- in
DefineIndex
we see another reminder:
* To avoid lock upgrade hazards, that lock should be at least
* as strong as the one we take here.
And decide to take againg ShareUpdateExclusiveLock for concurrently build. This part of the code is noticeably long (still not as large as, for example, planner), but readable.
I am missing something? Probably. So let's build PostgreSQL with -DLOCK_DEBUG
option to debug locking behavior and see that happens:
./configure --prefix=/home/melkij/tmp/pgdev/inst --enable-cassert --enable-debug CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG" --enable-tap-tests
make -sj 4
make install
initdb ...
pg_ctl start ...
# then psql with follow commands
create table foo as select generate_series(1,1000) as id;
set trace_locks = true;
create index concurrently on foo (id);
select oid, relname, relkind from pg_class where oid in (16387,16393);
# oid | relname | relkind
#-------+------------+---------
# 16387 | foo | r
# 16393 | foo_id_idx | i
trace_locks
will log many things, but we are interested in:
[vxid:3/62 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16393] AccessExclusiveLock
[vxid:3/62 txid:495] [CREATE INDEX] LOG: LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16393] ExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG: LockAcquire: lock [12664,16393] ExclusiveLock
lock [12664,16387]
here means database OID = 12664
, pg_class
OID = 16387
(such rows are logged from LockAcquireExtended
).
So, we actually acquire only ShareUpdateExclusiveLock
on table itself. OID=16393
with several other locks is the index that this command built. Although heavy locking levels are mentioned for this object, it will not interfere with other queries. Create index concurrently
command is specially designed to work safely while running normal application queries.