How to share connection pool with multiprocessing Python

Question

I'm trying to share a psycopg2.pool.(Simple/Threaded)ConnectionPool among multiple python processes. What is the correct way to approach this?

I am using Python 2.7 and Postgres 9.

I would like to provide some context. I want to use a connection pool is because I am running an arbitrary, but large (>80) number of processes that initially use the database to query results, perform some actions, then update the database with the results of the actions.

So far, I've tried to use the multiprocessing.managers.BaseManager and pass it to child processes so that the connections that are being used/unused are synchronized across the processes.

from multiprocessing import Manager, Process
from multiprocessing.managers import BaseManager


PASSWORD = 'xxxx'


def f(connection, connection_pool):
    with connection.cursor() as curs:  # ** LINE REFERENCED BELOW
        curs.execute('SELECT * FROM database')
    connection_pool.putconn(connection) 


BaseManager.register('SimpleConnectionPool', SimpleConnectionPool)
manager = BaseManager()
manager.start()
conn_pool = manager.SimpleConnectionPool(5, 20, dbname='database', user='admin', host='xxxx', password=PASSWORD, port='8080')

with conn_pool.getconn() as conn:
    print conn  # prints '<connection object at 0x7f48243edb48; dsn:'<unintialized>', closed:0>
    proc = Process(target=f, args=(conn, conn_pool))
proc.start()

proc.join()

** raises an error, 'Operational Error: asynchronous connection attempt underway'

If anyone could recommend a method to share the connection pool with numerous processes it would be greatly appreciated.

Answer 1

You don't. You can't pass a socket to another process. The other process must open the connection itself.