在函数中启动一个事务块

I'm trying to use create a transaction block inside a function, so my goal is to use this function one at time, so if some one use this Function and another want to use it, he can't until the first one is finish i create this Function :

我试着在一个函数中创建一个事务块，所以我的目标是每次都使用这个函数，所以如果有人使用这个函数，而另一个人想要使用它，直到第一个事务块完成，我才能创建这个函数:

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;

But it seams not work, i have a mistake Syntax error BEGIN;

但它不工作，我有一个语法错误的开始;

Without BEGIN; and COMMIT i get a correct result, i use this query to check :

没有开始;提交我得到了正确的结果，我用这个查询检查:

-- 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;

So the result is :

结果是:

+-----+----------------------------+------------+
|  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     |
+-----+----------------------------+------------+

But the result should be :

但结果应该是:

+-----+----------------------------+------------+
|  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     |
+-----+----------------------------+------------+

so the third one id = 3 should have the max_value = 3 and not 2, this happen because the first user Select the max = 1 and wait 10 sec and the second user Select the max = 1 and wait 3 sec before Insertion, but the right solution is : I can't use this Function Until the First one finish, for that i want to make something secure and protected.

所以第三个id = 3应该max_value = 3,而不是2,这是因为第一个用户选择max = 1并等待10秒和第二个用户选择max = 1,等待3秒插入之前,但正确的解决方案是:我不能使用这个函数,直到第一个完成,我想做一些安全保护。

My questions is :

我的问题是:

• how can i make a Transaction block inside a function?
• 如何在函数中创建事务块?
• Do you have any suggestion how can we make this, with a secure way?
• 你有什么建议吗?我们怎样才能保证安全?

Thank you.

谢谢你！

3 个解决方案

#1

Ok so you cannot COMMIT in a function. You can have a save point and roll back to the save point however.

你不能在函数中提交。您可以有一个保存点并回滚到保存点。

Your smallest possible transaction is a single statement parsed and executed by the server from the client, so every transaction is a function. Within a transaction, however, you can have save points. In this case you would look at the exception handling portions of PostgreSQL to handle this.

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

However that is not what you want here. You want (I think?) data to be visible during a long-running server-side operation. For that you are kind of out of luck. You cannot really increment your transaction ids while running a function.

但是这不是你想要的。您希望(我认为?)数据在长时间运行的服务器端操作期间是可见的。因为你有点不走运。在运行函数时，不能真正增加事务id。

You have a few options, in order of what I would consider to be good practices (best to worst):

你有几个选择，按照我认为是好的实践(从最好到最坏):

1. Break down your logic into smaller slices that each move the db from one consistent state to another, and run those in separate transactions.
2. 将逻辑分解成小块，每个小块将db从一个一致的状态移动到另一个，并在不同的事务中运行它们。
3. Use a message queue (like pg_message_queue)in the db, plus an external worker, and something which runs a step and yields a message for the next step. Disadvantage is this adds more maintenance.
4. 在db中使用消息队列(如pg_message_queue)，外加一个外部worker，以及运行一个步骤并为下一步生成消息的东西。缺点是增加了更多的维护。
5. Use a function or framework like dblink or pl/python, or pl/perlu to connect back to the db and run transactions there. ick....
6. 使用像dblink或pl/python或pl/perlu这样的函数或框架来连接到db并在那里运行事务。柳枝稷....

#2

You can use dblink for this. Something like :

你可以使用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;

#3

I don't know if this is a good way or not but what if we use LOCK TABLE for example like this :

我不知道这是不是一个好方法但是如果我们用锁表，比如这个

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;

It gives me the right result.

它给了我正确的结果。