On Sun, Jun 3, 2018 at 10:17 PM, rvd <rdh...@college.harvard.edu> wrote: > Hi all, > > I am writing a Flask API that needs to communicate with many datastores, > Postgres on AWS RDS being one of them. I want to avoid Flask-SQLAlchemy > (trying to reduce package dependence); I think the standard SQLAlchemy > library should suffice. I have described the issue thoroughly here (code is > here as well). But here is the synopsis. When I use a Session object in my > app, for the first few requests, it works great. Then, if I wait for a while > and fire off another request, I get a Psycopg2 error about the server > closing off the connection unexpectedly. This is rather crippling.
it seems like your source of connectivity has an idle connection timeout going on. What version of Postgresql is this, or is this redshift? are you using a proxy like pgbouncer or haproxy? mitigation here would be to either increase the timeout occurring within the server middleware (I don't think PG itself has an idle connection timeout feature), or turn the value of pool_recycle (http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=pool%20max_overflow#sqlalchemy.pool.Pool.params.recycle) way down. Additionally, you can use pool_pre_ping so that the connection is fresh upon checkout: http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=pre_ping#disconnect-handling-pessimistic I agree there's nothing at all in flask-sqlalchemy that has much to do with this. I have > spent a lot of time going through SO, asked in the SQLAlchemy IRC channel, > and tried many tutorials, and this seems to be a standard valid pattern, so > I am not sure what is going wrong. Should I not be removing the session? > Should I be using the @app.teardown_appcontext decorator (it seems that if I > just remove the sessions myself as I am doing now, I shouldn't have to)? > Should I be using a connection pool instead (it seems that QueuePool is > enabled under the hood by default anyway)? If it helps, I am using the > latest version of all packages, Python 3.6.5, and Postman to send the > requests to the server, which is running at localhost:5000. Any advice would > be much appreciated - thanks in advance! > > Best, > rvd > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
