0

Can i set transaction level in stored procedure in postgresql? Getting error calling sp1 below:

ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query

CREATE OR REPLACE PROCEDURE sp1()
LANGUAGE 'plpgsql'
AS $BODY$
begin
    update table1 set a=1;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
update table1 set a=2;
END;
$BODY$;
Arcil Tralleis
  • 91
  • 11
  • 1
    Did you read the error? Besides, why are you trying to read uncommitted data? If your queries block due to bad data access patterns, `READ UNCOMMITTED` won't fix anything. It *can* cause far more serious problems, like bad or lost data – Panagiotis Kanavos Jun 02 '21 at 10:52
  • 1
    From the manual: The SQL standard defines one additional level, READ UNCOMMITTED. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED. – Frank Heikens Jun 02 '21 at 11:00

1 Answers1

1

You cannot set the isolation level, because you already executed an UPDATE statement in the same transaction.

Setting a transaction to isolation level READ UNCOMMITTED will not have any effect, so you should simply remove that statement. This isolation level is useful in databases that take blocking read locks to allow for good concurrency at the price of consistency. But since readers are not blocked by writers in PostgreSQL, you don't need this crutch.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263