MirthPostgresTableSizeByChannel_psql_12.psql

-- 20201125T1403 EST - Fix bug in xpath not correctly matching values to correct channel
-- 20201125T1430 EST - Updated formatting only using: http://poorsql.com/ with minor hand edits
-- 20201125T1430 EST - Updated formatting only using: http://poorsql.com/ with minor hand edits
-- 20210321T1140 EST - Postgres 12+ XPATH works differently and required entry/ where as 9.6 requires it to be missing
--
-- Jon Bartels
-- Similar query for SQL Server is here - https://stackoverflow.com/questions/58942371/get-space-used-by-mirth-per-channel-in-sql-server
--

SELECT NAME AS CHANNEL_NAME
	, TOTAL_BYTES
	, CHANNEL_ID
	, PRUNE_ENABLED
	, PRUNE_DAYS
	, ARCHIVE_ENABLED
	, TABLE_SCHEMA
	, TABLE_NAME
	, MIRTH_ID
	, MIRTH_TYPE
	, ROW_ESTIMATE
	, INDEX_BYTES
	, TOAST_BYTES
	, TABLE_BYTES
FROM (
	WITH RECURSIVE PG_INHERIT(INHRELID, INHPARENT) AS (
			SELECT INHRELID, INHPARENT
			FROM PG_INHERITS
			UNION
			SELECT CHILD.INHRELID, PARENT.INHPARENT
			FROM PG_INHERIT CHILD, PG_INHERITS PARENT
			WHERE CHILD.INHPARENT = PARENT.INHRELID
			)
		, PG_INHERIT_SHORT AS (
			SELECT *
			FROM PG_INHERIT
			WHERE INHPARENT NOT IN (SELECT INHRELID FROM PG_INHERIT)
			)
	SELECT TABLE_SCHEMA
		, TABLE_NAME
		, NULLIF(REGEXP_REPLACE(TABLE_NAME, '\D', '', 'g'), '')::NUMERIC AS MIRTH_ID
		, SUBSTRING(TABLE_NAME FROM 'd_(\D+)\d+') AS MIRTH_TYPE
		, ROW_ESTIMATE
		, PG_SIZE_PRETTY(TOTAL_BYTES) AS TOTAL_BYTES
		, PG_SIZE_PRETTY(INDEX_BYTES) AS INDEX_BYTES
		, PG_SIZE_PRETTY(TOAST_BYTES) AS TOAST_BYTES
		, PG_SIZE_PRETTY(TABLE_BYTES) AS TABLE_BYTES
	FROM (
		SELECT *, TOTAL_BYTES - INDEX_BYTES - COALESCE(TOAST_BYTES, 0) AS TABLE_BYTES
		FROM (
			SELECT C.OID
				, NSPNAME AS TABLE_SCHEMA
				, RELNAME AS TABLE_NAME
				, SUM(C.RELTUPLES) OVER (PARTITION BY PARENT) AS ROW_ESTIMATE
				, SUM(PG_TOTAL_RELATION_SIZE(C.OID)) OVER (PARTITION BY PARENT) AS TOTAL_BYTES
				, SUM(PG_INDEXES_SIZE(C.OID)) OVER (PARTITION BY PARENT) AS INDEX_BYTES
				, SUM(PG_TOTAL_RELATION_SIZE(RELTOASTRELID)) OVER (PARTITION BY PARENT) AS TOAST_BYTES
				, PARENT
			FROM (
				SELECT PG_CLASS.OID
					, RELTUPLES
					, RELNAME
					, RELNAMESPACE
					, PG_CLASS.RELTOASTRELID
					, COALESCE(INHPARENT, PG_CLASS.OID) PARENT
				FROM PG_CLASS
				LEFT JOIN PG_INHERIT_SHORT ON INHRELID = OID
				WHERE RELKIND IN ('r', 'p')
				) C
			LEFT JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
			ORDER BY TOTAL_BYTES DESC
			) A
		WHERE OID = PARENT
		) A
	) TABLE_SIZES
LEFT JOIN D_CHANNELS ON D_CHANNELS.LOCAL_CHANNEL_ID = TABLE_SIZES.MIRTH_ID
LEFT JOIN CHANNEL ON CHANNEL.ID = D_CHANNELS.CHANNEL_ID
LEFT JOIN (
	SELECT (XPATH('entry/string/text()', ENTRY)) [1]::TEXT AS CID
		, (XPATH('entry/com.mirth.connect.model.ChannelMetadata/enabled/text()', ENTRY)) [1]::TEXT::boolean AS PRUNE_ENABLED
		, (XPATH('entry/com.mirth.connect.model.ChannelMetadata/pruningSettings/pruneMetaDataDays/text()', ENTRY)) [1]::TEXT::INT AS PRUNE_DAYS
		, (XPATH('entry/com.mirth.connect.model.ChannelMetadata/pruningSettings/archiveEnabled/text()', ENTRY)) [1]::TEXT::boolean AS ARCHIVE_ENABLED
	FROM (
		SELECT UNNEST(XPATH('/map/entry', VALUE::XML)) AS ENTRY
		FROM CONFIGURATION
		WHERE CATEGORY = 'core' AND NAME = 'channelMetadata'
		) X
	) AS M ON M.CID = D_CHANNELS.CHANNEL_ID;

This version works on PG 12.

PRUNE_ENABLED should be renamed to Channel enabled

I'm also thinking about what to do with the "events" table row. The table name is there but the channel name and ID are blank. I'll submit a PR