postgres: set lock_timeout and attempt to run migration repeatedly until success

[wkalt]

A common issue when running postgres migrations that require even a short-duration access exclusive lock, is that lock acquisition blocks until any transactions on a table prior to the lock is requested commit. The transaction requesting the access exclusive lock will in turn block any other transactions initialized after it, until it is able to complete. This means that even for migrations that execute very quickly, such as "alter table x add column y int", if there happens to be a long-running select running on the table before the migration is requested, you can still cause downtime.

To demonstrate this, you can open three psql sessions and try this:

session 1:

create table test(i int);
begin;
select * from test;

session 2 (the dbmate migration):

alter table test add column j int;

session 3 (a read request made after the migration is requested):

select * from test;

until the first session commits, session 3 and any subsequent requests will block on session 2. Once session 1 commits, session 2 will execute immediately.

One strategy to get around this is, before running session 2, set a short lock timeout:

[local]:wyatt=# set lock_timeout = '50ms';
SET
Time: 0.261 ms
[local]:wyatt=# alter table test add column j int;
ERROR:  canceling statement due to lock timeout
Time: 50.277 ms

and instead of running the migration once, run it repeatedly in a loop until it succeeds. This prevents subsequent transactions like session 3 from piling up in the lock queue behind the migration.

It would be useful if dbmate could cause this behavior via an annotation in the migration file, similar to transaction:false.

[wkalt]

it would probably be reasonable for the annotation to allow the desired timeout value to be specified

[wkalt]

note that this option should preclude transaction:false, since migrations run outside a transaction may not be safe to retry on failure. Though, going off memory there may already be a guard in place against multiple statements in a transaction:false migration.