PostgreSQL - 在函数中启动事务块

Question

我试图在一个函数中创建一个事务块，我的目标是使这个函数一次只能使用一次。如果有人使用了这个函数并且另一个人想要使用它，则必须等待第一个人完成后才能使用。

CREATE OR REPLACE FUNCTION my_job(time_to_wait integer) RETURNS INTEGER AS $$
DECLARE
  max INT;
BEGIN
   BEGIN;
       SELECT MAX(max_value) INTO max FROM sch_lock.table_concurente;
       INSERT INTO sch_lock.table_concurente(max_value, date_insertion) VALUES(max + 1, now());
       -- Sleep a wail
       PERFORM pg_sleep(time_to_wait);
       RETURN max;
   COMMIT; 
END;
$$ 
LANGUAGE plpgsql;

但似乎不起作用，我有一个语法错误 BEGIN;

如果没有BEGIN;和COMMIT，我会得到正确的结果，我使用这个查询来检查：

-- First user should to wait 10 second
SELECT my_job(10) as max_value;

-- First user should to wait 3 second
SELECT my_job(3) as max_value;

所以结果是：

+-----+----------------------------+------------+
|  id |              date          | max_value  |
+-----+----------------------------+------------+
|  1  | 2017-02-13 13:03:58.12+00  |      1     |
+-----|----------------------------+------------+
|  2  | 2017-02-13 13:10:00.291+00 |      2     |
+-----+----------------------------+------------+
|  3  | 2017-02-13 13:10:00.291+00 |      2     |
+-----+----------------------------+------------+

但结果应该是：

+-----+----------------------------+------------+
|  id |              date          | max_value  |
+-----+----------------------------+------------+
|  1  | 2017-02-13 13:03:58.12+00  |      1     |
+-----|----------------------------+------------+
|  2  | 2017-02-13 13:10:00.291+00 |      2     |
+-----+----------------------------+------------+
|  3  | 2017-02-13 13:10:00.291+00 |      3     |
+-----+----------------------------+------------+

因此，第三个 id = 3 应该具有 max_value = 3 而不是 2，这是因为第一个用户选择了 max = 1 并等待了 10 秒，第二个用户选择了 max = 1 并等待了 3 秒 才进行插入，但正确的解决方案是：在第一个完成之前，我不能使用此函数，因此我想要创建一些安全和受保护的东西。

我的问题是：

• 如何在函数内部创建事务块？
• 您有任何建议，可以以安全的方式实现吗？

谢谢。

- Youcef LAIDANI

---

很遗憾，这是不可能的。函数无法使用提交或回滚。 - user330315

哦，我的天啊，@a_horse_with_no_name，我该怎么办？有什么建议吗？ - Youcef LAIDANI

为什么不直接使用序列来生成数字呢？这样会快得多，而且可扩展性也更好。 - user330315

@a_horse_with_no_name 这只是一个例子，我还有另外一份工作需要做。我应该将它制作成一个函数来进行一些计算。 - Youcef LAIDANI

@a_horse_with_no_name 如果两个用户取相同的值怎么办？我需要使用这个值进行一些计算，而不仅仅是一个数字。 - Youcef LAIDANI

有一个需要注意的地方，你可能可以让第二个用户将事务级别设置为READ UNCOMMITTED作为暂时的解决方法，但在这样做之前，你需要真正了解其中的影响。 - Chris Travers

Answer 1

好的，所以你不能在函数中使用COMMIT。但是你可以有一个保存点并回滚到该保存点。

你最小的事务是由服务器从客户端解析和执行的单个语句，因此每个事务都是一个函数。然而，在事务内部，你可以有保存点。在这种情况下，你需要查看PostgreSQL的异常处理部分来处理它。

但这不是你想要的。你想（我认为？）在长时间运行的服务器端操作期间看到数据。对于这个问题，你有点倒霉。你不能在运行函数时真正增加你的事务ID。

你有几个选项，按照我认为的最佳实践的顺序排列（从好到坏）：

1. 将你的逻辑拆分成较小的片段，每个片段将数据库从一个一致状态移动到另一个状态，并在单独的事务中运行这些片段。
2. 在数据库中使用消息队列（如pg_message_queue），外部工作程序和运行步骤并产生下一步消息的东西。缺点是这会增加更多的维护工作。
3. 使用像dblink或pl/python或pl/perlu这样的函数或框架连接回数据库并在那里运行事务。呕......

- Chris Travers

---

这真的很令人难过 :( 好的，我会尝试遵循你的建议，无论如何谢谢你 :) - Youcef LAIDANI

Chris Travers，请检查我的答案，我不知道这是否是一个好主意，但它给了我正确的结果。 - Youcef LAIDANI

Answer 2

您可以使用dblink来实现这一点，类似于：

：

CREATE OR REPLACE FUNCTION my_job(time_to_wait integer) RETURNS INTEGER AS $$
DECLARE
  max INT;
BEGIN
   SELECT INTO RES dblink_connect('con','dbname=local');
   SELECT INTO RES dblink_exec('con', 'BEGIN');
   ...
   SELECT INTO RES dblink_exec('con', 'COMMIT');
   SELECT INTO RES dblink_disconnect('con');
END;
$$ 
LANGUAGE plpgsql;

- Roman Tkachuk

Answer 3

我不知道这是否是一个好的方式，但如果我们像这样使用 LOCK TABLE 会怎么样：

CREATE OR REPLACE FUNCTION my_job(time_to_wait integer) RETURNS INTEGER AS $$
DECLARE
  max INT;
     BEGIN
       -- Lock table so no one will use it until the first one is finish
       LOCK TABLE sch_lock.table_concurente IN ACCESS EXCLUSIVE MODE;

       SELECT MAX(max_value) INTO max FROM sch_lock.table_concurente;
       INSERT INTO sch_lock.table_concurente(max_value, date_insertion) VALUES(max + 1, now());
       PERFORM pg_sleep(time_to_wait);
       RETURN max;
     END;
     $$ 
LANGUAGE plpgsql;

它给了我正确的结果。

- Youcef LAIDANI

---

据我理解，问题是用户2需要查看用户1查询中当前正在发生的插入操作。我认为这并没有解决这个问题。 - Chris Travers

@ChrisTravers 这个工作得很好，所以当第一个用户完成时，它会插入 max_value = 2，然后第二个用户选择这个值并用它来插入 max_value = 3，这样我们就可以安全地访问这个值，它只被一个用户在同一时间使用。 - Youcef LAIDANI

啊，所以你的解决方案是表锁。我猜那也可以。 - Chris Travers

（以并发为代价） - Chris Travers

你使用锁的问题在于它会降低并发性。如果你必须使用锁，请确保事务尽可能短，因为锁将一直保持到事务完成。 - Chris Travers