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 Librarian and runs the scanner application,
which, in its turn, registers itself as the
Book Scanner module and indicates that it is
currently attempting to perform Book Scanning to
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).
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_libraries variable in the
postgresql.conf file:
shared_preload_libraries = 'pgpro_application_info'
Reload the database server for the changes to take effect.
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;
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 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.
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_INFO in 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_MODULE and action information set by
SET_MODULE or SET_ACTION
in the current session.
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.27. 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.27. 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. |
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 the V_SESSION_LONGOPS view.
To start a new row, set this to the result of the
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT()
function. When you call SET_SESSION_LONGOPS
to 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 call
SET_SESSION_LONGOPS to 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 which
SOFAR and TOTALWORK
are 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.
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.28. 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.28. 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. |
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)