postgresql trigger: disable auto commit and set is

问题:

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?

回答1:

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

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

End the transaction with either COMMIT or ROLLBACK.