On Tue, Jun 8, 2021, at 12:40 PM, 'Matt Zagrabelny' via sqlalchemy wrote:
>
>
> On Tue, Jun 8, 2021 at 10:28 AM Mike Bayer <mike...@zzzcomputing.com> wrote:
>> __
>>
>>>
>>>> however, I would advise using pool_pre_ping instead which is much easier
>>>> to use and has basically no downsides. this feature didn't exist when
>>>> the docs for "optimistic disconnect" were written.
>>>
>>> Sure. I was only looking at doing the optimistic disconnect because it
>>> seemed a little more resilient to failures (if a DB error happens mid
>>> transaction) and because I felt I could control the number of retries and
>>> put in an exponential backoff.
>>>
>>> Do you suggest I use the custom pessimistic ping code:
>>>
>>> https://docs.sqlalchemy.org/en/14/core/pooling.html#custom-legacy-pessimistic-ping
>>>
>>> to add in exponential backoff or add additional retries?
>>
>> what's the real-world use case where exponential backoff is useful?
>
> Unknown network failures, I suppose. I have an application that is throwing
> an exception right now due to:
>
> psycopg2.OperationalError: terminating connection due to administrator command
> SSL connection has been closed unexpectedly
right so if that happens on a connection that's been sitting in the pool when
you first go to use it, pre_ping will solve that. OTOH if this is happening
in the middle of a transaction you're running, you would have to implement your
own "retry" system at the level of an entire transaction. the "invalidate"
part of the equation should occur automatically. so with the ORM, it looks
like this:
Session = sessionmaker(some_engine)
num_retries = 3
retry = 0
while True:
try:
session = Session()
with session.begin():
do_my_operation(session)
except DBAPIError as e:
if e.connection_invalidated and retry < num_retries:
retry += 1
continue
else:
raise
else:
break
the above block is usually built into a decorator that would decorate
"do_my_operation()" , so that you can run the whole operation again.
>
> I don't know exactly what is causing the failure. Our VM guy seems to think
> it might be a network issue. Either way, I feel the code should retry a few
> times just to see if the cause of the error goes away.
we've seen this SSL error before but we've never been provided steps to
reproduce it.
>
>
>> do you expect databases to be shut down for a number of minutes without
>> disabling the application ?
>
> No. I don't know what the timing parameters are of my particular failure. Pre
> ping doesn't seem like it allows for any sort of length of time before
> failing.
well it doesn't have to, it's used to check for a stale connection in the pool.
pull connecvtion from pool , check if stale, then if so just replace it with a
new one. if it still can't connect, then the database is just down, that's an
abort.
>
> I don't know if this offers any more clarity to what I'm experiencing and
> what I'm trying to code around.
>
> Thanks again for the help and dialogue!
>
> -m
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3XEJWahpk9WRCNG%2BHkGGeiM7wR5zgmNeJHG2ECYkk%3Dc0A%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3XEJWahpk9WRCNG%2BHkGGeiM7wR5zgmNeJHG2ECYkk%3Dc0A%40mail.gmail.com?utm_medium=email&utm_source=footer>.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/cbdaec17-1da2-4051-ab16-fc40b52809bb%40www.fastmail.com.
