multiprocessing module and distinct psycopg2 connections
<p>I am very puzzled as to the behavior of some multiprocessing code that is using <em>psycopg2</em> to make queries in parallel to a postgres db.</p>

<p>Essentially, I am making the same query (with different params) to various partitions of a larger table. I am using multiprocessing.Pool to fork off a separate query.</p>

<p>My multiprocessing call looks like this:</p>

<pre><code>pool = Pool(processes=num_procs)
results=pool.map(run_sql, params_list)
</code></pre>

<p>My run_sql code looks like this:</p>

<pre><code>def run_sql(zip2):
    conn = get_connection()
    curs = conn.cursor()
    print "conn: %s curs:%s pid=%s" % (id(conn), id(curs), os.getpid())
    ...
    curs.execute(qry)
    records = curs.fetchall()

def get_connection()
    ...
    conn = psycopg2.connect(user=db_user, host=db_host, 
                         dbname=db_name, password=db_pwd)

    return conn
</code></pre>

<p>So my expectation is that each process would get a separate db connection via the call to <code>get_connection()</code> and that <code>print id(conn)</code> would display a distinct value.  However, that doesn't seem to be the case and I am at a loss to explain it. Even <code>print id(curs)</code> is the same. Only <code>print os.getpid()</code> shows a difference. Does it somehow use the same connection for each forked process ?</p>

<pre><code>conn: 4614554592 curs:4605160432 pid=46802
conn: 4614554592 curs:4605160432 pid=46808
conn: 4614554592 curs:4605160432 pid=46810
conn: 4614554592 curs:4605160432 pid=46784
conn: 4614554592 curs:4605160432 pid=46811
</code></pre>