Creating an index concurrently using asynpg freezes the program

[ionsome]

Describe the bug

Creating postgresql index concurrently causes a program stuck if you have another opened asyncpg transaction.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.29

DBAPI (i.e. the database driver)

asyncpg

Database Vendor and Major Version

PostgreSQL 16

Python Version

3.10

Operating system

OSX

To Reproduce

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text


async_engine = create_async_engine(
    'postgresql+asyncpg://user:password@127.0.0.1:5432/test_db',
    echo=True,
)

async def main() -> None:
    async with async_engine.connect() as connection1:
        await connection1.execute(
            text(
            '''CREATE TABLE IF NOT EXISTS table_1 (
                id INTEGER NOT NULL, 
                data VARCHAR NOT NULL
            )'''
            )
        )
        await connection1.commit()

    async with async_engine.connect() as connection1, \
        async_engine.connect() as connection2:
            await connection1.execute(text('SELECT 1'))  # remove this line and program won't freeze 
            await connection2.execution_options(isolation_level="AUTOCOMMIT")
            await connection2.execute(text('CREATE INDEX CONCURRENTLY ix_1 ON table_1 (data)'))  # stuck


if __name__ == '__main__':
    asyncio.run(main())

Error

Output before the program stuck:

2024-04-22 15:29:00,176 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2024-04-22 15:29:00,176 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-22 15:29:00,180 INFO sqlalchemy.engine.Engine select current_schema()
2024-04-22 15:29:00,180 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-22 15:29:00,183 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2024-04-22 15:29:00,183 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-22 15:29:00,186 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-22 15:29:00,186 INFO sqlalchemy.engine.Engine CREATE TABLE IF NOT EXISTS table_1 (
                id INTEGER NOT NULL, 
                data VARCHAR NOT NULL
            )
2024-04-22 15:29:00,186 INFO sqlalchemy.engine.Engine [generated in 0.00018s] ()
2024-04-22 15:29:00,195 INFO sqlalchemy.engine.Engine COMMIT
2024-04-22 15:29:00,249 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-22 15:29:00,249 INFO sqlalchemy.engine.Engine SELECT 1
2024-04-22 15:29:00,249 INFO sqlalchemy.engine.Engine [generated in 0.00021s] ()
2024-04-22 15:29:00,251 INFO sqlalchemy.engine.Engine BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
2024-04-22 15:29:00,251 INFO sqlalchemy.engine.Engine CREATE INDEX CONCURRENTLY ix_1 ON table_1 (data)
2024-04-22 15:29:00,251 INFO sqlalchemy.engine.Engine [generated in 0.00011s] ()

Additional context

This behaviour affects on op.get_context().autocommit_block() in alembic, there is a separate discussion sqlalchemy/alembic#1461

[zzzeek]

Hi, I'm traveling this week but sqlalchemy is a pass through to the database here which is deadlocking on two concurrent commands and is extremely normal and typical for postgresql. Alembic migrations should always use a single connection only as well. No bug is illustrated here as yet so let's please continue on the alembic discussion without fanning out multiple bug reports . If some behavior or documentation is not ideal we will open a bug at that point, thanks!

[CaselIT]

This seems an asyncpg thing.
This also reproduces:

import asyncio
import asyncpg


url = "postgresql://scott:tiger@127.0.0.1:5432/test"


async def main2():
    connection1 = await asyncpg.connect(url)
    await connection1.execute("drop table if exists table_1 ")
    await connection1.close()

    connection1 = await asyncpg.connect(url)
    await connection1.execute(
        """CREATE TABLE IF NOT EXISTS table_1 (
                id INTEGER NOT NULL, 
                data VARCHAR NOT NULL
            )"""
    )
    await connection1.close()
    connection1 = await asyncpg.connect(url)
    connection2 = await asyncpg.connect(url)
    async with connection1.transaction() as t:
        await connection1.fetch("SELECT 1")
        print("here")
        await connection2.execute(
            "CREATE INDEX CONCURRENTLY ix_1 ON table_1 (data)"
        )


if __name__ == "__main__":
    asyncio.run(main2())

[CaselIT]

For reference psycopg3 works correctly

import asyncio
import psycopg

try:
    asyncio.set_event_loop_policy(asyncio.WindowsSelectorEventLoopPolicy())
except AttributeError:
    pass

url = "postgresql://scott:tiger@127.0.0.1:5432/test"


async def main2():
    async with await psycopg.AsyncConnection.connect(url) as connection1:
        await connection1.execute("drop table if exists table_1 ")
        await connection1.commit()

    async with await psycopg.AsyncConnection.connect(url) as connection1:
        await connection1.execute(
            """CREATE TABLE IF NOT EXISTS table_1 (
                id INTEGER NOT NULL, 
                data VARCHAR NOT NULL
            )"""
        )
        await connection1.commit()
    async with await psycopg.AsyncConnection.connect(
        url
    ) as connection1, await psycopg.AsyncConnection.connect(
        url
    ) as connection2:
        await connection2.set_autocommit(True)
        await (await connection1.execute("SELECT 1")).fetchall()
        print("here")
        await connection2.execute(
            "CREATE INDEX CONCURRENTLY ix_1 ON table_1 (data)"
        )


if __name__ == "__main__":
    asyncio.run(main2())

not really sure what asyncpg is doing here.

[ionsome]

As I understand prepared statements in asyncpg can leave portals that hold old snapshots. And this makes index creation waiting.

https://www.postgresql.org/docs/current/sql-createindex.html

After the second scan, the index build must wait for any transactions that have a snapshot (see Chapter 13) predating the second scan to terminate.

Not sure if it can be fixed but some portals can be closed manually MagicStack/asyncpg#1088

[CaselIT]

It seems very strange that those are causing it since the behaviour happens also after closing the connection.
In the asyncpg example the connection that created the table is closed once that's created, it's not the same connection that hold the transaction, so it should not have any old snapshot held

Psycopg also uses prepared statements i think