/**
 * This view returns numbers of conflicts happend on standby servers, AKA followers.
 * Therefore the qeury has to run against the followers.
 * The conflicts are shown per database.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

 DO $$
 DECLARE
  version_greater_15 BOOLEAN;
 BEGIN
  SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 16 INTO version_greater_15;

  IF version_greater_15 THEN
		CREATE OR REPLACE VIEW monitoring_database_conflicts AS
		SELECT c.datid AS database_id
			, c.datname AS database_name
			, c.confl_tablespace AS conflicts_tablespace
			, c.confl_lock AS conflicts_locks
			, c.confl_snapshot AS conflichts_snapshot
			, c.confl_bufferpin AS conflichts_pinned_buffers
			, c.confl_deadlock AS conflicts_deadlocks
			, c.confl_active_logicalslot AS conflicts_logical_replication_slots
		FROM pg_catalog.pg_stat_database_conflicts AS c
		;

		COMMENT ON VIEW monitoring_database_conflicts IS 'This view returns numbers of conflicts happend on standby servers, AKA followers. Therefore the qeury has to run against the followers. The conflicts are shown per database.';
		COMMENT ON COLUMN monitoring_database_conflicts.database_id IS 'The OID of this database.';
		COMMENT ON COLUMN monitoring_database_conflicts.database_name IS 'The name of this database.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_tablespace IS 'The number of queries in this database that have been canceled due to dropped tablespaces.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_locks IS 'The number of queries in this database that have been canceled due to lock timeouts.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflichts_snapshot IS 'The number of queries in this database that have been canceled due to old snapshots.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflichts_pinned_buffers IS 'The number of queries in this database that have been canceled due to pinned buffers.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_deadlocks IS 'The number of queries in this database that have been canceled due to deadlocks.';
		COMMENT ON COLUMN monitoring_database_conflicts.conflicts_logical_replication_slots IS 'The number of uses of logical slots in this database that have been canceled due to old snapshots or too low a wal_level on the primary server.';
	ELSE
	CREATE OR REPLACE VIEW monitoring_database_conflicts AS
	SELECT c.datid AS database_id
		, c.datname AS database_name
		, c.confl_tablespace AS conflicts_tablespace
		, c.confl_lock AS conflicts_locks
		, c.confl_snapshot AS conflichts_snapshot
		, c.confl_bufferpin AS conflichts_pinned_buffers
		, c.confl_deadlock AS conflicts_deadlocks
	FROM pg_catalog.pg_stat_database_conflicts AS c
	;

	COMMENT ON VIEW monitoring_database_conflicts IS 'This view returns numbers of conflicts happend on standby servers, AKA followers. Therefore the qeury has to run against the followers. The conflicts are shown per database.';
	COMMENT ON COLUMN monitoring_database_conflicts.database_id IS 'The OID of this database.';
	COMMENT ON COLUMN monitoring_database_conflicts.database_name IS 'The name of this database.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflicts_tablespace IS 'The number of queries in this database that have been canceled due to dropped tablespaces.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflicts_locks IS 'The number of queries in this database that have been canceled due to lock timeouts.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflichts_snapshot IS 'The number of queries in this database that have been canceled due to old snapshots.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflichts_pinned_buffers IS 'The number of queries in this database that have been canceled due to pinned buffers.';
	COMMENT ON COLUMN monitoring_database_conflicts.conflicts_deadlocks IS 'The number of queries in this database that have been canceled due to deadlocks.';
	END IF;
END $$;