python - Alembic migration stuck with postgresql? -

i wrote migration script works fine on sqlite, if try apply same postgres stuck forever. simple ps can see postres stuck on "create table waiting". there best practice?

if it's stuck on lock, need see it's waiting for. it'd pretty odd create table stuck on lock, it's not impossible.

get stuck process id

get process id of waiting backend. can find in ps, or selecting pg_stat_activity, looking processes waiting true, find command you're interested in:

select * pg_stat_activity waiting; 

figure out lock it's waiting on

take @ lock stuck pid waiting on querying pg_locks:

select * pg_locks pid = <the-waiting-pid> , not granted; 

you can combine both these steps with:

\x  select *  pg_locks l inner join pg_stat_activity s on (l.pid = s.pid) waiting , not granted; 

then through results, or use like filter on s.query field find query you're trying identify locking issues for.

find holds lock

you can query pg_locks find out process(es) find lock, , they're doing.

say found create waiting locktype=relation lock of mode=accessexclusivelock on relation=14421 granted. want find locks held other sessions on relation:

select *  pg_locks l  inner join pg_stat_activity s on (l.pid = s.pid) locktype = 'relation' ,   relation = 14421; 

this should tell what's blocking create.

wrinkles

there's a handy lock monitoring query on postgresql wiki, it'll find row level locks. it's not helpful ddl.

also, i've intentionally not combined whole lot single query. it'd simple enough find lock holders block given backend pid in case of accessexclusivelock, weaker lock requests, it's not simple - i'd have write out rules locks conflict in sql, , that's pretty complicated. better eyeball it.