CodeMaestro Comments - Answer Overflow

CodeMaestro

•Created by CodeMaestro on 7/26/2024 in #help

xata raises error if `RESET ALL;` is called

More detail: I split the pool.acquire() and pool.release() calls to make it more obvious that the release raises the error (normally you'd use a context manager). The stack goes pretty deep but ultimately you end up in asyncpg/connection.py line 1500 in Connection.reset which is await self.execute(reset_query, timeout=timeout). Using the debugger I can see that reset_query=

SELECT pg_advisory_unlock_all();
CLOSE ALL;
UNLISTEN *;
RESET ALL;

SELECT pg_advisory_unlock_all();
CLOSE ALL;
UNLISTEN *;
RESET ALL;

Using this information we can modify our reproduction code like this:

import asyncio
import asyncpg


async def main():
    # Put a real connection string in here
    db_url_pg = "postgresql://<WORKSPACE_ID>:<API_KEY>@<REGION>.sql.xata.sh/<DATABASE_NAME>:<BRANCH>?ssl=require"
    
    # Use a connection instead of a pool
    connection = await asyncpg.connect(dsn=db_url_pg)
    sqls = [
        "SELECT pg_advisory_unlock_all();",
        "CLOSE ALL;",
        "UNLISTEN *;",
        "RESET ALL;",
    ]
    for sql in sqls:
        resp = await connection.execute(sql)
        print(f"SUCCESS: {sql=} {resp=}")  # Fails on RESET ALL;
    await connection.close()


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

import asyncio
import asyncpg


async def main():
    # Put a real connection string in here
    db_url_pg = "postgresql://<WORKSPACE_ID>:<API_KEY>@<REGION>.sql.xata.sh/<DATABASE_NAME>:<BRANCH>?ssl=require"
    
    # Use a connection instead of a pool
    connection = await asyncpg.connect(dsn=db_url_pg)
    sqls = [
        "SELECT pg_advisory_unlock_all();",
        "CLOSE ALL;",
        "UNLISTEN *;",
        "RESET ALL;",
    ]
    for sql in sqls:
        resp = await connection.execute(sql)
        print(f"SUCCESS: {sql=} {resp=}")  # Fails on RESET ALL;
    await connection.close()


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

This confirms that the first 3 statements succeed and that RESET ALL; is the failure. RESET ALL is correct postgres syntax so I suspect that xata is raising the setting "" is not allowed server side.

3 replies

Gaming

Programming