Расширение pgpro_application_info включено в состав Postgres Pro Enterprise как стандартное расширение. Чтобы задействовать pgpro_application_info, выполните следующие действия:

pgpro_application_info работает со следующими основными классами данных:

CREATE FUNCTION DBMS_APPLICATION_INFO.READ_V_SESSION(
    OUT SID INTEGER,
    OUT DBNAME TEXT,
    OUT MODULE TEXT,
    OUT ACTION TEXT,
    OUT CLIENT_INFO TEXT
)
CREATE VIEW V_SESSION AS SELECT * FROM DBMS_APPLICATION_INFO.READ_V_SESSION();

CREATE PROCEDURE DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
                INOUT RINDEX INTEGER,
                INOUT SLNO INTEGER,
                OP_NAME TEXT DEFAULT NULL,
                TARGET OID DEFAULT 0,
                CONTEXT INTEGER DEFAULT 0,
                SOFAR DOUBLE PRECISION DEFAULT 0,
                TOTALWORK DOUBLE PRECISION DEFAULT 0,
                TARGET_DESC TEXT DEFAULT 'unknown target',
                UNITS TEXT DEFAULT NULL
            )

CREATE FUNCTION DBMS_APPLICATION_INFO.READ_V_SESSION_LONGOPS(
    OUT SID INTEGER,
    OUT SERIAL_N INTEGER,
    OUT DBNAME TEXT,
    OUT OPNAME TEXT,
    OUT TARGET OID,
    OUT TARGET_DESC TEXT,
    OUT SOFAR DOUBLE PRECISION,
    OUT TOTALWORK DOUBLE PRECISION,
    OUT UNITS TEXT,
    OUT START_TIME TIMESTAMP,
    OUT LAST_UPDATE_TIME TIMESTAMP,
    OUT P_TIMESTAMP TIMESTAMP,
    OUT TIME_REMAINING INTEGER,
    OUT ELAPSED_SECONDS INTEGER,
    OUT CONTEXT INTEGER,
    OUT MESSAGE TEXT,
    OUT USERNAME TEXT
)
CREATE VIEW V_SESSION_LONGOPS AS SELECT * FROM DBMS_APPLICATION_INFO.READ_V_SESSION_LONGOPS();

В нашем простом примере приложение будет действовать от имени ленивого мальчика, считающего ворон:

DO $$
DECLARE
    rindex integer := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT();
    slno integer := NULL;
    total_crows integer := 146;
BEGIN
    CALL DBMS_APPLICATION_INFO.SET_CLIENT_INFO('Lazy boy');
    CALL DBMS_APPLICATION_INFO.SET_MODULE('Crow counter', 'Prepare');

    PERFORM pg_sleep(2);

    CALL DBMS_APPLICATION_INFO.SET_ACTION('Count');

    FOR i IN 1..total_crows LOOP
        CALL DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
            RINDEX=>rindex,
            SLNO=>slno,
            OP_NAME=>'Counting birds',
            CONTEXT=>42,
            SOFAR=>i,
            TOTALWORK=>total_crows,
            UNITS=>'birds'
        );
        PERFORM pg_sleep(0.4);
    END LOOP;

    CALL DBMS_APPLICATION_INFO.SET_ACTION(NULL);
END$$;

Если запустить этот код в psql и обращаться к V_SESSION и V_SESSION_LONGOPS в другом сеансе, можно получить такие результаты:

postgres=# select * from v_session_longops;
 sid | serial_n | dbname | opname | target | target_desc | sofar | totalwork | units | start_time | last_update_time | p_timestamp | time_remaining | elapsed_seconds | context | message | username
-----+----------+--------+--------+--------+-------------+-------+-----------+-------+------------+------------------+-------------+----------------+-----------------+---------+---------+----------
(0 rows)

postgres=# select * from v_session;
 sid | dbname | module | action | client_info
-----+--------+--------+--------+-------------
(0 rows)

