/**
 * Monitoring active database connections and their lock state
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_active_locks AS
SELECT DISTINCT l.pid
	, a.state
	, a.datname AS database_name
	, a.usename AS rolename
	, a.application_name
	, a.client_addr AS client_address
	, a.query_start
	, age (now(), a.query_start) AS query_age
	, a.wait_event_type
	, a.wait_event
	, l.locktype
	, l.mode
	, a.query
FROM pg_catalog.pg_stat_activity AS a
INNER JOIN pg_catalog.pg_locks AS l
	USING(pid)
;

COMMENT ON VIEW monitoring_active_locks IS 'Monitoring active database connections and their lock state';
COMMENT ON COLUMN monitoring_active_locks.pid IS 'The process id of the backend, needed in case a connection should be terminated with the function pg-terminate_backend(pid). For details see [SERVER SIGNALING FUNCTIONS](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL).';
COMMENT ON COLUMN monitoring_active_locks.state IS 'The current state of the connection.';
COMMENT ON COLUMN monitoring_active_locks.database_name IS 'The database name in which the queries have been executed.';
COMMENT ON COLUMN monitoring_active_locks.rolename IS 'The role/user who executed the queries.';
COMMENT ON COLUMN monitoring_active_locks.application_name IS 'The name of the application, could be empty if not set by a client.';
COMMENT ON COLUMN monitoring_active_locks.client_address IS 'The clients IP address.';
COMMENT ON COLUMN monitoring_active_locks.query_start IS 'The timestamp of the start of the query.';
COMMENT ON COLUMN monitoring_active_locks.query_age IS 'How long the query is already running.';
COMMENT ON COLUMN monitoring_active_locks.wait_event_type IS 'The type of event for which the backend is waiting, if any; otherwise NULL, for details see [WAIT EVENT TABLE](https://www.postgresql.org/docs/current/monitoring-monitoring_html#WAIT-EVENT-TABLE).';
COMMENT ON COLUMN monitoring_active_locks.wait_event IS 'Wait event name if backend is currently waiting, otherwise NULL. For details see [WAIT EVENT ACTIVITY TABLE](https://www.postgresql.org/docs/current/monitoring-monitoring_html#WAIT-EVENT-ACTIVITY-TABLE) and [WAIT EVENT TIMEOUT TABLE](https://www.postgresql.org/docs/current/monitoring-monitoring_html#WAIT-EVENT-TIMEOUT-TABLE)';
COMMENT ON COLUMN monitoring_active_locks.locktype IS 'The type of the lockable object: relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, advisory, or applytransaction, see [Wait Events of Type Lock](https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LOCK-TABLE).';
COMMENT ON COLUMN monitoring_active_locks.mode IS 'The name of the lock mode held or desired by this process. For details see [TABLE LEVEL LOCKS](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES) and [SERIALIZABLE ISOLATION LEVEL](https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE).';COMMENT ON COLUMN monitoring_active_locks.query IS 'This is the SQL source of the query.';