Skip site navigation (1) Skip section navigation (2)

Re: High CPU load on Postgres Server during Peak times!!!!

From: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: Dave Dutcher <dave(at)tridecap(dot)com>, "'Shiva Raman'" <raman(dot)shivag(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: High CPU load on Postgres Server during Peak times!!!!
Date: 2009-09-25 13:00:18
Message-ID: alpine.LFD.2.00.0909251456370.27334@bbs.intern (view raw or flat)
Thread:
Lists: pgsql-performance
Hello Craig,

Are you sure this is correct?

The test program (see below) with autocommit=0 counts up when an insert is 
done in 
another session and there is no commit done.

I think with each new select a new implicit transaction is done when no 
explicit "BEGIN" has been established.

Can one confirm this behavior?

Thnx.

Ciao,
Gerhard

# Disable autocommit!
my $dbh = DBI->connect($con, $dbuser, $dbpass, {RaiseError => 1, 
AutoCommit=>0}) || die "Unable to access Database '$dbname' on host 
'$dbhost' as user '$dbuser'. Error returned was: ". $DBI::errstr ."";

my $sth = $dbh->prepare('SELECT COUNT(*) FROM employee;');

for (;;)
{
   $sth->execute();
   my ($count) = $sth->fetchrow();
   print "count=$count\n";
   $sth->finish();
#  $dbh->commit;
   sleep(3);
}

$dbh->disconnect;

--
http://www.wiesinger.com/


On Thu, 24 Sep 2009, Craig James wrote:

> Dave Dutcher wrote:
>> You need a COMMIT for every BEGIN.  If you just run a SELECT statement
>> without first beginning a transaction, then you should not end up with a
>> connection that is Idle in Transaction.  If you are beginning a 
>> transaction,
>> doing a select, and then not committing, then yes that is a bug.
>
> The BEGIN can be hidden, though.  For example, if the application is written 
> in Perl,
>
> $dbh = DBI->connect($dsn, $user, $pass, {AutoCommit => 0});
>
> will automatically start a transaction the first time you do anything.  Under 
> the covers, the Perl DBI issues the BEGIN for you, and you have to do an 
> explicit
>
> $dbh->commit();
>
> to commit it.
>
> Craig
>
>
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

Responses

pgsql-performance by date

Next:From: Aidan Van DykDate: 2009-09-25 13:33:09
Subject: Re: PG 8.3 and large shared buffer settings
Previous:From: Dan SugalskiDate: 2009-09-25 10:05:38
Subject: Re: PG 8.3 and large shared buffer settings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group