postgres=# select * from v_session;
 sid  |  dbname  |    module    | action  | client_info
------+----------+--------------+---------+-------------
 3721 | postgres | Crow counter | Prepare | Lazy boy
(1 row)

postgres=# select * from v_session;
 sid  |  dbname  |    module    | action | client_info
------+----------+--------------+--------+-------------
 3721 | postgres | Crow counter | Count  | Lazy boy
(1 row)

postgres=# select * from v_session_longops;
 sid  | serial_n |  dbname  |     opname     | target |  target_desc   | sofar | totalwork | units |         start_time         |      last_update_time      |        p_timestamp         | time_remaining | elapsed_seconds | context |               message                | username
------+----------+----------+----------------+--------+----------------+-------+-----------+-------+----------------------------+----------------------------+----------------------------+----------------+-----------------+---------+--------------------------------------+----------
 3721 |     3721 | postgres | Counting birds |      0 | unknown target |    52 |       146 | birds | 2023-05-12 14:24:19.013571 | 2023-05-12 14:24:39.458126 | 2023-05-12 14:24:39.458126 |             36 |              20 |      42 | Counting birds: 52 of 146 birds done | postgres
(1 row)

postgres=# select * from v_session_longops;
 sid  | serial_n |  dbname  |     opname     | target |  target_desc   | sofar | totalwork | units |         start_time         |     last_update_time      |        p_timestamp        | time_remaining | elapsed_seconds | context |               message                | username
------+----------+----------+----------------+--------+----------------+-------+-----------+-------+----------------------------+---------------------------+---------------------------+----------------+-----------------+---------+--------------------------------------+----------
 3721 |     3721 | postgres | Counting birds |      0 | unknown target |    89 |       146 | birds | 2023-05-12 14:24:19.013571 | 2023-05-12 14:24:54.29141 | 2023-05-12 14:24:54.29141 |             22 |              35 |      42 | Counting birds: 89 of 146 birds done | postgres
(1 row)

postgres=# select * from v_session;
 sid  |  dbname  |    module    | action | client_info
------+----------+--------------+--------+-------------
 3721 | postgres | Crow counter | Count  | Lazy boy
(1 row)

postgres=# select * from v_session_longops;
 sid  | serial_n |  dbname  |     opname     | target |  target_desc   | sofar | totalwork | units |         start_time         |      last_update_time      |        p_timestamp         | time_remaining | elapsed_seconds | context |                message                | username
------+----------+----------+----------------+--------+----------------+-------+-----------+-------+----------------------------+----------------------------+----------------------------+----------------+-----------------+---------+---------------------------------------+----------
 3721 |     3721 | postgres | Counting birds |      0 | unknown target |   140 |       146 | birds | 2023-05-12 14:24:19.013571 | 2023-05-12 14:25:14.736656 | 2023-05-12 14:25:14.736656 |              2 |              55 |      42 | Counting birds: 140 of 146 birds done | postgres
(1 row)

postgres=# select * from v_session_longops;
 sid  | serial_n |  dbname  |     opname     | target |  target_desc   | sofar | totalwork | units |         start_time         |      last_update_time      |        p_timestamp         | time_remaining | elapsed_seconds | context |                message                | username
------+----------+----------+----------------+--------+----------------+-------+-----------+-------+----------------------------+----------------------------+----------------------------+----------------+-----------------+---------+---------------------------------------+----------
 3721 |     3721 | postgres | Counting birds |      0 | unknown target |   146 |       146 | birds | 2023-05-12 14:24:19.013571 | 2023-05-12 14:25:17.140533 | 2023-05-12 14:25:17.140533 |              0 |              58 |      42 | Counting birds: 146 of 146 birds done | postgres
(1 row)

postgres=# select * from v_session;
 sid  |  dbname  |    module    | action | client_info
------+----------+--------------+--------+-------------
 3721 | postgres | Crow counter |        | Lazy boy
(1 row)