python - Flask-SQLAlchemy 摆脱 `idle in transaction`

Question

我的 Flask 应用程序中的 idle in transaction 连接有问题。
一段时间后，postgres 自动关闭所有此类事务，并且应用程序无法建立新连接，这会导致 ISE:

[Tue Aug 27 11:24:43.354723 2019] [wsgi:error] [pid 25:tid 140264740386560] [remote 172.17.0.1:60687] sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'SELECT metadata.contracts.name AS metadata_contracts_name, metadata.contracts.table_path AS metadata_contracts_table_path, metadata.contracts.abort_on_error AS metadata_contracts_abort_on_error, metadata.contracts.active AS metadata_contracts_active, metadata.contracts.timestamp AS metadata_contracts_timestamp, metadata.contracts.modified_by AS metadata_contracts_modified_by, metadata.contracts.deleted AS metadata_contracts_deleted, metadata.contracts.subscribers AS metadata_contracts_subscribers, metadata.contracts.version AS metadata_contracts_version, metadata.contracts.id AS metadata_contracts_id, metadata.contracts.group_access AS metadata_contracts_group_access, metadata.contracts.restricted_access AS metadata_contracts_restricted_access, metadata.contracts.owner AS metadata_contracts_owner, metadata.contracts.user_access AS metadata_contracts_user_access \\nFROM metadata.contracts \\nWHERE metadata.contracts.id = %(id_1)s ORDER BY metadata.contracts.version DESC \\n LIMIT %(param_1)s'] [parameters: [{}]]

这是我的应用程序的简化 View :

from dagama import db
from dagama.contracts import bp
from dagama.contracts.models import Contract


@bp.route('/')
def contracts():
    contracts_qs = db.session.query(Contract)
    n = contracts_qs.count()
    return Response()

@bp.route('/<int:id>/')
def contract_details(id):
    contract = Contract.query.filter(Contract.id == id).order_by(Contract.version.desc()).first_or_404()
    return Response()

这里是来自生产数据库的统计数据:

datalake=# SELECT pid, application_name, client_addr, client_port, backend_start, xact_start, query_start,  wait_event, state, left(query, 64) FROM pg_stat_activity WHERE APPLICATION_NAME = 'dagama' OR client_addr = '172.17.0.2' ORDER BY query_start DESC;
  pid  | application_name | client_addr | client_port |         backend_start         |          xact_start           |          query_start          | wait_event |        state        |                               left
-------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------+---------------------+------------------------------------------------------------------
 41599 | dagama           | 172.17.0.2  |       60396 | 2019-08-27 14:34:43.75638+02  |                               | 2019-08-27 14:34:43.793666+02 | ClientRead | idle                | ROLLBACK
 18338 |                  | 172.17.0.2  |       58062 | 2019-08-27 14:09:13.919567+02 |                               | 2019-08-27 14:10:55.758383+02 | ClientRead | idle                | ROLLBACK
 30387 | dagama           | 172.17.0.2  |       43526 | 2019-08-27 14:10:55.324834+02 | 2019-08-27 14:10:55.329896+02 | 2019-08-27 14:10:55.371686+02 | ClientRead | idle in transaction | SELECT metadata.rules.id AS metadata_rules_id, metadata.rules.co
 18154 | dagama           | 172.17.0.2  |       57712 | 2019-08-27 14:09:11.633017+02 | 2019-08-27 14:09:13.906523+02 | 2019-08-27 14:09:13.982879+02 | ClientRead | idle in transaction | SELECT metadata.rules.id AS metadata_rules_id, metadata.rules.co
(4 rows)

调用 contract_details() 结果与 state = idle in transaction 连接，但是使用 db.session 就像在 contracts( ) 直接很好地回滚并关闭事务(xact_start 为空)。
Flask-SQLAlchemy 应该手动关闭线程本地 session here ，但它仅适用于 contracts() 中的用法。

我发现有类似问题的 SA 帖子:Flask and SQLAlchemy causes a lot of IDLE in transaction connections in PostgreSQL ，但没有任何自动关闭 session 的干净解决方案。

编辑
解决方案之一可能是手动回滚附加了所请求对象的 session :

@bp.route('/<int:id>/')
def contract_details(id):
    contract = Contract.query.filter(Contract.id == id).order_by(Contract.version.desc()).first_or_404()
    db.session.object_session(contract).rollback()
    return Response()

Answer 1

好的，问题的根源已找到并与我的模型定义相关联。

我创建了新的 SQLAlchemy() 对象，而不是使用应用程序初始化的对象。

所以代替

db = SQLAlchemy()


class Contract(db.Model):
...

我应该:

from dagama import db


class Contract(db.Model):
...

这就是调用 Contract.query... 生成的连接在应用程序上下文被销毁后未正确处理的原因。