What if Postgres had a “footgun-avoidance” command, especially for database migrations, that would prevent certain locks from being taken during a session? For example, an access exclusive lock is quite painful on a production system, so it would be great to disable commands using that lock, unless in a maintenance window.
Even if the lock is held momentarily, it’s not too hard to introduce deadlocks to the overall system. Especially if more than one table is locked at a time and if the connected applications are running complex transactions.
lock_timeout aborts a query instead of waiting for a lock to become available. The problem @tedchs is describing is that the migration would acquire the initial lock (which presumably wouldn’t have to wait, depending on situation of course). You’d still have outage with lock_timeout in such situations.
What if Postgres had a “footgun-avoidance” command, especially for database migrations, that would prevent certain locks from being taken during a session? For example, an access exclusive lock is quite painful on a production system, so it would be great to disable commands using that lock, unless in a maintenance window.
It’s only really a problem if the lock will be held “too long”, but that depends on the situation - large tables or ones that receive lots of writes.
Even if the lock is held momentarily, it’s not too hard to introduce deadlocks to the overall system. Especially if more than one table is locked at a time and if the connected applications are running complex transactions.
I feel like between ANALYZE and the really solid docs this isn’t that big of an issue though.
Sort of like BSD pledge(2), but for “I promise to acquire no locks other than {set}” instead of making system calls? I like it.
Maybe
lock_timeoutis good enough to control when you want locks to hold. But I agree that a static analysis can be great too.lock_timeoutaborts a query instead of waiting for a lock to become available. The problem @tedchs is describing is that the migration would acquire the initial lock (which presumably wouldn’t have to wait, depending on situation of course). You’d still have outage withlock_timeoutin such situations.for sure
lock_timeoutwill abort, I forgot to include that it requires a retry-loop (ideally with some exponential/random backoff) for it to be useful, a bit like I did in https://github.com/docteurklein/declarative-sql-migrations/blob/main/src/exec.sql#L13-L20.