Function get_table_bloat_approx_sql fails with division by zero

[FireEmerald]

• Function: get_table_bloat_approx_sql
• Code: pgwatch2/pgwatch2/metrics/00_helpers/get_table_bloat_approx_sql/12/metric.sql
• Error: ERROR: division by zero

Problem:

The divisions in these case statements fail when the tblpages from the subquery is 0 for some rows of a section of a partitioned table.

pgwatch2/pgwatch2/metrics/00_helpers/get_table_bloat_approx_sql/12/metric.sql

Lines 30 to 45 in 0b3c7e6

	CASE
	WHEN tblpages - est_tblpages > 0
	THEN 100 * (tblpages - est_tblpages) / tblpages::float
	ELSE 0
	END AS extra_ratio,
	fillfactor,
	CASE
	WHEN tblpages - est_tblpages_ff > 0
	THEN (tblpages - est_tblpages_ff) * bs
	ELSE 0
	END AS bloat_size,
	CASE
	WHEN tblpages - est_tblpages_ff > 0
	THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float
	ELSE 0
	END AS bloat_ratio,

Sample row of subquery:

est_tblpages | est_tblpages_ff | tblpages | fillfactor | bs   | tblid    | schemaname | tblname               | heappages | toastpages | is_na
-3             -3                0          100          8192   17624404   public       partitioned_table_001   0           0            t

Table partitioned_table_001 is a partition of partitioned_table.

Possible fix?

--   v-----new------v
WHEN tblpages > 0 AND tblpages - est_tblpages > 0
    THEN 100 * (tblpages - est_tblpages) / tblpages::float
ELSE 0
END                        AS extra_ratio,

Should be added to all three CASES.

[pashagolub]

Thanks for your support. We will fix this ASAP.