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
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;
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.