#!/usr/bin/perl 
use strict;
use warnings FATAL => 'all', NONFATAL => 'redefine';

=pod

Demonstrate transaction status at various points in the query lifecycle.

Sets up two database handles, one to run some transactions and the other to select from the
pg_stat_activity table to get information on current query status. Easier to read if you create
a separate database to run this test, and connect the pg_stat_activity handle ($monitorDBH)
to any existing database.

Expected output:

 Before create table: <IDLE>
 After create table: <IDLE> in transaction
 After commit: <IDLE>
 Before prepare: <IDLE>
 After prepare: <IDLE>
 After execute: <IDLE> in transaction
 After rollback: <IDLE>

=cut

use DBI;

my $dbname = 'test';
my $dbh = DBI->connect('dbi:Pg:db=' . $dbname, undef, undef, { RaiseError => 1, AutoCommit => 0 }) or die DBI->errstr;
my $monitorDBH = DBI->connect('dbi:Pg:db=tom', undef, undef, { RaiseError => 1, AutoCommit => 1 }) or die DBI->errstr;

# Make a table so we have something 'transactional'
print "Before create table: " . getTransactionStatus() . "\n";
eval { $dbh->do(q{create table tst ( id int )}); } or warn "$@";
print "After create table: " . getTransactionStatus() . "\n";
$dbh->commit;
print "After commit: " . getTransactionStatus() . "\n";

print "Before prepare: " . getTransactionStatus() . "\n";
my $sth = $dbh->prepare(q{select * from tst});
print "After prepare: " . getTransactionStatus() . "\n";
$sth->execute;
print "After execute: " . getTransactionStatus() . "\n";
$dbh->rollback;
print "After rollback: " . getTransactionStatus() . "\n";

$dbh->do(q{drop table tst});
$dbh->commit;

=head2 C<getTransactionStatus>

Show current transaction status for the db in $dbname.

=cut

sub getTransactionStatus {
	my $rslt = $monitorDBH->selectall_arrayref(q{select current_query from pg_stat_activity where datname = ?}, undef, $dbname);
	return join(':', map { @$_ } @$rslt);
}