postgresql trigger: disable auto commit and set isolation level

sql postgresql triggers autocommit transaction-isolation
giozh picture giozh · Jul 29, 2013 · Viewed 8.1k times · Source

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?

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Jul 30, 2013

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.

Related questions

Postgres trigger after insert accessing NEW

I have a pretty simple trigger: CREATE OR REPLACE FUNCTION f_log_datei() RETURNS TRIGGER AS $$ BEGIN INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id); END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER log_datei AFTER INSERT …

Read More
Postgresql trigger function with parameters

I want to create a trigger on a table called takes in postgresql to update a value in another table called student I'm trying to do it in the following way. But I'm getting an error that there is syntax …

Read More
PostgreSQL Update trigger

I have a table: CREATE TABLE annotations ( gid serial NOT NULL, annotation character varying(250), the_geom geometry, "rotationAngle" character varying(3) DEFAULT 0, CONSTRAINT annotations_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_srid_…

Read More