Postgres 函数/存储过程中的条件锁定

Question

我正在为网络爬虫构建一个事件溯源服务，其中有几个爬网程序工作线程抓取多个网站并试图为爬网资源保留增量。我选择了PostgreSQL作为底层数据存储。我需要让生产者能够使用名为"expectSeq"的标志进行乐观锁定，以控制是否应该为特定流编写事件。最初，我使用一个表，利用事务的自动增量为每个"流"构建乐观锁定功能，但我很快发现服务器可以处理多少个表有一个文件系统上限。

由于我不能再使用自动增量，因此我尝试使用两个表构建此功能，一个用于控制流的顺序，另一个用于存储事件本身。

我的第一个问题是，我应该使用存储过程还是函数。第二种是可以在存储过程或 Postgres 函数中包含条件事务。

我需要实现的逻辑是某种东西

storeEvent(stream, expectedSeq = null)
lock row for `streams`.stream
if expectedSeq = null
update stream row with seq + 1
release lock
write event to event table
else
if expectedSeq != seq + 1
release lock
abort
else
update seq + 1
release lock
write event to event table

Answer 1

感谢伊恩·哈里斯

CREATE OR REPLACE PROCEDURE store_event (v_topic varchar(40), v_expected_next_seq integer, v_data text)
LANGUAGE plpgsql
AS $$
DECLARE
next_seq integer;
BEGIN
-- FOR UPDATE clause places row level lock on table
next_seq := (
SELECT
seq
FROM
topics
WHERE
topic = v_topic
FOR UPDATE) + 1;
IF v_expected_next_seq IS NOT NULL AND next_seq != v_expected_next_seq THEN
RAISE 'Optimistic locking error';
END IF;
IF next_seq IS NULL THEN
RAISE 'Unknown topic';
END IF;
UPDATE
topics
SET
seq = next_seq
WHERE
topic = v_topic;
INSERT INTO events (topic, seq, data)
VALUES (v_topic, next_seq, v_data);
COMMIT;
END;
$$;