3 ms·

Huh. I was sure I was right about this but I tested and realised you are correct. CREATE INDEX (even with CONCURRENTLY) gets blocked by reads while waiting to a

Huh. I was sure I was right about this but I tested and realised you are correct. CREATE INDEX (even with CONCURRENTLY) gets blocked by reads while waiting to acquire the lock it needs, but it doesn't block reads during that time. I was getting confused because I'd seen my attempts to CONCURRENTLY add indexes time out waiting for locks, and had also experienced some of the other kinds of DDL that block reads while waiting for their lock (see https://dba.stackexchange.com/q/293992/18607 https://dba.stackexchange.com/q/293992/18607), and I guess I just fallaciously assumed that index creation therefore also blocked reads without ever having tested it. ~Will edit my prior post to add a note about the error!~ Nope, doesn't seem I'm allowed to edit it any more.

petergeoghegan 5y ago
CONCURRENTLY needs to wait for old transactions to go away, including those that haven't (and won't ever) touch the table that you're building an index on. So it's not waiting for a lock as such - it's waiting for older transactions to go away without conflicting in a way that can cause these "traffic jams". This can be a problem for the obvious reason, though generally only for the index build itself. Tricky problems with relation-level locks tend to come from a combination of one lock request that is "generally non-disruptive but long-lived", and another lock request that is "generally disruptive/blocking but short-lived". I have heard of quite a few problem scenarios where these locks conflict with each other (usually by chance), leading to "generally disruptive/blocking and long-lived" -- which can be very dangerous. But that's fundamental to how lock managers work in general. The Postgres implementation tries to make it as unlikely as reasonably possible. For example, autovacuum usually notices when something like this happens, and cancels itself.

petergeoghegan 5y ago

CONCURRENTLY needs to wait for old transactions to go away, including those that haven't (and won't ever) touch the table that you're building an index on. So it's not waiting for a lock as such - it's waiting for older transactions to go away without conflicting in a way that can cause these "traffic jams". This can be a problem for the obvious reason, though generally only for the index build itself. Tricky problems with relation-level locks tend to come from a combination of one lock request that is "generally non-disruptive but long-lived", and another lock request that is "generally disruptive/blocking but short-lived". I have heard of quite a few problem scenarios where these locks conflict with each other (usually by chance), leading to "generally disruptive/blocking and long-lived" -- which can be very dangerous. But that's fundamental to how lock managers work in general. The Postgres implementation tries to make it as unlikely as reasonably possible. For example, autovacuum usually notices when something like this happens, and cancels itself.