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?
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.