如何在 Postgres 中实现并行性

Question

我正在开发一种基于租户密钥和其他查询条件更新批处理日志的功能。

在我的功能中，我使用联接并逐个循环访问记录，按顺序更新批处理日志。

我的代码如下

CREATE TABLE linen_tenant_header
(
tenant_key character varying(200) NOT NULL,
tenant_name character varying(50) NOT NULL,
email character varying(50) NOT NULL,
created_user character varying(200) NOT NULL,
updated_user character varying(200),
created_time timestamp without time zone DEFAULT now(),
updated_time timestamp without time zone DEFAULT now()
);
CREATE TABLE linen_sc_batch_log
(
batch_id serial NOT NULL,
profile_header_id integer NOT NULL,
batch_status character varying(200),
batch_type character varying(200) NOT NULL,
batch_mode character varying(200) NOT NULL,
tenant_key character varying(200) NOT NULL,
created_user character varying(200),
updated_user character varying(200),
created_time timestamp without time zone DEFAULT now(),
updated_time timestamp without time zone DEFAULT now(),
);
CREATE TABLE linen_sc_expressions
(
expression_id integer NOT NULL,
reference_id integer NOT NULL,
tenant_key character varying(200) NOT NULL,
expression_text character varying(10000) NOT NULL,
start_date date NOT NULL,
end_date date,
created_user character varying(200) NOT NULL,
updated_user character varying(200),
created_time timestamp without time zone DEFAULT now(),
updated_time timestamp without time zone DEFAULT now()
);
CREATE OR REPLACE FUNCTION sample_function(
tenant_key TEXT
) RETURNS VOID AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM linen_sc_batch_log bl, linen_sc_expressions sce  where bl.profile_header_id = sce.reference_id and bl.tenant_key = $1 and sce.tenant_key = $1
LOOP
EXECUTE FORMAT('UPDATE linen_sc_batch_log set batch_status = %L where tenant_key = %L and batch_id = %L::INTEGER', 'EXTRACTED', tenant_key, rec.batch_id);
END LOOP;
END;
$$ LANGUAGE plpgsql;

我的功能工作正常，但我需要通过添加并行性来增强我的功能。

我想同时并行更新所有记录，而不是遍历所有记录。

我研究了谷歌和PostgreSQL的文章，但没有找到相关的概念。

Answer 1

我认为这可以在单个SQL语句中完成：

UPDATE linen_sc_batch_log AS bl
SET batch_status = 'EXTRACTED'
FROM linen_sc_expressions AS sce
WHERE bl.profile_header_id = sce.reference_id
AND bl.tenant_key = $1 AND sce.tenant_key = $1;

如果要并行化，可以在并发数据库连接上运行多个此类更新，并在每个更新中添加类似AND id % 10 =n的子句。