postgresql trigger: disable auto commit and set isolation level

Question

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?

What do you mean by "disable autocommit". Or at least, what do you think you mean by it? — Richard Huxton
– Richard Huxton, Commented Jul 29, 2013 at 16:41

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-07-30 01:38:44Z

9

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.

answered Jul 30, 2013 at 1:38

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mekbib.awoke · Accepted Answer · 2021-11-22 19:25:27Z

0

use the below

\set AUTOCOMMIT off

you can then end with

COMMIT;

answered Nov 22, 2021 at 19:25

mekbib.awoke

1,5902 gold badges12 silver badges17 bronze badges

2 Comments

Jeremy Caney Over a year ago

What's the benefit of this over the accepted answer from eight years ago?

mekbib.awoke Over a year ago

I thought it will be helpful

Collectives™ on Stack Overflow

postgresql trigger: disable auto commit and set isolation level

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related