beginning isolation level in postgresql

Question

I have the following statement in plpgsql:

CREATE OR REPLACE FUNCTION json_update (mdata JSON) RETURNS void AS $$
          DECLARE
            mdata JSON = mdata;
              mRecord record;
          BEGIN
            BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
              SELECT 
                 COALESCE(searches, 0) AS searched_quantity,
                 COALESCE(event, '2030-06-29') AS event_date,
              INTO mRecord 
                  FROM json_populate_record(NULL::my_app.app_report, mdata::JSON);
                  
              --here I also do some lengthy write operations and some calculation taking above data
            COMMIT;
         END;
$$ LANGUAGE plpgsql;

SELECT * FROM json_update('{"event":"2021-07-07","searches":10}');

I get a syntax error in the line: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

As far I know, this is how we set transaction levels outside of a stored procedure, and the syntax is fine if I do it in something like psql command line. BTW, I execute this from the pgAdmin's query panel.

Is there another syntax for setting transaction levels inside of stored procedures? BTW, I already read this and it doesn't really answer my question.

Does this answer your question? [set isolation level for postgresql stored procedures](https://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures) — Elijah, Apr 24 '22 at 15:02

score 0 · Answer 1 · answered Jul 07 '21 at 06:37

0

You can never control transactions in a function. If you really need to commit inside procedural code, you will have to use a stored procedure.

When the procedure is entered a transaction is started automatically, so you can't start another one, but you can set the isolation level as the first command:

CREATE OR REPLACE procedure json_update (mdata JSON) AS $$
DECLARE
   mRecord record;
BEGIN
  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT 
     COALESCE(searches, 0) AS searched_quantity,
     COALESCE(event, '2030-06-29') AS event_date
  INTO mRecord 
        FROM json_populate_record(NULL::my_app.app_report, mdata::JSON);
                  
  ... do your work here ...
  COMMIT;
END;
$$ LANGUAGE plpgsql;

More details in the manual

answered Jul 07 '21 at 06:37

do you mean ".... can't start another one", that concurrent transactions would fail? I was trying to get the behaviour like: the second transaction which comes in (in paralled) would wait until the first transaction completes – BumbleBee Jul 07 '21 at 14:55
_Calling_ the procedure implicitly starts a transaction, so you can't (and don't need to) start one at the beginning of the procedure. And repeatable read won't make the caller wait - it only makes sure the transaction inside the procedure sees a consistent state throughout all statements. – Jul 07 '21 at 15:16
so what can I do to make the send call wait? I tried using 'SELECT *.... FOR UPDATE` , but, once I hit requests from multiple threads, it gives me a `concurrent threads updating same record` error – BumbleBee Jul 07 '21 at 15:21
by, "... you can't start (and don't need to) start one at the beginning of the procedure" , do you mean the second transaction __is__ cancelled? – BumbleBee Jul 07 '21 at 15:22
Not sure which second transaction you mean, but unless you run a rollback or an error occurs, Postgres won't "cancel" a transaction. If you want to make sure that a procedure is only executed by a single session at any given time, you need to use other tools. The isolation level is not suited for that. But that's a completely different question. – Jul 07 '21 at 15:24
I've found similar question: https://stackoverflow.com/questions/68053327/does-sql-statement-ensure-atomicity-in-postgres/68117979#68117979 and they suggest using one of `FOR UPDATE` or `ISOLATION LEVEL`. And, I also don't know what you meant by `session`, but, I mean to basically read only committed values or wait until a commit is done for a single row of the database. – BumbleBee Jul 07 '21 at 15:42
Also, I tried the above syntax and it's giving me a `SET TRANSACTION ISOLATION LEVEL must be called before any query`, is it because of my variable declarations? I executed it in pgAdmin's query tool – BumbleBee Jul 07 '21 at 15:47

beginning isolation level in postgresql

1 Answers1