Postgresql – set the maximum locking level that a transaction can acquire

lockingpostgresql

What I want to achieve is something like this:

SET LOCAL max_locking_level="ACCESS SHARE";
ALTER TABLE foo ALTER COLUMN bar SET NOT NULL; -- Error, since it needs an ACCESSS EXCLUSIVE lock

But I haven't been able to find the right setting in the documentation yet.

For some context: we're using flywheel for scripted database migrations, and part of the review process is to make sure that migrations can be applied without downtime. One of the ways we're achieving this is by enforcing (configurable) statement/lock timeouts on all migrations. This is working well, but it still leaves room for error: some migrations need a long statement/lock timeout, which can be acceptable if it doesn't use exclusive locks. It has happened that developers underestimated the lock level an operation needed, and as a result created migrations that hold long ACCESS EXCLUSIVE locks, which will cause downtime when deployed. The only thing that prevents this is a good eye from code reviewers, which isn't reliable enough.

I'm hoping there is a way that we can enforce a max lock level, as this will make it easier to ensure prevent long exclusive locks (i.e. only allow long migrations if the max lock level is low)

Best Answer

In the case of ACCESS SHARE, you could do that with

SET default_transaction_read_only = on;

That will cause an error as soon as anybody wants to take a lock higher than ACCESS SHARE.

But it seems that your real objective is to detect if a transaction takes ACCESS EXCLUSIVE locks. You could do that by querying the pg_locks table after the statements are done:

BEGIN;

/* run statements that lock objects */

SELECT locktype, relation::regclass, objid, classid::regclass
FROM pg_locks
WHERE mode = 'AccessExclusiveLock';

ROLLBACK;

You'd run that on a test database, and it should show you all relations (tables, indexes, sequences, materialized views, or views) and other objects (where classid is the name of the catalog for the object) on which the statements take an ACCESS EXCLUSIVE lock.

Related Question