Postgresql – How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL

isolation-levelplpgsqlpostgresqlstored-procedurestransaction

I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within Chapter 42. PL/pgSQL — SQL Procedural Language only states that:

A new transaction starts out with default transaction characteristics such as transaction isolation level.

Can we change the default isolation level? Can you provide example code?

Best Answer

That should be simple:

ALTER PROCEDURE myproc
   SET default_transaction_isolation = 'repeatable read';

The downside to that is that because of the limitations of transaction management inside procedures, you can no longer COMMIT or ROLLBACK inside such a procedure.

If you need that, you could start the procedure body like this:

COMMIT;  -- otherwise you get a silly error
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Transaction management inside procedures is pretty limited at the moment.

Related Question