Session and optimistic disconnect handling

[Matt Zagrabelny]

Greetings SQLAlchemy folks,

I am following the guide at [0] for recovering from a database error in my SQLAlchemy code.

I normally use sessions for my SA work and am wondering if sessions will work with the aforementioned SA example. My initial attempt to combine the example at [0] with sessions did not seem to work as expected. What do folks think? Should it work?

Here is a code snippet of how I am creating sessions:

        connection_string = self.get_connection_string()
        engine            = create_engine(connection_string)
        Session           = sessionmaker(bind = engine)

If folks believe it should work, then I'll formulate a minimal working example and post my error.

Thank you!

-m

[0] https://docs.sqlalchemy.org/en/14/core/pooling.html#disconnect-handling-optimistic

[Mike Bayer]

ORM Sessions ride on top of connections, but since the Session under it's default pattern of being bound to an Engine does the "connect" internally, it's probably inconvenient to adapt the optimistic disconnect approach to it.     You would probably want to bind the Session to the Connection explicitly.  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.

--

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/CAOLfK3Ud5spwMWPQTJws4zJixu7FuS5bLTu3OCmpxFLUpYn62A%40mail.gmail.com.

[Matt Zagrabelny]

Hi Mike,

Thanks for the reply!

On Mon, Jun 7, 2021 at 6:08 PM Mike Bayer <mik...@zzzcomputing.com> wrote:

ORM Sessions ride on top of connections, but since the Session under it's default pattern of being bound to an Engine does the "connect" internally, it's probably inconvenient to adapt the optimistic disconnect approach to it.     You would probably want to bind the Session to the Connection explicitly.

I searched the SA docs, but could not find how to bind the Session to the Connection.

 

  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?

 

Thank you again for the help!

-m

[Mike Bayer]

On Tue, Jun 8, 2021, at 11:18 AM, 'Matt Zagrabelny' via sqlalchemy wrote:

Hi Mike,

Thanks for the reply!

On Mon, Jun 7, 2021 at 6:08 PM Mike Bayer <mik...@zzzcomputing.com> wrote:

ORM Sessions ride on top of connections, but since the Session under it's default pattern of being bound to an Engine does the "connect" internally, it's probably inconvenient to adapt the optimistic disconnect approach to it.     You would probably want to bind the Session to the Connection explicitly.

I searched the SA docs, but could not find how to bind the Session to the Connection.

you just pass the Connection as the "bind" argument to Session, there's an example of that here:

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites

 

  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?  do you expect databases to be shut down for a number of minutes without disabling the application ?        The "optimistic" approach inherently involves having a request fail with an exception in any case, which suggests you aren't going for a "shut down all the databases for minutes, nothing fails" kind of use case.     having a full retry-nothing-fails approach typically involves having "retry" at the level of your entire operation, not at the point at which it first connects.

that said, sure you can build "exponential backoff" into the pessimistic ping recipe, it just seems like a lot of effort for a solution that's incomplete in any case.

 

Thank you again for the help!

-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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3WYSinbhQLbMaBTf7sRRDZ8PtVcQ6u2-gV4ftjS36ZstA%40mail.gmail.com.

[Matt Zagrabelny]

On Tue, Jun 8, 2021 at 10:28 AM Mike Bayer <mik...@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

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.

 

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.

 

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

[Mike Bayer]

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 <mik...@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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOLfK3XEJWahpk9WRCNG%2BHkGGeiM7wR5zgmNeJHG2ECYkk%3Dc0A%40mail.gmail.com.

[Matt Zagrabelny]

On Tue, Jun 8, 2021 at 11:58 AM Mike Bayer <mik...@zzzcomputing.com> wrote:

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. 

Okay. That sounds pretty good.

I've turned on logging (at the DEBUG level) per...

https://docs.sqlalchemy.org/en/14/core/engines.html#dbengine-logging

and I am trying to force an invalid connection by:

while True:

    # read stdin

    o = SQLObject(something_from_stdin)

    session.add(o)

    session.commit()

    # Go to DB server and restart postgresql

However I don't see any indication that the pre_ping invalidated a connection...

# Here is the first commit from SA...

INFO:sqlalchemy.engine.base.Engine:select version()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('version',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('PostgreSQL 12.4 (Debian 12.4-3) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.0-13) 10.2.0, 64-bit',)
INFO:sqlalchemy.engine.base.Engine:select current_schema()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('current_schema',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('public',)
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('standard_conforming_strings',)
DEBUG:sqlalchemy.engine.base.Engine:Row ('on',)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO call_records (inserted_at, acct_code, vdn) VALUES (%(inserted_at)s, %(acct_code)s, %(vdn)s) RETURNING call_records.id
INFO:sqlalchemy.engine.base.Engine:{'inserted_at': 'now', 'acct_code': 'yuy', 'vdn': 'tyt'}
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (18,)
INFO:sqlalchemy.engine.base.Engine:COMMIT

# DB restarted and the second pass through the while loop...

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO call_records (inserted_at, acct_code, vdn) VALUES (%(inserted_at)s, %(acct_code)s, %(vdn)s) RETURNING call_records.id
INFO:sqlalchemy.engine.base.Engine:{'inserted_at': 'now', 'acct_code': '909', 'vdn': '909'}
DEBUG:sqlalchemy.engine.base.Engine:Col ('id',)
DEBUG:sqlalchemy.engine.base.Engine:Row (19,)
INFO:sqlalchemy.engine.base.Engine:COMMIT

Should I be seeing something in the logs about an invalidated connection?

Or am I not forcing an invalid connection correctly?

Thanks for the feedback!

-m

[Mike Bayer]

i think you need to turn on pool logging, echo_pool='debug'.  Also note the "pre-ping" feature in some cases uses a "ping" feature provided by the DBAPI which can transparently reconnect (such as mysqlclient) so nothing would be seen there.

--

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/CAOLfK3W539EFhaDYAu3A7Gxr3WrwbdCGX12V_Y19gXZz3O74sA%40mail.gmail.com.