触发器错误时的事务回滚

Question

我想检查要插入系统的房间是否在那个日期已经租出。我考虑过计算匹配房间号和日期的行数，然后回滚事务。但是，尽管我将代码更改为引发用户定义的异常，仍然出现以下错误：

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function "checkRoom"() line 17 at SQL statement

CREATE OR REPLACE FUNCTION "checkRoom"() RETURNS TRIGGER AS
$BODY$
DECLARE 
    counter integer;
  BEGIN
    SELECT COUNT("num_sesion")
    FROM "Sesion"
    INTO counter
    WHERE "Room_Name"=NEW."Room_Name" AND "Date"=NEW."Date";

    IF (counter> 0) THEN -- Probably counter>1 as it's triggered after the transaction..
        raise notice 'THERE'S A ROOM ALREADY!!';
        raise exception 'The room is rented at that date';
    END IF;
    RETURN new;
EXCEPTION
    WHEN raise_exception THEN
        ROLLBACK TRANSACTION;
        RETURN new;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;

然后我创建触发器：

CREATE TRIGGER "roomOcupied" AFTER INSERT OR UPDATE OF "Room_Name", "Date"
ON "Sesion" FOR EACH ROW
EXECUTE PROCEDURE "checkRoom"();

距离我上次使用SQL已经过去了2年，plsql和plpgsql之间的变化让我感到非常困惑。

- Jose

---

我从未使用过plpgsql，但您仍然可以执行ROLLBACK TRANSACTION。您只能引发错误，事务应由调用此操作的任何内容回滚。我觉得:P - Edwin Stoteler

你不需要显式回滚。除非你捕获并处理异常，否则它会自动完成。 - Denis de Bernardy

错误信息已经说明了一切：你不能在 PL/pgSQL 函数内提交或回滚事务。无论是插入还是更新控制事务的任何内容都需要回滚该事务。 - user330315

谢谢您的评论，但问题仍然存在:( 触发器被激活，然后异常出现，但问题是即使我将触发器执行时机更改为BEFORE，系统也不会回滚插入。因此，我尝试手动执行回滚操作。我从异常中得到了消息，但只有1行受到影响（添加的那一行）： 注意：已经有一个房间了！ 查询成功返回：一行受到影响，执行时间为11毫秒。 - Jose

Answer 1

你的触发器函数有几个问题：

• 使用IF EXISTS (...) THEN而不是计算所有出现次数。更快，更简单。请参见：

  • PL/pgSQL检查行是否存在

• 一个触发器函数AFTERINSERT OR UPDATE只需返回NULL。RETURN NEW仅适用于称为BEFORE的触发器。手册：

  返回值在操作后触发的行级触发器中被忽略，因此它们可以返回NULL。

• 不平衡的单引号。

• 正如@Pavel解释的那样，你无法从plpgsql函数内部控制事务。任何未处理的异常都会强制自动回滚整个事务。因此，只需删除EXCEPTION块。

重写后的假设触发器：

CREATE OR REPLACE FUNCTION check_room()
  RETURNS TRIGGER AS
$func$
BEGIN
   IF EXISTS (
         SELECT FROM "Sesion"    -- are you sure it's not "Session"?
         WHERE  "Room_Name" = NEW."Room_Name"
         AND    "Date" = NEW."Date") THEN
     RAISE EXCEPTION 'The room is rented at that date';
   END IF;
   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

使用BEFORE触发器更有意义。

但是，使用"Room_Name"和"Date"的唯一索引会更加高效。这样，任何违反规则的行都会引发重复键异常，并回滚事务（除非被捕获并处理）。在现代Postgres中，您还可以使用INSERT ... ON CONFLICT ...跳过或转移此类INSERT尝试。请参阅：

• 如何在PostgreSQL中执行UPSERT（MERGE，INSERT ... ON DUPLICATE UPDATE）？

高级用法：

• 如何在PostgreSQL中使用RETURNING和ON CONFLICT？

- Erwin Brandstetter

Answer 2

PostgreSQL在处理错误方面与其他数据库有很大不同。任何未处理的错误都会被抛给用户。在PL/pgSQL中，您可以捕获任何异常或引发任何异常，但不能显式控制事务。任何PostgreSQL语句都是在事务内执行的（包括函数）。当任何未处理的异常到达顶部时，最外层事务会自动中断。

您可以：

• 引发异常（通常在触发器中）

BEGIN
  IF CURRENT_USER <> 'Admin' THEN
    RAISE EXCEPTION 'missing admin rights';
  END IF;
  RETURN NEW;
END;

• 捕获异常

BEGIN
  BEGIN -- start of protected section
    -- do some, what can be stopped by exception
  EXCEPTION WHEN divide_by_zero THEN
    -- exception handler;
    RAISE WARNING 'I was here';
    -- should ignore
  EXCEPTION WHEN others THEN
    -- any unexpected exception
    RAISE WARNING 'some unexpected issue';
    RAISE; -- forward exception'
  END;

没有其他选择-因此用PL/pgSQL编写应用程序非常简单，但与PL/SQL或TSQL不同。

- Pavel Stehule