#!/usr/bin/perl
# -*- perl -*-

# Plugin to monitor PostgreSQL commits/rollbacks.  
# 
# "Why should I care?"
# - Too many commits can really bog down the database, as it checks all
# the tables for consitency after each change.  
# - Software is often set to 'AutoCommit = 1', meaning a commit is done
# after each transaction.  This is a good idea with brittle code so that
# you can get some work done if not all, but when you're inserting 10,000
# rows this can really suck.
# - If you see a spike in rollbacks, some db programmer is probably
# abusing their session, or a stored proceudre has gone horribly wrong
# and isn't leaving a trace.  Time for the rolled-up newspaper.

# Find out more at 
#  http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html
#  (where "8.2" can be the version of PostgreSQL you have installed)
#
# BjØrn Ruberg <bjorn@linpro.no> is large and in charge.
# Licenced under GPL v2.
#
# Revision History:
#  2005/03/10: v0.0001  Vajtsz (from openproject.hu)
#    "this is my first munin plugin sorry for dummy code"
#  2007/09/25: v1.0 Moses Moore <moc.iazom@sesom>
#    Nicolai Langfeldt - "I wounder if one of you would be bothered
#    to rework [this] over the pattern of postgres_block_read_ ..."
#    Omitted tracking numbackends because it is an instantaneous
#    measurement (GAUGE) instead of a cumulative (DERIVE) one.
#  2007/12/12: v1.01  Tim Retout <tim.retout@credativ.co.uk>
#    Use libpq environment variables for configuration.
#
# Usage:
#
#       Symlink into /etc/munin/plugins/ and add the monitored
#       database to the filename. e.g.:
#
#       ln -s /usr/share/munin/plugins/postgres_commits_ \
#         /etc/munin/plugins/postgres_commits_SomeDatabase
#       This should, however, be given through autoconf and suggest.
#
#       There is an additional means of using this:
#       if you make the special soft link postgres_commits_All, 
#       it will plot the commits & rollbacks for all databases 
#       on this server on one graph.  This isn't recommended, and
#       will not be offered by 'suggest', but it's here if you want it.
#
#       If required, give username, password and/or PostgreSQL server
#       host through environment variables... but for most sites you
#       should be able to omit username and password.
# 
#       You must also activate PostgreSQL statistics. See
#       http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html
#       for how to enable this. Specifically, the following lines must
#       exist in your postgresql.conf:
#
#           stats_start_collector = true
#           stats_block_level = true
#
#
# Parameters:
# 	
# 	config
# 	autoconf
#
# Configuration variables:
#
#	PGHOST		- Database server to use. Defaults to using ident
#			  authentication with the local server.
#	PGPORT		- Port to connect to. Defaults to '5432'.
#	PGDATABASE	- Database to connect to. Defaults to 'template1'.
#			  Note that this can be different from the database to
#			  monitor, which is defined via the symlink.
#	PGUSER		- User to connect as, if necessary.
#	PGPASSWORD	- Corresponding password to use, if necessary.
#
#	(See libpq documentation for more.)
#	Note that PGDATABASE will default to 'template1' in this plugin, and
#	without PGHOST it will try ident authentication with the local server,
#	as the user that the plugin is running as.
#
# Configuration example:
#
#	# Use local server, ident authentication with the 'postgres' user.
#	[postgres_*]
#	user postgres
#
#	# Use local server, TCP authentication with a username and password.
#	[postgres_*]
#	env.PGHOST localhost
#	env.PGUSER someuser
#	env.PGPASSWORD somepassword
#
# Magic markers
#%# family=auto
#%# capabilities=suggest

use strict;
use warnings;
use DBI;
use Data::Dumper;
use vars qw ( $debug $suggest $configure $dbh );
use vars qw ( @datasources );

# Default to template1 database.
$ENV{'PGDATABASE'} ||= 'template1';

my ($maybe_dbname) = ($0 =~ m/postgres_commits_(\S+)$/);
if ($maybe_dbname eq 'All' or $ARGV[0] eq 'suggest') {
  # doesn't always work.
  my @dsnnames = DBI->data_sources('Pg');
  foreach my $dsn (grep !/\=template\d$/, @dsnnames) {
    (my $db = $dsn) =~ s/^.*=//;
    $db =~ s/"//g; # "
    $db =~ s/ /\\ /g; # is whitespace allowed in database names?
    push(@datasources,$db);
  }
}

