带有 BEGIN 的 postgres 函数和事务 [英] postgres functions and transactions with BEGIN

问题描述

我有一些关于 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.

我的问题是:

1. 如果让我们说S1成功但S2失败，S1会被提交吗?
2. 我的理解是，BEGIN 之后的所有语句都作为单个事务执行，对吗?
3. 在 END 之前没有明确的 COMMIT 并且所有语句都成功的情况下，无论 autocommit = on/off 是否都会提交事务?
4. 假设 S1、S2、S3 都是 INSERT 语句.S1 和 S2 成功但 S3 失败，S1, S2 中的插入是否会在没有显式ROLLBACK 语句?

1. if lets say S1 is successful but S2 fails, will S1 get committed?
2. is my understanding that, all statements after BEGIN are executed as a single trasaction, correct?
3. in the absence of an explicit COMMIT before END and all statements are successful, will the transaction be committed regardless of autocommit = on/off ?
4. lets say S1, S2, S3 are all INSERT statements. S1 and S2 succeed but S3 fails, will the inserts in S1, S2 be reversed in the absence of an explicit ROLLBACK statement?

谢谢！

推荐答案

按编号回答:

1. 没有；如果S2失败，则整个事务中止，只能回滚.

1. 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 函数和事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！