Alembic migration stuck with postgresql?

Question

I wrote a migration script which works fine on sqlite, but if i try to apply the same to postgres it is stuck forever. With a simple ps i can see the postres stuck on "create table waiting". There are any best practice?

Answer 1

If it's really stuck on a lock, you need to see what it's waiting for. It'd be pretty odd for CREATE TABLE to be stuck on a lock, but it's not impossible.

Get the stuck process id

Get the process ID of the waiting backend. You can find it in ps, or by SELECTing from pg_stat_activity, looking for processes with waiting true, to find the command you're interested in:

SELECT * FROM pg_stat_activity WHERE waiting;

Figure out what lock it's waiting on

Take a look at the lock the stuck pid is waiting on by querying pg_locks:

SELECT * FROM pg_locks WHERE pid = <the-waiting-pid> AND NOT granted;

You can combine both these steps with:

\x

SELECT * 
FROM pg_locks l
INNER JOIN pg_stat_activity s ON (l.pid = s.pid)
WHERE waiting
AND NOT granted;

then look through the results, or use a LIKE filter on the s.query field to find the query you're trying to identify locking issues for.

Find who holds that lock

You can now query pg_locks to find out which process(es) find that lock, and what they're doing.

Say we found that the create was waiting for a locktype=relation lock of mode=AccessExclusiveLock on relation=14421 to be granted. We want to find locks held by other sessions on that relation:

SELECT * 
FROM pg_locks l 
INNER JOIN pg_stat_activity s ON (l.pid = s.pid)
WHERE locktype = 'relation'
AND   relation = 14421;

This should tell you what's blocking the create.

Wrinkles

There's a handy lock monitoring query on the PostgreSQL wiki, but it'll only find row level locks. So it's generally not helpful for DDL.

Also, I've intentionally not combined the whole lot into a single query. It'd be simple enough to find lock holders that block a given backend by pid in the case of an AccessExclusiveLock, but for weaker lock requests, it's not so simple - I'd have to write out the rules about which locks conflict with which in SQL, and that's pretty complicated. Better to just eyeball it.