在 PostgreSQL 中提交、保存点、回滚？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5448984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Commit, savepoint, rollback to in PostgreSQL?
提问by EmirZ
Can someone please explain to me why does COMMIT in this function returns EXCEPTION ?
有人可以向我解释为什么这个函数中的 COMMIT 返回 EXCEPTION 吗？
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;
and why I can not use ROLLBACK TO SavePoint when EXCEPTION block exists in function?
为什么当函数中存在 EXCEPTION 块时我不能使用 ROLLBACK TO SavePoint ？
回答by Frank Heikens
You can't use COMMIT in a stored procedure, the entire procedure is a transaction of it's own.
您不能在存储过程中使用 COMMIT，整个过程是它自己的事务。
回答by Kuberchaun
You can't commit in a plpgsql stored function/procedure using plpgsql as Frank Heikens answered. You can however work around this issue by using dblink(http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-connect.html) or another store procedure language such as plperl(untrusted). Check out this link where this talked about.
正如 Frank Heikens 回答的那样，您不能使用 plpgsql 在 plpgsql 存储函数/过程中提交。但是，您可以通过使用 dblink（http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-connect.html）或其他存储过程语言（例如 plperl（untrusted））来解决此问题。查看此链接，其中讨论了这一点。
The high level is you open a new connection using one of these methods and issue a separate transaction on that connection. Works for most cases not ideal because you are opening a new connection, but may work fine for most use cases.
高级是您使用这些方法之一打开一个新连接并在该连接上发出单独的事务。在大多数情况下工作并不理想，因为您正在打开一个新连接，但对于大多数用例可能工作正常。
