F.45. pgpro_application_info — port applications using DBMS_APPLICATION_INFO package #
pgpro_application_info is an extension designed to help developers who port applications using the DBMS_APPLICATION_INFO package from Oracle to Postgres Pro. It can also be used by database administrators as an additional source of data when analyzing performance and by developers when debugging.
pgpro_application_info creates procedures and functions that an application can use to report its status, performed actions, and action progress. The database administrator can access this information through specific views.
The typical sequence of client actions reporting to pgpro_application_info will look like this (in the example, a librarian wants to count all the words “the” in the library):
Once the client makes a connection to the database, it registers itself as the
Librarianand runs the scanner application, which, in its turn, registers itself as theBook Scannermodule and indicates that it is currently attempting to performBook Scanningto find all occurrences of the word “the” in 18042 books, having completed 0 scans as of now.Having scanned a few books, the application updates the information to reflect its progress.
When the scanning is completed, the application registers this fact and sets its current action as
Idle(or NULL).
F.45.1. Installation #
The pgpro_application_info extension is a built-in extension included into Postgres Pro Enterprise. To enable pgpro_application_info, do the following:
Add the library name to the
shared_preload_librariesvariable in thepostgresql.conffile:shared_preload_libraries = 'pgpro_application_info'
Reload the database server for the changes to take effect.
Note
To verify that the library was installed correctly, you can run the following command:
SHOW shared_preload_libraries;
Create the extension using the following query:
CREATE EXTENSION pgpro_application_info;
F.45.2. Usage #
pgpro_application_info handles the following main data classes:
Application Data: current module name, performed action, and client description. The database administrator can access the information about active clients and their actions via the V_SESSION view.
Information about Long Operations: textual descriptions and comments, operation progress report and optionally the link to the database object in question. The database administrator can access the information about long operations via the V_SESSION_LONGOPS view, which in addition to the data sent by the client contains operation start and latest update timestamps, as well as the estimated time remaining.
Note
Note that in Oracle's DBMS_APPLICATION_INFO textual parameters supplied by the user have various maximum length up to 64 bytes, but for convenience in pgpro_application_info all of them have the same maximum length of 64 bytes. Exceeding information is truncated.
F.45.2.1. Application Data #
To set application information, pgpro_application_info provides the following procedures:
-
CREATE PROCEDURE DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info TEXT)# Sets the client information. This is typically the client name or a more complex description, but strictly speaking, it can be any text provided by the user.
-
CREATE PROCEDURE DBMS_APPLICATION_INFO.SET_ACTION(action_name TEXT)# Sets the name of the executed action in the current module.
-
CREATE PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE(module_name TEXT, action_name TEXT DEFAULT NULL)# Sets the name of the current module (program) and action. When the program terminates, call this procedure with the name of the next module if there is one, or NULL otherwise.
-
CREATE PROCEDURE DBMS_APPLICATION_INFO.READ_CLIENT_INFO(OUT client_info TEXT)# Returns the client information previously set by
SET_CLIENT_INFOin the current session.-
CREATE PROCEDURE DBMS_APPLICATION_INFO.READ_MODULE(OUT module_name TEXT, OUT action_name TEXT)# Returns the module information previously set by
SET_MODULEand action information set bySET_MODULEorSET_ACTIONin the current session.
F.45.2.1.1. The V_SESSION View #
The V_SESSION view provides the information about all the active sessions reported via pgpro_application_info procedures, the connected clients, modules, and current actions. The view contains one row for each distinct session. When a session reports its information for the first time, the corresponding view entry is created. Each row exists during the corresponding session lifetime so the view is empty after server restart. The columns of the view are shown in Table F.34. For ease of use, Postgres Pro also provides an equivalent view called V$SESSION, familiar to Oracle users, which contains the same information. Note, however, that better compatibility with Oracle provided by this alternative is a deviation from the SQL standard and, consequently, makes the code less portable.
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();
Table F.34. V_SESSION Columns
| Name | Description |
|---|---|
SID | Session ID, PID |
DBNAME | Name of the database the session is connected to. If you want to view the entries only for the current database, add the following condition to the query: WHERE DBNAME=current_database(). |
MODULE | Name of the module executed by the session that was previously set by SET_MODULE. |
ACTION | Name of the action executed by the session that was previously set by SET_MODULE or SET_ACTION. |
CLIENT_INFO | Information about the client associated with the session that was previously set by SET_CLIENT_INFO. |
F.45.2.2. Information about Long Operations #
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
)
The SET_SESSION_LONGOPS procedure creates a new row (or updates the existing one) in the V_SESSION_LONGOPS view. Each row contains information about a single long operation (typically longer than 6 seconds): its description, the accessed objects, and the estimated time remaining.
RINDEX: A token which represents the row in theV_SESSION_LONGOPSview. To start a new row, set this to the result of theDBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT()function. When you callSET_SESSION_LONGOPSto update the existing row, set this to the returned value from the prior call.SLNO: Internal information saved between calls. To start a new row, set this to NULL. When you callSET_SESSION_LONGOPSto update the existing row, set this to the returned value from the prior call.OP_NAME: Short description of the performed operation, usually its name.TARGET: OID of the object that is being worked on during the long-running operation.CONTEXT: Any number the user wants to store.SOFAR: Any number the user wants to store. This is typically the amount of work which has been done so far.TOTALWORK: Any number the user wants to store. This is typically the amount of work to be done.TARGET_DESC: The description of the object that is being worked on during the long-running operation (TARGET).UNITS: The units in whichSOFARandTOTALWORKare represented.
SOFAR and TOTALWORK values are used to calculate the estimated time remaining in the V_SESSION_LONGOPS view, but valid values are any numbers so if they are inconsistent (for example, both values are negative, or TOTALWORK=0), the estimated time remaining will not be calculated.
F.45.2.2.1. The V_SESSION_LONGOPS View #
The V_SESSION_LONGOPS view provides the information about long operations previously passed by SET_SESSION_LONGOPS. The view contains one row for each distinct long operation. Each row exists until it is rewritten or the server is shut down. The view is empty after server restart. The columns of the view are shown in Table F.35. For ease of use, Postgres Pro also provides an equivalent view called V$SESSION_LONGOPS, familiar to Oracle users, which contains the same information. Also, the P_TIMESTAMP column was renamed TIMESTAMP in this view. Note, however, that better compatibility with Oracle provided by these alternatives is a deviation from the SQL standard and, consequently, makes the code less portable.
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();
Table F.35. V_SESSION_LONGOPS Columns
| Name | Description |
|---|---|
SID | ID of the session processing the long-running operation. |
SERIAL_N | Synonym for SID. Added for Oracle-compatibility reasons. |
DBNAME | Name of the database the session is connected to. If you want to view the rows only for the current database, add the following condition to the query: WHERE DBNAME=current_database(). |
OPNAME | Short description of the performed operation, usually its name. |
TARGET | OID of the object that is being worked on during the long-running operation. |
TARGET_DESC | The description of the object that is being worked on during the long-running operation (TARGET). |
SOFAR | The amount of work which has been done so far in units specified in UNITS. |
TOTALWORK | The amount of work to be done in units specified in UNITS. |
UNITS | The units in which SOFAR and TOTALWORK are represented. |
START_TIME | Operation start time (or the time the row was created). |
LAST_UPDATE_TIME | Last row update time. |
P_TIMESTAMP | Synonym for LAST_UPDATE_TIME. Added for Oracle-compatibility reasons. For convenience, you can use another parameter name by adding an alias. For example, to create a TIMESTAMP alias, add SELECT P_TIMESTAMP AS TIMESTAMP to the view definition. |
TIME_REMAINING | Estimate of time remaining for the operation to complete (in seconds). Calculated based on SOFAR and TOTALWORK values. The estimated time remaining will not be calculated, if these values are inconsistent, SOFAR=0, or LAST_UPDATE_TIME is the same as START_TIME. |
ELAPSED_SECONDS | Number of elapsed seconds from the operation start (row creation). |
CONTEXT | The number set by the user as the context. |
MESSAGE | Statistics summary message about the operation progress. |
USERNAME | Name of the user performing the operation. |
F.45.3. Example #
In our simple example, the application represents a lazy boy counting crows:
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$$;
If you run this code in psql and access V_SESSION and V_SESSION_LONGOPS in another session, you can get the following output:
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)