if (exists $ARGV[0]) {
    if ($ARGV[0] eq 'autoconf') {
        # Check for DBD::Pg
        if (! eval "require DBD::Pg;") {
            print "no (DBD::Pg not found)";
            exit 1;
        }
	# Try to detect PostgreSQL presence.
        my $tempdbh = DBI->connect ('dbi:Pg:', '', '');
        if ($tempdbh) {
            print "yes\n";
            exit 0;
        } else {
            print "no (Can't connect to given host, please check environment settings)\n";
            exit 1;
        }
    } elsif ($ARGV[0] eq 'debug') {
        # Set debug flag
        $debug = 1;
    } elsif ($ARGV[0] eq 'config') {
        # Set config flag
        $configure = 1;
    } elsif ($ARGV[0] eq 'suggest') {
        if (@datasources) {
          print join("\n",@datasources)."\n";
          exit 0;
        }
        else {
          exit 1;
        }
    }
}

die "No dbname configured (did you make the proper symlink?)" unless $maybe_dbname;
# Note that $dbname is the database being monitored, and can be different from
# $ENV{'PGDATABASE'}.
my $dbname = $maybe_dbname unless ($maybe_dbname eq 'All');
if ($maybe_dbname eq 'All' and not @datasources) {
  print "# Sorry, can't use the _All trick; no postgresql databases detected.\n";
  print "# Please make softlinks for each database name.\n";
  exit 1;
}

my $dbh = DBI->connect ('dbi:Pg:', '', '', {RaiseError =>1});
unless($dbh) {
    die("Error connecting to database. (". $DBI::errstr .")\n");
}

if ($configure) {
  print "graph_title Postgres commits/rollbacks on $maybe_dbname\n";
  print "graph_args --base 1000\n";
  print "graph_vlabel Sessions per \${graph_period}\n";
  print "graph_category PostgreSQL\n";
  print "graph_info Shows number of commits and rollbacks\n";
  if ($maybe_dbname eq 'All') {
    foreach my $ds (@datasources) {
      my ($c,$r) = ($ds."_commits",$ds."_rollbacks");
      print "$c.label $ds"."_c\n";
      print "$r.label $ds"."_r\n";
      print "$c.info Commits on database $ds\n";
      print "$r.info Rollbacks on database $ds\n";
      print "$c.min 0\n";
      print "$r.min 0\n";
      print "$c.type DERIVE\n";
      print "$r.type DERIVE\n";
    }
  }
  else {
    print "commits.label commits\n";
    print "commits.info SQL sessions terminated with a commit command.\n";
    print "commits.min 0\n";
    print "commits.type DERIVE\n";
    print "rollbacks.label rollbacks\n";
    print "rollbacks.info SQL sessions terminated with a rollback command.\n";
    print "rollbacks.min 0\n";
    print "rollbacks.type DERIVE\n";
  }
} elsif ($maybe_dbname eq 'All') {
    my $sql = "SELECT datname, xact_commit, xact_rollback from pg_stat_database";
    print "# $sql\n" if $debug;
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    if ($sth->rows > 0) {
      printf ("# Rows: %d\n",  $sth->rows) if $debug;
      while (my ($dn,$xc,$xr) = $sth->fetchrow_array()) {
        next if ($dn =~ /^template\d/); # system-only tables
        print $dn."_commits.value $xc\n";
        print $dn."_rollbacks.value $xr\n";
      }
    }
} else {
    my $sql = "SELECT xact_commit, xact_rollback from pg_stat_database where datname = ?";
;
    print "# $sql ($dbname)\n" if $debug;
    my $sth = $dbh->prepare($sql);
    $sth->execute($dbname);
    if ($sth->rows > 0) {
      printf ("# Rows: %d\n",  $sth->rows) if $debug;
      my ($xc,$xr) = $sth->fetchrow_array();
      print "commits.value $xc\n";
      print "rollbacks.value $xr\n";
    }
}