Asyncpg conflicts with autocommit_block

[ionsome]

Describe the bug Creating postgresql index concurrently in migrations using op.get_context().autocommit_block() may cause a program stuck if you have opened asyncpg transaction.

Expected behavior The program shouldn't stuck and migrations must be applied

To Reproduce

I've created a separated repo with reproducible examples: https://github.com/ionsome/alembic-asyncpg-concurrently-poc

docker-compose up -d
python3 example1_async_apply.py

After this logs examples stucks.

2024-04-20 15:11:56,002 INFO sqlalchemy.engine.Engine [no key 0.00014s] ()
2024-04-20 15:11:56,012 INFO sqlalchemy.engine.Engine COMMIT
2024-04-20 15:11:56,019 INFO sqlalchemy.engine.Engine BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
2024-04-20 15:11:56,020 INFO sqlalchemy.engine.Engine INSERT INTO table_1 VALUES(1, 'aboba')
2024-04-20 15:11:56,020 INFO sqlalchemy.engine.Engine [generated in 0.00014s] ()
2024-04-20 15:11:56,024 INFO sqlalchemy.engine.Engine SELECT 1
2024-04-20 15:11:56,024 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ()
2024-04-20 15:11:56,026 INFO sqlalchemy.engine.Engine CREATE INDEX CONCURRENTLY ix_1 ON table_1 (data)
2024-04-20 15:11:56,026 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ()

Versions.

• OS: macOS 14.2.1 (23C71), M1 Pro
• Python: 3.10
• Alembic: 1.13.1
• SQLAlchemy: 2.0.29
• Database: postgresql 16
• DBAPI: asyncpg 0.29.0, psycopg2 2.9.9

Have a nice day!