postgresqldatabase-migrationamazon-auroraora2pg

Postgres alternatives for DBMS_APPLICATION_INFO read_module, set_action, set_module procedures


I have an Oracle stored procedure which I'm migrating to PostgreSQL.

There are DBMS_APPLICATION_INFO.READ_MODULE, DBMS_APPLICATION_INFO.SET_ACTION, and DBMS_APPLICATION_INFO.SET_MODULE procedures in the stored procedure for which I'm unable to find alternatives in Postgres.

What could be the closest alternatives for these procedures in Postgres?


Solution

  • You can use the parameter application_name:

    Example:

     postgres=# show application_name;
     application_name 
    ------------------
     psql
    (1 row)
    
    postgres=# select application_name from pg_stat_activity where pid = pg_backend_pid();
     application_name 
    ------------------
     psql
    (1 row)
    
    postgres=# set application_name='myapp';
    SET
    postgres=# select application_name from pg_stat_activity where pid = pg_backend_pid();
     application_name 
    ------------------
     myapp
    (1 row)
    
    postgres=# 
    

    Here is a possible solution to implement action and module:

    begin;
    BEGIN
    
    set application_name='my_module:my_action';
    SET
    
    select application_name from pg_stat_activity where pid = pg_backend_pid();
      application_name   
    ---------------------
     my_module:my_action
    (1 row)
    
    commit;
    COMMIT
    
    select application_name from pg_stat_activity where pid = pg_backend_pid();
      application_name   
    ---------------------
     my_module:my_action
    (1 row)
    
    do
    $$
    declare
     v_module text;
     v_action text;
     v_application_name text;
     v_pos int;
    begin
     select application_name into v_application_name
     from pg_stat_activity 
     where pid = pg_backend_pid();
     v_pos = position(':' in v_application_name); 
     if (v_pos > 0)
     then
      v_module = substring(v_application_name, 1, v_pos - 1);
      v_action = substring(v_application_name, v_pos + 1);
      raise notice 'v_module=%, v_action=%', v_module, v_action;
     end if;
    end;
    $$;
    NOTICE:  v_module=my_module, v_action=my_action
    DO