Error with stored procedure with commit

[lucabergamini]

What action do you want to perform

I'm running a test which does something similar to to following code:

stored_proc_create = """
    CREATE OR REPLACE PROCEDURE test_stored_procedure_commit() LANGUAGE plpgsql AS $$
    BEGIN
        COMMIT;
    END;
    $$;
    """

    async with db.session() as session:
        await session.execute(sa.text(stored_proc_create))

    async with db.session(SessionType.AUTO_COMMIT) as session:
        await session.execute(sa.text("CALL test_stored_procedure_commit();"))

where db is a fixture from the library:

postgresql_proc_load_db = factories.postgresql_proc(load=[load_database])
postgresql_load_db = factories.postgresql("postgresql_proc_load_db")

(load_database does some initialisation for some data, this is part of a bigger project)

My setup is sqlalchemy over asyncpg and I'm ensuring I'm setting the session to auto-commit level in sqlalchemy.

What are the results

I'm getting

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.InvalidTransactionTerminationError'>: invalid transaction termination

which points to nested transactions or a session without autocommit preventing the stored procedure to commit

What are the expected results

The same exact code works while running regular code outside a test. Is this expected to work with pytest-postgres?

I can try to get to a more minimal example if needed.

[fizyk]

Hmmmm.... @lucabergamini the only commit there might be in the load_database is the one you'd place there 🤔

I'd gladly take a look at the minimal example