Creating an index concurrently using asynpg freezes the program about sqlalchemy

[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:[email protected]: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:[email protected]: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