Executing Transaction control procs in PG-promise (Autocommit)

292 Views Asked by At

I have a proc like this

CREATE OR REPLACE PROCEDURE "TRY-DQ-7a28fd25d95c0969bff16b963af1c832"()
LANGUAGE plpgsql
AS $$
 DECLARE "Continue" boolean :=True; 
 BEGIN
 COMMIT;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
    INSERT INTO test1 (a) VALUES (i);
    IF i % 2 = 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
COMMIT;
 END 
 $$;

Which i want to execute using PG-promise ,whats different is this proc requires autocommit ON to execute correctly atleast on pgAdmin how do i achieve the same in pg-promise? or the driver below it?

UPDATE:

This seems to be bug in PG/Or something that i am misunderstanding (This was surfaced via pg-promise so first i thought this was something to do with it)

Setup:

CREATE OR REPLACE PROCEDURE "SP"(n integer)
LANGUAGE plpgsql
AS $$
 BEGIN
 FOR i IN 0..n LOOP
    COMMIT;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
    CREATE TEMP TABLE "x" (i integer) ON COMMIT DROP;
 END LOOP;
 END 
 $$;

pg-promise invocation:

setInterval(() => {
    pgWriter.any(`
    DO 
    $$
    BEGIN
    CALL "SP"(10);
    END 
    $$;`)
    .then(console.log)
    .catch(console.error);
}, 1000);

OR pgAdmin invocation:

DO 
$$
BEGIN
CALL "SP"(10);
END 
$$;

Results: The very first invocation fails with following message (Only First) run it again on the same connection it will normally execute.

ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE"
PL/pgSQL function "Q"."SP"(integer) line 5 at SET
SQL statement "CALL "Q"."SP"(10)"
PL/pgSQL function inline_code_block line 3 at CALL
SQL state: 25001

Analysis : So far i am thinking it should be a problem in my code maybe they are setup something one the run first time which helps them to run it multiple times later (some state), also interestingly 2 lines seems to be important

  1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
  2. CREATE TEMP TABLE "x" (i integer) ON COMMIT DROP;

If either of them is commented the error goes away..

If community knows anything about this or point error in my code will really help Thanks in advance.

0

There are 0 best solutions below