different type of locks for index operations?

[jjb]

The warning about locks is fantastic and the recent discussion about doing retries is really cool!

I'm wondering if the lock timeout situation is different for indexes than it is for other operations.

• When adding an index, I believe that pg takes a SHARE lock that does not block other db operations. So, I think we don't need to set a lock timeout?
• Same story for dropping concurrently?

PG slack discussion: https://postgresteam.slack.com/archives/C0FS3UTAP/p1604344392130600

[jjb]

perhaps I'm forgetting that both need a brief exclusive lock to get the index into the catalog and get the process started? The docs don't seem to mention this. But some blog posts maybe do?

[ankane]

Hey @jjb, can you add the relevant parts of the discussion to this issue? From my understanding, it should be okay to wait longer for locks that don't block reads or writes, but I'm not sure it's worth the extra complexity (and I'm not sure there's a simple way to do it with PgBouncer).

[jjb]

Thanks for your response. Let me first figure out what type of locks are actually needed and then I'll see if I have something valuable to propose. https://dba.stackexchange.com/questions/280284/what-type-of-locks-are-needed-when-creating-a-postgres-index-concurrently

[jjb]

Okay, @Melkij came in with an epic answer to that question. concurrent index build acquires ShareUpdateExclusiveLock, which will not cause queries to wait. Not sure if this is a good reason to change strong_migrations behavior or not.

https://www.percona.com/blog/2018/10/24/postgresql-locking-part-2-heavyweight-locks/#PostgreSQLlocking-ShareUpdateExclusiveLock(SHAREUPDATEEXCLUSIVE)

[ankane]

Hey @jjb, thanks for the update. We could add a share_lock_timeout option that increases the lock timeout for operations that only need a SHARE* locks like adding and removing indexes (and then sets it back), but I'm not sure it's worth the extra complexity right now. I'd be surprised if most apps are running into regular lock timeouts, and I imagine many that are use a connection pooler like PgBouncer (in transaction mode, where this approach won't work). This may change if Postgres 14 supports a lot more connections without a bouncer.

[jjb]

ok