/**
 * This view is monitoring active vacuum progress
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW monitoring_vacuum AS
SELECT spv.datname AS database_name
	, c.relnamespace::RegClass AS schema_name
	, spv.relid::RegClass AS table_name
	, spv.pid process_id
	,CASE phase
		WHEN 'scanning heap' THEN
				CASE
					WHEN spv.heap_blks_total > 0 THEN
						round(spv.heap_blks_scanned / spv.heap_blks_total, 1)
					ELSE
						0::float
					END
		WHEN 'vacuuming heap' THEN
			CASE WHEN spv.heap_blks_total > 0 THEN
				round(spv.heap_blks_vacuumed / spv.heap_blks_total, 1)
			ELSE
				0::float
			END
		ELSE
			NULL::float
	END AS progress
FROM pg_stat_progress_vacuum AS spv
INNER JOIN pg_class AS c
	ON spv.relid = c.oid
;

COMMENT ON VIEW monitoring_vacuum IS 'This view returns the current progress of active vacuum calls.';
COMMENT ON COLUMN monitoring_vacuum.database_name IS 'The name of the database.';
COMMENT ON COLUMN monitoring_vacuum.schema_name IS 'The name of the schema.';
COMMENT ON COLUMN monitoring_vacuum.table_name IS 'The name of the table.';
COMMENT ON COLUMN monitoring_vacuum.process_id IS 'The backend process id (pid).';
COMMENT ON COLUMN monitoring_vacuum.progress IS 'Current state of the vacuum process.';