Postgres：如何在同一时间内执行多个查询？

Question

我有一个过程用于更新记录值，想要将其应用于表中的所有记录（超过30k条），该过程的执行时间为2至10秒，因为它取决于网络负载。

现在我正在使用"UPDATE table SET field = procedure_name(paramns);"来执行此操作，但是针对这么多记录的处理需要长达40分钟。

现在，我使用4个不同的连接分叉到后台，并设置WHERE子句以迭代行ID的模数来加快查询速度，（ WHERE id_field % 4 = ），这样做效果很好，可以将表格填充时间缩短到约10分钟。

但是，我想避免使用cron、shell作业和多个连接进行此操作，我知道可以使用libpq来实现，但是是否有一种方法可以启动查询（四个不同的非阻塞查询）并且不等待其执行完成，而是在单个连接中？

或者，如果有人能够给我指出如何使用PostgreSQL内部或简单地以C语言编写并绑定为存储过程的方法，那将更好。谢谢Darius。

- canni

Answer 1

可以一次性更新多行数据。以下是在Postgres中的示例：

UPDATE
    table_name
SET
    column_name = temp.column_name 
FROM
    (VALUES
        (<id1>, <value1>),
        (<id2>, <value2>),
        (<id3>, <value3>)
    ) AS temp("id", "column_name")
WHERE
    table_name.id = temp.id

- Pieter

Answer 2

我对这个问题有一个确定的答案 - 如果你能告诉我们你的腹肌锻炼是什么！我正在一分一秒地变胖，我自己也需要答案...

好吧，我还是回答了。

如果你在一个数据库服务器上更新一个表，在40分钟内“单线程”完成，在4个线程中只需10分钟，那么瓶颈不是数据库服务器；否则，它会陷入I/O困境。如果你执行一堆UPDATE，每个记录一个调用，网络往返时间会让你崩溃。

我非常确定这就是情况，而不是DB的I/O瓶颈或者procedure_name(paramns);可能需要很长时间。(如果那是需要2-10秒的过程，那么做30K条记录需要2500分钟)。我确定的原因是启动4个并发进程可以将时间缩短1/4。所以特别是DB服务器上没有I/O问题。

这可能是将业务逻辑放在服务器上的SP的借口。优化不幸意味着打破规则。结果是难以维护。但是，呃！！

然而，最佳解决方案是设置使用“批量更新”查询。这可能意味着您需要采取几个奇怪和不直观的步骤，例如：

• 如果多个用户可以同时运行，这将需要很多修改。
• 重构系统，使得procedure_name(paramns)可以通过select语句获取处理所有记录所需的所有数据。可能需要使用创造性的连接操作。如果它是一个存储过程，那么现在你正在将逻辑转移到客户端。
• 让程序创建一个XML或其他可导入的平面文件格式，其中包含要更新的记录的主键和新的字段值或值。将所有更新写入此文件，而不是在数据库上执行它们。
• 在数据库上有一个与该平面文件布局相匹配的临时表。
• 在数据库上运行导入操作-清除临时表并导入文件。
• 对临时表和待更新的表进行联接更新，例如，UPDATE mytbl, mytemp WHERE myPK=mytempPK SET myval=mytempnewval（当然要使用正确的联接语法）。
• 在编码之前，您可以先手动尝试其中一些方法，看看是否值得提高速度。
• 如果可能，仍然可以将所有这些放在一个存储过程中！

我不能做出任何保证，特别是当我看着我的越来越胖的肚子时，但这有潜力将您的更新工作缩短到不到一分钟。

- FastAl

---

长时间执行时间的原因是该程序使用 plperl 函数进行通信，通过 SNMP 或原始套接字返回结果和表格给最终用户。只有内部数据库存储过程可以访问更新数据，因此可以预测何时以及如何更新数据。最终用户只能读取表格，无法进行修改。 - canni

Answer 3

我认为你不能这样做。单个连接一次只能处理一个查询。在libpq文档的“异步命令处理”章节中有描述：

“成功调用PQsendQuery后，调用PQgetResult一次或多次以获取结果。在PQgetResult返回空指针（表示命令已完成）之前，不能再次调用PQsendQuery（在同一连接上）。"

- Tomasz Myrta

Answer 4

PHP有一些用于异步查询的函数：

• pg_send_execute()
• pg_send_prepare()
• pg_send_query()
• pg_send_query_params()

对于其他编程语言，我不清楚，你需要查阅相关手册。

- Frank Heikens

---

问题在于我在这个项目中没有使用PHP，数据库本身是一个应用程序，所有核心和业务逻辑都是基于plpgsql和plperl构建的。 - canni