PostgreSQL触发器:禁用自动提交并设置隔离级别 [英] postgresql trigger: disable auto commit and set isolation level

问题描述

我在数据库INSTEAD OF INSERT上的表上编写触发器，该触发器进行了一些操作，然后将数据插入到不同的相关表中.现在我需要禁用自动提交并在触发器内设置不同的隔离级别，该怎么办?

i'm writing a trigger on database INSTEAD OF INSERT ON a table, that made some operation, then insert data into different related tables. Now i need to disable autocommit and set a different isolation level inside trigger, how can i do?

推荐答案

除嵌入式SQL外，PostgreSQL没有禁用自动提交的设置.如果您尝试关闭自动提交功能(例如PSQL)，则会看到类似此错误的信息.

PostgreSQL doesn't have a setting that disables autocommit except for embedded SQL. If you try to set autocommit off in, say, PSQL, you'll see something like this error.

sandbox=# set autocommit=off;
ERROR:  SET AUTOCOMMIT TO OFF is no longer supported

相反，请使用BEGIN启动事务.在PostgreSQL中，您可以启动事务并在单个语句中设置隔离级别. (其他平台需要多个语句.)骨架语法(适用于PostgreSQL 9.2)是

Instead, use BEGIN to start a transaction. In PostgreSQL, you can start a transaction and set the isolation level in a single statement. (Other platforms require multiple statements.) Skeleton syntax for PostgreSQL 9.2 is

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

where transaction_mode is one of:

    ISOLATION LEVEL { SERIALIZABLE   | REPEATABLE READ | 
                      READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

使用COMMIT或ROLLBACK结束事务.

End the transaction with either COMMIT or ROLLBACK.

这篇关于PostgreSQL触发器:禁用自动提交并设置隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！