We use this backed onto a Multi-AZ PostgreSQL on RDS. When the DB fails over during updates/etc, errbot dies.
(psycopg2.OperationalError) SSL connection has been closed unexpectedly
[SQL: 'SELECT core.key AS core_key, core.value AS core_value \nFROM core \nWHERE core.key = %(key_1)s'] [parameters: {'key_1': 'bl_plugins'}] (Background on this error at: http://sqlalche.me/e/e3q8)
Which is ok, there's one error when the PG instance is switched. But it never reconnects, and every command produces output like:
(sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'SELECT x.key AS x_key, x.value AS x_value \nFROM x \nWHERE x.key = %(key_1)s'] [parameters: [{}]]
As far as I can tell, every method in SQLStorage that access the DB should be wrapped in a session transaction (not just the methods that write to the DB), as discussed in the SQLAlchemy docs. No idea why @session_scope isn't part of SQLAlchemy, but implementing that would work I think?
Might also be worth adding pool_size=1, pool_recycle=300 or something to the default engine too as a bit of added resiliency.
We use this backed onto a Multi-AZ PostgreSQL on RDS. When the DB fails over during updates/etc, errbot dies.
Which is ok, there's one error when the PG instance is switched. But it never reconnects, and every command produces output like:
As far as I can tell, every method in SQLStorage that access the DB should be wrapped in a session transaction (not just the methods that write to the DB), as discussed in the SQLAlchemy docs. No idea why
@session_scopeisn't part of SQLAlchemy, but implementing that would work I think?Might also be worth adding
pool_size=1, pool_recycle=300or something to the default engine too as a bit of added resiliency.