Message-ID: <4C250F2C.2070709@audioboundary.com>
Date: Fri, 25 Jun 2010 21:18:52 +0100
From: Tom Molesworth <tom@audioboundary.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org, mallah.rajesh@gmail.com
Subject: Re: Re: sudden spurt in swap utilization (was:cpu bound
	postgresql 	setup.)
References: <AANLkTinDBPQL-SjLaCr7AVgzouWbRrjwA6MAgBih_ciy@mail.gmail.com>
	<4C2492F9.2020706@gmail.com>
	<AANLkTikUDoXMF-7N7csuYPWIzn9gSKm_g4NnmjvAYu8N@mail.gmail.com>
	<4C2477A20200002500032B03@gw.wicourts.gov>
	<AANLkTimPhkr2nvuLo-fpBfUdN08KsDFTwzL9ubJP5VG2@mail.gmail.com>
	<AANLkTikJEyqi_8WRI6FjH3OBnnIj4SV9EbTBLl2EGCZw@mail.gmail.com>
	<4C24DBA9.7030303@audioboundary.com>
	<AANLkTils-kuUrmSMRmB1eplblsAf-hbbr6iU65Gtahj-@mail.gmail.com>
In-Reply-To: <AANLkTils-kuUrmSMRmB1eplblsAf-hbbr6iU65Gtahj-@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------090204070905000607010605"

This is a multi-part message in MIME format.
--------------090204070905000607010605
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 25/06/10 20:00, Rajesh Kumar Mallah wrote:
> Dear tom, we have autocommit off in dbi. Any commit or rollback from
> the persistent modperl process immediately issues begin work; if the
> modperl process is waiting for request the database backend remains in
> idle in transaction state. Unless we modify data in a http request we
> neighter issue a commit nor rollback.
>    

The backend shouldn't go to 'idle in transaction' state until there is 
some activity within the transaction. I've attached an example script to 
demonstrate this - note that even SELECT queries will leave the handle 
as 'IDLE in transaction' unless you've changed the transaction isolation 
level from the default.

Any queries that are idle in transaction will block connection pooling 
and cause old versions of table rows to hang around, as described in 
other replies. Note that this is nothing to do with mod_perl, it's 
purely due to the way transactions are handled - a one-off script would 
also have this issue, but on exit issues an implicit rollback and 
disconnects.

Typically your database wrapper would handle this (I think DBIx::Class 
should take care of this automatically, although I haven't used it myself).

Tom


--------------090204070905000607010605
Content-Type: application/x-perl;
 name="dbiPgConnectionHandling.pl"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="dbiPgConnectionHandling.pl"

#!/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);
}


--------------090204070905000607010605--