[petergeoghegan]

"So far, so good. What's the problem here? The DDL statement can simply wait patiently until it's able to acquire its ACCESS EXCLUSIVE lock, right? The problem is that any other statements that require a lock on the users table are now queued behind this ALTER TABLE statement, including other SELECT statements that only require ACCESS SHARE locks."

This is the most important individual point that the blog post makes in my view. Lots of Postgres users are left with the wrong general idea about how things in this area work (e.g., the locking implications of autovacuum), just because they missed this one subtlety.

I'm not sure what can be done about that. It seems like Postgres could do a better job of highlighting when an interaction between two disparate transactions/sessions causes disruption.

[Fire-Dragon-DoL]

Yes this is the keypoint. There is a famous blogpost on how to address this which boils down to acquiring a lock explicitly with a very short timeout, and only after acquiring the lock the alter statement is executed. Do this in a loop and essentially it prevents the disaster, but it might fail the migration if unable to take the lock (just repeat the migration)

[Jcowell]

> the locking implications of autovacuum

I thought the only case this would be a worry is if the auto vacuum is to prevent wraparound