4

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.

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

1 Comment

Could you please suggest a standard way to deal with this issue? is it advisable to create a new connection for each process and close it upon exit(given that this will add some overhead)?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.