Maybe this is a psycopg question and if so please say so. I have a multi-threaded server which maintains a thread-pool (and a corresponding connection pool) for servicing requests. In order to mitigate python's high-water-mark memory usage behavior for large queries, I'm attempting to handle queries in particular using a forked subprocess from the request thread.
I'm using the connection invalidation recipe described here (the second one that adds listeners to the Pool): http://docs.sqlalchemy.org/en/latest/core/pooling.html#using-connection-pools-with-multiprocessing It seems to be working correctly -- that is, I can see that the child process is indeed creating a new connection. However, I'm still experiencing intermittent hangs in the child process during connection creation. I've gotten a stack trace using gdb, and I think I understand what is going on but I'm not sure how to protect the critical section. It looks like threads creating connections in the parent process acquire some threading synchronization primitive inside psycopg's _connect function (that's in c so I didn't see the actual source). This apparently occurs occasionally at the same time as the fork, so that the child process never sees the primitive release in the parent process and hangs forever. Interestingly, hangs stop after the server has been running for a while, presumably because the parent process is warmed up and has a full connection pool, and is no longer creating connections. Here is my stack on a hung process: #17 <built-in function _connect> #19 file '/usr/lib64/python2.6/site-packages/psycopg2/__init__.py', in 'connect' #24 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/default.py', in 'connect' #29 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/strategies.py', in 'connect' #33 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in '__connect' #36 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 'get_connection' #39 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 'checkout' #43 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in '_checkout' #47 file '/usr/lib64/python2.6/site-packages/sqlalchemy/pool.py', in 'connect' #50 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py', in '_wrap_pool_connect' #54 file '/usr/lib64/python2.6/site-packages/sqlalchemy/engine/base.py', in 'contextual_connect' #58 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in '_connection_for_bind' #61 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in '_connection_for_bind' #65 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/session.py', in 'connection' #70 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '_connection_from_session' #74 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '_execute_and_instances' #77 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '__iter__' #91 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in '__getitem__' #99 file '/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py', in 'first' I'm using sqlalchemy 1.0.12 and psycopg 2.5.3 My quick and dirty fix would be to fill the connection pool in the parent process by force before servicing requests, but that is a hack, and in case of an invalidated connection the server would be susceptible to the issue again while recreating the invalid connection in the parent process. I apparently need to synchronize my fork in one thread with connections being created in others, but I'm not sure how to do that. Any pointers would be great. TIA, Uri -- 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.
