1. 20
    1. 5

      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.

      1. 4

        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.

        1. 1

          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.

      2. 2

        I feel like between ANALYZE and the really solid docs this isn’t that big of an issue though.

      3. 2

        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.

      4. 1

        Maybe lock_timeout is good enough to control when you want locks to hold. But I agree that a static analysis can be great too.

        1. 2

          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.

          1. 1

            for sure lock_timeout will 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.