text: CREATE SCHEMA IF NOT EXISTS metric_helpers AUTHORIZATION postgres;
  
  GRANT USAGE ON SCHEMA metric_helpers TO admin;
  GRANT USAGE ON SCHEMA metric_helpers TO robot_zmon;
  
  SET search_path TO metric_helpers;
  
  -- table and btree bloat estimation queries are borrowed from https://github.com/ioguix/pgsql-bloat-estimation
  CREATE OR REPLACE FUNCTION get_table_bloat_approx (
      OUT t_database name,
      OUT t_schema_name name,
      OUT t_table_name name,
      OUT t_real_size numeric,
      OUT t_extra_size double precision,
      OUT t_extra_ratio double precision,
      OUT t_fill_factor integer,
      OUT t_bloat_size double precision,
      OUT t_bloat_ratio double precision,
      OUT t_is_na boolean
  ) RETURNS SETOF record AS
  $_$
  SELECT
    current_database(),
    schemaname,
    tblname,
    (bs*tblpages) AS real_size,
    ((tblpages-est_tblpages)*bs) AS extra_size,
    CASE WHEN tblpages - est_tblpages > 0
      THEN 100 * (tblpages - est_tblpages)/tblpages::float
      ELSE 0
    END AS extra_ratio,
    fillfactor,
    ((tblpages-est_tblpages_ff)*bs) AS bloat_size,
    CASE WHEN tblpages - est_tblpages_ff > 0
      THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
      ELSE 0
    END AS bloat_ratio,
    is_na
  FROM (
    SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
      ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
      tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
      -- , stattuple.pgstattuple(tblid) AS pst
    FROM (
      SELECT
        ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
          - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
          - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
        ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
        toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
      FROM (
        SELECT
          tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
          tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
          coalesce(toast.reltuples, 0) AS toasttuples,
          coalesce(substring(
            array_to_string(tbl.reloptions, ' ')
            FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
          current_setting('block_size')::numeric AS bs,
          CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
          24 AS page_hdr,
          23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
            + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
          sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,
          bool_or(att.atttypid = 'pg_catalog.name'::regtype)
            OR count(att.attname) <> count(s.attname) AS is_na
        FROM pg_attribute AS att
          JOIN pg_class AS tbl ON att.attrelid = tbl.oid
          JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
          LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
            AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
          LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
        WHERE att.attnum > 0 AND NOT att.attisdropped
          AND tbl.relkind = 'r'
        GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
        ORDER BY 2,3
      ) AS s
    ) AS s2
  ) AS s3 WHERE schemaname NOT LIKE 'information_schema';
  $_$ LANGUAGE sql SECURITY DEFINER IMMUTABLE STRICT SET search_path to 'pg_catalog';
  
  REVOKE ALL ON FUNCTION get_table_bloat_approx() FROM public;
  GRANT EXECUTE ON FUNCTION get_table_bloat_approx() TO admin;
  GRANT EXECUTE ON FUNCTION get_table_bloat_approx() TO robot_zmon;
  
  CREATE OR REPLACE VIEW table_bloat AS SELECT * FROM get_table_bloat_approx();
  
  REVOKE ALL ON TABLE table_bloat FROM public;
  GRANT SELECT ON TABLE table_bloat TO admin;
  GRANT SELECT ON TABLE table_bloat TO robot_zmon;
  
  CREATE OR REPLACE FUNCTION get_btree_bloat_approx (
      OUT i_database name,
      OUT i_schema_name name,
      OUT i_table_name name,
      OUT i_index_name name,
      OUT i_real_size numeric,
      OUT i_extra_size numeric,
      OUT i_extra_ratio double precision,
      OUT i_fill_factor integer,
      OUT i_bloat_size double precision,
      OUT i_bloat_ratio double precision,
      OUT i_is_na boolean
  ) RETURNS SETOF record AS
  $_$
  SELECT
    current_database(),
    nspname AS schemaname,
    tblname,
    idxname,
    bs*(relpages)::bigint AS real_size,
    bs*(relpages-est_pages)::bigint AS extra_size,
    100 * (relpages-est_pages)::float / relpages AS extra_ratio,
    fillfactor,
    CASE WHEN relpages > est_pages_ff
      THEN bs*(relpages-est_pages_ff)
      ELSE 0
    END AS bloat_size,
    100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio,
    is_na
  FROM (
    SELECT coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
      ) AS est_pages,
      coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
      ) AS est_pages_ff,
      bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na
      -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
    FROM (
      SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,
        ( index_tuple_hdr_bm +
            maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
              WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
              ELSE index_tuple_hdr_bm%maxalign
            END
          + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
              WHEN nulldatawidth = 0 THEN 0
              WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
              ELSE nulldatawidth::integer%maxalign
            END
        )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
        -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
      FROM (
        SELECT
          i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,
          current_setting('block_size')::numeric AS bs, fillfactor,
          CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
            WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
            ELSE 4
          END AS maxalign,
          /* per page header, fixed size: 20 for 7.X, 24 for others */
          24 AS pagehdr,
          /* per page btree opaque data */
          16 AS pageopqdata,
          /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
          CASE WHEN max(coalesce(s.null_frac,0)) = 0
            THEN 2 -- IndexTupleData size
            ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
          END AS index_tuple_hdr_bm,
          /* data len: we remove null values save space using it fractionnal part from stats */
          sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
          max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
        FROM pg_attribute AS a
          JOIN (
            SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,
              indrelid, indexrelid, indkey::smallint[] AS attnum,
              coalesce(substring(
                array_to_string(idx.reloptions, ' ')
                 from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
            FROM pg_index
              JOIN pg_class idx ON idx.oid=pg_index.indexrelid
              JOIN pg_class tbl ON tbl.oid=pg_index.indrelid
              JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
            WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
          ) AS i ON a.attrelid = i.indexrelid
          JOIN pg_stats AS s ON s.schemaname = i.nspname
            AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
            OR   (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
          JOIN pg_type AS t ON a.atttypid = t.oid
        WHERE a.attnum > 0
        GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
      ) AS s1
    ) AS s2
      JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
  ) AS sub;
  $_$ LANGUAGE sql SECURITY DEFINER IMMUTABLE STRICT SET search_path to 'pg_catalog';
  
  REVOKE ALL ON FUNCTION get_btree_bloat_approx() FROM public;
  GRANT EXECUTE ON FUNCTION get_btree_bloat_approx() TO admin;
  GRANT EXECUTE ON FUNCTION get_btree_bloat_approx() TO robot_zmon;
  
  CREATE OR REPLACE VIEW index_bloat AS SELECT * FROM get_btree_bloat_approx();
  
  REVOKE ALL ON TABLE index_bloat FROM public;
  GRANT SELECT ON TABLE index_bloat TO admin;
  GRANT SELECT ON TABLE index_bloat TO robot_zmon;
  
  CREATE OR REPLACE FUNCTION pg_stat_statements(showtext boolean) RETURNS SETOF public.pg_stat_statements AS
  $$
    SELECT * FROM public.pg_stat_statements(showtext);
  $$ LANGUAGE sql IMMUTABLE SECURITY DEFINER STRICT;
  
  REVOKE ALL ON FUNCTION pg_stat_statements(boolean) FROM public;
  GRANT EXECUTE ON FUNCTION pg_stat_statements(boolean) TO admin;
  GRANT EXECUTE ON FUNCTION pg_stat_statements(boolean) TO robot_zmon;
  
  CREATE OR REPLACE VIEW pg_stat_statements AS SELECT * FROM pg_stat_statements(true);
  
  REVOKE ALL ON TABLE pg_stat_statements FROM public;
  GRANT SELECT ON TABLE pg_stat_statements TO admin;
  GRANT SELECT ON TABLE pg_stat_statements TO robot_zmon;
  
  RESET search_path;
  