SQLAlchemy utilizes connection pooling. This means that the same connection can be reused across different SQLAlchemy Sessions. However, a single SQLAlchemy session is contained within itself and discarded after closing. The connection however, stays 'active'.

I want to save something into the database session storage in Postgresql, using set_config:

PERFORM set_config('session.storage', 'remember-me-across-this-session', false)

Now, this is in scope of a database session. My question is: When SQLAlchemy creates a new SQLAlchemy Session using the same connection, does this also create a new database session, or will the connection re-use the same database session across it's lifetime?

Note: I have tried to test this already by setting all the relevant pool sizes to minimum (max_overflow 0, pool_size 1, pool_recycle 600) and then running the following script (simplified):

print(DBSESSION.execute(text("SELECT set_config('session.storage', 'remember-me-across-this-session', false)")).first())
print(DBSESSION.execute(text("SELECT current_setting('session.storage')")).first())
transaction.commit()
DBSESSION.close()

for _ in range(5):
    print(DBSESSION.execute(text("SELECT current_setting('session.storage')")).first())
    transaction.commit()
    DBSESSION.close()

This test did not "remember" the value set in the first line across the following sessions in the loop, thus confirming that the database session does get reset between SQLAlchemy sessions across the same connection. However because this is a critical part of the logic, I'd love a second opinion / affirmation to make sure I didn't screw up.

CodePudding user response：

1. Does SQLAlchemy reset the database session?

No, it does not.

1.1. About SQLAlchemy sessions

SQLAlchemy session.close() closes remaining SQLAlchemy Transactions, which call ROLLBACK on their connections and return them to the pool.

From How to close a SQLAlchemy session:

A SQLAlchemy Session generally represents the scope of one or more transactions, upon a particular database connection.

1.2. About PostgreSQL sessions

From How do I find out the numeric ID of the current Postgres Transaction:

a session is "synonymous with a TCP connection".

1.3. About PostgreSQL SET

From https://www.postgresql.org/docs/9.3/sql-set.html:

If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET command disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another SET.

2. Why does the test in the question work?

In the test, no operation caused Zope to mark the session state as changed.

Zope transaction.commit() checks whether the session state is changed:

• If no, it calls SQLAlchemy session.close() and sets its reference to the SQLAlchemy Transaction to None.
  • As described in 1.1. About SQLAlchemy sessions, this calls ROLLBACK on the connection.
    • As described in 1.3. About PostgreSQL SET, the effects of the SET command disappear.
• Else, it commits the SQLAlchemy Transaction.
  • As described in 1.3. About PostgreSQL SET, the effects will persist until the end of the (PostgreSQL) session.

You can verify the Else case by explicitly marking the session as changed:

print(DBSESSION.execute(text("SELECT set_config('session.storage', 'remember-me-across-this-session', false)")).first())
print(DBSESSION.execute(text("SELECT current_setting('session.storage')")).first())
mark_changed(DBSESSION.registry())  # Add this
transaction.commit()

Note: Since transaction.commit() and transaction.abort() already implicitly call session.close(), the subsequent call to DBSESSION.close() practically does nothing.

3. How to reset session run-time parameters?

Add an event listener for sqlalchemy.events.PoolEvents.checkin and call RESET.

@event.listens_for(engine, 'checkin')
def receive_checkin(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute('RESET session.storage')
    # cursor.execute('RESET ALL')