连接到PostgreSQL时出错无法腌制psycopg2.extensions.connection对象 [英] Error Connecting To PostgreSQL can't pickle psycopg2.extensions.connection objects

问题描述

我正在尝试创建一个具有主要父流程和&的架构.它可以创建新的子进程.主父进程将始终处于循环状态，以检查是否有任何子进程可用.

I am trying to create an architecture that will have a main parent process & it can create new child processes. The main parent process will always be on loop to check if there is any child process available.

我使用了psycopg2.pool模块的ThreadedConnectionPool来为创建的所有子进程建立公共数据库连接.这意味着程序将一次连接到数据库，并为每个子进程执行所有SQL查询.因此，无需每次都连接数据库以执行SQL查询.

I have used ThreadedConnectionPool of psycopg2.pool module in order to have a common database connection for all child processes created. That means the program will be connecting once to the database and execute all the SQL queries for each of the child processes. So there is no need to connect to Database every time for execution of SQL queries.

代码如下:

from multiprocessing import Process, Lock
import time, os, psycopg2
from psycopg2 import pool

def child(dbConnection, lock, num, pid, sleepTime, query):
    lock.acquire()

    start = time.time()

    print("Child Process {} - Process ID: {}".format(num + 1, str(os.getpid())))

    db_cursor = dbConnection.cursor()
    db_cursor.execute(query)
    records = db_cursor.fetchmany(2)

    print("Displaying rows from User Master Table")

    for row in records:
        print(row)

    print("Executed Query:", query)
    print("Child Process {} - Process ID {} Completed.".format(num + 1, str(os.getpid())))

    end = time.time()
    print("Time taken:", str(end - start), "seconds")

    lock.release()
    time.sleep(sleepTime)

if __name__ == "__main__":
    try:
        connectionPool = psycopg2.pool.ThreadedConnectionPool(5, 21, user = "dwhpkg", password = "dwhpkg", host = "127.0.0.1", port = "5432", database = "dwhdb")

        while True:

            processes = []

            print("Main Process ID: {}".format(str(os.getpid())))
            lock = Lock()


            # 21 Times Process Execution
            for count in range(21):
                if connectionPool :
                    print("Connection Pool Successfully Created")

                # Getting DB Connection From Connection Pool
                dbConnection = connectionPool.getconn()

                if dbConnection:
                    sql_execute_process = Process(target = child, args = (dbConnection, lock, count, os.getpid(), 4, 'SELECT * FROM public."USER_MASTER"',))

                    sql_execute_process.start()

                    processes.append(sql_execute_process)
                    print("Parent Process:", os.getpid())

                    print(processes)

                    time.sleep(5)

            for process in processes:
                process.join()

    except (Exception, psycopg2.DatabaseError) as error:
        print("Error Connecting To PostgreSQL", error)

    finally:
        # Closing DB Connection
        if connectionPool:
            connectionPool.closeall
        print("Connection Pool is closed")

当我尝试运行上面的代码时，它出现以下错误:

When I try to run the above code, it gives the following error:

Main Process ID: 46700
Connection Pool Successfully Created
Error Connecting To PostgreSQL can't pickle psycopg2.extensions.connection objects
Connection Pool is closed

(task_env) C:\Users\sicuser\Desktop\ジート\03_作業案件\タスク機能プロトタイプ作成\開発>Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Users\sicuser\AppData\Local\Programs\Python\Python37\lib\multiprocessing\spawn.py", line 99, in spawn_main
    new_handle = reduction.steal_handle(parent_pid, pipe_handle)
  File "C:\Users\sicuser\AppData\Local\Programs\Python\Python37\lib\multiprocessing\reduction.py", line 82, in steal_handle
    _winapi.PROCESS_DUP_HANDLE, False, source_pid)
OSError: [WinError 87] The parameters are incorrect.

对于故障排除，我还使用了调试"模式，并试图找出错误的位置.使用调试，我发现由于以下行而发生了错误:

For troubleshooting, I have also used the Debugging mode and tried to find out the error location. Using debugging, I have found the error is occurring due to the line below:

sql_execute_process.start()

【错误消息】

Main Process ID: 47708
Connection Pool Successfully Created
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Users\sicuser\AppData\Local\Programs\Python\Python37\lib\multiprocessing\spawn.py", line 105, in spawn_main
    exitcode = _main(fd)
  File "C:\Users\sicuser\AppData\Local\Programs\Python\Python37\lib\multiprocessing\spawn.py", line 115, in _main
    self = reduction.pickle.load(from_parent)
EOFError: Ran out of input
Error Connecting To PostgreSQL can't pickle psycopg2.extensions.connection objects

操作系统环境为Windows和Python版本:Python 3.7.4

The OS Environment is Windows and Python version: Python 3.7.4

期待专家的支持.

推荐答案

在上述解决方案中，您将ThreadedConnectionPool与multiprocessing.Process实例( thread！= process )一起使用. > 多个进程不能安全地共享同一连接；请查看psycopg关于线程和过程安全性.

In your solution above you're using a ThreadedConnectionPool with multiprocessing.Process instances (thread != process).
Multiple processes cannot safely share the same connection; check the details on psycopg's section about thread and process safety.

您还在子级中的关键代码上使用了Lock，这基本上阻止了您并行执行任务；即使可以工作，性能也与单个流程解决方案基本相同.

You're also using a Lock for the critical code in the child which basically prevents you from executing tasks in parallel; even if it worked the performance would largely be the same to a single process solution.

解决方案取决于子进程的CPU密集程度和持久性:

The solution depends on how CPU intensive and long-lived will the child processes be:

• 如果孩子们会很轻松/短暂，只需使用一个(主)线程即可完成所有工作
• 对于繁重/长期存在的子进程，请从子进程内部连接到数据库(不要与主进程共享连接)

这篇关于连接到PostgreSQL时出错无法腌制psycopg2.extensions.connection对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！