Xata•2mo ago

xata raises error if `RESET ALL;` is called

I have encountered a bug with xata when using asyncpg and a connection pool. Root cause: When a connection is released back into the pool, the RESET ALL; is executed and xata raises a setting "" is not allowed error. Minimum reproduction code:

import asyncio
import asyncpg  # Using asyncpg==0.29.0


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"
    pool = await asyncpg.create_pool(dsn=db_url_pg)
    connection = await pool.acquire()
    version = await connection.fetchval("SELECT version();")
    print(version)
    await pool.release(connection, timeout=10)  # Raises asyncpg.exceptions._base.UnknownPostgresError: setting "" is not allowed


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

import asyncio
import asyncpg  # Using asyncpg==0.29.0


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"
    pool = await asyncpg.create_pool(dsn=db_url_pg)
    connection = await pool.acquire()
    version = await connection.fetchval("SELECT version();")
    print(version)
    await pool.release(connection, timeout=10)  # Raises asyncpg.exceptions._base.UnknownPostgresError: setting "" is not allowed


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

2 Replies

CodeMaestro•2mo ago

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.

PostgreSQL Documentation

RESET

RESET RESET — restore the value of a run-time parameter to the default value Synopsis RESET configuration_parameter RESET ALL Description …

kostas•2mo ago

Hi, thank you for the report and the detailed investigation! This is indeed the root cause of the issue, as RESET is not yet in the list of supported statements: https://xata.io/docs/postgres#supported-statements During the Beta phase we're striving to improve support for full Postgres syntax coverage. I've also added an internal issue to follow up on this particular statement and we'll let you know as soon as we have an update.

Gaming

Programming

xata raises error if `RESET ALL;` is called