I am developing an app together with a partner. I do the database part in PostgreSQL, my partner implements the app on the web-server with Python using SQLAlchemy. We make heavy use of server-side functions. A SELECT query on one of those looks like this in the DB log:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT col_a, col_b FROM some_func(E'myvalue');
ROLLBACK;
In the functions I write certain input to a log table. The app queries by SELECT, SQLAlchemy only sees a SELECT statement and insists on a ROLLBACK. Logging fails. I need it to COMMIT instead. My partner claims there is no easy way, we would have to remove SQLAlchemy altogether. I think he must be wrong but lack the knowledge to claim otherwise.
Is there an easy way to make SQLAlchemy COMMIT instead of ROLLBACK?
What keeps me from just executing trans.commit()? Do I need to set autoflush=False for that?
I have scanned the FAQ, but did not find an answer there.
Searching SO revealed some related questions like here and here, but I am not in the clear.
Maybe this recipe would work?
If you're using SQLAlchemy's connection pooling, then what you're seeing is probably the automatic rollback that happens when a connection is closed after use. It's apparently necessary to guarantee that the connection is 'clean' for the next time it's pulled out of the pool. See this page for more info; search for 'pooling mechanism' near the top.
From what I recall (it's been a couple years since I last worked with this) changing the isolation level to autocommit won't solve the problem, since it won't see the SELECT statement as requiring a commit.
You really just want to wrap that statement in a transaction. I don't know how your code is structured, but you should just be able to use SQLAlchemy's connection.begin and connection.commit. You could even just execute the BEGIN and COMMIT as arbitrary SQL.