[sqlalchemy] Re: SQLAlchemy connection errors
Ok, i found the issue! When I tried to use pg8000 to connect to postgres i got an error: cannot insert multiple commands into a prepared statement. Apparently multiple queries is fine with psycopg2, but not using pg8000. We prefix all queries because of a workaround. We handle multi-tenancy using the postgres search_path, but setting the search_path once on an new transaction event resulted in another issue; the search_path is sometimes lost during requests! I can't see why exactly, but it appears sometimes during a request another connection is used which doesn't fire a new transaction event.. To workaround this issue, we prefix every query fired with a set search_path to customer_schema; original query in an before_cursor_execute callback. So if i leave out this search_path prefix query, and monkey patch_all (when using gevent) everything runs without trouble. But this still leaves the strange issue where the search_path is sometimes suddenly lost during requests.. feeding strategy=threadlocal might seem relevant here (as it should bind 1 connection to each thread handling a request)? Furthermore i need this behaviour because in some requests i also rely on a for_update lock during the request.. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLAlchemy connection errors
Hi Jonathan, Thanks for your response, i removed the newresponse event like you mentioned and added a finished callback to the request (in a tween): request.add_finished_callback(cleanup_db_session) Unfortunatly the plot thickens! Since the last time i tested our project, the problem *doesn't* occur anymore using waitress, cogen or gunicorn (in sync mode), they all handle all requests i spam correctly... but it now consistently raise an (InterfaceError) cursor already closed exception on ~80% of the requests when using gunicorn (in gevent mode). It actually doesn't seem to make any difference wether i use db_session.remove() or not! Could gunicorn's gevent mode have any effect on the application layer? Any other ideas? Matthijs On Wednesday, 10 September 2014 18:18:42 UTC+2, Jonathan Vanasco wrote: 1. I had a concern a few months ago when doing an audit of code. I ended up not having an issue, but this link might be of help: https://groups.google.com/d/topic/sqlalchemy/Z7tMCB_IK1M/discussion 2. I use `add_finished_callback`, which runs unconditionally at the end of every request. request.add_finished_callback(cleanup_db_session) Actually, I think this may be caused or exacerbated by your use of `NewRespsone`. I think that event can be triggered multiple times during a single request, and if you generate multiple response objects, only the first one would have an active connection. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLAlchemy connection errors
1. I had a concern a few months ago when doing an audit of code. I ended up not having an issue, but this link might be of help: https://groups.google.com/d/topic/sqlalchemy/Z7tMCB_IK1M/discussion 2. I use `add_finished_callback`, which runs unconditionally at the end of every request. request.add_finished_callback(cleanup_db_session) Actually, I think this may be caused or exacerbated by your use of `NewRespsone`. I think that event can be triggered multiple times during a single request, and if you generate multiple response objects, only the first one would have an active connection. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
