在PostgreSQL中，Commit、Savepoint和Rollback to是什么意思？

Question

请问有人能解释一下为什么这个函数中的COMMIT会返回异常吗？

DECLARE
  XNar CURSOR (forDATE Varchar) IS 
   SELECT NARUCENO, ISPORUKA_ID FROM XDATA_NARUDZBE 
   WHERE TO_CHAR(XDATA_NARUDZBE.DATUM, 'DD.MM.YYYY') = forDATE;
 LastDate  DATE;
 OutResult INTEGER;
 curNAR    NUMERIC;
 curISP    VARCHAR;
 RXNar     RECORD; 
BEGIN

 OutResult := 1;

 SELECT MAX(DATUM) INTO LastDate FROM XDATA_NARUDZBE;  

 FOR RXNar IN XNar(TO_CHAR(LastDate, 'DD.MM.YYYY')) LOOP

   IF (RXNar.NARUCENO <> 0) AND (RXNar.ISPORUKA_ID = 'R01') THEN
     UPDATE NARUDZBE SET ISPORUCENO = RXNar.NARUCENO 
      WHERE NARUDZBE.PP_ID  = RXNar.PP_ID
        AND NARUDZBE.ART_ID = RXNar.ART_ID
        AND NARUDZBE.ISPORUKA_ID = 'R01';
   END IF;

  END LOOP;

 COMMIT; <--- ????

 RETURN OutResult;

 EXCEPTION
  WHEN OTHERS THEN
   OUTRESULT := 0;
   RAISE;    
   RETURN OutResult;

END;    

为什么在函数中存在异常块时，我不能使用ROLLBACK TO SavePoint？

- EmirZ

Answer 1

您不能在存储过程中使用COMMIT命令，因为整个存储过程本身就是一个事务。

- Frank Heikens

---

PL/pgSQL提供了一种灵活的方法来处理动态查询。使用EXECUTE语句和字符串构造函数（如quote_ident 和 quote_literal）可以在运行时动态生成SQL命令并将其执行。这对于需要动态构建复杂SQL查询的应用程序非常有用。但是，在编写此类代码时，请务必小心，以避免潜在的SQL注入攻击。 - user533832

@JackPDouglas：这就是OP需要的，嵌套块。 - Frank Heikens

换句话说，他需要在过程外面进行“提交(commit)”操作吗？我的理解是他可以在过程内部回滚子事务，但不能完全“提交(commit)”（或显式地“回滚(rollback)”整个事务）。 - user533832

我对PG还不熟悉...谢谢你为我澄清！下一个问题呢？ - EmirZ

Answer 2

您不能在使用plpgsql作为存储函数/过程时进行提交，正如Frank Heikens所回答的那样。但是，您可以通过使用dblink (http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-connect.html) 或另一种存储过程语言（如plperl（不受信任））来解决此问题。请查看此链接以了解更多信息。

http://postgresql.1045698.n5.nabble.com/Re-GENERAL-Transactions-within-a-function-body-td1992810.html

高层次的解决方案是使用其中一种方法打开新连接并在该连接上发出单独的事务。对于大多数情况而言，这种方法可行但并不理想，因为您正在打开一个新连接，但对于大多数用例而言可能很好用。

- Kuberchaun

Answer 3

从PostgreSQL11开始有一个procedure模块。基于手册的演示。

CREATE OR REPLACE PROCEDURE insert_data_drop(_a integer, _b integer)
LANGUAGE plpgsql
AS $$
begin
   INSERT INTO tbl(a) VALUES (_a);
   INSERT INTO tbl(b) VALUES (_b);
   Rollback;
   INSERT INTO tbl(a) VALUES (_a);
end
$$;

现在 CALL insert_data_drop(1, 2); 将把1插入到列a，2将不会被保存。然而，SAVEPOINT似乎无法工作。它会显示错误信息，例如：

ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function insert_data_drop(integer,integer) line 5 at SQL stateme

- jian