问题描述
我有一些关于 postgres 函数和事务如何工作的问题.
I have some questions about how postgres functions and transactions work.
目前我的函数是这样的:
Currently my function looks like this:
CREATE OR REPLACE FUNCTION test_function(some_id character varying)
RETURNS character varying AS
$BODY$
BEGIN
S1;
S2;
S3;
.
.
Sn;
RETURN some_id;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
语句可以是基于 some_id
的 INSERT
、UPDATE
或普通的 SELECT
查询.我从 postgre documentation 中了解到,此函数中的所有语句都是作为单个事务执行并在 END 处提交.
The statements can be INSERT
, UPDATE
or plain SELECT
queries based on some_id
. As I understand from postgre documentation, all statements in this function are executed as a single transaction and committed at the END.
我的问题是:
- 如果让我们说
S1
成功但S2
失败,S1
会被提交吗? - 我的理解是,
BEGIN
之后的所有语句都作为单个事务执行,对吗? - 在
END
之前没有明确的COMMIT
并且所有语句都成功的情况下,无论 autocommit = on/off 是否都会提交事务? - 假设
S1、S2、S3
都是INSERT
语句.S1
和S2
成功但S3
失败,S1, S2
中的插入是否会在没有显式ROLLBACK
语句?
- if lets say
S1
is successful butS2
fails, willS1
get committed? - is my understanding that, all statements after
BEGIN
are executed as a single trasaction, correct? - in the absence of an explicit
COMMIT
beforeEND
and all statements are successful, will the transaction be committed regardless of autocommit = on/off ? - lets say
S1, S2, S3
are allINSERT
statements.S1
andS2
succeed butS3
fails, will the inserts inS1, S2
be reversed in the absence of an explicitROLLBACK
statement?
谢谢!
推荐答案
按编号回答:
没有;如果
S2
失败,则整个事务中止,只能回滚.
No; if
S2
fails, the whole transaction is aborted and can only be rolled back.
可能有误会.启动事务的 SQL 语句 BEGIN
与启动 PL/pgSQL 块的 BEGIN
完全不同.后者不启动一个事务.
There is probably a misunderstanding. The SQL statement BEGIN
that starts a transaction is something quite different from the BEGIN
that starts a PL/pgSQL block. The latter does not start a transaction.
如果没有显式的 SQL 命令BEGIN
,每个语句都会在自己的事务中运行(自动提交").
If there was no explicit SQL command BEGIN
, every statement runs in its own transaction (“autocommit”).
函数中的所有语句都在单个事务中执行.
All statements in a function are executed in a single transaction.
您不能在函数中使用 COMMIT
(或 ROLLBACK
).
You cannot have COMMIT
(or ROLLBACK
) in a function.
是的.这是与 1. 相同的问题,只是是否定的.
Yes. This is the same question as 1., only in the negative.
这篇关于带有 BEGIN 的 postgres 函数和事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!