postgresql - 在 Postgres DB 上的 PLPGSQL 过程中使用 BEGIN/END 事务管理时的方法/逻辑？

Question

我主要是 Java/Python 开发人员，但我想从数据库端了解更多，因为它是如此关键。我最近一直在研究事务管理，我很好奇，据我所知， Postgres 中的函数不保存任何事务数据。但是，另一方面，程序可以。

我试图更好地理解当人们想要BEGIN/END在他们的程序中创建一个块时的用例是什么。例如，这个代码片段

CREATE PROCEDURE myCoolProcedure(some_arg int) LANGUAGE plpgsql
AS $procedure$
DECLARE

BEGIN
    -- BEGIN here?
    LOOP
     EXIT WHEN record_cnt = 0;
     
    -- or here?
     WITH my_cte AS (SELECT *
                     FROM some_table t1
                     WHERE t1.some_column = 'TOYOTA'
                     LIMIT 500)
                     INSERT INTO another_table t2 (SELECT * FROM my_cte);
     -- COMMIT here? 
         END;
          -- BEGIN here?
                     UPDATE some_log_table slt1
                     SET slt1.status = 'DONE'
                     WHERE slt1.id = some_arg;
        -- COMMIT another here? or?
         -- END;
                   

            -- Should I be handling some Exceptions some around here???
            EXCEPTION WHEN OTHERS THEN
            -- error handling here
     
    GET DIAGNOSTICS record_cnt := row_count;
       END LOOP;

END
$procedure$

希望这不是太糟糕。我只想知道一些事情：

1. 我相信你不能 COMMIT在任何使用异常处理的 BEGIN/END 块中。我知道我能够COMMIT在相同的块中（除非我疯了）但是在异常捕获之后。这是真的？
2. 放置 BEGIN/END 块的逻辑是什么？每个 DML 语句（INSERT、DELETE、UPDATE等）是否应该放在它自己的 BEGIN/END 块中？
3. 我的印象是COMMMIT结束事务并启动了一个新事务，但我也听说从未在循环中使用它们。这是怎么回事？我们应该COMMIT在任何 DM/DDL 声明之后吗？
4. 当然，如果您COMMIT在Exception之后的Exception块中执行此操作，这是否意味着您正在提交的是从保存点回滚的更改。

但是，是的，总的来说，我想知道如何将它们应用于您的 plpgsql 脚本（特别是程序）的哲学/方法是什么

谢谢

标签： postgresqlstored-procedurestransactionsplpgsqlatomic