Turn off connections invalidation functionality

[Anupama Goparaju]

Hi,

Is there a way to safely turn off connection invalidation functionality (based on invalidation time set, all the connections created prior to the timestamp are invalidated) in sqlalchemy?

https://github.com/Noethys/Connecthys/blob/master/connecthys/lib/sqlalchemy/pool.py#L574

Thanks,

Anupama

[Mike Bayer]

Im not sure if I understand the question?   if you don't call .invalidate(), then the connection is not invalidated.

what does "turn off" mean ?

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/07c76e07-17d4-4cea-9e42-1965ad80a06fn%40googlegroups.com.

[Mike Bayer]

I've spent some time thinking about what might be being asked here.    the only thing I can think of is that when a particular database connection is found to be in what we call a "disconnect" state, the connection is invalidated, so that the connection will reconnect and make a new connection.   But also, this operation will typically assume the "disconnect" condition is that the database was restarted, or some other network condition has probably made all the connections that are pooled also invalid.   So the entire pool will be invalidated in this case as well.

Why someone might want to turn that off is if they are getting lots of invalidated connections for some other reason and they are not able to solve that problem, so they'd like the pool to not be invalidated totally.  This means that if the database is restarted, and for example you have 20 pooled connections, you will in a high-request environment get up to 20 server errors unless pool_pre_ping is turned on so that the connections are refreshed one at at time.

To disable the pool invalidation upon receipt of a single connection shown to be in a disconnect, implement the handle_error event: https://docs.sqlalchemy.org/en/14/core/events.html#sqlalchemy.events.ConnectionEvents.handle_error and then set invalidate_pool_on_disconnect to False: https://docs.sqlalchemy.org/en/14/core/connections.html?highlight=invalidate_pool_on_disconnect#sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect .

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a1807f9d-e0c4-4ee6-9c03-ff2a56afeded%40www.fastmail.com.

[Anupama Goparaju]

Great, thanks for the info.

[Anupama Goparaju]

I haven't read the response before and tried to skip the lib logic by overriding the function below in my child call extending the NullPool to do nothing. Is this safe to do?

def _invalidate(self, connection, exception=None, _checkin=True):

      pass

[Mike Bayer]

I can't guarantee that overriding private methods is safe, no.

there's a public API to disable errors resulting in invalidation, I suggest you use that.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9a2d0da5-69a9-488a-a522-98d9cecbe4b7n%40googlegroups.com.

[Anupama Goparaju]

Thanks, i will give it a try.

[Anupama Goparaju]

Thanks. Looks like this works but we need to set it for every exception scenario. Just confirming if the exception_context is thread safe to set the attribute.

Also, does this setting prevent pool invalidation on all sort of exceptions related to connections?

@event.listens_for(Engine, 'handle_error')
def receive_handle_error(exception_context):
     if exception_context.invalidate_pool_on_disconnect:
         exception_context.invalidate_pool_on_disconnect = False

[Mike Bayer]

On Mon, Jan 10, 2022, at 1:30 PM, Anupama Goparaju wrote:

Thanks. Looks like this works but we need to set it for every exception scenario. Just confirming if the exception_context is thread safe to set the attribute.

yes this all happens local to the execute() function call, is not exposed to threads

Also, does this setting prevent pool invalidation on all sort of exceptions related to connections?

yes, the pathway by which getting an exception would lead to invalidating the connection is blocked by this event handler.   I'm not sure why you'd want to set that in all cases as there are legitimate "disconnect" error scenarios (such as database was stopped and restarted), unless you are doing something with the DBAPI connection that allows it to recover by itself, I guess.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4f695913-b7fc-479b-9186-dae67110ccc8n%40googlegroups.com.

[Anupama Goparaju]

cx-oracle SessionPool is used underlying that will probe connections on checkout and recover. Hence, trying to disable SQLAlchemy invalidate pool.

Thanks!