BEGIN;

DROP FUNCTION IF EXISTS create_checkpoints();

CREATE FUNCTION create_checkpoints() RETURNS VOID AS
$create_checkpoints$
BEGIN

    DROP TABLE IF EXISTS checkpoints;
    
    CREATE UNLOGGED TABLE checkpoints AS
        SELECT log_time-checkpoint_time AS checkpoint_start,
               log_time AS checkpoint_end,
               buffers * COALESCE(setting::BIGINT, 8192) AS bytes_written
            FROM (
                SELECT log_time,
                       matches[1]::bigint AS buffers,
                       (matches[2]::numeric * '1 second'::INTERVAL) AS checkpoint_time
                    FROM (
                        SELECT log_time,
                               regexp_matches(message, $x$checkpoint complete: wrote (\d+).+total=([0-9.]+) s$x$) AS matches
                        FROM
                            postgres_log
                         ) as extract_log
                 ) checkpoints
            LEFT OUTER JOIN server.current_pg_settings ON name='block_size';

END;
$create_checkpoints$
    LANGUAGE 'plpgsql';

DROP FUNCTION IF EXISTS checkpoints_data(OUT start TIMESTAMP,
                                         OUT elapsed INTERVAL,
                                         OUT avg_checkpoint_interval INTERVAL,
                                         OUT checkpoints_req_pct BIGINT,
                                         OUT checkpoint_write_pct BIGINT,
                                         OUT backend_write_pct BIGINT,
                                         OUT avg_checkpoint_write TEXT,
                                         OUT written_per_sec TEXT,
                                         OUT alloc_per_sec TEXT);

CREATE FUNCTION checkpoints_data(OUT start TIMESTAMP,
                                         OUT elapsed INTERVAL,
                                         OUT avg_checkpoint_interval INTERVAL,
                                         OUT checkpoints_req_pct BIGINT,
                                         OUT checkpoint_write_pct BIGINT,
                                         OUT backend_write_pct BIGINT,
                                         OUT avg_checkpoint_write TEXT,
                                         OUT written_per_sec TEXT,
                                         OUT alloc_per_sec TEXT) RETURNS SETOF RECORD AS
$checkpoints_data$

BEGIN
  RETURN QUERY
  SELECT cast(date_trunc('minute',query_start) AS timestamp) AS start,
  date_trunc('second',time_elapsed) AS elapsed,
  date_trunc('second',time_elapsed / (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_interval,
  (100 * checkpoints_req) / (checkpoints_timed + checkpoints_req) AS checkpoints_req_pct,
  100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_write_pct,
  100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) AS backend_write_pct,
  pg_size_pretty(buffers_checkpoint * block_size / (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_write,
  pg_size_pretty(cast(block_size * (buffers_checkpoint + buffers_clean + buffers_backend) / extract(epoch FROM time_elapsed) AS int8)) AS written_per_sec,
  pg_size_pretty(cast(block_size * (buffers_alloc) / extract(epoch FROM time_elapsed) AS int8)) AS alloc_per_sec
  FROM (SELECT one.now AS query_start,
  two.now - one.now AS time_elapsed,
  two.checkpoints_timed - one.checkpoints_timed AS checkpoints_timed,
  two.checkpoints_req - one.checkpoints_req AS checkpoints_req,
  two.buffers_checkpoint - one.buffers_checkpoint AS buffers_checkpoint,
  two.buffers_clean - one.buffers_clean AS buffers_clean,
  two.maxwritten_clean - one.maxwritten_clean AS maxwritten_clean,
  two.buffers_backend - one.buffers_backend AS buffers_backend,
  two.buffers_alloc - one.buffers_alloc AS buffers_alloc,
  (SELECT cast(current_setting AS integer) from server.pg_stat_bgwriter_end ) AS block_size
  FROM server.pg_stat_bgwriter_start one
  INNER JOIN server.pg_stat_bgwriter_end two
  on two.now > one.now)
  bgwriter_diff WHERE (checkpoints_timed + checkpoints_req) > 0;

END;
$checkpoints_data$

  LANGUAGE 'plpgsql';

COMMIT;