/**
 * Creates a view to get all connections and their locks.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */
 CREATE OR REPLACE VIEW pg_active_locks AS
 SELECT DISTINCT pid
 	, state
 	, datname AS database_name
 	, usename AS rolename
 	, application_name
 	, client_addr AS client_address
 	, query_start
	, age (now(), a.query_start) AS query_age
 	, wait_event_type
 	, wait_event
 	, locktype
 	, mode
 	, query
 FROM pg_stat_activity AS a
 INNER JOIN pg_locks AS l
 	USING(pid)
 ;

COMMENT ON VIEW pg_active_locks IS 'Creates a view to get all connections and their locks';
COMMENT ON COLUMN pg_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 pg_active_locks.state IS 'The current state of the connection.';
COMMENT ON COLUMN pg_active_locks.database_name IS 'The database name in which the queries have been executed.';
COMMENT ON COLUMN pg_active_locks.rolename IS 'The role/user who executed the queries.';
COMMENT ON COLUMN pg_active_locks.application_name IS 'The name of the application, could be empty if not set by a client.';
COMMENT ON COLUMN pg_active_locks.client_address IS 'The clients IP address.';
COMMENT ON COLUMN pg_active_locks.query_start IS 'The timestamp of the start of the query.';
COMMENT ON COLUMN pg_active_locks.query_age IS 'How long the query is already running.';
COMMENT ON COLUMN pg_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-pg_html#WAIT-EVENT-TABLE).';
COMMENT ON COLUMN pg_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-pg_html#WAIT-EVENT-ACTIVITY-TABLE) and [WAIT EVENT TIMEOUT TABLE](https://www.postgresql.org/docs/current/monitoring-pg_html#WAIT-EVENT-TIMEOUT-TABLE)';
COMMENT ON COLUMN pg_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 pg_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 pg_active_locks.query IS 'This is the SQL source of the query.';