How to run an alter table migration with alembic - taking too long and never ends

Question

I'm trying to run a migration with alembic (add a column) but it taking too long - and never ends. The table has 100 rows and i don't see an error.

This is my migration code in python

"""

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'd6fe1dec4bcd'
down_revision = '3f532791c5f3'
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column('products2', sa.Column(
        'product_status', sa.String(255)))


def downgrade() -> None:
    op.drop_column('products2', 'product_status')

This is what i see in postgres when I check

SELECT * FROM pg_stat_activity WHERE state = 'active';
ALTER TABLE products2 ADD COLUMN product_status VARCHAR(255)

This is what I see in terminal

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 3f532791c5f3 -> d6fe1dec4bcd, create product status column

How can I fix this? I'm running the postgres in a Google Cloud COnsole, but i don`t see any error on their platform

Perhaps something is locking the table? The queries [here](https://wiki.postgresql.org/wiki/Lock_Monitoring) may help. — snakecharmerb, Dec 04 '22 at 21:01
these queries are returning the same alter table I created. Any ideas on how to move from here? — Filipe Ferminiano, Dec 04 '22 at 21:04
Perhaps the alter table is being run twice? It's hard to know what is going on, but if those queries are returning result then the problem is that the table is locked, and the migration is waiting because it needs an exclusive lock to perform the alter table. — snakecharmerb, Dec 04 '22 at 21:13
https://stackoverflow.com/questions/1063043/how-to-release-possible-postgres-row-locks this post solves my question — Filipe Ferminiano, Dec 04 '22 at 21:14
Where is the rest of the output of "SELECT * FROM pg_stat_activity"? — jjanes, Dec 05 '22 at 02:06

score 0 · Accepted Answer · answered Dec 04 '22 at 21:14

Get the active locks from pg_locks:

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t 
WHERE l.relation = t.relid 
ORDER BY relation asc;
Copy the pid(ex: 14210) from above result and substitute in the below command.

SELECT pg_terminate_backend(14210)

How to run an alter table migration with alembic - taking too long and never ends

1 Answers1