Wrong conversion of Oracle procedure with PRAGMA AUTONOMOUS_TRANSACTION to PG procedure with AUTONOMOUS_TRANSACTION and PG_BACKGROUND active

[eiav-dev]

Good day.

I am contacting you because when migrating a procedure from Oracle to PostgreSQL containing a PRAGMA AUTONOMOUS_TRANSACTION clause, when converted to the corresponding PG procedure, it calls the procedure with a select * from clause, but the procedure should probably be called with CALL. The PG_BACKGROUND and AUTONOMOUS_TRANSACTION parameters from ora2pg.conf are in active state.

Oracle Example:

CREATE OR REPLACE PROCEDURE TEST_S.TEST_PROC(P_NEW_VALUE IN NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN 
  INSERT INTO TEST_S.TEST(TEST_FIELD) VALUES (P_NEW_VALUE);
  COMMIT;
END;

PostgreSQL Example:

CREATE EXTENSION IF NOT EXISTS pg_background;

CREATE OR REPLACE PROCEDURE test_s.test_proc (p_new_value numeric) AS $body$
DECLARE
	v_query     text;
BEGIN
	v_query := 'SELECT true FROM test_proc_atx ( ' || quote_nullable(p_new_value) || ' )';
	PERFORM * FROM pg_background_result(pg_background_launch(v_query)) AS p (ret boolean);
END;
$body$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE PROCEDURE test_s.test_proc_atx (p_new_value numeric) AS $body$
BEGIN
  insert into test_s.test(test_field) values (p_new_value);
  commit;
end;
$body$
LANGUAGE PLPGSQL
;

I saw a similar problem in #1458. Maybe this will help solve the problem faster.

Also, please tell me if you can add a replacement that will add the schema name test_s before test_proc_atx in the script 'SELECT true FROM test_proc_atx (...' for similar procedures, because without it an error occurs when executing the procedure: "SQL Error [42883]: ERROR: procedure test_proc_atx(unknown) does not exist". A similar problem was found in functions using PRAGMA AUTONOMOUS_TRANSACTION.