[check_postgres] [Check_postgres-commit] [commit 1/3] Fix the check_bloat SQL to take inherited tables into account, and assume 2k for non-analyzed columns.

[Guillaume Lelarge]

On Mon, 2013-02-11 at 16:24 -0500, check_postgres at bucardo.org wrote:
> Committed by gp <geert.pante at ebit.be>
> 
> Subject: [commit 1/3]   Fix the check_bloat SQL to take inherited tables into account, and assume 2k for non-analyzed columns.
> 

This commit works from 9.0 till 9.2, but doesn't work with 8.4 and
previous releases. Perhaps adding a test to check the release and use
either the old or the new querie depending on the release?

> ---
>  check_postgres.pl |   28 ++++++++++++++++++++--------
>  1 files changed, 20 insertions(+), 8 deletions(-)
> 
> diff --git a/check_postgres.pl b/check_postgres.pl
> index 95e8247..6852d48 100755
> --- a/check_postgres.pl
> +++ b/check_postgres.pl
> @@ -32,7 +32,7 @@ $Data::Dumper::Useqq = 1;
>  
>  binmode STDOUT, ':utf8';
>  
> -our $VERSION = '2.20.0';
> +our $VERSION = '2.20.1';
>  
>  use vars qw/ %opt $PGBINDIR $PSQL $res $COM $SQL $db /;
>  
> @@ -3552,20 +3552,27 @@ FROM (
>    LEFT JOIN
>    (
>      SELECT
> -      ma,bs,schemaname,tablename,
> +      ma,bs,foo.nspname,foo.relname,
>        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
>        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
>      FROM (
>        SELECT
> -        schemaname, tablename, hdr, ma, bs,
> -        SUM((1-null_frac)*avg_width) AS datawidth,
> -        MAX(null_frac) AS maxfracsum,
> +        ns.nspname, tbl.relname, hdr, ma, bs,
> +        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
> +        MAX(coalesce(null_frac,0)) AS maxfracsum,
>          hdr+(
>            SELECT 1+count(*)/8
>            FROM pg_stats s2
> -          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
> +          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
>          ) AS nullhdr
> -      FROM pg_stats s, (
> +      FROM pg_attribute att 
> +      JOIN pg_class tbl ON att.attrelid = tbl.oid
> +      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace 
> +      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
> +      AND s.tablename = tbl.relname
> +      AND s.inherited=false
> +      AND s.attname=att.attname,
> +      (
>          SELECT
>            BLOCK_SIZE,
>              CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#"[0-9]+.[0-9]+#"%' for '#')
> @@ -3573,10 +3580,11 @@ FROM (
>            CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
>          FROM (SELECT version() AS v) AS foo
>        ) AS constants
> +      WHERE att.attnum > 0 AND tbl.relkind='r'
>        GROUP BY 1,2,3,4,5
>      ) AS foo
>    ) AS rs
> -  ON cc.relname = rs.tablename AND nn.nspname = rs.schemaname
> +  ON cc.relname = rs.relname AND nn.nspname = rs.nspname
>    LEFT JOIN pg_index i ON indrelid = cc.oid
>    LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
>  ) AS sml
> @@ -9737,6 +9745,10 @@ Items not specifically attributed are by GSM (Greg Sabino Mullane).
>  
>  =over 4
>  
> +=item B<Version 2.20.1> January 9, 2013
> +  Fix the check_bloat SQL to take inherited tables into account, and assume 2k for non-analyzed columns.
> +    (Geert Pante)
> +
>  =item B<Version 2.20.0>
>  
>    Add check for pgagent jobs (David E. Wheeler)

-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com